UTS Library Database

1
UTS Library Database

• Ankur Kandikatla
• HD Assignment 3, Autumn 2007

2
UTS Library Database

• This database aims to model the UTS library
  system, with simplification
• Has information about borrowers, their
  memberships, library branches , staff and books
• Particular focus on the transaction aspect of
  database, i.e. checking out of books

3
UTS Library Database ERD
4
1m Relationship
Catalogue Books
Book
Catalogue
Foreign key
CatalogueNo Title Year Other columns
005.74 KORT (ED.5) Database System Concepts 2006
005.7585 WELS Everyday Oracle DBA 2006

CatalogueNo BookID BorrowType
005.74 KORT (ED.5) 1 Standard
005.74 KORT (ED.5) 2 Standard
005.74 KORT (ED.5) 3 7 Day Loan
005.7585 WELS 1 Closed Reserve
5
mm Relationship
CheckOut-Books
CheckOut
CheckOutNo DateOut BorowerID
1 1-Apr-2007 3
2 3-Mar-2007 2
3 6-May-2007 4
4 18-May-2007 6
BorrowItem
mm relationship
CatalogueNo BookID CheckOutNo DateReturn
005.74 KORT (ED.5) 1 1 15-Apr-2007
005.74 CHAO 2 1 17-Apr-2007
Books
CatalogueNo BookID BorrowType OtherColumns
005.74 KORT (ED.5) 1 Standard ..
005.74 KORT (ED.5) 2 Standard ..
005.74 CHAO 2 7 Day Loan ..
6
Single Table Query
Selecting a few items from the catalogue SELECT
CatalogueNo, Title FROM Catalogue
catalogueno
title -------------------------------------------
-------------- 005.74 KORT (ED.5) Database
System Concepts 005.74 CHAO Database
Development and Management 005.74 POWE
Beginning Database Design 005.7585 WELS
Everyday Oracle DBA 658.872 KUMA Mobile
Database Systems (5 rows)
7
NATURAL JOIN

• Identifying the branch of staff members
• SELECT SFirstName, SLastName, BranchID
• FROM Staff NATURAL JOIN LibBranch

sfirstname slastname branchid -------------
---------------------- Colin Richardson
City Kathy Jays KuringGai
Neeraj Matta City David Tong
KuringGai Mushfika Hossain
City Tat Nguyen City Mark
Bonnett City (7 rows)
8
NATURAL JOIN (Cross Product)

• Identifying the branch of staff members
• SELECT SFirstName, SLastName, Staff.BranchID
• FROM Staff, LibBranch
• WHERE Staff.BranchId LibBranch.BranchId

sfirstname slastname branchid -------------
---------------------- Colin Richardson
City Kathy Jays KuringGai
Neeraj Matta City David Tong
KuringGai Mushfika Hossain
City Tat Nguyen City Mark
Bonnett City (7 rows)
9
GROUP BY

• The number of books issued in each check out
  transaction
• SELECT CheckOutNo, count() as Books
• FROM BorrowedItem
• GROUP BY CheckOutNo

checkoutno books -------------------
1 2 2 2 3
1 4 3 (4 rows)
10
Sub Query

• The name of the borrowers who have checked out
  more than two books in one transaction
• SELECT BFirstName, BLastName
• FROM Borrower NATURAL JOIN Checkout
• WHERE checkoutno
• (SELECT CheckOutNo FROM BorrowedItem GROUP BY
  CheckOutNo HAVING Count()gt2)

bfirstname blastname -----------------------
Anna Cameron (1 row)
11
Self Join

• Finding two books by the same publisher
• SELECT c1.Title, c2.Title, c1.Publisher, c1.Year
• FROM Catalogue c1, Catalogue c2
• WHERE c1.Publisher 'Wiley'
• AND c2.Publisher 'Wiley'
• AND c1.CatalogueNo gt c2.CatalogueNo

- RECORD 1 ------------------------ title
Mobile Database Systems title Beginning
Database Design publisher Wiley year 2006
12
Check Statements

• Checking for book borrow type
• CONSTRAINT Book_BorrowType CHECK
• (BorrowType IN ('7 Day Loan', 'Closed Reserve',
  'Standard'))
• Checking for Postcode
• CONSTRAINT LibPostcode_Range CHECK
• ((LibPostcode gt 1000) AND (LibPostcode lt9999))

13
Action Statements

• On Delete Restrict
• CONSTRAINT fk_Staff FOREIGN KEY(BranchID)
  REFERENCES LibBranch(BranchID)
• ON DELETE RESTRICT
• ON UPDATE CASCADE
• On Delete Cascade
• CONSTRAINT fk_BorrowerMembership FOREIGN
  KEY(BorrowerID) REFERENCES Borrower(BorrowerID)
• ON DELETE CASCADE
• ON UPDATE CASCADE

14
Views

• Creating a view for 7 Day Loan books
• CREATE VIEW SevDayLoan(CatalogueNo, Title,
  BookID, BorrowType, Branch)
• AS SELECT Book.CatalogueNo, Title, BookID,
  BorrowType, BranchID FROM Book NATURAL JOIN
  Catalogue
  WHERE BorrowType '7 Day Loan'
• Selecting 7 Day Loan books available in the City
  Campus
• SELECT CatalogueNo, Title, BookID, Branch
• FROM SevDayLoan WHERE Branch'City'

catalogueno title
bookid branch ---------------------------------
-------------------------- 005.74 KORT (ED.5)
Database System Concepts 3 City
005.74 POWE Beginning Database Design
2 City 658.872 KUMA Mobile
Database Systems 2 City 658.872 KUMA
Mobile Database Systems 3
City (4 rows)