Database Assignment: Separating Apples from Oranges

A public library records data about the details of each transaction in one large file, consisting of Patron Name, Patron Address, Book ID, Book Title, Book Author, Borrow Date, Due Date, and Return Date. No separate record is kept of patrons, books, and book borrowings other than in this file. As shown below, when a book is to be checked out, the various details of that borrowing are recorded in this file as a new row:

1. With this file structure, what problems is the library liable to run into in terms of conducting day-to-day transactions or retrieving information? Specifically:
   • adding a new record (such as of a new book just purchased)
   • modifying an existing record (such as changing a patron’s address)
   • deleting a record (such as of a patron who has discontinued membership)
   • querying a record (such as whether the collection comprises a certain book)
2. Take the above structure and break it down into separate, smaller files so as to avoid the problems discussed above.