DATABASE DESIGN PROBLEM. NOTE*: I have to help someone who is not physically her
ID: 3722055 • Letter: D
Question
DATABASE DESIGN PROBLEM.
NOTE*: I have to help someone who is not physically here finish his assignment, but I've never learned anything about database. Show me the right way please. Do you have to use SQL to solve this problem? Read carefully and full answers will be greatly rated. thanks.
Explore the data included in Table 4-9. Assume that the primary key of this relation consists of two components: Author's ID (AID) and Book number (BNbr). The relation includes data regarding authors, books, and publishers. In addition, it tells what an individual author's per book royalty amount is in the case of multi- authored books. Your task is to: a. Identify the functional dependencies between the attributes. b. Identify the normal form in which the relation currently is. c. Identify the errors in the data that have been made possible by its poor structural characteristics. d. Take the actions (if any) necessary to convert the r elation into the third normal form. Identify all intermediate steps.Explanation / Answer
a. Functional Dependencies
AID -> ALname,AFname,AInst
BNbr -> BName,BPublish,BPrice
AID,BNbr -> PubCity,AuthBRoy
b
The relation is currently is in Unnormalized. It is not even in 1NF as there are non atomic attributes.
c. Anomalies
Insertion Anomaly
An new Author cannot be inserted without inserting the Book information .
Deletion Anomaly
If we delete information about a book ,the information about the author will also get deleted.
Updation anomaly
The information needs to be updated at multiple places due to redundancy. Otherwise inconsistency occurs.
d. 3NF
based on functional dependencies, following relations need to be used :
Author(AID , ALname,AFname,AInst)
Book(BNbr , BName,BPublish,BPrice)
BookAuthor(AID,BNbr , PubCity,AuthBRoy)
underlined are primary keys and italicised are foreign keys.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.