Exercise: More Normalization Review the following tables then answer the questio

Question

Exercise: More Normalization

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

1. All the tables are in 3NF except READERS are in 3NF as READERS has attribute ADDRESS which is not atomic.In other tables all the non primary keys are fully functionally dependent on the primary key and there are no transitive dependencies.

2. If two Authors work on the same Title , the TITLES table should include 2 Authors data.

TITLES (TITLE_ID, PK,NAME1,Author1_ID, FK,NAME2,Author2_ID, FK)

3. Yes,the new table is required as there exists transitive dependency , CITY_ID -> PHONE

READERS (READER_ID, PK,First_Name,Last_Name,ADDRESS(CITY_ID, FK,PHONE)) should be normalized into

READERS (READER_ID, PK,First_Name,Last_Name,CITY_ID FK)

ADDRESS(CITY_ID, PK,FK,PHONE)

4. The referential integrity rule is violated and there exists redundant data in the form of dates . DATE in BORROWING table should match DATE_OF_BORROWING? in HISTORY table.

?One single BORROWING table with a DATE_OF_RETURNING column? is better option.

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.