Translating ER Model into Relational Model

1. Translating ER Model into Relational Model

2. ER Model  Relational Model • Considerations: • Minimize the number of relations to reduce query-processing time. • Do not allow null values if possible • Provide a semantically clear design • Provide a design that accommodates potential changes of the schema • Reality: decreasing the number of relations • Pros: higher efficiency of query processing • Cons: more nulls; less semantic clarity; less flexibility

3. ER Model  Relational Model (cont.) • Step 0: Identify: • 0.1. Entity types (strong & weak, superclass/subclass) • 0.2. Relationship types • 0.3. Special attributes (composite, multi-valued, derived) • Step 1: Strong entity types and single-valued attributes • Step 2: Many-to-many relationships • Step 3: One-to-many relationships • Step 4: One-to-one relationships • Step 5: n-ary relationships (n  3) • Step 6: Weak entity types • Step 7: Multi-valued attributes • Step 8: Superclasses and subclasses • Note: Steps 1-8 may not be followed in the above sequence. For example, you may do step 6 before step 2, and so on Applicable to both binary and unary (recursive) relationships

4. An ER Model

5. Step 0 0.1.1. Strong entity types: Department, Course, Instructor, TeachingAssistant 0.1.2. Weak entity types: TeachingPreference 0.1.3. Superclasses/Subclasses: Instructor/(DeptHead, ParttimeInstructor) 0.2.1. Relationship types related to strong entity types: • Many-to-many: None • One-to-many: Department:Course, Department:Instructor, Instructor(Superviser):Instructor(Supervisee) • One-to-one: Department:DeptHead • n-ary: Course:Instructor:TeachingAssistant 0.2.2. Relationship types related to weak entity types: • One-to-many: Instructor:TeachingPreference

6. Step 0 (cont.) 0.3.1. Composite attributes: address(street, city, state, zipcode) 0.3.2. Multi-valued attributes: phoneNo 0.3.3. Derived attributes: unitsTaught

7. Step 1: Strong Entity Types • Each strong entity type maps into a relation(table) • Each simple attribute of the strong entity type maps to an attribute of the relation • Each composite attribute will be broken into multiple simple attributes (address = street, city, state, zip) • We’ll deal with Multi-valued attributes later (PhoneNo) • Derived attributes, by definition, can be derived, and therefore are not necessary to be represented • The primary key of the entity type maps to the primary key of the relation • Primary keys are underlined

8. Step 1: Example • At this step, we got: Department (deptCode, deptName, street, city, state, zipcode) Course (courseNo, sectionNo, title, courseType, units) Instructor (instructorID, fName, lName, gender, ssn, position) TeachingAssistant (teachingAssistantID, ssn, studentID, salary) • How about phoneNo of Department and unitsTaught of Instructor? • We will deal with multi-valued attributes (e.g., phoneNo) later • We don’t need to add derived attributes (e.g., unitsTaught) in relations.

9. Step 2: Many-to-Many Relationships • Each Many-to-Many relationship type maps into a relation (i.e., a relationship relation) • The primary key of this relation is the combination of the primary keys of the participating entity types • These are also foreign keys • Attributes of the relationship type maps to attributes of the relation, similar to those of strong entity types

10. Step 2: Example Employee AssignedTo Project 1..3 0..* empID {PK} name salary … projectID{PK} startDate endDate … position Employee (empID, name, salary) Project (projectID, startDate, endDate) Assignment (empID, projectID, position)

11. Step 3: One-to-Many Relationships • For each One-to-Many relationship type related to strong entity types: • Can be treated as a Many-to-Many relationship • Add a relationship relation • Do not need to introduce a separate relation • Add a foreign key (FK) (and relationship attributes) • to the relation on the “many” side • that references the PK on the “one” side • Can we add the FK on the “one” side? Why or why not?

12. Step 3: Example • If we add an FK to the “many” side, we get: • If we add an FK to the “one” side, we get:

