COP 4710: Database Systems Fall 2013 Chapter 3 – In Class Exercises

1. COP 4710: Database Systems Fall 2013 Chapter 3 – In Class Exercises Instructor : Dr. Mark Llewellyn markl@cs.ucf.edu HEC 236, 407-823-2790 http://www.cs.ucf.edu/courses/cop4710/fall2013 Department of Electrical Engineering and Computer Science Computer Science Division University of Central Florida

2. Transform each of the ER diagrams shown on this and the following few pages, into a set of relational schemas which show referential integrity constraints. Introduction Problem #1 Employee employee-id employee-name address {skill} [years-employed]

3. Employee-ID Employee-Name Address Date-Employed Employee-ID Skill EMPLOYEE SKILL EMPLOYEE Solution for Problem #1 Derived attribute is “replaced” with the attribute actually maintained in the database. Transformation technique is not indicated at this level. Multi-valued attribute is contained in a separate table.

4. Problem #2 Flight flight-id (flight-number, date) number-of-passengers

5. FLIGHT Flight-Number Date Number-of-Passengers Solution for Problem #2 Composite attribute Flight-ID is replaced by both of the sub-component attributes of the original composite attribute. Note that since the original composite attribute was a key attribute that all of the sub-component attributes are now key attributes.

6. Problem #3 Employee Course employee-id employee-name course-id course-title completes date-completed

7. Solution for Problem #3 EMPLOYEE Employee-ID Employee-Name COMPLETION Employee-ID Course-ID Date-Completed COURSE The M:M relationship is modeled as a third table. Course-ID Course-Title

8. Problem #4 Employee Course employee-id employee-name course-id course-title certificate certificate-number date-completed Note that this is the same problem as #3, however, this time the relationship has been modeled as an associative entity rather than as a simple N:M binary relationship due to the presence of an identifier in the associative relationship.

9. EMPLOYEE Employee-ID Employee-Name CERTIFICATE Certificate-No Employee-ID Course-ID Date-Completed COURSE Course-ID Course-Title Solution for Problem #4 When the certificate is modeled as an associative entity and has an identifier (in this case the certificate number), that identifier becomes the key of the relation scheme with the identifiers in the two participating entity sets becoming foreign keys in the associative entity table.

10. Problem #5 Movie Blu-Ray movie-name copy-number blu-ray-title is-stocked-as Basic 1:M binary relationship.

11. MOVIE Movie-Name DVD Copy-No blu-ray-title Solution for Problem #5

12. Problem #6 Vehicle vehicle-id (vehicle-name: make, model) price engine-displacement d Vehicle-type= =“C” =“T” Truck Car number-of-passengers cab-type payload-capacity

13. Solution for Problem #6 VEHICLE type Vehicle-ID Price Make Model Engine-Displacement CAR C-Vehicle-ID No-of-Passengers TRUCK T-Vehicle-ID Cab-Type Capacity

14. Problem #7 Patient Responsible Physician Patient-ID Patient-Name Admit-Date P-type cares-for Physician-ID Physician-Name P-type= d =“O” =“R” Outpatient Resident Patient Bed Is-assigned Bed-ID Checkback-Date Date-Discharged

15. Solution for Problem #7 RESPONSIBLE PHYSICIAN Physician-ID Physician-Name PATIENT P-type Patient-Name Patient-ID Admit-Date Physician-ID OUTPATIENT O-Patient-ID Checkback-Date BED RESIDENT PATIENT Bed-ID R-Patient-ID Date-Discharged Bed-ID

16. Problem #8 Part Supplier Part-No Description Location Qty-On-Hand Part-Type(Man?, Pur?) Supplier-ID Supplier-Name o Part-Type: Man?=“Y” Pur?=“Y” Supplies Unit-Price Manufactured-Part Purchased-Part Routing-Number

17. Solution for Problem #8 PART Part-No Description Location Manufactured ? Purchased ? Quantity-on-Hand MANUFACTURED PART M-Part-No. Routing-Number PURCHASED PART P-Part-No. SUPPLY LINE P-Part-No. Supplier-ID Unit-Price SUPPLIER Supplier-ID Supplier-Name