1 / 14

UTS Library Database

UTS Library Database. Ankur Kandikatla HD Assignment 3, Autumn 2007. 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

bary
Download Presentation

UTS Library Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. 1:mRelationship Catalogue – Books Book Catalogue Foreign key

  5. m:m Relationship CheckOut-Books CheckOut BorrowItem m:m relationship Books

  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(*)>2); 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 > 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 >= 1000) AND (LibPostcode <=9999))

  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)

More Related