DATA MODELLING

Participated this project to develope a data model using the Entity, Relation, Attribute approach to design a relational database model encompassing the needs of the Toronto Public Library as they move towards full automation.

The purpose is to improve patron satisfaction by automating the entire book borrowing process. In particular, full automation will improve the efficiency between library branches.

Developed skills on:

• Implementing the ERA(Entity, Relation, Attribute) approach to design a relational data model
• Interpreting situation descriptions to develop an efficient relational data model
• Creating ER(Entity Relationship) diagrams that illustrate the data model
• Task sharing
• Time management

l. ASSUMPTIONS

1. The design focuses on books only.
2. The library is considering converting to barcode cataloguing. A barcode attribute has been added to the Book.Copy Table in preparation for the conversion.
3. Plans for completing library transactions if the system goes down: rather than rely on Card catalogue (maintenance costs are too great) employees will fill out forms that include library card number, and book id. When the transaction is later input into database, the current date will be overridden by the actual date of the transaction.
4. The book purchase process has been fully automated. Library heads use the Purchase Order Screen to list the books to be purchased for their branch. The White Hot Books list appears as a secondary screen and acts as a reminder to the Head Librarians that they must either select or reject these items.
5. Employees are not treated as a special kind of patron if /when they use library services.
6. Library employees process interloan requests. When the requested book becomes available, a library employee will telephone the patron and inform them that the book is in their branch and that it will be held for three days.
7. The Cutter# that has made up a portion of the Call# for non-fiction books will not be modeled. This identifier is considered redundant data since it can be recreated from the title and author name.
8. The front end programmer is responsible for designing an application that will support our data model, including performing necessary calculations, e.g. dynamically changing the overdue balance when a patron makes a fine payment. This application will use an interface that supports the necessary input and output screens, e.g. book loan and book return.

lI. DESCRIPTION OF TABLES (36 tables)

AUTHOR

This table lists authors by an identification number. This I.D. is used for the AUTHOR/BOOK table to list multiple authors and to cross-reference pseudonyms, nicknames or married names.

AUTHOR SURNAME is labelled N.N., but AUTHOR GIVEN NAMES is not. This is because some people have only one name. Subtleties of multiple surnames and last name first are not dealt with here.

Birth and death dates provide optional information to help separate one author from the next and to provide historical context.

AUTHOR / BOOK

This table represents a many-to-many relationship between the AUTHOR and BOOK entities and allows a book record to indicate multiple authors and an author to have worked on multiple books.

AUTHOR NAME TYPE

Some authors may have pseudonyms or get married and change their names. This table (excerpted here) lists the different kinds of names authors may use. This table is used by the AUTHOR table.

AUTHOR ROLE

Some authors are more precisely an illustrator, co-author, co-editor, editor and so on. This table helps define those roles.

AVAILABLE BOOKS

This is the set of books that central administration indicates it can order. All the columns must be filled in. The DATE OF PUBLICATION column is used particularly when the book is marked P for PENDING PUBLICATION.

AVAILABLE BOOKS -- AUTO-ORDER

This represents the subset of books equivalent to the "White Hot Books" list presently prepared by central administration staff and distributed to branches. These books will automatically be sent to at least some branches unless branches indicate otherwise.

AVAILABILITY CODE

This table lists the short forms for different levels of availability, such as AVAILABLE and PENDING PUBLICATION.

BOOK

This is half the heart of the cataloguing system. It works in conjunction with its dependent, BOOK.COPY.

Many of the fields are optional, mainly because older books in the system may lack precise details about publisher or came out before the ISBN system was developed.

The LOC CARD NUMBER refers to the Library of Congress cataloguing-in-publication system whereby it will catalogue a book even before its released. This allows libraries to refer to its master card information and reduces the time needed to catalogue a book. (Not all books are so catalogued, so this is an optional field.)

The SIZE column has information about the book's height or width, depending on how it will be shelved. (Some shelves will be specifically for oversized books.)

EDITION refers to first, second, third edition and so on.

BOOK.COPY

This contains information specific to the actual copy of a book that a branch has. It's a dependent of BOOK and retains all the attributes of its parent, while adding a COPY primary key to the inherited BOOK key.

The branch that owns the copy and the branch that lends it is distinguished. If a book is not on loan, the LENDING BRANCH I.D. is null. COPY STATUS indicates, for instance, if a book is borrowed, has been destroyed or does not circulate.

The library system is converting to a bar code system for speeding up circulation. However, not all books will be barcoded by the time the cataloguing system is up, so this field is optional.

The FIRST CIRCULATED ON field helps keep track of how often a book is borrowed. This field and the DATE BORROWED field are used to generate the BOOK.COPY CIRCULATION HISTORY table.

BOOK.COPY CIRCULATION HISTORY

This table keeps track of how often a book is borrowed. Library staff review this lending pattern and discard books that are no longer popular.

BOOK.COPY -- FICTION

A subset of BOOK.COPY, it's used to indicate the special call numbers used by fiction books -- a combination of author and library section. For instance, an Asimov book might go in science fiction while a Hammet might end up in the detective section.

BOOK.COPY -- NON-FICTION

Another subset of BOOK.COPY, this table records the Dewey Decimal system call number. The cutter number is not recorded as it contains only redundant information.

BOOK.COPY RECOVERY

