Transforming an ER Model into a Relational Schema

1. Transforming an ER Model into a Relational Schema Cs263 Lecture 10

2. Transforming an ER Model into a Relational Schema There are a series of steps that are recommended for converting an E/ER model into a relational schema. These steps are not hard and fast rules, but rather a series of pertinent suggestions that the database designer must use wisely.

3. E/ER to Relational Schema Conversion Steps • Step 1 • For each ‘strong’ entity in the E/ER model, create a base relation with a column for each simple attribute of that entity. • The key attribute for the entity becomes the primary key of the relation.

4. Emp Job EmpNo Name Hire Date Comm Salary Emp EmpNoNumber(4) Name Varchar2(10) Job Varchar2(9) Sal Number(7,2) Comm Number(7,2) HireDate Date Primary Key Example – Converting ‘Strong’ Entities

5. Dept Primary Key Dept DeptNoNumber(2) Name Varchar2(14) Location Varchar2(13) Location Name DeptNo Example – Converting ‘Strong’ Entities

6. E/ER to Relational Schema Conversion Steps • Step 2 For each ‘weak’ entity, create a relation consisting of all the simple attributes of that entity and also include columns for the primary keys of those entities on whose existence it is dependent.

7. Composite Primary Key Example – Converting ‘Weak’ Entities Loan Payment Date Amount Loan No Payment No Discriminator Payment LoanNoChar(8) PaymentNo Number(4) Date Date Amount Number(9,2)

8. E/ER to Relational Schema Conversion Steps • Step 3 When two entities participate in a one-to-many (1-M) relationship, the relation representing the entity with the M (Many) cardinality must have a foreign key column representing this relationship.

9. Dept D# E# Emp Assigned Works in Emp EmpNoNumber(4) Name Varchar2(10) Job Varchar2(9) Sal Number(7,2) Comm Number(7,2) HireDate Date DeptNoNumber(2) Foreign Key Example – Converting (1-M)Relationships As an Employee ‘must’ work in a department, then the DeptNo column in the Emp relation (table) cannot be NULL!

10. Emp EmpNoNumber(4) Name Varchar2(10) Job Varchar2(9) Sal Number(7,2) Comm Number(7,2) HireDate Date DeptNoNumber(2) Foreign Key Example – Converting (1-M)Relationships Dept D# E# Emp Assigned Works in As an Employee ‘may not’ work in a department, then the DeptNo column in the Emp relation (table) can be NULL!

11. E/ER to Relational Schema Conversion Steps • Step 4 When two entities participate in a (1-1) relationship, a foreign key column must be included in the relation that represents one of these entities.

12. Emp EmpNoNumber(6) Name Varchar2(10) Address Varchar2(200) DOB Date IDNoChar(10) Foreign Key Foreign Key ID Card IDNoChar(10) Issued Date Expires Date EmpNoNumber(6) Example – Converting (1-1)Relationships Emp E# ID# ID Card Has Belongs to EITHER ONE IS ACCEPTABLE

13. E/ER to Relational Schema Conversion Steps • Step 5 When two entities participate in a many-to-many (M-M) relationship, then a relation must be created consisting of foreign keys for the two relations that represent the participating entities.

14. Person P# C# Car Drives Driven by Person-Car PersonNoNumber(6) CarNoChar(7) Foreign Key Foreign Key Example – Converting (M-N)Relationships Person P# Car P#,C# Person-Car C#

15. E/ER to Relational Schema Conversion Steps • Step 6 Where an entity has a multi-valued attribute, create a relation with a column as a foreign key to the relation that represents the entity and a column for the multi-valued attribute.

16. Programmer EmpNoNumber(2) Name Varchar2(14) Primary Key Composite Primary Key Prog-Lang EmpNoNumber(2) LanguageVarchar2(20) Example – Multi-valued Attributes Programmer Language Language Language Name EmpNo

17. E/ER to Relational Schema Conversion Steps • Step 7 When more than two entities participate in a relationship, then a relation must be created consisting of foreign keys to those relations representing the entities participating in the relationship.

18. Composite Primary Key Example – Converting TernaryRelationships Match Match No Player Player No Booking Offence Offence No Booking MatchNoNumber(4) PlayerNoNumber(6) OffenceNo Number(3) Time Date

19. E/ER to Relational Schema Conversion Steps • Sub-typing (OO design not necessary for assignment) For sub-typing, create separate relations for each subtype consisting of those attributes which are peculiar to the given subtype, and include a foreign key to the super-type, that will act as the sub-type’s primary key. Create a separate relation for the super-type!

20. SECRETARY TECHNICIAN ENGINEER Typing Speed Grade Type Employee EmployeeNo Name Address Secretary EmployeeNo TypingSpeed Example – Converting Sub-types EMPLOYEE Employee No Name Address Technician EmployeeNo Grade Engineer EmployeeNo Type

21. E/ER to Relational Schema Conversion Steps • Step 8 – Option B For sub-typing, create separate relations for each subtype consisting of all the attributes of the super-type, and all those which are peculiar to the given subtype. The primary key of the super-type becomes the primary key of each sub-type. Do not create a relation for the super-type!

22. VEHICLE Vehicle No Licence No Price TRUCK CAR No of Axles Tonnage No of Seats VehicleNo LicenceNo Price NoOfSeats Example – Converting Sub-types Truck Car VehicleNo LicenceNo Price NoOfAxles Tonnage