The city library of Batu Pahat has a database to keep track of its books, the people who borrow books and who has borrowed which books. The tables with the data are included below: • A table called BOOK, which contains data about the books. It has the attributes book title, ISBN, number of book copies (which is used to separate different copies of the same book), year of publication and author. • A table called BORROWER, which contains data about the persons who can borrow books. It has the attributes borrower id, borrower name, address, phone number, and the number of books that this person has borrowed at the moment. • A table called BORROW, where the borrow details are stored. It has the attributes book title, number of book copies, borrower id, date (which is the date when the book was borrowed), and borrower name. (For well-known reasons, there are very few books in the library.) Unfortunately, the design of the database is not very good. Your mission is to analyze the problems by doing a normalization process. Make sure that you follow all the following: • Identify all the attributes for each table: o BOOK (at least 7 attributes), BORROWER (at least 8 attributes) and BORROW (at least 6 attributes) • Identify the database dependencies (transitive dependencies and partial dependencies) • Identify all the primary keys, foreign keys, candidate keys and composite keys • Describe and illustrate the process of normalizing the data to first normal form (1NF), second normal form (2NF) and third normal form (3NF).
The city library of Batu Pahat has a
borrow books and who has borrowed which books. The tables with the data are included
below:
• A table called BOOK, which contains data about the books. It has the attributes
book title, ISBN, number of book copies (which is used to separate different copies
of the same book), year of publication and author.
• A table called BORROWER, which contains data about the persons who can
borrow books. It has the attributes borrower id, borrower name, address, phone
number, and the number of books that this person has borrowed at the moment.
• A table called BORROW, where the borrow details are stored. It has the attributes
book title, number of book copies, borrower id, date (which is the date when the
book was borrowed), and borrower name.
(For well-known reasons, there are very few books in the library.)
Unfortunately, the design of the database is not very good. Your mission is to analyze
the problems by doing a normalization process. Make sure that you follow all the
following:
• Identify all the attributes for each table:
o BOOK (at least 7 attributes), BORROWER (at least 8 attributes) and BORROW
(at least 6 attributes)
• Identify the database dependencies (transitive dependencies and partial
dependencies)
• Identify all the primary keys, foreign keys, candidate keys and composite keys
• Describe and illustrate the process of normalizing the data to first normal form (1NF),
second normal form (2NF) and third normal form (3NF).
Step by step
Solved in 7 steps
