Library Data Case Study

This case study details the design of a database for a public library. The initial, unnormalized relation is broken down into normalized relations: Patron, Book, Author, Book_author, and Rent. Primary and foreign keys are identified. The solution includes an ER diagram, a data structure diagram, and a list of relevant YouTube videos covering normalization, relations, ER diagrams, Visio, and keys. The case study aims to improve library management by transitioning from a manual system to a DBMS, reducing errors, redundancy, and potential book theft.

A Library Data Case Study
STUDENT NAME:
2 November, 2016

Contents
DATABASE STORY..............................................................................................................................................2
RELATIONS............................................................................................................................................................2
ER DIAGRAM.........................................................................................................................................................3
DATA STRUCTURE DIAGRAM..........................................................................................................................4
YOUTUBE VIDEO LIST........................................................................................................................................4
1 | P a g e

DATABASE STORY
The database design is about a public library. It has been designed to manage the information
about the library, its books and the rent information in the library. Earlier they has the book
keeping system in which the data was on paper and it was difficult to manage the records
manually. Hence, the library decided to have its own DBMS. The database will have multiple
tables which will hold the information about the people who rent the books, the books present
in the library and their authors etc. This will make the entire management easier to implement
and handle. This will avoid errors of book keeping, redundancy in the data and theft of the
books. Moreover there will be database files for the records to all the past information and
add up the growing rent records.
RELATIONS
Relation as per the original data in the assignment:
Library (PatronName , HomePhone#, LibraryCard #, Call#, DueDate, BookName,
AuthorName)
Relations after breakdown of the original relation and addition of primary and foreign keys
are:
1. Patron(LibraryCard #, PatronName, HomePhone#)
2. Book (book_id, bookname)
3. Author (author_id, authorname)
4. Book_author(book_id, author_id)
5. Rent(LibraryCard #, Call#, DueDate, book_id)
Note: italics – foreign key and underline – primary key.
2 | P a g e

ER DIAGRAM
The ER-diagram for the above relations is as follows:
3 | P a g e

DATA STRUCTURE DIAGRAM
The data structure diagram is as follows:
YOUTUBE VIDEO LIST
1. Normalization: https://www.youtube.com/watch?v=UrYLYV7WSHM
2. Relations: https://www.youtube.com/watch?v=NvrpuBAMddw
3. ER-diagram: https://www.youtube.com/watch?v=mQ4D0drMrYI
4. Visio: https://www.youtube.com/watch?v=sIX80pvJnMY
5. Keys: https://www.youtube.com/watch?v=FCtGbrou5DM
4 | P a g e