LMS Library Management System Schema
The tables are owned by the user LMS. For example, to select author data, you would run: SELECT * FROM LMS.AUTHORS;
AUTHORS (an author may exist without a corresponding book)
Column
Comments
Au_ID
Primary Key
Au_LName
Au_FName
Phone
Address
City
State
Country
PostalCode
BOOKS_AUTHORS
BOOKS (Subclass of Library_Items)
ISSUE_DETAILS
Column
Comments
Book_ID
Primary Key, Foreign Key into Books
Au_ID
Primary Key, Foreign Key into Authors
Column
Comments
Book_ID
Primary Key, Foreign Key into Library_Items
Genre
Such as Fiction, Literature, Classics, etc.
Edition
Column
Comments
Issue_Detail_ID
Primary Key
Item_Copy_ID
Foreign Key into Library_Item_Copies
Member_ID
Foreign Key into Members
Issue_Date
Due_Date
Return_Date
Comments
JOURNALS (Subclass of Library_Items)
Column
Comments
Journal_ID
Primary Key, Foreign Key into Library_Items
Volume
Volume number
Issue
Issue within a volume
Journal_Editor
Publish_Date
MIS531 ý ABOR
LIBRARY_ITEMS
Column
Comments
Lib_Item_ID
Primary Key
Title
Title of the Item
Item_Type
Magazine, journal or book
No_Of_Pages
Number of pages in the book
Standard_Price
Listed Retail Price of the book
Pub_ID
Foreign Key into Publishers (for Journals, Magazines and Books)
LIBRARY_ITEM_COPIES
Column
Comments
Item_Copy_ID
Primary Key
Date_Of_Purchase
Date of Purchase by the Library
Purchase_Price
Price at which the item was purchased
Is_Bound
If the item is hard-bound or not (Y/N)
Is_Issued
Whether the item is currently issued or not (Y/N)
Lib_Item_ID
Foreign Key into Library_Items
MAGAZINES (Subclass of Library_Items)
MEMBERS
Column
Comments
Magazine_ID
Primary Key, Foreign Key into Library_Items
Edition
Type
Such as Fashion, Sports, Business, etc.
Column
Comments
Member_ID
Primary Key
First_Name
Last_Name
Member_Since
Expiry_Date
Expiration of membership
No_Of_Items_Issued
Number of library item copies (books, magazines, etc.) currently with a member (note: this is periodically updated and may not be accurate)
Address_1
Address_2
City
State
Zip_Code
Phone_Num
Phone number
Balance
A negative balance means they owe us money (e.g., due to fines). This is updated when the member returns a book late and also when they pay a fine
PUBLISHERS
Column
Comments
Pub_ID
Primary Key
Pub_Name
City
State
MIS531 ý ABOR