Review the following tables then answer the questions at the end of the assignme
ID: 3735930 • Letter: R
Question
Review the following tables then answer the questions at the end of the assignment.
AUTHOR table
Author_ID, PK
First_Name
Last_Name
TITLES table
TITLE_ID, PK
NAME
Author_ID, FK
DOMAIN table
DOMAIN_ID, PK
NAME
TITLE_ID, FK
READERS table
READER_ID, PK
First_Name
Last_Name
ADDRESS
CITY_ID, FK
PHONE
CITY table
CITY_ID, PK
NAME
BORROWING table
BORROWING_ID,PK
READER_ID, FK
TITLE_ID, FK
DATE
HISTORY table
READER_ID,FK,PK
TITLE_ID,FK,PK
DATE_OF_BORROWING
DATE_OF_RETURNING
Are these tables in 3rd Normal Form (3NF)? Why or why not?
What if two authors work together on the same title?
Should the column ADDRESS in the READERS table have its own table? Why or why not?
When a reader borrows a book, I make an entry in BORROWING table. After he returns the book, I delete that entry and I make another entry in the HISTORY table. Is this a good idea? Do I break any rules? If so, which ones? Should I have instead one single BORROWING table with a DATE_OF_RETURNING column? Why or why not?
Explanation / Answer
Below is the relational schema of the tables listed above the primary and foreign key columns are highlighted in bolder text
Answer for Questions are
Question 1
Are these tables in 3rd Normal Form (3NF)? Why or why not?
Answer
Yes the tables that listed are in the third normal form 3NF because
The explanation of three normalizations are given below
1NF
2NF
e.g, The author table columns and Title table columns connot be merged because
the First_Name in the Author table don't be to related with Title table Title_Id
3NF
For e.g, for transitive dependency is the dependency with city, state and address with zip code and they are unbreakable
Question 2
What if two authors work together on the same title?
Answer
As per the table design two authors cannot be worked on the same title, but we can make it to work by removing author_id column from Title table and by adding new table which has only primary key columns of Author and Title tables.
Author_Title(Author_Id, Title_Id)
Question 3
Should the column ADDRESS in the READERS table have its own table? Why or why not?
Answer
No not really required to move the address to a seperate column as it is the single column that only holds the reader's address and directly dependent to the Primary Key READER_ID.
If the address is splitted into State, City, ZipCode then it is required to move it in seperate tabl, because both the state and city depends on zipcode which makes it as transitive dependency.
Question 4
When a reader borrows a book, I make an entry in BORROWING table. After he returns the book, I delete that entry and I make another entry in the HISTORY table. Is this a good idea? Do I break any rules? If so, which ones? Should I have instead one single BORROWING table with a DATE_OF_RETURNING column? Why or why not?
Answer
Its better to add a new column Date_of_Returning in the Borrowing table, and we can remove the History table as the history can be maintained easily by using Date and Date_of_Returning columns in Borrowing table
Why because all the column that present in History table are already present in Borrowing table, so there is no real use of History table in this situation
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.