Database Quires and Anamolies
vikky1246$
DBA.docx
The following tables form part of a database held in a relational DBMS:
|
|
|
|
|
1. Write SQL Queries and expected output for the following (Marks : 7)
a. How many hotels are there?
Select
b. List all double rooms (Hotel name & Room No) with a price below £40.00 per night, in ascending order of price.
c. How many different number of guests have made bookings for August?
d. What is the lost income from unoccupied rooms at the Grosvenor Hotel on 10/12/2021?
e. Increase the price of all rooms by 5%.
f. What is the most commonly booked room type for each hotel in London?
g. List all guests (Name) currently staying at the Grosvenor Hotel.
2. The table shown below lists sample dentist/patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day. (Marks: 13)
· The table shown below is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies.
· Identify the functional dependencies represented by the attributes shown in the table. State any assumptions you make about the data and the attributes shown in this table.
· Describe and illustrate the process of normalizing the table shown to 3NF relations. Identify the primary, alternate, and foreign keys in your 3NF relations.
|
Dentist No |
Dentist Name |
Patient No |
Patient Name |
AppointmentDateTime |
SurgeryType |
|
S1011 |
Tony Smith |
P100 |
Gillian White |
12-Sep-2021 10:00 |
S15 |
|
S1011 |
Tony Smith |
P105 |
Jill Bell |
12-Sep-2021 12:00 |
S15 |
|
S1024 |
Helen Pearson |
P108 |
Ian Mackay |
12-Sep-2021 10:00 |
S10 |
|
S1024 |
Helen Pearson |
P108 |
Ian Mackay |
14-Sep-2021 14:00 |
S10 |
|
S1032 |
Robin Plevin |
P105 |
Jill Bell |
14-Sep-2021 16:30 |
S15 |
|
S1032 |
Robin Plevin |
P110 |
John Walker |
15-Sep-2021 18:00 |
S13 |
