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.

1. 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

1. ISBN-> Title, Edition, Year, PublisherID, PublisherName, PublisherAddress, AuthorID, AuthorName, AuthorAddress, AuthorEmail
2. PublisherID-> PublisherName, PublisherAddress
3. 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)

2. 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

1. BuildingNumber and ApartmentNumber

Step2: Identify functional dependencies

1. BuildingNumber, ApartmentNumber
2. BuildingNumber -> (BuildingName, BuildingAddress, BuildingManager)
3. 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)