1. In our design of the database, we need store each participant\'s customerlD,
ID: 3698456 • Letter: 1
Question
1. In our design of the database, we need store each participant's customerlD, name, and contact info along with the reservation. Also, we put the tripID, trip date, price. Assume that for the same trip, its price and fees are fixed (the same in all reservations). To fulfill this design the following reservation table is designed (ReservationID is the primary key) Reservation (ReservationID, TripID, Trip Date, Price, Fees, (CustomerlD, Last Name, FirstName phone)) Example Reservation table: ReservationID TripID TripDate Price Fees CustomerID LastName FirstName Phone 1600001 Northfold Liam Ocean Northfold Liam Kasuma Sujata Chau Brown 21 3/12/17 $56 $20 101 102 558-8432 435-9834 558-8432 787-3224 Clement 887-4463 Brianne 442-5543 Arnold 6/18/17$80$15101 21 4/3/17 $56 $20 103 119 126 1600002 32 1600003 Please normalize the Reservation table into 3rd normal formExplanation / Answer
Answer is as follows :
With given data We will get following functional dependency which are as follows :
ReservationId is Primary Key here but the dependencies are :
TripId -> TripDate , Price, Fees
ReservationId, TripId -> CustomerId
CustomerId -> LastName, FirstName , Phone
But these dependecies are in 1NF.
So for converting it into 3NF first we generate candidate key for generated functional dependency :
(ReceptionId , TripId, CustomerId ).
So for converting this to 3NF first we convert this to 2NF.
For 2NF, we remove all the partial dependecies and generate with candidate key :
ReceptionId , TripId, CustomerId -> Tripid , TripDate,Price,Fees
ReceptionId , TripId, CustomerId -> LastName , FirstName, Phone
ReceptionistId, TripId -> CustomerId.
Now the dependecies are comes in 2NF.
Now convert these to 3NF :
For converting this to 3NF we remove transitive dependecny.
After removing Transitive Dependency we get the following dependency :
ReceptionId , TripId, CustomerId -> Tripid , TripDate,Price,Fees
ReceptionId , TripId, CustomerId -> LastName , FirstName, Phone
ReceptionistId, TripId -> CustomerId(is removed due to transitive functional dependency).
So the relational schema in 3NF is :
(ReceptionId , TripId, CustomerId ,Tripid , TripDate,Price,Fees)
(ReceptionId , TripId, CustomerId , LastName , FirstName, Phone)
The bold letters in schema is candidate / super key.
if there is any query please ask in comments..
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.