Are these tables respect the 3NF Database Normalization? Are these tables respect the 3NF Database Normalization? database database

Are these tables respect the 3NF Database Normalization?


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.


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

TITLES table

  • TITLE_ID, PK
  • NAME

TitleAutors table

  • TITLE_ID,
  • AUTHOR_ID

You could change the BORROWING table to have the status of the entry (OUT, IN, MISSING, UNKNOWN) and have a STATUS_DATE.


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

In order to answer this question earnestly, one needs to know the entire set of functional dependencies that govern your data, and you have not provided those.

For your scheme to be in 3NF, for example, it would require that domainID -> titleID, or, in other words, that there is only one title for each domain, and that knowing the domain implies that you can know the title. On the face of it, that seems curious, but the only one who can tell for sure whether or not this is an accurate representation of the business reality that you're dealing with, is you.