ER Diagram Reduction to

Relation Schemas

1. Strong Entity Sets with Simple Attributes

a) Rectangles in ER diagram are entity sets

b) Relation name is the name of the entity set

c) Attributes are the same as in the entity set

d) PK is the PK of the entity set

i) MUST be underlined in relation  schema

classroom( building, room_number, capacity)

department ( dept_name, building, budget)

course ( course_id, title, credits)

instructor ( id, name, salary )

student ( id, name, tot_cred)

2. Strong Entity Sets with Complex Attributes

a) Relation name is the name of the entity set

b) Composite attributes

i) Keep the inner attributes

ii) Do NOT create an attribute for the composite attribute itself

c) Multi-valued attributes

i) Create an additional relation schema that has attributes consisting of

(1) PK of the entity set that contains the multi-valued attribute

(2) attribute that was multi-valued

ii) The PK of the new relation is ALL the attributes

time_slot(time_slot_id)

time_slot_ids (time_slot_id, day, start_time, end_time)

* Because time_slot relation only contains a single attribute which is its own PK, the two relations can be combined to:

time_slot (time_slot_id, day, start_time, end_time)

3. Weak Entity Sets

a) Rectangles connected to identifying relationships (double-diamonds) are weak entity sets

i) Discriminator is the set of attributes that are underlined with dashes

b) Name of relation is the name of the entity set

c) PK is made up of

i) PK  of the strong identifying entity set

ii) Discriminator of the weak entity set

d) Other attributes as they appear in the weak entity set

        section( course_id, sec_id, semester, year)

4. Relationship Sets

a) Diamonds in ER diagram are relationship sets

b) Name of relation is the name of the relationship entity set

c) Attributes are the PKs of the two participating entity sets

d) Rename attributes that have the same name

course_dept	( course_id, dept_name)
inst_dept	( id, dept_name)
stud_dept	( id, dept_name)
advisor	( inst_id, stud_id )
teaches	( id,  course_id, sec_id, semester, year )
takes	( id, course_id, sec_id, semester, year, grade )
sec_course	(course_id, sec_id, semester, year )
prereq	( course_id, prereq_id )
sec_class	( building, room_number )
sec_time_slot	(course_id, sec_id, semester, year, time_slot_id )

5. Eliminate Redundant Schemas

a) Determine cardinality of each of the relationship relations

Card	Relation	Attributes
M:1	course_dept	( course_id, dept_name)
M:1	inst_dept	( id, dept_name)
M:1	stud_dept	( id, dept_name)
1:M	advisor	( inst_id, stud_id )
M:M	teaches	( id,  course_id, sec_id, semester, year )
M:M	takes	( id, course_id, sec_id, semester, year, grade )
M:1	sec_course	( course_id, sec_id, semester, year )
M:M	prereq	( course_id, prereq_id )
M:1	sec_class	(sec_id, semester, year, building, room_number )
M:1	sec_time_slot	( course_id, sec_id, semester, year, time_slot_id )

a) Find all relationship schemas that are 1:1 or 1:M or M:1

b) Prepare to remove redundant relationship schemas by adding PK attributes of the ONE side as FK attributes to the MANY side

Card	Relation	Attributes	Replace Using FK ?
M:1	course_dept	( course_id, dept_name)	course->dept_name
M:1	inst_dept	( id, dept_name)	instructor->dept_name
M:1	stud_dept	( id, dept_name)	student->dept_name
M:1	sec_class	( building, room_number )	section->building,room_number
M:1	sec_time_slot	( time_slot_id )	section->time_slot_id
M:1	sec_course	(course_id, sec_id, semester, year )	from weak entity – just remove
1:M	advisor	( inst_id, stud_id )	keeping
M:M	teaches	( id,  course_id, sec_id, semester, year )
M:M	takes	( id, course_id, sec_id, semester, year, grade )
M:M	prereq	( course_id, prereq_id )

course ( course_id, title, dept_name, credits)

instructor ( id, name, dept_name, salary )

student ( id, name, dept_name, tot_cred)

section( course_id, sec_id, semester, year, building, room_number)

a) Define all FKs on relation schemas

course ( course_id, title, dept_name, credits)           

instructor ( id, name, dept_name, salary )                   

student ( id, name, dept_name, tot_cred)                  

section( course_id, sec_id, semester, year, building, room_number, time_slot_id)

FK course(dept_name)                                               REFERENCES department(dept_name)

FK instructor(dept_name)                                         REFERENCES department(dept_name)

FK student(dept_name)                                             REFERENCES department(dept_name)

FK section(building, room_number)                      REFERENCES classroom(building, room_number)

FK section(course_id)                                                  REFERENCES course(course_id)

FK (time_slot_id)                                                           REFERENCES time_slot(time_slot_id)

b) Delete redundant relationship schemas and define FKs on remaining schemas

advisor ( inst_id, stud_id )

teaches ( id,  course_id, sec_id, semester, year )

takes ( id, course_id, sec_id, semester, year, grade )

prereq ( course_id, prereq_id )

FK advisor (inst_id)                                                       REFERENCES instructor(id)

FK advisor (stud_id)                                                      REFERENCES student(id)

FK teaches(id)                                                                 REFERENCES instructor (id)

FK teaches (course_id, sec_id, semester, year) REFERENCES section(course_id, sec_id, semester, year)

FK takes(id)                                                                      REFERENCES student(course_id)

FK takes (course_id, sec_id, semester, year)    REFERENCES section(course_id, sec_id, semester, year)

FK prereq (course_id)                                                  REFERENCES course(course_id)

FK section(course_id)                                                  REFERENCES course(course_id)

1. Relation Schemas for University Enterprise

classroom( building, room_number, capacity)

department ( dept_name, building, budget)

course ( course_id, title, credits)

instructor ( id, name, salary )

student ( id, name, tot_cred)

section(course_id, sec_id, semester, year, building, room_number, time_slot_id )

time_slot (time_slot_id, day, start_time, end_time)

advisor ( inst_id, stud_id )

teaches ( id,  course_id, sec_id, semester, year)

takes ( id, course_id, sec_id, semester, year, grade )

prereq ( course_id, prereq_id )

FK course(dept_name)                                               REFERENCES department(dept_name)

FK instructor(dept_name)                                         REFERENCES department(dept_name)

FK student(dept_name)                                             REFERENCES department(dept_name)

FK section(building, room_number)                      REFERENCES classroom(building, room_number)

FK section(course_id)                                                  REFERENCES course(course_id)

FK (time_slot_id)                                                           REFERENCES time_slot(time_slot_id)

FK advisor (inst_id)                                                       REFERENCES instructor(id)

FK advisor (stud_id)                                                      REFERENCES student(id)

FK teaches(id)                                                                 REFERENCES instructor (id)

FK teaches (course_id, sec_id, semester, year) REFERENCES section(course_id, sec_id, semester, year)

FK takes(id)                                                                      REFERENCES student(course_id)

FK takes (course_id, sec_id, semester, year)    REFERENCES section(course_id, sec_id, semester, year)

FK prereq (course_id)                                                  REFERENCES course(course_id)

FK section(course_id)                                                  REFERENCES course(course_id)