13. Step 3: Example • At this step, we got: Department:Course Course (courseNo, sectionNo, title, courseType, units, deptCode) Foreign key deptCode references Department (deptCode); Department:Instructor Instructor (instructorID, fName, lName, gender, ssn, position, deptCode) Foreign key deptCode references Department (deptCode); Offers Department Course 1..1 1..* Has Department Instructor 1..1 1..*

14. Step 3: Example (cont.) • Instructor(Supervisor):Instructor(Supervisee) • Add a foreign key to the “Many” side: Instructor (Supervisee) Instructor (instructorID, fName, lName, SSN, position, deptCode, supervisorID) Foreign key supervisorID references Instructor (instructorID) • In case of recursive relationship, we need to rename the foreign key. • Due to the partial participation of Instructor(Supervisee), supervisorID can be null. • If you want to avoid null values, we need to create a new relation: Supervision (instructorID, supervisorID) Foreign key instructorID references Instructor (instructorID) Foreign key supervisorID references Instructor (instructorID) • Avoid nulls for foreign keys, but lower efficiency of query processing 0..1 Instructor Supervisor 0..3 Supervises The “Many” side has partial participation. (i.e., an instructor may not have a supervisor.) Supervisee

15. Step 4: One-to-One Relationships • Three approaches Employee (empID, empName) Account (username, password) • Foreign key approach • Add an FK to the entity type that “fully” participates in the relationship Account (username, password, empID) Foreign key empID references Employee (empID) • Merged relation approach • Merge the two entity types and the relationship into a single relation Employee (empID, empName, username, password) • Relationship relation approach • Add another relation to represent the relationship Employee_Account (empID, username) Partial participation Full participation

16. Step 4: Example • In our case, we have an one-to-one relationship: • Department fully participants in Manages relationship; • Only a subclass of Instructor, DeptHead, participates in this relationship • Because DeptHead is a subclass of Instructor, we will do Step 8 first and accomplish this step later

17. Step 5: n-ary Relationships • Each n-ary (n3) relationship type maps into a relation • The primary key of this relation is the combination of the primary keys of the participating entity types • These are also foreign keys. • Exception: if the cardinality of any entity type is 1, the primary key for this entity type is not as part of the primary key of the new relation. • Attributes of the relationship type maps to attributes of the relation, similar to those of strong entity types

18. Step 5: Example Supplier (supplierID, supplierName) Project (projectID, projectName) Part (partID, partName) Foreign keys Supply (supplierID, projectID, partID, quantity) Primary key

19. Step 5: Example • In our case, • TeachingCourse (courseNo, sectionNo, instructorID, teachingAssistantID, instructorEval, taEval) • PK: {courseNo, sectionNO} • FKs: {courseNo, sectionNo}, instructorID, teachingAssistantID Course (courseNo, sectionNo, …) Instructor (instructorID, …) TeachingAssistant (TeachingAssistantID, …)

20. Step 5: Example • At this step, we got: TeachingCourse (courseNo, sectionNo, instructorID, teachingAssistantID, instructorEval, taEval) Foreign key {courseNo, sectionNo} references Course (courseNo, sectionNo) Foreign key instructorID references Instructor (instructorID) Foreign key teachingAssistantID references TeachingAssistant (teachingAssistantID);

21. Step 6: Weak Entities • For each weak entity, create a relation that includes all the simple attributes of that entity. • The primary key of a weak entity is partially or fully derived from each owner entity. • The identification of a weak entity’s primary key depends on the relationship with its owner entity.

22. Step 6: Example • First, we create a relation for the weak entity. TeachingPreference (courseType, numCourses) The primary key is undetermined at present. • Second, according to its relationship with the owner entity, we add the primary key. TeachingPreference (instructorID, courseType, numCourses) Foreign key instructorID references Instructor (instructorID); primary key foreign key

