How many tables?
The situation
A college library keeps everything in one table:
Loan(issue_id, book_isbn, book_title, author_name, author_country,
member_id, member_name, member_dept, issue_date, due_date)
What is true about the data:
issue_idis unique for every loan- one ISBN is always one title, by one author
- one author has one country
- one member has one name and one department
- the same book can be borrowed many times, by different members
The librarian reports three problems. Corrections to an author's country have to be made in many rows. A newly acquired book that nobody has borrowed cannot be recorded at all. And when the last loan of a book is deleted, the book's details disappear from the database.
You decompose the table into 3NF, keeping every fact and every dependency.
How many tables do you end up with, and what are they?