A public library records data about the details of each transaction in one large

Question

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: 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) Take the above structure and break it down into separate, smaller files so as to avoid the problems discussed above.

Explanation / Answer

The importance of Normalizing:

The process of breaking the larger data base table (relation) in to smaller tables is called as the normalization.

With the existing structure, Patron Name, Address, Book ID, Title, Author, Borrow Date, Due date and return date are kept in the same table.

When you add a new patron, only the patron name, and address are the fields that are getting filled – the rest of the fields belonging to the Book and dates are left empty and are consuming storage space. Similarly when you create a new book, only the book id, author, and title are the required fields – the remaining fields stay empty and consume space.

The solution would be break it down into smaller tables and link them with primary key and secondary keys as shown below:

Patron Table

Patron ID (Primary Key)

Patron Name

Patron Address

Book ID (Foreign key referes Books Table)

Books Table:

Book ID (Primary Key)

Author

Title

Issue ID (Foreign key refers Issues Table)

Issues Table:

Issue ID (Primary Key)

Borrow Date

Due Date

Return Date

Now we can have records without causing duplicate rows and redundant memory storage space wasted. Hence it is efficient to normalize the data base tables.

Apart from that, the library information system can include the following functionalities as well:

New Book entry, Book search, Catalog search, catalog creation.

-----

Patron ID (Primary Key)

Patron Name

Patron Address

Book ID (Foreign key referes Books Table)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.