ER Diagram

Week 15 Entity Relationship Diagram Database System COSC-2103 MS. HIRA KANWAL HIRA.KANWAL@KFUEIT.EDU.PK A ternary relationship with attributes A unary relationship with an attribute. This has a many-to-many relationship Representing a bill-of -materials structure Examples of multiple relationships – entities can be related to one another in more than one way Employees and departments What is a Good Data Model? A good data model is simple. ◦ Data attributes that describe any given entity should describe only that entity. ◦ Each attribute of an entity instance can have only one value. A good data model is essentially nonredundant. ◦ Each data attribute, other than foreign keys, describes at most one entity. A good data model should be flexible and adaptable to future needs. Key constraints: Examples Example Scenario 1: An inventory database contains information about parts and manufacturers. Each part is constructed by exactly one manufacturer. Example Scenario 2: A customer database contains information about customers and sales persons. Each customer has exactly one primary sales person. What do the ER diagrams look like? Entity Relationship Diagram Methodology An ERD design methodology 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Identify attributes Map attributes Draw fully attributes Check results Example A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number. An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Identify roles, events, locations, tangible things or concepts about which the end-users want to store data Example - entities A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number. An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Find natural associations between pairs of entities using a relationship matrix Relationship Matrix Department Employee Supervisor Project Department Employee Supervisor Project Relationship Matrix Department Employee Supervisor Project Department is assigned run by Employee belongs to works on Supervisor runs Project uses An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Put entities in rectangles and relationships on line segments connecting the entities ERD Components Entity1 Entity2 Relationship An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Put entities in rectangles and relationships on line segments connecting the entities Department Supervisor run by Project Employee works on is assigned An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Determine the number of occurences of one entity for a single occurrence of the related entity Cardinality For each entity X and every entity Y it has a relationship with ◦ Each X has NUMBER Y For example, ◦ Each department has exactly one supervisor Symbol Meaning One and only one One or more Zero or more Zero or one Sample E-R Diagram Summary of multiplicity constraints Basic E-R Notation Entity symbols Relationship symbols A special entity that is also a relationship Attribute symbols An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Determine the number of occurences of one entity for a single occurrence of the related entity Cardinality "list" Each department has exactly one supervisor. A supervisor is in charge of one and only one department. Each department is assigned at least one employee. Each employee works for at least one department. Each project has at least one employee working on it. An employee is assigned to 0 or more projects. Department is assigned Employee Supervisor run by Project works on An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Identify the data attributes that uniquely identify one and only one occurrence of the related entity Example - primary keys A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number. Attributes Entity1 Entity2 Relationship Attribute Primary Key Department is assigned Employee Supervisor run by Department Name Employee Number Supervisor Number Project Number Project works on An ERD design methodology 1. 2. 3. 4. 5. 6. Identify entities Find relationships Draw rough ERD Fill in cardinality Define primary keys Draw key-based ERD Eliminate many-to-many relationships and include primary and foreign keys Many-to-many = bad Cannot be represented in the relational model and pose some other problems Solution ◦ Replace it with an association entity ◦ Each entity forms a relation with it Verbal Example Many to many ◦ A department has one or more employees ◦ An employee belongs to one or more departments Associative entity ◦ A department has one or more Employee-Department ◦ An employee has one or more Employee-Department Department is assigned Employee Department Employee is assigned Employee Department involves Strong vs. Weak Entities, and Identifying Relationships Strong entities ◦ exist independently of other types of entities ◦ has its own unique identifier ◦ represented with single-line rectangle Weak entity ◦ dependent on a strong entity…cannot exist on its own ◦ Does not have a unique identifier ◦ represented with double-line rectangle Identifying relationship ◦ links strong entities to weak entities ◦ represented with double line diamond Figure : Strong and weak entities Strong entity Identifying relationship Weak entity Associative Entities It’s an entity– it has attributes AND it’s a relationship–it links entities together When should a relationship with attributes instead be an associative entity? ◦ All relationships for the associative entity should be many ◦ The associative entity could have meaning independent of the other entities ◦ The associative entity preferably has a unique identifier, and should also have other attributes ◦ The associative may be participating in other relationships other than the entities of the associated relationship ◦ Ternary relationships should be converted to associative entities Figure : An associative entity (CERTIFICATE) Associative entity involves a rectangle with a diamond inside. Note that the many-to-many cardinality symbols face toward the associative entity and not toward the other entities Repeating groups, normalisation Normalization ◦ Eliminate redundancy ◦ Organise data efficiently ◦ Reduce potential for anomalies Normal form - "how normalised" a relational database is There are 6 normal forms Text limited to 3 Conclusions Just a small start An important process Get lots of practice Ask lots of questions 7. Identify Attributes Objective: Name the information details (fields), which are essential to the system under development The only attributes indicated are the names of A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors, and employees, as well as, the supervisor numbers, employee numbers and project numbers. 8. Map Attributes Objective: For each attribute, match it with exactly one entity that it describes Attribute Entity Department Name Employee Number Department Employee Name Employee Supervisor Number Employee Supervisor Name Supervisor Project Name Supervisor Project Project Number Project 9. Draw Fully Attributed ERD Objective: Adjust the ERD from Step 6 to account for entities or relationships discovered in Step 8. 9. Draw Fully Attributed ERD Data Dictionary Sample An example for a Banking System Middle Name First Name PAN Last Name Name Customer No Gender Customer Address Telephone Transaction No Transaction Date Account No Account Type Account TType TP Account Amount Customer No Account No Balance