Translating ER Model into Relational Model ER Model

Translating ER Model into Relational Model

ER Model Relational Model Considerations: Minimize the number of relations to reduce queryprocessing 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 2

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 Applicable to both binary and Step 3: One-to-many relationships unary (recursive) 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 3

An ER Model INFO 605 4

Step 0 0. 1. 1. Strong entity types: Department, Course, Instructor, Teaching. Assistant 0. 1. 2. Weak entity types: Teaching. Preference 0. 1. 3. Superclasses/Subclasses: Instructor/(Dept. Head, Parttime. Instructor) 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: Dept. Head n-ary: Course: Instructor: Teaching. Assistant 0. 2. 2. Relationship types related to weak entity types: One-to-many: Instructor: Teaching. Preference 5

Step 0 (cont. ) 0. 3. 1. Composite attributes: address(street, city, state, zipcode) 0. 3. 2. Multi-valued attributes: phone. No 0. 3. 3. Derived attributes: units. Taught 6

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 (Phone. No) 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 7

Step 1: Example At this step, we got: Department (dept. Code, dept. Name, street, city, state, zipcode) Course (course. No, section. No, title, course. Type, units) Instructor (instructor. ID, f. Name, l. Name, gender, ssn, position) Teaching. Assistant (teaching. Assistant. ID, ssn, student. ID, salary) How about phone. No of Department and units. Taught of Instructor? We will deal with multi-valued attributes (e. g. , phone. No) later We don’t need to add derived attributes (e. g. , units. Taught) in relations. 8

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 9

Step 2: Example Employee 1. . 3 emp. ID {PK} name salary … Assigned. To 0. . * project. ID{PK} start. Date end. Date … position Employee (emp. ID, name, salary) Project (project. ID, start. Date, end. Date) emp. ID name salary project. ID start. Date end. Date 0001 Harry 80 k 1001 9/10/2003 3/14/2004 0002 Leon 100 k 1002 4/3/2003 7/2/2004 0003 John 90 k Assignment (emp. ID, project. ID, position) emp. ID project. ID position 0001 1001 manager 0002 1001 designer 0001 1002 developer 0003 1002 manager 10

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? 11

Step 3: Example instructor. ID position 11 assistant dept. Code dept. Name 22 associate mathematics 33 full acct accounting If we add an FK to the “many” side, we get: If we add an FK to the “one” side, we get: instructor. ID position dept. Code 11 assistant math 22 associate math 33 full acct dept. Code dept. Name instructor. ID mathematics 11 mathematics 22 acct accounting 33 12

Step 3: Example At this step, we got: Department Offers 1. . 1 1. . * Course Department: Course (course. No, section. No, title, course. Type, units, dept. Code) Foreign key dept. Code references Department (dept. Code); Department Has 1. . 1 1. . * Instructor Department: Instructor (instructor. ID, f. Name, l. Name, gender, ssn, position, dept. Code) Foreign key dept. Code references Department (dept. Code); 13

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

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

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

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 17

Step 5: Example Supplier (supplier. ID, supplier. Name) Project (project. ID, project. Name) Part (part. ID, part. Name) Foreign keys Supply (supplier. ID, project. ID, part. ID, quantity) Primary key 18

Step 5: Example In our case, Course (course. No, section. No, …) Instructor (instructor. ID, …) Teaching. Assistant (Teaching. Assistant. ID, …) Teaching. Course (course. No, section. No, instructor. ID, teaching. Assistant. ID, instructor. Eval, ta. Eval) PK: {course. No, section. NO} FKs: {course. No, section. No}, instructor. ID, teaching. Assistant. ID 19

Step 5: Example At this step, we got: Teaching. Course (course. No, section. No, instructor. ID, teaching. Assistant. ID, instructor. Eval, ta. Eval) Foreign key {course. No, section. No} references Course (course. No, section. No) Foreign key instructor. ID references Instructor (instructor. ID) Foreign key teaching. Assistant. ID references Teaching. Assistant (teaching. Assistant. ID); 20

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. 21

Step 6: Example First, we create a relation for the weak entity. Teaching. Preference (course. Type, num. Courses) The primary key is undetermined at present. Second, according to its relationship with the owner entity, we add the primary key. foreign key primary key Teaching. Preference (instructor. ID, course. Type, num. Courses) Foreign key instructor. ID references Instructor (instructor. ID); 22

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 23

Step 7: Example At this step, we got: Dept. Phone. No (dept. Code, phone. No) Foreign key dept. Code references Department (dept. Code); An example on multi-valued attribute that is composite Customer (cust. No, l. Name, f. Name) Credit. Card (cust. No, card. No, expiration) Foreign key cust. No references Customer (cust. No); 24

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 25

Step 8: Example At this step, we got: Superclass: Instructor (instructor. ID, f. Name, l. Name, gender, ssn, position, dept. Code) Foreign key dept. Code references Department (dept. Code); Subclasses: Dept. Head (instructor. ID, start. Date, bonus) Foreign key instructor. ID references Instructor (instructor. ID); Parttime. Instructor (instructor. ID, hourly. Rate) Foreign key instructor. ID references Instructor (instructor. ID); 26

Go back to Step 4: Example At this step, we got: Department Instructor Manages 1. . 1 Dept. Head Department (dept. Code, dept. Name, street, city, state, zipcode, dept. Head. ID) Foreign key dept. Head. ID references Dept. Head (instructor. ID) 27

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. 28

Final Results of Relational Schema (Cont. ) Department (dept. Code, dept. Name, street, city, state, zipcode, dept. Head. ID) Foreign key dept. Head. ID references Dept. Head (instructor. ID); Dept. Phone. No (dept. Code, phone. No) Foreign key dept. Code references Department (dept. Code); Instructor (instructor. ID, f. Name, l. Name, gender, ssn, position, dept. Code) Foreign key dept. Code references Department (dept. Code); Supervision (instructor. ID, supervisor. ID) Foreign key instructor. ID references Instructor (instructor. ID) Foreign key supervisor. ID references Instructor (instructor. ID); Teaching. Preference (instructor. ID, course. Type, num. Courses) Foreign key instructor. ID references Instructor (instructor. ID); 29

Final Results of Relational Schema (Cont. ) Dept. Head (instructor. ID, start. Date, bonus) Foreign key instructor. ID references Instructor (instructor. ID); Parttime. Instructor (instructor. ID, hourly. Rate) Foreign key instructor. ID references Instructor (instructor. ID); Course (course. No, section. No, title, course. Type, units, dept. Code) Foreign key dept. Code references Department (dept. Code); Teaching. Assistant (teaching. Assistant. ID, ssn, student. ID, salary); Teaching. Course (course. No, section. No, instructor. ID, teaching. Assistant. ID, instructor. Eval, ta. Eval) Foreign key {course. No, section. No} references Course (course. No, section. No) Foreign key instructor. ID references Instructor (instructor. ID) Foreign key teaching. Assistant. ID references Teaching. Assistant (teaching. Assistant. ID); 30

Use “ ” to Represent Foreign Keys 31

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 32