lab5

COSC 304 - Introduction to Database Systems
Lab 5: Converting ER/UML diagrams into the relational model

Example #1

Convert the fish store ER diagram into the relational model:

Answer:

Fish (id, name, color, weight, tankNumber, speciesId)  // tankNumber is a FK to Tank, speciesId is a FK to Species
Species (id, name, preferredFood)
Tank (number, name, volume, color)
Event (fishId, eventDate, note) // fishId is a FK to Fish

Example #2

Convert the ER diagram for hotel reservation system into the relational model:

Answer:

Hotel (name, streetNumber, streetName, city, state, postalCode, webAddress, phoneNumber)
Floor (hotelName, number) // hotelName is a FK to Hotel
Room (hotelName, floorNumber, identifier, numBeds, isSmokingRoom) 
   // (hotelName, floorNumber) is a FK to Floor ; hotelName is a FK to Hotel
Customer (id, firstName, middleName, lastName, address, city, state, country, postalCode, homePhoneNumber, workPhoneNumber, cellPhoneNumber, emailAddress)
Reservation (hotelName, customerId, arrivalDate, departDate, smokingRoom, numBeds, highOrLowFloor, creditCardType, creditCardNum, creditCardExpiry, roomIdentifier)
   // hotelName is a FK to Hotel ; customerId is a FK to Customer ; (hotelName, roomIdentifier) is a FK to Room
Invoice (id, hotelName, customerId, arrivalDate, openDate, closeDate, totalBalance) 
   // (hotelName,customerId,arrivalDate) is FK to Reservation
InvoiceCharge (invoiceId, transactionId, chargeType, description, amount) // InvoiceId is a FK to Invoice

Lab 5 Assignment