This table indicates current status of attempts to recover overdue books.

BOOK.COPY RECOVERY HISTORY

This is a history table listing the various attempts to recover a book in case, for instance, the collector tried more than once to retrieve a book.

BRANCH

This lists the important facts about a branch. Not all branches have fax machines, so this is an optional field. The HEAD LIBRARIAN column uses the EMPLOYEE I.D. key from the EMPLOYEE table. The city is assumed to be TORONTO, hence CITY CODE is not needed.

EMPLOYEE

This table contains most of the important details about an employee. The SUPERVISOR field has a recursive relation to the EMPLOYEE entity via the EMPLOYEE NUMBER key. This field is optional because at least one person (at the very top of the chain) will have no supervisor.

EMPLOYEE -- FULL-TIME

Specifies monthly salary. Also specifies length of time employee has been full-time in case this affects benefits.

EMPLOYEE -- PART-TIME

Specifies hourly wage. Also specifies length of time employee has been part-time in case this affects benefits. (Employee may have switches from full-time to part-time at some point.)

EMPLOYEE POSITION

Matches the EMPLOYEE POSITION I.D. to the name of the position.

LANGUAGE

This table tries to match various possible names for languages (including the name of the language in its own language) to a uniform language code. Used in catalogue search purposes.

LIBRARY SECTION

This table lists the different parts of a library that a fiction book could be placed.

PATRON

This information contains basic information about a patron. Name considerations similar to those in the AUTHOR table were used (for instance, some patrons may use only one name, as occurs in some cultures). TOTAL FINES is derived data but could be omitted if there are no performance issues. A patron is assumed to have an address but not necessarily a telephone number.

PATRON / BOOK

This table does not list patron borrowings (which is recorded under BOOK.COPY) but rather it keeps track of reserve or hold requests on a book. Since it's unpredictable what copy of a book will be the first to be returned, a hold is placed on a title (so to speak) not the actual copy of that title. As more than one patron can place a hold on a book and a patron can place holds on more than one book, this is a many-to-many relationship.

PATRON STATUS

This table keeps track of whether a patron card is valid.

PATRON TYPE

There are presently three categories of patron. This table distinguishes limits and fine policies for each category and matches the type abbreviation to the full type name.

PURCHASE ORDER

This table has the header information for a purchase order. This table is used at the central administrative ordering stage after various branches have initiated branch purchase orders that can be reconciled under one supplier.

PURCHASE ORDER / AVAILABLE ITEM / BRANCH PURCHASE ORDER

This compound-relation table records purchase order items. Because the specifications of this case study indicate purchase orders can mix and match books and branches, three foreign keys are used so that all permutations of central purchase order, item ordering code and branch purchase order can be kept track of.

PURCHASE ORDER -- BRANCH

We assume that branches submit an order for one book title using one branch purchase order.

PURCHASE ORDER STATUS

Indicates how far an order has been processed. Other codes might in this table might include NP for no purchase order returned with order or DD for damaged.

SUBJECT

This matches official subject labels to a subject number. Because one Dewey Decimal number could represent a book with multiple subjects, our relational data model uses a different system.

SUBJECT / BOOK

This many-to-many table accommodates the fact that one book can have many subjects and one subject can be covered in many books.

SUPPLIER I.D.

This table lists important information about suppliers, whether they are vendors, agents or publishers themselves.

SUPPLIER TYPE

This table distinguishes between vendors, agents and publishers.

SUPPLIER / PUBLISHER

For the purposes of our case study, we're assuming that all publishers sell their own books, but agents or vendors may be used for a better price. This table supplies a recursive listing of which suppliers can sell books from which publishers.

TITLE

This table is used to reduce ambiguity in titles and for use in the TITLE / BOOK table.

TITLE / BOOK

This many-to-many table accommodates the fact that two entirely different books can have the same title (such as two different textbooks called CALCULUS) and that a book can have more than one title.

lII. TABLES

AUTHOR

AUTHOR/BOOK

AUTHOR NAME TYPE

AUTHOR ROLE

AVAILABLE BOOKS

AVAILABLE BOOKS -- AUTO-ORDER

AVAILABILITY CODE

BOOK

BOOK (cont.)

BOOK.COPY

BOOK.COPY (cont.)

BOOK.COPY CIRCULATION HISTORY

BOOK.COPY -- FICTION

BOOK.COPY -- NON-FICTION

BOOK.COPY RECOVERY

BOOK.COPY RECOVERY HISTORY

BRANCH

COPY STATUS

EMPLOYEE

EMPLOYEE (cont.)

EMPLOYEE -- FULL-TIME

EMPLOYEE -- PART-TIME

EMPLOYEE POSITION

LANGUAGE

LIBRARY SECTION

PATRON

PATRON (cont.)

PATRON/BOOK

PATRON STATUS

PATRON TYPE

PURCHASE ORDER

PURCHASE ORDER / AVAILABLE ITEM / BRANCH PURCHASE ORDER

PURCHASE ORDER -- BRANCH

PURCHASE ORDER STATUS

RECOVERY STATUS I.D.

SUBJECT

SUBJECT / BOOK

SUPPLIER I.D.

SUPPLIER I.D. (cont.)

SUPPLIER TYPE

SUPPLIER / PUBLISHER

TITLE

TITLE / BOOK