IE 304 Production and Service Information Systems

You have volunteered to design a database for your local library. Each title carried by the library is identified by a unique "ISBN" code (you will see these numbers on your textbooks). You will also assign a unique code to each author represented in the library's collection (some have similar or identical names). Some "authors" are companies and some do not have traditional Western names, so you will not divide author names into a "first" and "last" part. Some books have more than one author. Sometimes the order in which authors are listed is important, so that information also needs to be stored in some way. For each author, you want to store the following, when applicable: date of birth, date of death, and the country in which they were born.

A few of the authors are local and occasionally come to the library for readings, book signings, and so forth. For these authors, you keep contact information including postal address, e-mail address, and phone number.

Each book has a single publisher. You assign a unique code to each publisher, and want to store the publisher's name and contact information. For each book, you want to store its publication date.

You have more than one copy of some books. Each copy is identified by a unique bar code number. For each copy, you want to store the date it was acquired, and whether it was acquired used or new.

You have a large number of borrowers, each identified by a unique library card number. For each borrower, you keep contact information, including address, phone number, and e-mail address. You want to keep track of all past and present borrowing, including borrow date and return date (return date does not apply to books currently on loan), and the number of times the loan has been renewed.

Draw an entity-relationship diagram and write a design outline of the database. Assume you do have access to a zip code table.