Sept. 2004 1

• ER-to-Relational Mapping Principles

ER-to-Relational Mapping

And examples

Sept. 2004 2

General process

1. Create a relation for each strong entity type

2. Create a relation for each weak entity type

3. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK

4. For each binary 1:n relationship, choose the n-side entity and include an FK with respect to the other entity.

5. For each binary M:N relationship, create a relation for the relationship

6. For each multi-valued attribute create a new relation

7. For each n-ary relationship, create a relation for the relationship

Sept. 2004 3

1. Create a relation for each strong entity type

•include all simple attributes

•choose a primary key

Suppose we have:

course 1 Noffered in

Section noterm

meeting

course noname

credit hoursdescription

section

Sept. 2004 4

We create a relation for Course - four attributes, course_no is the PK.

course 1 Noffered in

Section noterm

meeting

course noname

credit hoursdescription

section

CourseCourse_no name credit_hours description

Sept. 2004 5

2. Create a relation for each weak entity type•include primary key of owner (an FK)•Owner’s PK + partial key become the PK

Suppose we have:

course 1 Noffered in

Section noterm

meeting

course noname

credit hoursdescription

section

Sept. 2004 6

We create a relation for Section

course 1 Noffered in

Section noterm

meeting

course noname

credit hoursdescription

section

Section

Course_no Section_no Term

•PK is {course_no, section_no}.

•course_no is an FK.

•meeting is not a simple attribute, so it’s not included.

Sept. 2004 7

How to handle the identifying relationship in which

a weak entity set is involved?• Relation for a weak entity set must include

multiple attributes for its complete key (including those belonging to its identifying entity sets), as well as its own, partial key attributes.

• An identifying (double-diamond) relationship is redundant and yields no relation.

Sept. 2004 8

An example showing

Logins HostsAt

name name

Hosts(hostName) this is for the strong entity set.Logins(loginName, hostName, time)At(loginName, hostName, hostName2)

Must be the same

time

At becomes part ofLogins, so eliminated!

Sept. 2004 9

3. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK.

department instructorchair1 1

dept_no dname instr_no iname

There are two choices here

•choose department, or

•choose instructor

Which is the better choice?

Sept. 2004 10

Department is the better choice since it must participate in the relationship.

department instructorchair1 1

dept_no dname instr_no iname

Department

chairdept_no dname

If we choose department then instr_no is included as, of course, an FK. Note that instr_no must have a value.

Sept. 2004 11

4. For each binary 1:n relationship, choose the n-side entity and include an FK w.r.t the other entity.

department instructoremploys1 N

dept_no dname instr_no iname

We must choose instructor

We end up with:

instructorinstr_no iname dept_no

•PK is instr_no

•dept_no is an FK

Note that Step 1 would lead to the instructor relation - we have now augmented instructor with the dept_no attribute.

Sept. 2004 12

5. For each binary M:N relationship, create a relation for the relationship

•include PKs of both participating entities and any attributes of the relationship•PK is the catenation of the participating entities’ PKs

student courseenrollm ngrade

Enroll

student_no Course_no grade

•PK is {student_no, course_no}

•student_no is a FK

•course_no is a FK

•grade is an attribute of Enroll

course_nostudent_no

Sept. 2004 13

6. For each multi-valued attribute create a new relation•include the PK attributes of the entity type•PK is the PK of the entity type and the multi-valued attribute

course 1 Noffered in

Section noterm

meeting

course noname

credit hoursdescription

section

Meeting is a multi-valued attribute

Sept. 2004 14

Create a relation for meeting

Section was created because of Step 2 - its PK is {course_no, section_no}

meeting

Meeting

course_no section_no meeting•PK is {course_no, section_no, meeting}.

•Meeting is an all-key relation.

Sept. 2004 15

7. For each n-ary relationship, create a relation for the relationship

•include PKs of all participating entities and any attributes of the relationship•PK may be the catenation of the participating entity PKs (depends on cardinalities)

semester courseoffersm nroom course_nosemester_no

instructor

instr_no

p

Sept. 2004 16

We need one relation, offers, with PK of {semester_no, course_no, instr_no}

semester courseoffersm nroom no course_nosemester_no

instructor

instr_no

p

Offers

course_no instr_no semester_no Room_no

Sept. 2004 17

Another perspective to see how to map E/R Diagrams to Relations

• Entity sets become relations with the same set of attributes.– Regular entity set– Weak entity set – need help

• Relationships – Identifying relationship

• eliminated, actually merged to weak entity set relation.– Regular relationship

• become relations whose attributes are only:– The keys of the connected entity sets.– Attributes of the relationship itself.

Sept. 2004 18

Combining Relations for binary relationship

• Then to combine the relation for an entity-set E with the relation R for a many-one relationship from E to another entity set.

• Of course, the combination (merging) is applicable to one-one relationship.– To avoid null values, we chose full

participating entity set relation to add a foreign key referencing the opposite entity set.