Scribd
Upload
120 views2 pages

Tutorial2 - ER To Relational Model

This document provides the entity-relationship (ER) diagram for a sample super rent company translated to initial relational database tables. It identifies tables for Customer, ClubMember, Branch, VehicleType, Vehicle, Reservation, RentalAgreement, and TimePeriod. The document notes that the tables represent most but not all of the entity sets and asks how to complete the translation by following the ER diagram to tables process.

Uploaded by

John Gong
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
120 views2 pages

Tutorial2 - ER To Relational Model

This document provides the entity-relationship (ER) diagram for a sample super rent company translated to initial relational database tables. It identifies tables for Customer, ClubMember, Branch, VehicleType, Vehicle, Reservation, RentalAgreement, and TimePeriod. The document notes that the tables represent most but not all of the entity sets and asks how to complete the translation by following the ER diagram to tables process.

Uploaded by

John Gong
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CPSC 304

Tutorial 2 Translation of ER to Relational Model


Tutorial Goal: Translate ER Model to Relational model, with appropriate
keys, foreign keys and other constraints

Below is the sample ERD solution for the Super Rent.

In translating this diagram to tables, we have identified the following tables so far, which
represent most (but not all) of the entity sets. Start with the given tables and complete the
translation by following the ER Diagram-to-Tables process.
You may create new tables, add attributes to the given tables or combine tables to complete
the translation.
Tables discovered so far:
Customer(dlicense, phone, name, addr)
Primary key: dlicense
Alternate key: (phone, name)
Note: After discussions with the company we realized that they also identify each customer
by the phone and name. So, we state it that as an alternate key for this table.
ClubMember(dlicense, points, fees)
Primary key: dlicense
Foreign key: dlicense references Customer
Branch(location, city)
Primary key: location, city
VehicleType(vtname, features, wrate, drate, hrate, krate, wirate, dirate, hirate)
Primary key: vtname
Vehicle(vlicense, initprice, odometer, year, status, forRentFlag)
Primary key: vlicense
Comments: We decided to use a forRentFlag to tell if a vehicle is available for renting. Do
we need any other table for the vehicles?
Reservation(confNo, fromDate, fromTime, toDate, toTime)
Primary key: confNo
Foreign key: (fromDate, fromTime, toDate, toTime) references TimePeriod
RentalAgreement(rentId, cardNo, expDate, odometer, rentedfromDate, rentedfromTime,
rentedtoDate, rentedtoTime)
Primary key: rentId
Foreign key: (rentedfromDate, rentedfromTime, rentedtoDate, rentedtoTime) references
TimePeriod
TimePeriod(fromDate, fromTime, toDate, toTime)
Primary key : (fromDate, fromTime, toDate, toTime)

You might also like