Exercises The following tables form part of a database held in a relational DBMS:-
where where
Hotel
(hotelNo, hotelName, city
!oom
(roomNo, hotelNo, type, price
Boo"ing
(hotelNo, lNo, g#estNo, date$rom, dateTo, ro roomNo
%#est
(g#estNo, g#estName, g#est&ddress
Hotel Hotel contain containss hotel hotel details details and hotelNo hotelNo is is the primary primary "ey' !oom contains room details for each hotel and (roomNo, (roomNo, hotelNo forms the primary "ey' Boo"ing contains details of the boo"ings and (hotelNo, g#estNo, date$rom forms the primary "ey'
and
%#est contains g#est details and g#estNo is the primary "ey
Exercises $or the )*ercises + +./, #se the Hotel schema defined at the start of the )*ercises at the end of 0hapter 1 Simple Queries
+
List full detai details ls of all hotels hotels..
S)2)0T 3 $!4M Hotel' +/
List full detai details ls of all hotels hotels in London. London.
S)2)0T 3 $!4M Hotel 5H)!) city 6 72ondon8' +9
List the names names and addre addresses sses of all guests guests in in London, London, alphabetic alphabetically ally ordere ordered d by name. name.
S)2)0T guestName, guestAddress $!4M Guest 5H)!) address 2;) 7<2ondon<8 4!D)! B= guestName ' Strictly spea"ing, this wo#ld also find rows with an address li"e: 7>? 2ondon &@en#e, New =or"8 +>?
List all double double or family family rooms rooms with a price price below below £40.00 £40.00 per night, night, in ascendin ascending g order order of of price. price.
S)2)0T 3 $!4M Room 5H)!) price A ? &ND type N (7D8, 7$8 4!D)! B= price ' (Note, &S0 is the defa#lt setting +>>
List the bookin bookings gs for which which no dateTo dateTo has has been been specifie specified. d.
Database Systems: nstr#ctor8s %#ide - Cart
S)2)0T 3 $!4M Booking 5H)!) dateTo S N22' Aggregate Functions
+>.
How many hotels are there?
S)2)0T 04NT(3 $!4M Hotel' +>1
hat is the a!erage price of a room?
S)2)0T &E%( price $!4M Room' +>
hat is the total re!enue per night from all double rooms?
S)2)0T SM(price $!4M Room 5H)!) type 6 7D8' +>F
How many different guests ha!e made bookings for "ugust?
S)2)0T 04NT(DSTN0T guestNo $!4M Booking 5H)!) (dateFrom A6 D&T)8.??-?/-?>8 &ND dateTo G6 D&T)8.??-?/-?>8 4! (dateFrom G6 D&T)8.??-?/-?>8 &ND dateFrom A6 D&T)8.??-?/-1>8' Subqueries and Joins
+>+
List the price and type of all rooms at the #ros!enor Hotel.
S)2)0T price, type $!4M Room 5H)!) hotelNo 6 (S)2)0T hotelNo $!4M Hotel 5H)!) hotelName 6 7%ros@enor Hotel8' +>
List all guests currently staying at the #ros!enor Hotel.
S)2)0T 3 $!4M Guest 5H)!) guestNo 6 (S)2)0T guestNo $!4M Booking 5H)!) dateFrom A6 0!!)NTD&T) &ND
dateTo G6 0!!)NTD&T) &ND hotelNo 6 (S)2)0T hotelNo $!4M Hotel 5H)!) hotelName 6 7%ros@enor Hotel8' +>/
List the details of all rooms at the #ros!enor Hotel, including the name of the guest staying in the room, if the room is occupied.
S)2)0T r 3 $!4M Room r 2)$T I4N
.
Database Systems: nstr#ctor8s %#ide - Cart
(S)2)0T g.guestName, h.hotelNo, b.roomNo $!4M Guest g, Booking b, Hotel h 5H)!) g.guestNo = b.guestNo &ND b.hotelNo = h.hotelNo &ND
hotelName 6 7%ros@enor Hotel8 &ND dateFrom A6 0!!)NTD&T) &ND dateTo G6 0!!)NTD&T) &S JJJ 4N r.hotelNo = .hotelNo AN! r.roomNo = .roomNo' +>9
hat is the total income from bookings for the #ros!enor Hotel today?
S)2)0T SM(price $!4M Booking b, Room r, Hotel h 5H)!) (dateFrom A6 0!!)NTD&T) &ND
dateTo G6 0!!)NTD&T) &ND r.hotelNo = h.hotelNo &ND r.roomNo = b.roomNo &ND hotelName 6 7%ros@enor Hotel8' +.?
List the rooms that are currently unoccupied at the #ros!enor Hotel.
S)2)0T 3 $!4M Room r 5H)!) roomNo N4T N (S)2)0T roomNo $!4M Booking b, Hotel h 5H)!) (dateFrom A6 0!!)NTD&T) &ND
dateTo G6 0!!)NTD&T) &ND b.hotelNo = h.hotelNo &ND hotelName 6 7%ros@enor Hotel8' +.>
hat is the lost income from unoccupied rooms at the #ros!enor Hotel?
S)2)0T SM(price $!4M Room r 5H)!) roomNo N4T N (S)2)0T roomNo $!4M Booking b, Hotel h 5H)!) (dateFrom A6 0!!)NTD&T) &ND
dateTo G6 0!!)NTD&T) &ND b.hotelNo = h.hotelNo &ND hotelName 6 7%ros@enor Hotel8' Grouping
+..
List the number of rooms in each hotel.
S)2)0T hotelNo , 04NT(roomNo &S count $!4M Room %!4C B= hotelNo ' +.1
List the number of rooms in each hotel in London.
S)2)0T hotelNo , 04NT(roomNo &S count $!4M Room r, Hotel h 5H)!) r.hotelNo = h.hotelNo &ND city 6 72ondon8 %!4C B= hotelNo '
1
Database Systems: nstr#ctor8s %#ide - Cart
+.
hat is the a!erage number of bookings for each hotel in "ugust?
S)2)0T &E%( $!4M ( S)2)0T hotelNo , 04NT(hotelNo &S $!4M Booking b 5H)!) (dateFrom A6 D&T)8.??-?/-?>8 &ND
dateTo G6 D&T)8.??-?/-?>8 4! (dateFrom G6 D&T)8.??-?/-?>8 &ND
dateFrom A6 D&T)8.??-?/-1>8 %!4C B= hotelNo' =es - this is legal in SK2-9.L +.F
hat is the most commonly booked room type for each hotel in London?
S)2)0T M&J( $!4M ( S)2)0T type, 04NT(type &S $!4M Booking b, Hotel h, Room r 5H)!) r.roomNo = b.roomNo &ND b.hotelNo = h.ho telNo &ND
city 6 72ondon8 %!4C B= type' +.+
hat is the lost income from unoccupied rooms at each hotel today?
S)2)0T hotelNo , SM(price $!4M Room r 5H)!) roomNo N4T N (S)2)0T roomNo $!4M Booking b, Hotel h 5H)!) (dateFrom A6 0!!)NTD&T) &ND
dateTo G6 0!!)NTD&T) &ND b.hotelNo = h.hotelNo %!4C B= hotelNo ' Populating Tables
+.
$nsert records into each of these tables.
NS)!T NT4 Hotel E&2)S (7H>>>8, 7%ros@enor Hotel8, 72ondon8' NS)!T NT4 Room E&2)S (7>8, 7H>>>8, 7S8, .??' NS)!T NT4 Guest E&2)S (7%>>>8, 7Iohn Smith8, 72ondon8' NS)!T NT4 Booking E&2)S (7H>>>8, 7%>>>8, D&T)8.??F-?>-?>8, D&T)8.??F-?>-?.8, 7>8'
Database Systems: nstr#ctor8s %#ide - Cart
%pdate the price of all rooms by &'.
+./
CD&T) Room S)T price 6 price3>?F' General
+.9
$n!estigate the ()L dialect on any *+( that you are currently using. *etermine the compliance of the *+( with the $(- standard. $n!estigate the functionality of any etensions the *+( supports. "re there any functions not supported?
This is a small st#dent proect, the res#lt of which is dependent on the dialect of SK2 being #sed +1?
(how that a /uery using the H"$1# clause has an e/ui!alent formulation without a H"$1# clause.
Hint: &llow the st#dents to show that the restricted gro#ps co#ld ha@e been restricted earlier with a 5H)!) cla#se +1>
(how that ()L is relationally complete.
Hint: &llow the st#dents to show that each of the relational algebra operations can be e*pressed in SK2
Case Study 2 2or 3ercises .567.40, use the 8ro9ects schema defined in the 3ercises at the end of :hapter &. .56
List all employees in alphabetical order of surname, and then first name.
S)2)0T 3 $!4M )mployee 4!D)! B= lName, fName' .55
List all the details of employees who are female.
S)2)0T 3 $!4M )mployee 5H)!) se* 6 7female8' .54
List the names and addresses of all employees who are anagers.
S)2)0T fName, lName, address $!4M )mployee 5H)!) position 6 7Manager8' .5&
8roduce a list of the names and addresses of all employees who work for the $T department.
S)2)0T fName, lName, address from )mployee e, Department d 5H)!) edeptNo 6 ddeptNo &ND ddeptName 6 7T8' .5
8roduce a complete list of all managers who are due to retire this year, in alphabetical order of surname.
.5;
2ind out how many employees are managed by <=ames "dams>.
F
Database Systems: nstr#ctor8s %#ide - Cart
S)2)0T 04NT(empNo $!4M )mployee 5H)!) deptNo 6 (S)2)0T deptNo $!4M )mployee 5H)!) fName 6 7Iames8 &ND lName 6 7&dams8' &ND (fName AG 7Iames8 &ND lName AG 7&dams8'
.5
8roduce a report of the total hours worked by each employee, arranged in order of department number and within department, alphabetically by employee surname.
S)2)0T elName, efName, ho#rs5or"ed $!4M 5or"s4n w, )mployee e, Department d 5H)!) edeptNo 6 ddeptNo &ND eempNo 6 wempNo 4!D)! by ddeptNo, elName' 6.39
For each project on which more than two employees worked, list the project number, project name, and the number of employees who work on that project.
S)2)0T pproNo, proName, co#nt(empNo $!4M Croect p, 5or"s4n w 5H)!) pproNo 6 wproNo %!4C B= pproNo H&EN% 04NT (empNo G .' .40
List the total number of employees in each department for those departments with more than @0 employees. :reate an appropriate heading for the columns of the results table.
S)2)0T 04NT(empNo as emp0o#nt, deptNo $!4M )mployee %!4C B= deptNo H&EN% 04NT(empNo G >?'
+
Database Systems: nstr#ctor8s %#ide - Cart
Chapter 7 SQ! "ata "e#inition $e%ie& Questions >
*escribe the eight base data types in ()L.
The eight base types are: Boolean, character, bit (remo@ed from SK2:.??1, e*act n#meric, appro*imate n#meric, datetime, inter@al, large obect See Section >. .
*iscuss the functionality and importance of the $ntegrity 3nhancement 2eature A$32B.
!e#ired data:
N4T N22 of 0!)&T)O&2T)! T&B2)
Domain constraint:
0H)0; cla#se of 0!)&T)O&2T)! T&B2) and 0!)&T) D4M&N
)ntity integrity:
C!M&!= ;)= (and NK) cla#se of 0!)&T)O&2T)! T&B2)
!eferential integrity:
$4!)%N ;)= cla#se of 0!)&T)O&2T)! T&B2)
%eneral constraints:
0H)0; and NK) cla#ses of 0!)&T)O&2T)! T&B2) and (0!)&T) &SS)!T4N
See Section . 1
*iscuss each of the clauses of the :C3"T3 T"+L3 statement.
The cla#ses are (see Section 1.:
•
col#mn definition'
•
C!M&!= ;)=
•
$4!)%N ;)=
•
0H)0; constraints
*iscuss the ad!antages and disad!antages of !iews.
See Section F
*escribe how the process of !iew resolution works.
Described in Section 1 +
hat restrictions are necessary to ensure that a !iew is updatable?
S4 standard specifies the @iews that m#st be #pdatable in a system that conforms to the standard Definition gi@en in SK2 standard is that a @iew is #pdatable if and only if: •
DSTN0T is not specified' that is, d#plicate rows m#st not be eliminated from the #ery res#lts
Database Systems: nstr#ctor8s %#ide - Cart
•
)@ery element in the S)2)0T list of the defining #ery is a col#mn name (rather than a constant, e*pression, or aggregate f#nction and no col#mn appears more than once
•
The $!4M cla#se specifies only one table' that is, the @iew m#st ha@e a single so#rce table for which the #ser has the re#ired pri@ileges f the so#rce table is itself a @iew, then that @iew m#st satisfy these conditions This, therefore, e*cl#des any @iews based on a oin, #nion (N4N, intersection (NT)!S)0T, or difference ()J0)CT
•
The 5H)!) cla#se does not incl#de any nested S)2)0Ts that reference the table in the $!4M cla#se
•
There is no %!4C B= or H&EN% cla#se in the defining #ery
n addition, e@ery row that is added thro#gh the @iew m#st not @iolate the integrity constraints of the base table (Section F
hat is a materialiDed !iew and what are the ad!antages of a maintaining a materialiDed !iew rather than using the !iew resolution process?
MaterialiPed @iew is a temporary table that is stored in the database to represent a @iew, which is maintained as the base table(s are #pdated &d@antages
- may be faster than trying to perform @iew resol#tion - may also be #sef#l for integrity chec"ing and #ery optimisation
See Section / /
*escribe the difference between discretionary and mandatory access control. hat type of control mechanism does ()L support.
Discretionary each #ser is gi@en appropriate access rights (or pri!ileges on specific database obects Mandatory each database obect is assigned a certain classification le!el (eg Top Secret, Secret, 0onfidential, nclassified and each sub9ect (eg #ser, application is gi@en a designated clearance le!el (Top Secret G Secret G 0onfidential G nclassified
SK2 sec#rity mechanism is based on discretionary access control 9
*iscuss how the "ccess :ontrol mechanism of ()L works.
)ach #ser has an authori'ation identi#ier (allocated by DB& )ach obect has an o&ner nitially, only owner has access to an obect b#t the owner can pass pri@ileges to carry o#t certain actions on to other #sers @ia the %!&NT statement and ta"e away gi@en pri@ileges #sing !)E4;)
Exercises &nswer the following #estions #sing the relational schema from the )*ercises at the end of 0hapter
/
Database Systems: nstr#ctor8s %#ide - Cart
>?
:reate the Hotel table using the integrity enhancement features of ()L.
0!)&T) D4M&N HotelNumber &S 0H&!(' 0!)&T) T&B2) Hotel(
hotelNo
HotelNumber
N4T N22,
hotelName
E&!0H&!(.?
N4T N22,
city
E&!0H&!(F?
N4T N22,
C!M&!= ;)= (hotelNo ' >>
1ow create the Coom, +ooking, and #uest tables using the integrity enhancement features of ()L with the following constraintsE AaB
Type must be one of (ingle, *ouble, or 2amily.
AbB
8rice must be between £@0 and £@00.
AcB
room1o must be between @ and @00.
AdB
date2rom and dateTo must be greater than today>s date.
AeB
The same room cannot be double booked.
AfB
The same guest cannot ha!e o!erlapping bookings.
0!)&T) D4M&N RoomType &S 0H&!(> 0H)0;(E&2) N (7S8, 7$8, 7D8' 0!)&T) D4M&N HotelNumbers &S HotelNumber 0H)0;(E&2) N (S)2)0T hotelNo $!4M Hotel' 0!)&T) D4M&N Room"rice &S D)0M&2(F, . 0H)0;(E&2) B)T5))N >? &ND >??' 0!)&T) D4M&N RoomNumber &S E&!0H&!( 0H)0;(E&2) B)T5))N 7>8 &ND 7>??8' 0!)&T) T&B2) Room(
roomNo
RoomNumber
N4T N22,
hotelNo
HotelNumbers
N4T N22,
type
RoomType
N4T N22 D)$&2T 7S8
price
Room"rice
N4T N22,
C!M&!= ;)= (roomNo, hotelNo, $4!)%N ;)= ( hotelNo !)$)!)N0)S Hotel 4N D)2)T) 0&S0&D) 4N CD&T) 0&S0&D)' 0!)&T) D4M&N GuestNumber &S 0H&!(' 0!)&T) T&B2) Guest(
guestNo
GuestNumber
N4T N22,
guestName
E&!0H&!(.?
N4T N22,
guestAddress
E&!0H&!(F?
N4T N22'
0!)&T) D4M&N GuestNumbers &S GuestNumber
9
Database Systems: nstr#ctor8s %#ide - Cart
0H)0;(E&2) N (S)2)0T guestNo $!4M Guest' 0!)&T) D4M&N Booking!ate &S D&T)TM) 0H)0;(E&2) G 0!!)NTD&T)' 0!)&T) T&B2) Booking (
hotelNo
HotelNumbers
N4T N22,
guestNo
GuestNumbers
N4T N22,
dateFrom
Booking!ate
N4T N22,
dateTo
Booking!ate
N22,
roomNo
RoomNumber
N4T N22,
C!M&!= ;)= (hotelNo, guestNo, dateFrom , $4!)%N ;)= ( hotelNo !)$)!)N0)S Hotel 4N D)2)T) 0&S0&D) 4N CD&T) 0&S0&D), $4!)%N ;)= ( guestNo !)$)!)N0)S Guest 4N D)2)T) N4 &0T4N 4N CD&T) 0&S0&D), $4!)%N ;)= ( hotelNo, roomNo !)$)!)N0)S Room 4N D)2)T) N4 &0T4N 4N CD&T) 0&S0&D), 04NST!&NT RoomBooked 0H)0; (N4T )JSTS ( S)2)0T 3 $!4M Booking b 5H)!) b.dateTo # Booking.dateFrom &ND
b.dateFrom $ Booking.dateTo &ND b.roomNo = Booking.roomNo &ND b.hotelNo = Booking.hotelNo , 04NST!&NT GuestBooked 0H)0; (N4T )JSTS ( S)2)0T 3 $!4M Booking b 5H)!) b.dateTo # Booking.dateFrom &ND
b.dateFrom $ Booking.dateTo &ND b.guestNo = Booking.guestNo ' >.
:reate a separate table with the same structure as the +ooking table to hold archi!e records. %sing the $1(3CT statement, copy the records from the +ooking table to the archi!e table relating to bookings before @st =anuary 6005. *elete all bookings before @st =anuary 6005 from the +ooking table.
%R&AT& TAB'& Booking(ld)
hotelNo
0H&!(
N4T N22,
guestNo
0H&!(
N4T N22,
dateFrom
D&T)TM)
N4T N22,
dateTo
D&T)TM)
N22,
roomNo
E&!0H&!(
N4T N22'
NS)!T NT4 Booking(ld (S)2)0T 3 $!4M Booking 5H)!) dateTo A D&T)8.??1-?>-?>8' D)2)T) $!4M Booking
>?
Database Systems: nstr#ctor8s %#ide - Cart
5H)!) dateTo A D&T)8.??1-?>-?>8' >1
:reate a !iew containing the hotel name and the names of the guests staying at the hotel.
0!)&T) E)5 Hotel!ata(hotelName, guestName &S
S)2)0T h.hotelName, g.guestName
$!4M Hotel h, Guest g, Booking b
5H)!) h.hotelNo = b.hotelNo &ND g.guestNo = b.guestNo &ND
b.dateFrom A6 0!!)NTD&T) &ND b.dateTo G6 0!!)NTD&T)' >
:reate a !iew containing the account for each guest at the #ros!enor Hotel.
0!)&T) E)5 Booking(utToday &S
S)2)0T g.guestNo,g.guestName,g.guestAddress,r.price*)b.dateTo+b.dateFrom $!4M Guest g, Booking b, Hotel h, Room r 5H)!) g.guestNo = b.guestNo &ND r.roomNo = b.roomNo &ND
b.hotelNo = h.hotelNo &ND h.hotelName 6 7%ros@enor Hotel8 &ND b.dateTo 6 0!!)NTD&T)' >F
#i!e the users anager and *eputy full access to these !iews, with the pri!ilege to pass the access on to other users.
%!&NT &22 C!E2)%)S 4N Hotel!ata T4 Manager, Director 5TH %!&NT 4CT4N' %!&NT &22 C!E2)%)S 4N Booking(utToday T4 Manager, Director 5TH %!&NT 4CT4N' >+
#i!e the user "ccounts (3L3:T access to these !iews. 1ow re!oke the access from this user.
%!&NT S)2)0T 4N Hotel!ata T4 &cco#nts' %!&NT S)2)0T 4N Booking(utToday T4 &cco#nts' !)E4;) S)2)0T 4N Hotel!ata $!4M &cco#nts' !)E4;) S)2)0T 4N Booking(utToday $!4M &cco#nts' >
:onsider the following !iew defined on the Hotel schemaE
0!)&T) E)5 HotelBooking%ount (hotelNo , booking%ount &S
S)2)0T h.hotelNo, 04NT(3 $!4M Hotel h, Room r, Booking b 5H)!) h.hotelNo = r.hotelNo &ND r.roomNo = b.roomNo %!4C B= h.hotelNo '
>>
Database Systems: nstr#ctor8s %#ide - Cart
$or each of the following #eries, state whether the #ery is @alid and for the @alid ones sho#ld how each of the #eries wo#ld be mapped onto a #ery on the #nderling base tables (a
S)2)0T 3 $!4M HotelBooking%ountS)2)0T h.hotelNo, 04NT(3 $!4M Hotel h, Room r, Booking b 5H)!) h.hotelNo = r.hotelNo &ND r.roomNo = b.roomNo %!4C B= h.hotelNo '
(b
S)2)0T hotelNo $!4M HotelBooking%ount 5H)!) hotelNo = H//01S)2)0T h.hotelNo $!4M Hotel h, Room r, Booking b 5H)!) h.hotelNo = r.hotelNo &ND r.roomNo = b.roomNo AN!
h.hotelNo = H//01 %!4C B= h.hotelNo ' (c
S)2)0T MN(booking%ount $!4M HotelBooking%ountn@alid booking%ount is based on an aggregate f#nction, so cannot be #sed within another aggregate f#nction
(d
S)2)0T 04NT(3 $!4M HotelBooking%ountn@alid for reason gi@en abo@e
(e
S)2)0T hotelNo $!4M HotelBooking%ount 5H)!) booking%ount G >???n@alid booking%ount is based on an aggregate f#nction, so cannot be #sed in the 5H)!) cla#se
(f
S)2)0T hotelNo $!4M HotelBooking%ount 4!D)! B= booking%ount ' S)2)0T h.hotelNo, 04NT(3 &S booking%ount $!4M Hotel h, Room r, Booking b 5H)!) h.hotelNo = r.hotelNo &ND r.roomNo = b.roomNo
>.
Database Systems: nstr#ctor8s %#ide - Cart
%!4C B= h.hotelNo 4!D)! B= booking%ount '
>9
"ssume that we also ha!e a table for suppliersE
2upplier (supplierNo, partNo, price and a @iew 2upplier"arts , which contains the distinct part n#mbers that are s#pplied by at least one s#pplier: 0!)&T) E)5 2upplier"arts (partNo &S
S)2)0T DSTN0T partNo $!4M 2upplier s, "art p 5H)!) s.partNo = p.partNo-
>1