23. Step 7: Multi-valued Attributes • Each multi-valued attribute maps into a separate relation • The relation has an attribute for each simple attribute of the multi-valued attribute • Include also an attribute for the primary key of the entity or relationship type that the attribute belongs to • This is also a foreign key • The primary key of this relation is the combination of all the attributes if the multi-valued attribute is simple • If the multi-valued attribute is composite, the primary key of this relation may be a combination of some of the attributes

24. Step 7: Example • At this step, we got: DeptPhoneNo (deptCode, phoneNo) Foreign key deptCode references Department (deptCode); • An example on multi-valued attribute that is composite Customer (custNo, lName, fName) CreditCard (custNo, cardNo, expiration) Foreign key custNo references Customer (custNo);

25. Step 8: Superclasses/Subclasses • Each superclass maps into a relation • Each subclass also maps into a relation • The primary key is the same as the primary key of its superclass • This is also a foreign key • Treat attributes that belongs to the subclasses only as usual • For multi-leveled hierarchy, the primary key of the root entity propagates down a hierarchy of entity types

26. Step 8: Example • At this step, we got: Superclass: Instructor (instructorID, fName, lName, gender, ssn, position, deptCode) Foreign key deptCode references Department (deptCode); Subclasses: DeptHead (instructorID, startDate, bonus) Foreign key instructorID references Instructor (instructorID); ParttimeInstructor (instructorID, hourlyRate) Foreign key instructorID references Instructor (instructorID);

27. Go back to Step 4: Example • At this step, we got: Department (deptCode, deptName, street, city, state, zipcode, deptHeadID) Foreign key deptHeadID references DeptHead (instructorID) Instructor Manages Department DeptHead 1..1 1..1

28. Final Results of Relational Schema • How to get the final results of relational schema: • Revisit Step 1 through Step 8, write down all relation schemas. If a relation schema occurs more than once, write down the latest modified one. • Finally, we got totally 10 relations.

29. Final Results of Relational Schema (Cont.) Department (deptCode, deptName, street, city, state, zipcode, deptHeadID) Foreign key deptHeadID references DeptHead (instructorID); DeptPhoneNo (deptCode, phoneNo) Foreign key deptCode references Department (deptCode); Instructor (instructorID, fName, lName, gender, ssn, position, deptCode) Foreign key deptCode references Department (deptCode); Supervision (instructorID, supervisorID) Foreign key instructorID references Instructor (instructorID) Foreign key supervisorID references Instructor (instructorID); TeachingPreference (instructorID, courseType, numCourses) Foreign key instructorID references Instructor (instructorID);

30. Final Results of Relational Schema (Cont.) DeptHead (instructorID, startDate, bonus) Foreign key instructorID references Instructor (instructorID); ParttimeInstructor (instructorID, hourlyRate) Foreign key instructorID references Instructor (instructorID); Course (courseNo, sectionNo, title, courseType, units, deptCode) Foreign key deptCode references Department (deptCode); TeachingAssistant (teachingAssistantID, ssn, studentID, salary); TeachingCourse (courseNo, sectionNo, instructorID, teachingAssistantID, instructorEval, taEval) Foreign key {courseNo, sectionNo} references Course (courseNo, sectionNo) Foreign key instructorID references Instructor (instructorID) Foreign key teachingAssistantID references TeachingAssistant (teachingAssistantID);

31. Use “” to Represent Foreign Keys

32. Summary of the Schema Mapping Process • Step 0: Identification • Step 1: Strong entity • Entity -> a new relation • Step 2: Many-to-Many relationships • Add a new relation • Step 3: One-to-Many relationships • FK on the M-side • Step 4: One-to-one relationships • FK on the fully participating side • Step 5: n-ary relationships • Add a new relation • Step 6: Weak entity • A new relation with FK from the strong entity as part of its PK • Step 7: Multi-valued attributes • A new relation with FK • Step 8: Superclasses and subclasses • FK in each subclass