uts library database n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
UTS Library Database PowerPoint Presentation
Download Presentation
UTS Library Database

Loading in 2 Seconds...

play fullscreen
1 / 14

UTS Library Database - PowerPoint PPT Presentation


  • 185 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'UTS Library Database' - bary


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
uts library database

UTS Library Database

Ankur Kandikatla

HD Assignment 3, Autumn 2007

uts library database1
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
1 m relationship
1:mRelationship

Catalogue – Books

Book

Catalogue

Foreign key

m m relationship
m:m Relationship

CheckOut-Books

CheckOut

BorrowItem

m:m relationship

Books

single table query
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)

natural join
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)

natural join cross product
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)

group by
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)

sub query
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)

self join
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

check statements
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))

action statements
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

views
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)