Introduction to Database Systems Mapping ER Models to Relational Schemas

1. Introduction to Database SystemsMapping ER Models to Relational Schemas Irvanizam Zamanhuri, M.Sc Informatics (Computer Science) Study Program Syiah Kuala University http://www.informatika.unsyiah.ac.id Email: irvanizam.zamanhuri@informatika.unsyiah.ac.id

2. name buys Person Product price name ssn Conceptual and Logical Design Conceptual Model: Relational Model:

3. Mapping an E-R Diagram to a Relational Schema We cannot store date in an ER schema (There are no ER database management systems)  We have to translate our ER schema into a relational schema  What does “translation” mean?

4. courseno no. of equip students given family name STUDENT COURSE subject studno Mapping Entity Types to Relations • For every entity type create a relation • Every atomic attribute of the entity type becomes a relation attribute • Composite attributes: include all the atomic attributes • Derived attributes are not included (but remember their derivation rules) • The relation is a subset of the cross product of the domains of the attributes • Omit derived attributes

5. courseno no. of equip students given family name STUDENT COURSE subject studno Mapping Entity Types to Relations (cntd.) STUDENT (studno, givenname, familyname) COURSE(courseno, subject, equip)

6. given family courseno no. of labmark equip students name ENROL STUDENT exammark COURSE subject studno Mapping Many:many Relationship Types to Relations Create a relation with the following set of attributes: N(degree of relationship) U primary_key(Ei) U {a1…aM} i=1 primary keys of each participating entity type in the relationship attributes on the relationship type (if any)

7. given family courseno no. of labmark equip students name ENROL STUDENT exammark COURSE subject studno Mapping Many:many Relationship Types to Relations (cntd.) ENROL(studno,courseno, labmark, exammark)

8. STAFF E1 U primary_key(E2) U {a1…an} studno relation for entity E1 primary key for E2, is now a foreign key to E2 Mapping One:many Relationship Types to Relations given family name roomno slot name m 1 TUTOR Idea: “Post the primary key” • Given E1 at ‘many’ end of relationship and E2 at ‘one’ end of relationship, add to the relation for E1 • Make the primary key of the entity at the ‘one’ end (the determined entity) a foreign key in the entity at the ‘many’ end (the determining entity). Include any relationship attributes with the foreign key entity STUDENT attributes on the relationship type (if any)

9. name STAFF studno given family roomno slot name m 1 TUTOR STUDENT Mapping One:many Relationship Types to Relations(cntd.) The relation STUDENT (studno, givenname, familyname) is extended to STUDENT (studno, givenname, familyname, tutor, roomno,slot) and the constraint Foreign Key STUDENT(tutor,roomno) references STAFF(name,roomno)

10. Mapping one:many Relationship Types to Relations (cntd.)

11. primary_key(E1) U primary_key(E2) U {a1,…,am} primary key for E1, is now a foreign key to E1; also the PK for this relation primary key for E2, is now a foreign key to E2 STAFF given family name roomno slot name m 1 TUTOR studno STUDENT Mapping One:many Relationship Types to Relations(cntd.) Another Idea: • If the relationship type is optional to both entity types, • and an instance of the relationship is rare, • and there are many attributes on the relationship then… Create a new relation with the set of attributes: attributes on the relationship type (if any)

12. STAFF given family name roomno slot name m 1 TUTOR studno STUDENT Mapping One:many Relationship Types to Relations(cntd.) TUTOR(studno, staffname, rommno, slot) and Foreign key TUTOR(studno) references STUDENT(studno) Foreign key TUTOR(staffname,roomno) references STAFF(name,roomno) Compare with the mapping of many:many relationship types!

13. Mapping One:many Relationship Types to Relations (cntd.)

14. Optional Participation of the Determined Entity (‘one end’) A school entity instance does not have to participate in a relationship instance of REG SCHOOL(hons, faculty) STUDENT(studno, givenname, familyname, ??? ) A student entity instance must participate in a relationship instance of REG given family 1 SCHOOL REG name studno hons m faculty STUDENT

15. Optional Participation of the Determined Entity hons can’t be NULL because it is mandatory for a student to be registered for a school  “not null” constraint No student is registered for “mi”, so “mi” doesn’t occur as a foreign key value (but that’s no problem)

16. STAFF studno Optional Participation of the Determinant Entity (‘many end’) given family name roomno slot name m 1 TUTOR STUDENT A student entity instance does not have to participate in a relationship instance of TUTOR A staff entity instance must participate in a relationship instance of TUTOR

17. Optional Participation of the Determinant Entity (‘many end’) 1. STUDENT (studno, givenname, familyname, tutor, slot) STAFF(name, roomno) Integrity constraint: pname,roomno STAFF – ptutor,roomno STUDENT =  2. STUDENT(studno, givenname, familyname) STAFF(name, roomno) TUTOR(studno, tutor, roomno, slot) Do we also need an integrity constraint?

