ERD to Relational Model The conceptual model depicted

1: 1 has Hospital Persons 3: N Rooms 1: 1 have 1: N In

Conversion steps 1. For each base entity create a relation that includes all the

Conversion Steps 2. For each weak entity type, if not already an attribute, add

Conversion Steps 3. For each 1: N relationship add the primary key of the

Conversion Steps 4. For each 1: 1 relationship a) mandatory both sides - combine

Conversion Steps 5. M: N relationship - for each M: N create a new

Conversion Steps 6. Superclass/subclass relationships mandatory nondisjoint(and) - single relation with a type code

Conversion Steps Hospital(hosp_num, name, address, capacity) Rooms(hosp_num, room_num, type, num_beds) Persons (SIN, name, address,

Slides: 9

Download presentation

ERD to Relational Model The conceptual model depicted by an ERD is designed without a particular implementation model in mind. For our project the implementation model is Relational so our ERD will be converted to relations (Tables). During this conversion the logical relationships between tables will be generated.

1: 1 has Hospital Persons 3: N Rooms 1: 1 have 1: N In 0: M Patients N 0: N Equipment {mandatory, Or} diagnose M Doctors Staff

Conversion steps 1. For each base entity create a relation that includes all the attributes of the entity. Hospital(hosp_num, name, address, capacity) Rooms(hosp_num, room_num, type, num_beds) Persons (SIN, name, address, birthdate) Equipment(serial_no, description, cost) Doctors (doctor_num, specialty) Patients(SIN, date_admitted, date_left) Staff (class_type)

Conversion Steps 2. For each weak entity type, if not already an attribute, add the primary key of its owner entity type. This attribute will be a part of the primary key. Hospital(hosp_num, name, address, capacity) Rooms(hosp_num, room_num, type, num_beds) * Persons (SIN, name, address, birthdate) Equipment(serial_no, description, cost) Doctors (doctor_num, specialty) Patients( SIN, date_admitted, date_left) Staff (class_type)

Conversion Steps 3. For each 1: N relationship add the primary key of the 1 (parent) side to the attributes of the N (child) side Hospital(hosp_num, name, address, capacity) Rooms(hosp_num, room_num, type, num_beds) Persons (SIN, name, address, birthdate) Equipment(serial_no, description, cost, hosp_num, room_num) Doctors (doctor_num, specialty) Patients( date_admitted, date_left) Staff (class_type)

Conversion Steps 4. For each 1: 1 relationship a) mandatory both sides - combine the two relations into one. b) mandatory one side - mandatory side is child and the primary key of parent is added to child. c) optional both sides - select one as child and follow b) d) recursive - primary key must also be entered as a foreign key - different name

Conversion Steps 5. M: N relationship - for each M: N create a new relation with primary keys of each relation as attributes add any new attributes due to the relationship Hospital(hosp_num, name, address, capacity) Rooms(hosp_num, room_num, type, num_beds) Persons (SIN, name, address, birthdate) Equipment(serial_no, description, cost, hosp_num, room_num) Doctors (doctor_num, specialty) Patients( SIN, hosp_num, room_numdate_admitted, date_left) # Staff (class_type) Docpat( doctor_num, SIN, diagnosis) #

Conversion Steps 6. Superclass/subclass relationships mandatory nondisjoint(and) - single relation with a type code optional nondisjoint (and) - two relations - type code in 2 nd mandatory disjoint(or) optional disjoint(or) - many relations, one for each pairing. Child gets FK

Conversion Steps Hospital(hosp_num, name, address, capacity) Rooms(hosp_num, room_num, type, num_beds) Persons (SIN, name, address, birthdate) Equipment(serial_no, description, cost) Doctors (doctor_num, , SIN, specialty) Patients( SIN, hosp_num, room_num, date_admitted, date_left) Staff ( SIN, class_type) ** Docpat( doctor_num, SIN, diagnosis)