Do these 3NF database normalization tables comply?

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 rk
• READER_ID , fk
• TITLE_ID , fk
• DATE

HISTORY table

• READER_ID
• TITLE_ID
• DATE_OF_BORROWING

• DATE_OF_RETURNING

  • Do these 3NF database normalization tables comply?
  • What if 2 authors work together for the same title?
  • Should Addresss columns have their own table?
  • When the reader borrows the book, I make an entry in the BORROWING table. After he returns the book, I will delete this entry and I will make another entry in the HISTORY table. Is that a good idea? Am I braking any rule? Should I instead have one DISEASE table with a DATE_OF_RETURNING column?

Answer 1

This is a bit like a home problem, but let me answer anyway:

• No, tables are not in 3NF; tables with surrogate keys are rare. For example, the READERS table has two candidate primary keys: READER_ID and (First_Name, Last_Name). Of course, it depends on your problem area: if you want to have two individuals with the same name, address and phone, then this is in 3NF. Also, in my experience, 3NF usually has more problems than it's worth.
• Again, this depends on your area of ​​concern. You can create a link between many and many of AUTHORS and TITLES through an intermediate table.
• See 1.
• You can refuse BORROWING and create a perfectly working application, since the HISTORY contains all the information you need. The less information you need to track, the better.

Answer 2

I would change the Titles to MANY-TO-MANY and leave the addresses.

TITLES table

• TITLE_ID , PK
• NAME

TitleAutors table

• TITLE_ID
• AUTHOR_ID

You can modify the BORROWING table to have a record status (OUT, IN, MISSING, UNKNOWN) and have STATUS_DATE.

Answer 3

How do you expect anyone to seriously answer this question without any knowledge of your business domain?

To answer this question sincerely, you need to know the whole set of functional dependencies that manage your data, and you did not provide them.

For example, your 3NF scheme requires that domainID → titleID, or, in other words, there is only one title for each domain, and that knowing the domain means that you can know the title. At first glance, this seems curious, but the only one who can say for sure whether this is an accurate representation of the business reality you are dealing with is you.

Answer 4

Another thought that came to my mind after reading other comments.

• Can an author be a reader? (And vice versa)

If possible, you may have redundant first names and last names entered into your system, and it would be susceptible to update the anomalies. For example, if Jane Smith was a reader and author and married, and her Surname changed to Williams, then it was possible to update the surname Reader, and not her surname.

You will fix this, perhaps by creating a User table in which you have two foreign keys for the table of authors and readers. Just a thought ...;)