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
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,
- 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.
personid, borrowdate, duedate, returndate)
firstname, middlename, lastname)
(bookdescid, title, subtitle, edition, voltitle, volnumber, language,
place, year, isbn, dewey, subjectid)
firstname, middlename, lastname, parentname, address, city, zipcode,
phonenumber, emailaddress, property, studentno, idcardno)
Use the Postgres database to find out the
domains of the attributes and to see sample data.
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.