Are these tables respect the 3NF Database Normalization?

Question

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
• TITLE_ID
• DATE_OF_BORROWING

• DATE_OF_RETURNING

  1. Are these tables respect the 3NF Database Normalization?
  2. What if 2 authors work together for the same title?
  3. The column Addresss should have it's own table?
  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 one entry in HISTORY table. Is this a good idea? Do I brake any rule? Should I have instead one single BORROWING table with a DATE_OF_RETURNING column?

Answer 1

This looks a bit like a homework problem, but let me answer anyway:

1. No, the tables are not in 3NF; tables with surrogate keys rarely are. For example, the READERS table has two candidate primary keys: READER_ID and (First_Name, Last_Name). Of course, that depends on your problem domain: if you're willing to have two separate individuals with the same name, address and phone, then it's in 3NF. Also, in my experience, 3NF is usually more trouble than it's worth.
2. Again, that depends on your problem domain. You could create a many-to-many relation between AUTHORS and TITLES through an intermediate table.
3. See 1.
4. You could dispense with BORROWING and create a perfectly working application, as HISTORY contains all information you need. The less information you have to track, the better.