Database Quires And Anamolies

by


 

 

The following tables form part of a database held in a relational DBMS:

 

Hotel
hotelNo hotelName city
101 Grosvenor London
102 Swallow London
201 Waldorf Astoria New York
202 Plaza New York
401 Sofitel Minneapolis
Guest
guestNo guestName guestAddress
40 John Smith 10 E 42 St, New York, NY
41 Mary Jones 110 Elm St, Garden City, NY
52 Thomas Smith 40-40 172 St, Flushing, NY
53 Joseph Schmo 1052 E 85 St, Brooklyn, NY
60 Ima Sample 3010 Broadway, New York, NY
61 Shesa Sample 2510 Utica Ave, Minneapolis, MN
62 George Seaver 41 Arschloch Rd, Grenewich, CT
63 Jerome Koosman 54 Morris Ave, Stillwater, MN

 

Room
roomNo hotelNo Type Price
201 101 Single 30
202 101 Double 40
203 101 Single 30
204 101 Double 40
205 101 Double 40
206 101 Suite 100
201 102 Single 25
202 102 Double 30
203 102 Single 25
204 102 Double 30
205 102 Double 30
206 102 Suite 80
201 201 Single 80
202 201 Double 100
203 201 Single 80
204 201 Double 100
205 201 Double 100
206 201 Suite 300
 

 

Booking
hotelNo guestNo dateFrom dateTo roomNo
101 40 8/12/2021 8/14/2021 201
101 52 10/12/2021 10/13/2021 204
101 63 10/24/2021 10/27/2021 206
201 40 8/24/2021 8/26/2021 205
201 40 10/31/2021 11/2/2021 203
201 52 10/28/2021 10/31/2021 201

 

 

 

 

  1. Write SQL Queries and expected output for the following (Marks : 7)
    1. How many hotels are there?

Select

  1. List all double rooms (Hotel name & Room No) with a price below £40.00 per night, in ascending order of price.
  2. How many different number of guests have made bookings for August?
  3. What is the lost income from unoccupied rooms at the Grosvenor Hotel on 10/12/2021?
  4. Increase the price of all rooms by 5%.
  5. What is the most commonly booked room type for each hotel in London?
  6. List all guests(Name) currently staying at the Grosvenor Hotel.

 

 

  1. The table shown below lists sample dentist/patient appointment data. A patient is given an appointmentat a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, adentist 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. Stateany 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. Identifythe 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