PRACTICE DATABASE DESIGN PROBLEM Imagine that your town libr

PRACTICE DATABASE DESIGN PROBLEM

Imagine that your town library wants to keep track of its business in a database, and that you have been

called in to build the database. You talk to the town librarian, review the old paper-based records, and watch

people use the library for a few days. You learn the following about the library:

1. Any resident of the town can get a library card simply by asking for one. The library considers

each cardholder a member of the library.

2. The librarian wants to be able to contact members by telephone and by mail. She calls

members when their books are overdue or when requested materials become available. She likes to

mail a thank-you note to each patron on his or her anniversary of becoming a member of the

library. Without a database, contacting members efficiently can be difficult; for example,

multiple members can have the same name. Also, a parent and a child might have the same first and

last name, live at the same address, and share a phone.

3. The librarian tries to keep track of each member

s reading interests. When new books come in,

the librarian alerts members whose interests match those books. For example, long-time

member Sue Doaks is interested in reading Western novels, growing orchids, and baking bread.

There must be some way to match her interests with available books. One complication is that,

although the librarian wants to track all of a member

’

s reading interests, she wants to classify

each book as being in just one category of interest. For example, the classic gardening book

Orchids of France would be classified as a book about orchids or a book about France, but not

both.

4. The library stocks thousands of books. Each book has a title and any number of authors. Also,

more than one book in the library might have the same title. Similarly, multiple authors might

have the same name.

5. A writer could be the author of more than one book.

6. A book will be checked out repeatedly as time goes on. For example, Orchids of France could

be checked out by one member in March, by another member in July, and by another member

in September.

7. The library must be able to identify whether a book is checked out.

8. A member can check out any number of books in one visit. Also, a member might visit the

library more than once a day to check out books.

9. All books that are checked out are due back in two weeks, with no exceptions. The late fee is

50 cents per day. The librarian would like to have an automated way of generating an overdue

book list each day so she can telephone offending members.

10. The library has a number of employees. Each employee has a job title. The librarian is paid a

salary, but other employees are paid by the hour. Employees clock in and out each day.

Assume that all employees work only one shift per day and that all are paid weekly. Pay is

deposited directly into an employee

’

no checks are hand-delivered. The

database needs to include the librarian and all other employees.

’

s checking account

—

s database, following the rules set forth in this tutorial. Your instructor will specify the

format of your work. Here are a few hints in the form of questions:

Design the library

’

A book can have more than one author. An author can write more than one book. How would

you describe the relationship between books and authors?

The library lends books for free, of course. If you were to think of checking out a book as a sales

transaction for zero revenue, how would you handle the library

’

s revenue-generating event?

A member can borrow any number of books at one checkout. A book can be checked out more

than once. How would you describe the relationship between checkouts and books?

Solution

Designing the database for a library is good plan. After reviewing the entire article it is easily sure that library has not been handled effectively which means old tradition is being used. Most of the work a librarian is doing manual work and it may not be effective and it is time consuming task. When the database is getting designed the first thing they are supposed to do is create the data dump which should be continue the important details of the Library. The data should include the details of employees who are working for, how many books are available, which author has how many books or how many books are there for each author and book name and category. This will easily reduce the problem. As mentioned in the question itself a book can have more than one author and an author can have more than one book, to ease out the problem a database should be created in such a way that each author should have a list of books and the categories. The moment you pull the details of author ABC the entire list of books should appear along with the category. In the similar fashion the books need to be arranged in the library. If you take an example of book for Yoga, there will be numerous people who will write books on yoga and interpreted by different names. It becomes important for to discriminate the kind of books and the real authors of books. There are many people who will be fans of different authors and different subjects too. Hence it becomes extremely important to differentiate the books by authors and category of the books.

Yes, of course the books will be lend for free from Library. If you want to see the checkout of books like a sales transaction. Each book when it is checked out, the librarian should collect like $5 as an advance and update the system as checked out and update the amount in the system. Similarly when the book is checked in updated the system as checked in and return the amount. This way it can showcase as a sales transaction and also you can implement the stock in stock out mechanism.

A member can borrow any number of books and one book can be borrowed more than once. When the database is designed it becomes utmost important to have a customer list where every time a book is checked out the librarian will have to enter the details of how many books a customer has checked out or how many customers have used the borrowed the same book. An updated list needs to be maintained in the database, this is very helpful to maintain a tracker of which book is with which customer and which customer has carried out which books and how many books one individual has carried. Also when a librarian wants to search for any book, it becomes easy to track if the particular book is available in store or it is checked out by somebody else.