MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping

1. MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping an ER model to tables © Akhilesh Bajaj, 2000, 2002, 2003, 2006. All Rights Reserved.

2. Review of the ER Model So Far • Entities and entity sets • Relationships and relationship sets • Attributes of entity sets and relationship sets • Degree of relationship sets • Cardinality of relationship sets • Keys of entity sets • Existence dependencies (weak entity sets) • Subclass / Superclass hierarchy amongst entity sets

3. End-user descriptions One ER diagram Set of tables Goals Today • Learn how to map an ER diagram to a set of tables • In-class assignment on mapping ER diagrams to tables. • Goal of ER module:

4. What is a Table • A table has a name • A table has columns • - the columns are also called attributes. • The data in the table is stored as rows. • A table has a primary key (determines each row uniquely). • A database consists of many such tables, and is • called a relational schema.

5. Mapping Strong Entity Sets • Create a separate table for the strong entity set. • The name of the table is the name of the entity set. • The columns of the table are the attributes of the • strong entity set. • The primary key of the table is the primary key of the entity set.

6. Mapping Weak Entity Sets • Create a separate table for the weak entity set. • The name of the table is the name of the entity set. • The columns of the table are the attributes of the • weak entity set + the primary key of the corresponding strong • entity set. • The primary key of the table is the primary key of the • corresponding strong entity set + the unique identifier of the • weak entity set.

7. Mapping Superclass / Subclass Hierarchies • Create a separate table for the superclass first, using the • rules for mapping entity sets we have seen earlier. • For each subclass entity set, create a separate table. The • columns of each table = the additional attributes of the • corresponding subclass entity set + the primary key of the • superclass entity set. • The primary key of the subclass table is the primary key • taken from the superclass table. • Recall that the hierarchy triangle is NOT a relationship set • and is not mapped into a table.

8. Mapping Multivalued Attributes • Create a separate table for the multivalued attribute. • The name of the table is the name of the attribute. • The columns of the table are the primary key of the • entity set to which the attribute belongs + a separate column for • values of the attribute. • The primary key of the table is all the columns of the table. • If there are separate multivalued attributes, then create a • separate table for EACH attribute.

9. Mapping Composite Attributes • Only attributes that are the leaves of a composite hierarchy • are mapped as columns of a table. • If the composite attribute is single-valued, treat the leaf attributes • as ordinary attributes, when mapping them. • If the composite attribute is multivalued, treat the leaf attributes • as multivalued-attributes, when mapping them, except create only • ONE new table with all the leaf attributes in it. The Primary Key • of this table will be all the attributes of the table. • Note: There can never be a case where only some leaf attributes • are multivalued and others are not. Either the entire composite • Attribute (such as address) is multivalued, or it is not.

10. Mapping Relationship Sets • 1. Mapping Existence Relationship sets (Weak entity set • relationship set with its strong entity set: DOUBLE Diamond) : • Existence relationship sets (between weak and corresponding • strong entity sets) are NOT mapped into tables.These are • the ones that are represented by double diamonds. • We should note that a weak entity set can have other, normal • relationship sets with other entity sets also. These are treated as • normal (single diamond) relationship sets, when mapping. • Let’s see how to map normal relationship sets to tables next.

11. Mapping Relationship Sets • 2. MAPPING A RELATIONSHIP SET (SINGLE DIAMOND) • 2 a) Binary relationshipsthat are 0/1:1 ..…◊ ..... Any cardinality: • Add a column(s) in the table that corresponds to the entity • set that is on the “Any Cardinality” side. • Example: 0/1:1 here Side “Any Cardinality here” Side 0:1 0:n R A B • The column(s) we add to B here is the primary key of the entity set • that is on the 0/1:1 side (A in this case) and any other attributes of the • relationship set. So, we borrow the primary key of A into B, but it • does NOT become part of the primary key of B. It’s still a foreign • key referring A though. We can also preface the columns added to B with the label • of R, to show that the columns are borrowed because of R. • [Nota Bene: If the cardinality is 0:1, null values have to be allowed, • if it is 1:1, then null values should not be allowed, • for the primary key of A when put in B.]

12. Mapping Relationship Sets • 2 b). Binary relationship sets with cardinality NOT as in 2a) • AND all ternary & higher degree relationship sets: • Create a separate table with the same name as the name of • the relationship set • The columns of the table are the attributes of the relationship • set (if any) + primary keys of all the entity sets that participate • in the relationship set. • The primary key of the table is = the primary keys of all the • entity sets that participate in the relationship set. • If any of the entity sets in the relationship set are weak, recall that the • primary key of a weak entity set is the unique identifier + the primary key • of the corresponding strong entity set. Note: Rules 1 & 2 are mutually exclusive: Rule 1 is for double diamonds, rule 2 is for single diamonds

13. Sequence Of Steps • Given an ER diagram, what sequence of steps should we follow? • Map the strong entity sets • Map the weak entity sets • Map the subclass entity sets • Map multivalued attributes, if any • Map Composite attributes, if any • Map relationship sets

14. Example 1 The examples are deliberately abstract, to emphasize the steps, independent of any particular situation. Attrib B1 Attrib A2 Attrib R11 Attrib B2 Attrib A1 0:1 1:m A B R1 0:1 Attrib R21 1:n R2 Attrib C1 C Attrib C2 1:n Attrib D1 D Attrib C3 Attrib D2

15. Attrib A2 Attrib A1 1:1 A 0:m Example 2 The examples are deliberately abstract, to emphasize the steps, independent of any particular situation. Attrib B1 Attrib B2 0:m B Attrib R11 R1 1:n Attrib D1 D Attrib D2

16. In Class Assignment 1 • Al’s motor shop (AMS) is an automobile repair facility owned • by the Capone family. AMS has 5 repair bays. Each repair bay • (place where car is repaired) has a bay_id and bay_location. • AMS employs 14 employees. Each employee has an • employee_id, address, phone and salary. Of these 14, 2 are • office staff. They are further described by typing_speed and • degree_held. The 12 mechanics are further described by • tech_level. Each mechanic is assigned to work on one bay. • AMS customers have a cust_id, name, address, phone. In • addition, Mr. Capone also wants to capture information • for each customer that lists the mechanic who last did a job • for the customer, the date on which the job was done, and • the amount the customer paid to AMS. • The above requirements have been captured in an ER diagram. • Please map the ER diagram to tables.

17. In Class Assignment address Salary phone address Bay_id phone name Cust_id Bay_loc Typing_speed Degree_held Emp_id Tech_level Repair_bays Employees date payment IS A User-defined, disjoint, total 0:n 1:1 Mechanics Office_staff Works 0:1 Last_job 0:n Customers

18. Tables Mapped from the ER Diagram Employees (emp_id, phone, address, salary) Mechanics (emp_id, tech_level, bay_id) emp_id FK REF employees, bay_id FK REF bays Office_staff (emp_id, typing_speed, degree_held) emp_id FK REF employees Repair_bays (bay_id, bay_loc) Customers (cust_id, name, address, phone, last_job_emp_id, last_job_date, last_job_payment)l last_job_emp_id FK refs mechanics