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 {PK}, name, color, weight, tankNumber, speciesId)  // tankNumber is a FK to Tank, speciesId is a FK to Species
Species (id {PK}, name, preferredFood)
Tank (number {PK}, name, volume, color)
Event (fishId {PK}, eventDate {PK}, note) // fishId is a FK to Fish

Example #2

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

Answer:

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

Lab 5 Assignment