This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: aTime staffNo dentistName PK 3NF / BCNF fd1 fd2 staffNo aDate fd4 FK FK PK patNo surgeryNo fd5 FK PK staffNo aDate aTime dentistName patNo patName PK fd2 1NF surgeryNo fd4 fd2 and fd4 violates 2NF 2NF staffNo aDate Fd3’ violates 3NF surgeryNo staffNo dentistName fd1 fd3 staffN aDate aTime patNo patName fd5 patNo patName PK Fd3’ For the Exercises 5.7 – 5.28, use the Hotel schema defined at the start of the Exercises at the end of Chapter 3 (p 87). Simple Queries 5.7 List full details of all hotels. SELECT * FROM Hotel ; 5.11 List the bookings for which no dateTo has been specified. SELECT * FROM Booking WHERE dateTo IS NULL; 5.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel? SELECT SUM(price) FROM Room r WHERE roomNo NOT IN (SELECT roomNo FROM Booking b, Hotel h WHERE ( dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE) AND b.hotelNo = h.hotelNo AND hotelName = ‘Grosvenor Hotel’);...
View Full Document
- Spring '10
- Entity-relationship model, Database normalization, training session, Third normal form, FK PK