18. Optional Participation of the Determinant Entity (cntd.)

19. year 1 YEAR 1 YEARTUTOR name roomno 1 STAFF Mapping one:one Relationship Types to Relations • Post the primary key of one of the entity types into the other entity type as a foreign key, including any relationship attributes with it or • Merge the entity types together Which constraint holds in this case?

20. studno Multi-Valued Attributes (… if they are allowed) For each multi-valued attribute of Ei, create a relation with the attributes primary_key(Ei) U multi-valued attribute The primary key comprises all attributes given family dateofbirth name STUDENT contact

21. name STAFF roomno m 1 appraisee appraiser APPRAISAL Mapping Roles & Recursive Relationships How can the entity STAFF appear in both of its roles ? STAFF(name, roomno, ) appraiser, approomno

22. name Multiple Relationships between Entity Types 1. Treat each relationship type separately 2. Represent distinct relationships by different foreign keys drawing on the same relation given family name SUPERVISE 1 m STAFF STUDENT m 1 EXAMINE roomno studno STAFF(name,roomno) STUDENT(studno,given,family, ??? ) STUDENT(studno,given,family, ??? ) EXAMINER( ??? ) SUPERVISOR( ??? ) EXAMINER-SUPERVISOR( ??? )

23. roomno name COURSE subject studno Non-binary Relationship STAFF STAFF p given family courseno equip name TUTORS n m STUDENT slot COURSE(courseno, subject, equip) STUDENT(studno, givenname, familyname) STAFF(staffname, roomno) TUTORS( ??? )

24. c-name • Strong Entity • Identifying entity for • ORDER • Identifying entity for • ORDER_LINES CUSTOMER address 1 orderid m • Weak entity • Identifying entity for • ORDER_LINES ORDER date 1 ORDER-MAKEUP CUST-ORDER m part ORDER_ ORDER • Weak entity LINE LINES quantity Weak Entities

25. customer address orderid ORDER date Mapping Weak Entities to Relations n primary_key(E0) U U discriminator(Ei) U {a1,…,an} i=1 Create a relation with the attributes: Discriminators of identifying weak entity type Attributes of the weak entity type Primary key of identifying weak entity type CUSTOMER 1 CUST-ORDER How should we choose the primary key? m

26. given given family family courseno equip name name m m STUDENT STUDENT COURSE subject studno studno 1 1 1 m m ENROL labmark exammark COURSE_ENROL STUD_ENROL Association Entity Types An entity type that represents a relationship type:

27. given given family family courseno equip name name m m STUDENT STUDENT COURSE subject studno studno 1 1 1 m m ENROL labmark exammark COURSE_ENROL STUD_ENROL Mapping Association Types to Relations We have: • COURSE(courseno, subject, equip) • STUDENT(studno, givenname, familyname) Then: • ENROL(courseno, studno, labmark, exammark)

28. faculty given family 1 SCHOOL REG name year studno m hons STUDENT m m n YEARREG 1 slot labmark YEAR ENROL TUTOR 1 exammark YEARTUTOR m n TEACH name 1 courseno m 1 subject roomno COURSE STAFF m 1 equip appraisee appraiser APPRAISAL Translation of the University Diagram STUDENT (studno, givenname, familyname, hons, tutor, slot, year) ENROL(studno,courseno, labmark,exammark) COURSE(courseno, subject, equip) STAFF(lecturer, roomno, appraiser) TEACH(courseno,lecturer) YEAR(year, yeartutor) SCHOOL(hons, faculty)

29. Exercise: Supervision of PhD Students A database needs to be developed that keeps track of PhD students: • For each student store the name and matriculation number. Matriculation numbers are unique. • Each student has exactly one address. An address consists of street, town and post code, and is uniquely identified by this information. • For each lecturer store the name, staff ID and office number. Staff ID's are unique. • Each student has exactly one supervisor. A staff member may supervise a number of students. • The date when supervision began also needs to be stored. • For each research topic store the title and a short description. Titles are unique. • Each student can be supervised in only one research topic, though topics that are currently not assigned also need to be stored in the database.

30. Exercise: Supervision of PhD Students Tasks: a) Design an entity relationship diagram that covers the requirements above. Do not forget to include cardinality and participation constraints. b) Based on the ER-diagram from above, develop a relational database schema. List tables with their attributes. Identify keys and foreign keys.

31. References In preparing these slides I have used several sources. The main ones are the following: Books: • A First Course in Database Systems, by J. Ullman and J. Widom • Fundamentals of Database Systems, by R. Elmasri and S. Navathe Slides from Database courses held by the following people: • Warner Nutt (Free University of Bozen-Bolzano) • Enrico Franconi (Free University of Bozen-Bolzano) • Carol Goble and Ian Horrocks (University of Manchester)