You are designing a database for a library system with the following requirements:
Each Book has a unique ISBN, Title, and Author.
Members can borrow multiple Books, but each Borrow Transaction must record the Date Borrowed, Due Date, and Return Date.
Which of the following is the best approach to design this schema in 3NF?

Question

You are designing a database for a library system with the following requirements:

Members can borrow multiple Books, but each Borrow Transaction must record the Date Borrowed, Due Date, and Return Date.

Which of the following is the best approach to design this schema in 3NF?

Accepted Answer

Use three tables:  Books (ISBN, Title, Author) ,  Members (MemberID, Name) , and  Borrows (MemberID, ISBN, BorrowDate, DueDate, ReturnDate) .

Answer

Create a table with a composite key  (ISBN, MemberID)  and add  BorrowDate  and  DueDate  as attributes.

Answer

Store  Author  and  Title  directly in the  Borrows  table to avoid  joins .

Answer

Create one table with all information:  (ISBN, Title, Author, MemberID, BorrowDate, DueDate, ReturnDate) .

Crowdly