A library allows students to check out books for a limited period of time. The l

Question

A library allows students to check out books for a limited period of time. The librarian records various data whenever a student borrows a book. The loan period for a book may vary from book to book and, for the same book, from student to student depending on their ID (depending on if a student needs it for a certain class or not). For each transaction record, the library also documents which department the student belongs to and who is the head of that department. If the book was returned, the return date is documented. If a book is out and has not been returned yet, value 01-01-1900 is stored in that field. If the library has more than one copy of the same book, it will assign different ID numbers for each copy. Assume each book has only one edition and is uniquely identified by title and author.

The library is interested in using the database in order to monitor which books get checked out most frequently/least frequently, send reminders, send fines for overdue books, etc..

Here is a table for the library: http://i.imgur.com/zpFwy.jpg
-------------
a. What are some shortcomings (redundancies, anomalies) of the current design of the library database? Examples?

b. What is an example of a primary key in association with the table.

c. What are the entities in this database? What type of relationship is between them?

d. Suggest a normalized design (perhaps by dividing the database into multiple tables) that will overcome the shortcomings identified. Make sure to identify the primary key in each table.

I'm not sure if getting answer to something like this is possible, but might as well give it a shot.

Explanation / Answer

(a)

In such a large tables, problems occur while adding or making changes to the data. When a record is to be added, all the column data is to be added. Similarly, updating a description column for a single part requires a change in thousands of rows.

(b)

Stu_ID can be considered as an example of the primary key in the table. The student ID remains unique and its value cannot be null. So it can be considered as a primary key

(c)

The entities are: Student, Department, Book, Author, publisher

There exist many to one relation between student and Department. Many students can belong to one Department, but one student can belong to only one department.

One-to-many relation between Student and book and publisher and book

Many-to-many relation Book and Author

(d)

Student (Stu_ID, Stu_Name, Stu_Dep);

Department (Dept_name, Dept_head);

Book (Book_ID, Book_Title, Book_Author, Borrow_Date, Due_Date, Return_date)

Author (Book_Title, Book_Author, Author_State)

Publisher (Book_ID, Pub_Year, Publisher, publi_Sate)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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