An agency called InstantCover supplies part-time/temporary staff to hotels throughout Scotland. The table shown in Figure 2 lists the time spent by agency staff working at two hotels. The National Insurance Number (NIN) is unique for employee.in
InstantCover(NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
i. Provide examples of insertion, deletion, and modification anomalies.
INSERTION: a. To insert the details of new members for example
INSERT INTO InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
VALUES (872342, 23, 40, ‘Johnny Yes Papa’, 32, ‘London’);
DELETION
b. we delete a row from InstantCover relation that represents the last member
DELETE FROM InstantCover
c. If we want to change the value of one attributes of a particular hotel in the InstantCover relation
ALTER TABLE InstantCover
MODIFY HotelLocation VARCHAR(20) NOT NULL
b. Describe and illustrate the process of normalizing the table shown in Figure 2 to 3NF. State any assumptions you make about the data shown in this table.
1NF
FD1- NIN, ContractNo --> HoursPerWeek, eName, HotelNo, HotelLocation (Primary Key)
FD2- NIN --> HoursPerWeek, eName, HotelNo, HotelLocation (Partial Dependency)
FD3-ContractNo --> HoursPerWeek, eName (Partial Dependency)
FD4-HotelNo --> HotelLocation (Transitive Dependency}
InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
2NF
FD1- NIN, ContractNo --> HoursPerWeek, eName, HotelNo, HotelLocation (Primary Key)
FD2- NIN --> HoursPerWeek, eName, HotelNo, HotelLocation (Partial Dependency)
FD4-HotelNo --> HotelLocation (Transitive Dependency}
InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
Contract ( ContractNo ,HoursPerWeek, eName)
3NF
FD1- NIN, ContractNo --> HoursPerWeek, eName, HotelNo, HotelLocation (Primary Key)
FD2- NIN --> HoursPerWeek, eName, HotelNo, HotelLocation (Partial Dependency)
InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
Contract ( ContractNo ,HoursPerWeek, eName)
Hotel ( HotelNo,HotelLocation)
2. SQL
Hotel (Hotel_No, Name, Address)
Room (Room_No, Hotel_No, Type, Price)
Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No)
Guest (Guest_No, Name, Address)
Simple Queries
1. List full details of all hotels
SELECT *
FROM Hotel
2.List full details of all hotels in London.
SELECT *
FROM Hotel WHERE Address= ‘London’
SELECT *
FROM Guest
WHERE Address = ‘London’ ORDER BY Name ASC;
4. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
SELECT *
FROM Room
WHERE Price < 40
AND type IN (‘D’, ‘F’)
ORDER BY Price;
5. List the bookings for which no date_to has been specified.
SELECT *
FROM Booking
WHERE Date_To IS NULL;
Aggregate Functions
1. How many hotels are there?
SELECT COUNT(*)
FROM Hotel;
2. What is the average price of a room?
SELECT AVG(Price)
FROM Room;
3. What is the total revenue per night from all double rooms?
SELECT SUM(price)
FROM Room
WHERE type = ‘D’;
4. How many different guests have made bookings for August?
SELECT COUNT(Guest_No)
FROM Booking
WHERE(Date_From <='08/31/2019 ' AND Date_To >='08/01/2019')
Subqueries and Joins
1. List the price and type of all rooms at the Grosvenor Hotel.
SELECT Price, Type
FROM Room
WHERE Hotel_No =
(SELECT Hotel_No
FROM Hotel
WHERE Name = 'Grosvenor Hotel');
2. List all guests currently staying at the Grosvenor Hotel.
SELECT *
FROM Guest
WHERE Guest_No =
(SELECT Guest_No
FROM Booking
WHERE Date_From <= CURRENT_DATE AND Date_To >= CURRENT_DATE AND Hotel_No = (SELECT Hotel_No FROM Hotel WHERE Name = 'Grosvenor Hotel'));
3. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.
SELECT Room.*, Guest.Name
FROM Room
INNER JOIN Hotel on Hotel.Hotel_No = Room.Hotel_No
INNER JOIN Booking on Booking.Hotel_No = Hotel.Hotel_No
INNER JOIN Guest on Booking.Guest_No = Guest.Guest_No
WHERE Hotel.Name = 'Grosvenor Hotel';
4. What is the total income from bookings for the Grosvenor Hotel today?
SELECT SUM(Price)
FROM Booking b, Room r, Hotel h
WHERE (Date_From <= CURRENT_DATE AND Date_To>= CURRENT_DATE)
AND r.Hotel_No = h.Hotel_No
AND r.Room_No = b.Room_No
AND Name = 'Grosvenor Hotel';
5. List the rooms that are currently unoccupied at the Grosvenor Hotel.
SELECT *
FROM Room r
WHERE Room_No
NOT IN (SELECT Room_No FROM Booking b, Hotel h
WHERE (Date_From<= CURRENT_DATE AND Date_To>= CURRENT_DATE)
AND b.Hotel_No = h.Hotel_No
AND Name= 'Grosvenor Hotel');
6. What is the lost income from unoccupied rooms at the Grosvenor Hotel?
SELECT SUM(Price)
FROM Room r
WHERE Room_No NOT IN
(SELECT Room_No
FROM Booking b,Hotel h
WHERE (Date_From <= CURRENT_DATE AND Date_To>= CURRENT_DATE)
AND b.Hotel_No = h.Hotel_No
AND Name= 'Grosvenor Hotel');
Grouping
1. List the number of rooms in each hotel.
SELECT Hotel_No,
COUNT(Room_No)
AS count FROM Room GROUP BY Hotel_No;
2. List the number of rooms in each hotel in London.
SELECT r.Hotel_No,
COUNT(Room_No)
FROM Room r, Hotel h
WHERE r.Hotel_No=h.Hotel_No
AND Address = 'London' GROUP BY r.Hotel_No;
3. What is the average number of bookings for each hotel in August?
SELECT Hotel_No, y/31
FROM (SELECT Hotel_No, COUNT(Hotel_No) AS y
FROM Booking
WHERE (Date_From <= '8/31/2019' AND Date_To >= '8/1/2019') GROUP BY Hotel_No);
4. What is the most commonly booked room type for each hotel in London?
SELECT MAX(X)
FROM ( SELECT Type, COUNT(Type) AS X
FROM Booking b, Hotel h, Room r
WHERE r.Room_No = b.Room_No
AND b.Hotel_No = h.Hotel_No
AND Address= 'London'
GROUP BY Type);
5. What is the lost income from unoccupied rooms at each hotel today?
SELECT Hotel_No, SUM(Price)
FROM Room r
WHERE Room_No NOT IN
(SELECT Room_No
FROM Booking b, Hotel h
WHERE (Date_From<= CURRENT_DATE AND Date_To>= CURRENT_DATE)
AND b.Hotel_No = h.Hotel_No) GROUP BY Hotel_No;
){kind=link}
0 Comments