COP 4710 Database Systems Fall 2012 Chapter 3

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

Introduction • 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. Problem #1 Employee employee-id employee-name address {skill} [years-employed] COP 4710: Database Systems (Chapter 3) Page 2 © Dr. Mark

Solution for Problem #1 EMPLOYEE Employee-ID Employee-Name Address Date-Employed EMPLOYEE SKILL Employee-ID Skill Multi-valued attribute is contained in a separate table. COP 4710: Database Systems (Chapter 3) Page 3 Derived attribute is “replaced” with the attribute actually maintained in the database. Transformation technique is not indicated at this level. © Dr. Mark

Problem #2 Flight flight-id (flight-number, date) number-of-passengers COP 4710: Database Systems (Chapter 3) Page 4 © Dr. Mark

Solution for Problem #2 FLIGHT Flight-Number Date Number-of-Passengers 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 subcomponent attributes are now key attributes. COP 4710: Database Systems (Chapter 3) Page 5 © Dr. Mark

Problem #3 Employee Course completes employee-id employee-name course-id course-title date-completed COP 4710: Database Systems (Chapter 3) Page 6 © Dr. Mark

Solution for Problem #3 EMPLOYEE Employee-ID Employee-Name COMPLETION Employee-ID Course-ID Date-Completed COURSE Course-ID The M: M relationship is modeled as a third table. Course-Title COP 4710: Database Systems (Chapter 3) Page 7 © Dr. Mark

Problem #4 Employee Course certificate employee-id employee-name certificate-number date-completed course-id course-title 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. COP 4710: Database Systems (Chapter 3) Page 8 © Dr. Mark

Solution for Problem #4 EMPLOYEE Employee-ID Employee-Name CERTIFICATE Certificate-No Employee-ID Course-ID COURSE Course-ID Course-Title COP 4710: Database Systems (Chapter 3) Date-Completed 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. Page 9 © Dr. Mark

Problem #5 Movie DVD is-stocked-as movie-name copy-number dvd-title Basic 1: M binary relationship. COP 4710: Database Systems (Chapter 3) Page 10 © Dr. Mark

Solution for Problem #5 MOVIE Movie-Name DVD Copy-No dvd-title COP 4710: Database Systems (Chapter 3) Page 11 © Dr. Mark

Vehicle Problem #6 vehicle-id (vehicle-name: make, model) price engine-displacement d Car number-of-passengers Truck cab-type payload-capacity COP 4710: Database Systems (Chapter 3) Page 12 © Dr. Mark

Solution for Problem #6 VEHICLE Vehicle-ID Price Make Model Engine-Displacement CAR C-Vehicle-ID No-of-Passengers TRUCK T-Vehicle-ID COP 4710: Database Systems (Chapter 3) Cab-Type Page 13 Capacity © Dr. Mark type

Problem #7 COP 4710: Database Systems (Chapter 3) Page 14 © Dr. Mark

Solution for Problem #7 RESPONSIBLE PHYSICIAN Physician-ID PATIENT Patient-ID Admit-Date Physician-ID ptype OUTPATIENT O-Patient-ID Checkback-Date BED RESIDENT PATIENT R-Patient-ID Date-Discharged COP 4710: Database Systems (Chapter 3) Bed-ID Page 15 Bed-ID © Dr. Mark

Problem #8 COP 4710: Database Systems (Chapter 3) Page 16 © Dr. Mark

Solution for Problem #8 PART Part-No Description Location Manufactured ? Purchased ? Quantity-on-Hand MANUFACTURED PART M-Part-No. PURCHASED PART P-Part-No. SUPPLY LINE P-Part-No. Supplier-ID Unit-Price SUPPLIER Supplier-ID Supplier-Name COP 4710: Database Systems (Chapter 3) Page 17 © Dr. Mark