Database concepts

Assessment

Libray Database

The following is a schema for a library system. A borrower can take a book out on loan from a library branch.

Note that there are four tables and are described as:

BORROWER

• Has five fields
• CardNo is the primary key (the borrowers library card)
• The remaining fields are
  • LName (the borrowers last name)
  • FName (the borrowers first name)
  • Suburb (suburb the borrower lives in)
  • Postcode (postcode for that suburb)

BOOK

• Has four fields
• ISBN is the primary key (International Standard Book Number)
• The remaining fields are
  • YearPublished (the year the book was published)
  • PublisherName (the name of the organisation that published the book)

LOAN

• Has five fields
• ISBN, BranchID, CardNo and DateOut are the combined primary key
  • ISBN is also a foreign key which references ISBN in the BOOK table
  • CardNo is also a foreign key which references CardNo in the BORROWER table
  • CardNo is also a foreign key which references BranchID in the BRANCH table
  • DateOut (the date the book was borrowed)
• The remaining field is
  • DateIn (the date the book was returned)

BRANCH

• Has two fields
• BranchID is the primary key (each branch has a unique number)
• The remaining field is
  • BranchName (the city, suburb or town where the branch is located)