Location: [CPSC 333] [Ongoing Examples] [Library Information System] Version One
This page was most recently modified on February 6, 1997
The system is used to keep track of the books available for loan to card holders. Each book has an ISBN number (and no two books have the same ISBN number). In order to answer queries about the books in the library it is necessary for the system to keep track of the title and author of each book as well. Titles and authors are not generally unique. (Detailed information about authors won't be maintained, so you can assume that the ``author'' for a book will be represented by a short character string.)
There may be more than one copy of a book in the library. Each copy of the same book in the library has a different ``copy number.'' In general, a ``copy number'' of a book is a small integer; if there are n copies of some book in the library then these copies have copy numbers from 1 to n inclusive. Each copy of a book might have a different storage location (building and floor number) and the system will be used to maintain this information as well.
The system also stores information about card holders (who are entitled to borrow books from the library). Each card holder has a unique card number, as well as a name (first name, middle initial, and last name), telephone number, and address (first line, second line, city, province, and postal code). Each card holder can have zero or more copies of books out on loan; clearly, a copy of a book can be out on loan to at most one card holder at a time. It is not necessary for this system to maintain information about a loan after the book has been returned to the library. Since the system will be used to produce reminder notices of overdue books it is necessary to remember the due date for each outstanding loan.
This system was considered in several lab exercises.
Name: author
Kind: At
Type: string
Description: Non-key attribute of Book
Name: Book
Kind: E
Type: @ISBN number + title + author
Description: Multiple copies of each book may exist
Name: Book Copy
Kind: WE
Type: @ISBN number + @copy number + building + floor number
Description: Depends on Book
Name: building
Kind: At
Type: string
Description: Non-key attribute of Book Copy
Name: Card Holder
Kind: E
Type: @card number + first name + middle initial + last name + telephone number + first line of address + second line of address + city + province + postal code
Description: Represents people who are entitled to borrow books
Name: card number
Kind: At
Type: integer
Description: Key attribute of Card Holder
Name: city
Kind: At
Type: string
Description: Non-key attribute of Card Holder
Name: copy number
Kind: At
Type: integer
Description: Key attribute of Book Copy; if there are n copies of a book, then the copy numbers used are 1 ... n
Name: due date
Kind: At
Type: date
Description: Non-key attribute of Loan
Name: first line of address
Kind: At
Type: string
Description: Non-key attribute of Card Holder
Name: first name
Kind: At
Type: string
Description: Non-key attribute of Card Holder
Name: floor number
Kind: At
Type: integer
Description: Non-key attribute of Book Copy
Name: ISBN number
Kind: At
Type: integer
Description: Key attribute of Book; should be positive and exactly nine digits long
Name: last name
Kind: At
Type: string
Description: Non-key attribute of Card Holder
Name: Loan
Kind: AO - 1c:Mc Single
Type: @ISBN number + @copy number + card number + due date
Description: Each copy of a book can be on loan to at most one card holder, and each card holder can have zero or more copies of books out on loan
Name: middle initial
Kind: At
Type: character
Description: Non-key attribute of Card Holder
Name: postal code
Kind: At
Type: string
Description: Non-key attribute of Card Holder; should have length six. First, third, and fifth characters should be letters; others should be digits
Name: province
Kind: At
Type: [ `British Columbia' | `Alberta' | `Saskatchewan' | `Manitoba' | `Ontario' | `Quebec' | `Newfoundland' | `Nova Scotia' | `Prince Edward Island' | `New Brunswick' | `Yukon Territory' | `Northwest Territories' ]
Description: Non-key attribute of Card Holder
Name: second line of address
Kind: At
Type: string
Description: Non-key attribute of Card Holder
Name: telephone number
Kind: At
Type: integer
Description: Non-key attribute of Card Holder; should be either seven or ten digits long ...
Name: title
Kind: At
Type: string
Description: Non-key attribute of Book
[Problem Statement] [References in Exercises] [ERD] [Data Dictionary]
Location: [CPSC 333] [Ongoing Examples] [Library Information System] [Version One]