ERD Relationships

ERD Relationships

1. Represents a business association between two entities

   • Based on business rules
   • Relationships are bi-directional in that they can be interpeted both to and from
   • Types of relationships
     • One to One relationship
       • An entity is related to a most one instance of relation with another entity and no more than one
       • Example Business rule: Suppose a company policy states that every department has a manager and that a manager can manage one and only one department within the company. This would then consist of a one to one relationship of manager to department and vise versa
     • One to Many relationship
       • An entity can be related to another by atleast one or more than one relations.
       • Example Business Rule: Suppose the company does sales. In this case a customer can have one order related to them or more than one . However the reverse case is not true as one order can belong only to a single customer and not many customers
     • Many-To-Many relationship
       • Relational databases do not support a many to many relationship between two entities and must be reduced to a one to many relationships
       • Requires for an associative entity to be created which contains the primary key attributes of two entities that share the many-to-many relationship and any other attributes that describe the association.
         • ***The primary key of the asssociative entity is made up of two or more attributes. IN this case it would be the primary key of the two original entities that would become the composite primary key

2. Types of Relationships depends on the cardinality that exists between entities. Three major types consist of : One-to-One , One-to-Many, Many-to-Many

   • One-to-One:
     • For example if a given an application that records parking stall assignments for employees, the business rule states: Each employee is assigned one, and only one parking stall. Therefore, one instance of the employee entity can be related to one instance of the parking stall entity and one instance of the parking stall entity can be related to one instance of the employee entity.
       • Another example could be student registration. A student has only ONE student ID and a Student ID can only belong to one student
   • One-to-Many
     • For example in the customer order example the business rule states that One customer can place many Orders but one and only one customer places an order. That means customer can place lots of orders but One order is placed by one
       • For example in a Library business environment, One person can take out Many books but only one customer can take out the book. In this case 1 customer can take out many books but a book can only be taken out by a single individual at a time
   • Many-to-Many
     • Using Customer Order as an example the business rule states that Many items can be purchased on one specific order AND one specific item can appear on many different orders
       • For example Bank products. One customer can have 0 products (CC's) , 1 Product or Many Products from the bank. Likewise a Bank product (CC) can be owned by 0 Customers, 1 Customer, or Many Customers. This shows a Many to Many relationship
   • Cardinality defines the number of instances of an entity that can be related to a SINGLE instance of another
     • In the relational model, tables can be related as any of "one-to-many" or "many-to-many". This is said to be the cardinality of a given table in relation to another. For example, consider a database designed to keep track of hospital records. Such a database could have many tables like: a doctor table with information about physicians; a patient table for medical subjects undergoing treatment; and a department table with an entry for each division of a hospital. In that model: There is a many-to-many relationship between the records in the doctor table and records in the patient table because doctors have many patients, and a patient could have several doctors; There is a one-to-many relationship between the department table and the doctor table because each doctor may work for only one department, but one department could have many doctors
     • More Examples
       • One to:
         • To One
         • To Zero or One
         • To One, or Many
         • To Zero or One or Many
         • To Many

3. Defining Relationships

   • Foreign key
     • Defines a relationship between entities.
     • Is a Primary key of another table that is an attribute of another table
     • Referred to as the child entity in the relationship
   • Primary Key
     • Referred to as the parent entity if it has a foreign key associated with it
   • A primary key and a foreign key must be the same data but the two attributes do not need to have the same name

4. A box represents a base entity.

5. a rounded box is referred to as an associative entity.

   • ***a dashed line represents a non-identifying relatinoship--> foreign key in the CHILD ENTITY does not act as part of the primary key for that entity [BLUE]
   • a solid line represents an identifying relationship. This means that the foreign key does indeed act as part of the primary key in the child entity [RED]

Bilder und Dateien der Mindmap