Transforming ER & EER diagrams into Relations (Chapter 9)

1. Transforming ER & EER diagrams into Relations(Chapter 9)

2. Overview • A relatively straightforward process with a well-defined set of rules. • Many CASE Tools can automatically perform many of the conversion steps. • CASE tools often cannot model complex data relationships. • There are sometimes legitimate alternatives where you will need to choose a particular solution. • You must be prepared to perform a quality check on the results obtained with a CASE Tool.

3. Map Simple Regular Entities • Each regular entity type in an ER diagram is transformed into a relation. • The name given to the relation is generally the same as the entity type. • Each simple attribute of the entity type become an attribute of the relation. • Choose one of the key(s) as primary key of the relation. • How about composite attributes? • How about multi-valued attributes?

4. Street Name City ID Address State Zip Map Composite Attributes • When a regular entity type has a composite attribute, only the simple component attributes of the composite attribute are included in the new relation. COSTOMER CUSTOMER(ID, Name, Street, City, State, Zip)

5. Map Multi-valued Attributes • When a regular entity type contains a multivalued attribute, two new relations (rather than one) are created. • The first relation contains all of the attributes of the entity type except the multi-valued attribute. • The second relation contains two sets of attributes. • The primary key from the first relation, which becomes a foreign key of the second relation. • Multi-valued attribute itself. • The primary key of the second of relation is the combination of all attributes. • The name of the second relation should capture the meaning of the multi-valued attribute.

6. Name SSN Street City Address Skills State Zip Example EMPLOYEE EMPLOYEE(SSN, Name, State, City, State, Zip) EMPLOYEE_SKILL(SSN, Skill)

7. SSN DeptName Name Major_in Map Binary One-Many Relationship • Create a relation for each of the two entity types participating in the relationship. • Include the primary key attribute (or attributes) of the entity on the one-side of the relationship as a foreign key in the relation that is on the many-side of the relationship ( a mnemonic you can use to remember this rule is this: The primary key migrates to many side). N 1 STUDENT DEPT STUDENT(SSN, Name, DeptName) DPET(DeptName)

8. Name CID SID Text taking Map Binary Many-Many Relationships • Suppose that there is a binary relationship (M:N) between two entity types A and B. For such a relationship, create a new relation C: • Include as foreign key attributes in C the primary key for each of the two participating entity type. • These attributes become the primary key of C. • Any attributes that are associated with the relationship are included with the relation C. Grade STUDENT COURSE N M STUDENT(SID, NAME) COURSE(CID, Text) Taking(SID, CID, Grade)

9. Name SSN Manages Map Binary One-One Relationship • Binary 1:1 relationship can be viewed as a special case of 1:m relationship. The process of mapping such a relationship to relations requires to steps: • first, two relations are created, one for each of the participating entity type. • Second, the primary key of one of the relations is included as foreign key in the other relation. DeptName 1 1 DEPT EMPLOYEE EMPLOYEE(SSN, Name) EMPLOYEE(SSN, Name, DeptName) DEPT(DeptName, ManagerSSN) DEPT(DeptName)

10. Name SSN Map Unary One-Many Relationship • The entity type in the unary relationship is mapped to a relation using the procedure described before. Then a foreign key attribute is added within the same relation that references the primary key values. • Note that the foreign key attribute name should reflect the role name on the one-side. • A recursive foreign key is a foreign key in a relation that references the primary key values of that same relation. Supervisee N EMPLOYEE Manages 1 Supervisor EMPLOYEE(SSN, Name, SupervisorSSN)

11. Quantity Name Unit_cost Map Unary Many-Many relationship • With this type of relationship: • Two relations are created: one to represent the entity type in the relationship and the other an associative relation to represent the M:N relationship itself. • The primary key of the associative relation consists of two parts: both take their values from the primary key of the other relation. • Any attribute of the relationship is included in the associative relation. ID Item M PART Contains N Components COMPONENT(ItemID, ComponentID, Quantity) PART(ID, Name, Unit_Cost)

12. name name addr. SSN salary sex sex birthdate birthdate DEPENDANT_ OF Dependants Map Weak Entities • For each weak entity type, create a new relation and include all of the simple attributes (or simple components of composite attributes) as attributes of this relation. • Then, include the primary key of the owner relation as a foreign key attribute in this relation. • The primary key of the new relation is the combination of this primary key of the owner and the partial key of the weak entity type. N 1 Employees relationship

13. Example Results Employee( SSN, name, addr, salary sex, birthdate) Dependants(name, birthdate, sex, relationship, empSSN) • The relation for the weak entity not only has the attributes of itself, but also has the key attributes of the other entity sets. • Do not construct a relation for a “double-diamond” relationship.

14. ProjName Quantity SName ProjName Quantity SName 1 1 N N SUPPLIER SUPPLY PROJECT SPJ SS SUPPLY N SP PartNo 1 PART PartNo Map Ternary (n-ary) Relationship • It is recommended that you convert the ternary (n-ary) relationship to a number of binary relationships, and then transform the diagram into relationships. SUPPLIER PROJECT PART

15. Example Result SUPPLIER(SName) PROJECT(ProjName) SUPPLY(SName, ProjName, PartID, Quantity) PART(PartID)

16. Map Supertype/Subtype Relationships • The relational data model does not yet directly support/subtype relationships. • There are various strategies that database designer can use to represent these relationships. • In this lecture, we introduce the most commonly employed strategy.

17. Mapping Strategy • Create a separate relation for the supertype and for each of its subtypes. • Assign to the relation created for the supertype the attributes that are common to all memebers of the supertype, including the primary key. • Assign to the relation for each subtype the primary key of the supertype, and this primary key is also a foreign key that references the primary key in relation representing the supertype. • Assign to the relation for each subtype the attributes that are unique to that subtype.

18. Address Name SSN Date_hired Billing_rate Annual_salary Stock_option Contract_No. Hourly_rate Example EMPLOYEE d    HOURLY EMPLOYEE SALARED EMPLOYEE CONSULTANT EMPLOYEE( SSN, Name, Address, Date_hired) HOURLY_EMPLOYEE(SSN, Hourly_Rate) SALARIED_EMPLOYEE(SSN, Annual_salary, Stock_Option) CONSULTANT(SSN, Contract_No, Billing_rate)

19. Map Categories • Problem • Multiple supertypes may have different primary keys. • Solution • The concept of surrogate key, a specified new key attribute for the subtype.

20. CName Name SSN Address Total_Balance BName Has_Acct Example PERSON COMPANY U  N ACCOUNT HOLDER M BANK

21. Example Results COMPANY(CName, Address, OwnerID) PERSON(SSN, Name, OwnerID) ACCOUNTHOLDER(OwnerID) HASACCOUNT(OwnerID, BName, Total_Balance) BANK(BName)