CS 386 Introduction to Database Systems

LibraryDB Database Description

---

Librarydb is a database that keeps track of information concerning the books and their circulation in an imaginary departmental library. The data that populates the database are artificially constructed and by no means correspond to actual real world data (this is a disclaimer :).

LibraryDB consists of the following tables:

• Person, which keeps tracks of the people who borrow books from the library. The attributes contain personal and contact information (e.g. first and last name, e-mail, etc.).
• Author, which keeps track of personal information about authors (first, middle, last names).
• Publisher, which keeps track of publishers (their full name).
• Subject, which keeps track of possible subjects (e.g. databases, mathematics etc.).
• Book_Description, which contains information (title, subtitle, language etc.) about the books that are available in the library. Every book can have one or more physical copies in the database and has one subject. It can have one or more authors and it is related to one or more publishers.
• Book, which keeps track of the physical books that exist in the library. (For example, the library may have two copies of the book called: 'Database Management Systems').
• Action, which keeps track of the check-ins and check-outs of the books. Every action is done by only one person and refers to an action of checking-in or checking-out books. If there is no return date, it means the book has been checked out but not returned.
• RelAuth, which associates book_descriptions with authors. A book may be associated with several authors and an author may be associated with several books. There is also an attribute 'Role' that specifies the role of the author for the book (author, editor, translator, etc.).
• RelPub, which associates Publishers with book_descriptions. There is an attribute 'Role' here too.
• BorrowRel, which associates physical books with a transaction (either check-in or check-out of the book).

Here are the tables in the LibraryDB, with keys underlined.

· Action (transactionid, personid, borrowdate, duedate, returndate)

· Author(authorid, firstname, middlename, lastname)

· Book (bookid, bookdescid)

· Book_description (bookdescid, title, subtitle, edition, voltitle, volnumber, language, place, year, isbn, dewey, subjectid)

· Borrowrel (transactionid, bookid)

· Person (personid, firstname, middlename, lastname, parentname, address, city, zipcode, phonenumber, emailaddress, property, studentno, idcardno)

· Publisher (publisherid, publisherfullname)

· Relauth (bookdescid, authorid, role)

· Relpub (bookdescid, publisherid, role)

· Subject (subjectid, subjecttype)

Use the Postgres database to find out the domains of the attributes and to see sample data.

Click here to see a diagram representing the LibraryDB schema. Note that boxes represent relation schemas, e.g., the Person box is the Person table, and items within a box represent attributes. Also, lines between boxes represent foreign keys, and underlined attributes make up the primary key for the table.