Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Part A: Relational Database Design 1 . Answer questions below regarding the relational database design for the R University book loan management database. Book (

Part A: Relational Database Design
1. Answer questions below regarding the relational database design for the R University book loan management database.
Book (Call_No, Title, AuthorID, Author_Bio, Location, Copy_Barcode, Status)
Semantics of attributes is self-explanatory. Some additional notes are as follows:
-A call number is the combination of letters and numbers that indicates where books can be found in the library.
-A book may have multiple book copies. Each book copy has a unique copy barcode. All copies of a book share same call number.
-A book may have one or more authors and an author can write more than one book. Each author has some description (Author_Bio).
-Status presents the status of a book copy e.g. available, on loan
Answer following questions:
1.1 List all likely functional dependencies on the above relation Book according to the business rules. If there are no functional dependencies among attributes, you must state so. Do not write redundant or trivial functional dependencies (e.g. Call_No ->Call_No). Specify all candidate keys for Book relation and explain your answer.
1.2 Please identify the highest normal form of Book relation. Explain your answer.
1.3 Following results in Question 1.2, if Book relation is not in 3NF, decompose it into relations in 3NF. Merge relations with a common primary key and remove any redundant subset relations. Check if the merged relations are in 3NF, and apply further decomposition if needed until all final relations are in 3NF. Write down the final relational database schema and indicate the primary key (underlined) and foreign key(s)(with an asterisk*) in each relation.
Important:final schema in Question 1.3 show the workings of FDs (Question 1.1) and normal form reasoning (Questions 1.2).
2. Given the following relation for real estate sales:RealEstate (PropertyID, Address, AgentName, Price, Commission, AgentPhone, CustomerName, CustomerPhone, SalesTax)
and the followingFUNCTIONAL DEPENDENCIES: PropertyID ->Address, Price
AgentName ->AgentPhone
Price ->SalesTax
CustomerName ->CustomerPhone
AgentName, Price ->Commission
Answer following questions: 2.1 Find All Candidate Keys of the RealEstate relation. Explain your answer.
2.2 Please identify the highest normal form of RealEstate relation. Explain your answer.
2.3 Decompose the RealEstate relation into 3NF relations if it is not in 3NF already. Merge relations with a common primary key and remove any redundant subset relations. Check if the merged relations are in 3NF, and apply further decomposition if needed until all final relations are in 3NF. Write down the final relational database schema and indicate the primary key (underlined) and foreign key(s)(with an asterisk*) in each relation.
Important: SHOW final schema in Question 2.3 if you do not show the workings of FDs (Question 2.1) and normal form reasoning (Questions 2.2).
please provide corr

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals Of Database Systems

Authors: Ramez Elmasri, Sham Navathe

4th Edition

0321122267, 978-0321122261

More Books

Students also viewed these Databases questions

Question

f. Did they change their names? For what reasons?

Answered: 1 week ago