UTS Library

1
UTS Library
By Helen Spiropoulos
2
SpiropoulosUTSLibrary
This database models the UTS Library Website
www.lib.uts.edu.au It allows users to search for
resources (book, journal, dvd, cd etc) by
subject, author, title, category and
publisher. Staff can check if students have
loans overdue and also when student cards expire.
They can also access the sales contact of
publishers if an item needs replacing. The
attributes in each table allow for an extensive
range of queries.
3
UTSLIB ERD
4
SpiropoulosUTSLibrary
One to Many Relationship
Primary Key
pubID PubName SalesContact
1 Wiley
2 Prentice Hall
3 Pearson Education
4 McGraw-Hill
5 East Sussex
6 Everbest sales_at_everbest.com

• One publisher can publish many resources

Foreign Key
ISBN Title Edition PubID Year Media Copies
0131451413 Database concepts 2 3 2005 book 1
1256748844 Managing a Corporate Enterprise 3 3 2005 dvd 1
5
SpiropoulosUTSLibrary
Many to Many Relationship
ISBN Title Ed pubID Year Media Copies
0131451413 Database concepts 2 3 2005 book 1
0471347116 Data management databases and organizations 4 1 2003 book 2
0072880678 Database design, application development, and administration 2 4 2004 book 4
One resource can be for many subjects One subject
can have many resources
subNo subName Falculty
31061 Database Principles Information Technology
21121 Managing electronic business processes Information Technology
SubNo ISBN
31061 0471347116
31061 0131451413
31061 0072880678
21121 0471180327
31474 0072880678
LIB_Resource
LIB_Subject
LIB_ResSubject
ISBN Title Ed PubID YearPub Media Copies
SubNo SubName Faculty
SubNo ISBN
6
SpiropoulosUTSLibrary
Query on a single entity/table
select from LIB_author where augender 'M'
Select all male authors
auID auName auBirth auDeath augender
1 Kroenke, David M. M
2 Watson, Richard T M
3 Mannino, Micheal V. M
4 McKeown, Patrick G. 1943 M
5 Hartas, Leo M
6 Prentice, Steve M
select isbn, media from LIB_resource where media
ltgt 'book'
ISBN Media
1256748844 dvd
1246450099M magazine
Select all resources other than books
7
SpiropoulosUTSLibrary
Query using Natural Join
List subject, resource and copies of all
resources currently used by the faculty of
Information Technology
select subname, title, copies from lib_resource
natural join lib_ressubject natural join
lib_subject Where falculty Information
Technology
subName Title Copies
Database Principles Database concepts 1
Database Principles Data management databases and organizations 2
Database Fundamentals Database design, application development, and administration 4
Database Principles Database design, application development, and administration 4
Managing Electronic Business Processes Metamorphosis a guide to the World Wide Web electronic commerce 1
8
SpiropoulosUTSLibrary
Query using Cross Product
select subname, title, copies from lib_resource,
lib_subject, lib_ressubject where
lib_resource.isbn lib_ressubject.isbn and
lib_subject.subno lib_ressubject.subno and
faculty 'Information Technology'
subName Title Copies
Database Principles Database concepts 1
Database Principles Data management databases and organizations 2
Database Fundamentals Database design, application development, and administration 4
Database Principles Database design, application development, and administration 4
Managing Electronic Business Processes Metamorphosis a guide to the World Wide Web electronic commerce 1
9
SpiropoulosUTSLibrary
Group By using Having
show the authors who have authored more than 1
resource
select auname, count() from lib_author,
lib_resauthor, lib_resource where
lib_resource.isbn lib_resauthor.isbn and
lib_resauthor.auID lib_author.auID group by
auname having count() gt 1
auName Count
Jones, Katee K. 2
Watson, Richard T. 2
10
SpiropoulosUTSLibrary
Query using a Sub Query
show the known oldest author who is still alive
select auname, aubirth from lib_author where
aubirth lt all (select aubirth from lib_author
where audeath is null and aubirth is not null)
auName auBirth
McKeown, Patrick G. 1943
11
SpiropoulosUTSLibrary
Self Join
Select all authors who authored two books
(assuming no author has Authored more than two
books) and the ISBN of each book.
select au.auname, resau1.isbn as book1,
resau2.isbn as book2 from lib_resauthor resau1,
lib_resauthor resau2, lib_author au where
resau1.auid au.au and resau2.auid
au.auid and resau1.isbn lt resau2.isbn
auName Book1 Book2
Watson, Richard T. 0471180327 0471347116
Jones, Katee K. 1246450099M 1256748844
12
SpiropoulosUTSLibrary
Data Integrity CHECK Constraints
Create table LIB_Author ( values go
here. auBirth CHAR(4), auDeath CHAR(4), auGen
der TEXT, CONSTRAINT LIB_Author_AuBirth CHECK
((AuBirth lt AuDeath) AND (AuBirth gt 0000)
AND (AuBirth lt 9999)), CONSTRAINT
LIB_Author_AuDeath CHEck (AuDeath gt
AuBirth), CONSTRAINT LIB_Author_AuGender CHECK
(AuGender IN ('M','F')) )
13
SpiropoulosUTSLibrary
SQL Syntax for Actions
Create table LIB_ResCategory( catID INTEGER, ISB
N TEXT, CONSTRAINT LIB_ResCategoryPk PRIMARY KEY
(CatID,ISBN), CONSTRAINT LIB_ResCategoryFk_INVALI
D_ISBN FOREIGN KEY (ISBN) REFERENCES
LIB_Resource (ISBN) ON DELETE CASCADE ON UPDATE
CASCADE, CONSTRAINT LIB_ResCategoryFk_INVALID_Cat
ID FOREIGN KEY (CatID) REFERENCES
LIB_Category (CatID) ON DELETE RESTRICT ON
UPDATE CASCADE )
This will delete/update the entry of the isbn
in Lib_rescategory if the resource is
deleted/updated from Lib_resource
This will restrict the deletion of a category
if there are still resources in the category.
It will also update any changes in catID
from LIB_category to the matching catID in
LIB_resCategory.
14
SpiropoulosUTSLibrary
Creating a View
create view booksonloan (isbn, title, copyno,
daysout, due) as select lib_loan.isbn,
title, lib_loan.copyno, '07 June, 2007' -
dateloan, datedue from lib_resource,
lib_copy, lib_loan where lib_resource.isbn
lib_copy.isbn and lib_copy.copyno
lib_loan.copyno and lib_copy.isbn
lib_loan.isbn
ISBN Title copyNo DaysOut Due
0471347116 Data management databases and organizations 1 13 2007-06-25
0471347116 Data management databases and organizations 2 10 2007-06-28
0131451413 Database concepts 1 33 2007-06-05
1246450099M Greece and why it rocks 1 13 2007-06-25
15
SpiropoulosUTSLibrary
THE END