Introduction to Database Systems Mapping ER Models to

Introduction to Database Systems Mapping 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

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

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?

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 given family name STUDENT studno no. of students courseno equip COURSE subject

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

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 {a 1…a. M} i=1 primary keys of each participating entity type in the relationship given attributes on the relationship type (if any) family name STUDENT studno labmark ENROL exammark no. of students courseno equip COURSE subject

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

Mapping One: many Relationship Types to Relations given family m STUDENT roomno slot name studno name TUTOR 1 STAFF Idea: “Post the primary key” ® Given E 1 at ‘many’ end of relationship and E 2 at ‘one’ end of relationship, add to the relation for E 1 ® 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 E 1 U primary_key(E 2) U {a 1…an} relation for entity E 1 primary key for E 2, is now a foreign key to E 2 attributes on the relationship type (if any)

Mapping One: many Relationship Types to Relations (cntd. ) given family roomno slot name studno name m STUDENT TUTOR 1 STAFF 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)

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

Mapping One: many Relationship Types to Relations (cntd. ) given family roomno slot name studno name m STUDENT TUTOR 1 STAFF 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: primary_key(E 1) U primary_key(E 2) U {a 1, …, am} primary key for E 1, is now a foreign key to E 1; also the PK for this relation primary key for E 2, is now a foreign key to E 2 attributes on the relationship type (if any)

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

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

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

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)

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

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?

Optional Participation of the Determinant Entity (cntd. )

Mapping one: one Relationship Types to Relations l year 1 YEARTUTOR name 1 roomno 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 STAFF l Merge the entity types together Which constraint holds in this case?

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 dateofbirth studno family name STUDENT contact

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

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 STAFF m m STUDENT 1 roomno EXAMINE studno STAFF(name, roomno) STUDENT(studno, given, family, ? ? ? ) STUDENT(studno, given, family, EXAMINER( SUPERVISOR( ? ? ? ? ? ) ) ) EXAMINER-SUPERVISOR( ? ? ? )

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

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

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

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

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

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

Exercise: Supervision of Ph. D Students A database needs to be developed that keeps track of Ph. D 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.

Exercise: Supervision of Ph. D 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.

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)