Database Quires and Anamolies

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.