Normalize the following two relations to BCNF. Please use the assumptions listed for the questions to normalize.
Question:
Normalize the following two relations to BCNF. Please use the assumptions listed for the questions to normalize.
Each question is 15 points. It is important to show all steps for normalization to BCNF we discussed in class to get full credit.
- TEXTBOOK (ISBN, Title, Edition, Year, PublisherID, PublisherName, PublisherAddress, AuthorID, AuthorName, AuthorAddress, AuthorEmail)
Assumptions: Every textbook has a unique ISBN. A textbook is written by one author. A textbook has only one publisher. An author can write many books. A publisher can publish many books. The publisher ID is unique for every publisher. The AuthorID is unique for every author)
Step 1:
ISBN > ( Title, Edition, Year, PublisherID, PublisherName, PublisherAddress, AuthorID, AuthorName, AuthorAddress, AuthorEmail)
Step2: Identify functional dependencies
- ISBN-> Title, Edition, Year, PublisherID, PublisherName, PublisherAddress, AuthorID, AuthorName, AuthorAddress, AuthorEmail
- PublisherID-> PublisherName, PublisherAddress
- Author-> AuthorName, AuthorAddress, AuthorEmail
Step3: Is there a determinant that is not a candidate key.
Yes, PublisherID, AuthorID
Final Normalizied Relations
TEXTBOOK ( PublisherID , AuthorID , ISBN, Title, Edition, Year)
PUBLISHER (PublisherID, PublisherName, PublisherAddress, AuthorID )
AUTHOR (AuthorID, AuthorName, AuthorAddress, AuthorEmail)
- APARTMENT (BuildingNumber, ApartmentNumber, NumberOfBedrooms, Rent, BuildingName, BuildingAddress, BuildingManager)
Assumptions: The building number is unique for every building. A building can have many apartments. The same apartment number can repeat in two building, however each building will not have apartments with the same apartment number. The Rent, NumberofBedrooms is associated with an apartment. Every building has a specific building manager.
Step1: Identify all candidate keys
- BuildingNumber and ApartmentNumber
Step2: Identify functional dependencies
- BuildingNumber, ApartmentNumber
- BuildingNumber -> (BuildingName, BuildingAddress, BuildingManager)
- ApartmentNumber -> (NumberOfBedrooms, Rent)
Step3: Is there a determinant that is not a canindate key.
Yes. BuildingNumber, and ApartmentNumber.
Final Normalizied Relations
BUILDING (BuildingNumber, BuildingName, BuildingAddress, BuildingManager, ApartmentNumber )
APARTMENT (ApartmentNumber, NumberOfBedrooms, Rent)
Essentials of Database Management
ISBN: 978-0133405682
1st edition
Authors: Jeffrey A. Hoffer, Heikki Topi, Ramesh Venkataraman