Question

Exercise I (50 pts): This exercise deals with a database that stores information about Hotel Management System. Customer (C email, name, country) Payment (Invoice id, C email, payment method, date) invoice (Invoice id, starus, invoice description) Has (Bill id. Invoice id) 9a Bill (Invoice id, Bill id, amount, Bname, type, date, reservation id) Reservation (Hotel id, room id, C email, date, period, reservation id) Rooms(Hotel id. room id, price, category) Hotel (Hotel id. H name. country) Own (room id, Hotel id) 1- Draw the ER Diagram for the proposed Hotel Management System 2- Create in SQL language a database with your name. Then, create Customer and Invoice tables with their proposed attributes. Be sure to include the correct names and types for all attributes, and any primary key 3- Write SQL queries to retrieve the requested information from your database. Suppose that all tables described previously a List the name of the customers along with the rooms category they did reserve and the date of their reservation. Each of the customers must have at least a reservation. foreign key constraints. chn or are included in you database. AMI b- List the total number of payed invoices for each Customer. List all the customers who belongs to the country Lebanon or that they have not any reservation yet. d- List all the reservation id which are not billed on date 2019-09-09 and which have an amount of 1000.00 and below. C- Write the relation algebra equations for all the requested lists in the previous question (3-a,b.c.d). 4- Exercise II (20 pts): SQL and relational algebra PS:each wrong answer eliminates a correct answer We consider the following relation: ID title iournal, issue, year, startpage, endpage, TR-ID)

Database

Answer 1

Answer:

1. ER Diagram

Payadas method Country name C-emaf Did cbic Payment CUStoner Staius Inveice-1d generaie d TAVoie 7n Voice descripion las Date ill-id 1y Pe sname Andunt mad e HOie fooms Hoiel-id country ROom-id calegory Price H-hamc Booked eeserved Reservation Reservation -id dale feriod

2. Create database

CREATE DATABASE abc;

USE abc;

Create Customer and Invoice table

CREATE TABLE Customer( C_email varchar(200) PRIMARY KEY, name varchar(200), country varchar(200));

CREATE TABLE Invoice( Invoice_id varchar(200) PRIMARY KEY, status varchar(200), invoice_description varchar(200));

3. SQL Queries

a) SELECT c.name, r.category, re.date FROM

Customer c INNER JOIN Reservation re

ON c.C_email = re.C_email

INNER JOIN Hotel h

ON re.Hotel_id = h.Hote_id

INNER JOIN Rooms r

ON re.room_id = r.room_id;

b) SELECT c.name, count(p.Invoice_id) FROM

Customer c INNER JOIN Payment p

ON c.C_email = p.C_mail

GROUP BY c.name;

c) SELECT DISTINCT(name) FROM ( SELECT c.name FROM Customer c

WHERE c.country NOT IN ('Lebanon')

UNION

SELECT * FROM Customer c

WHERE c.C_email NOT IN ( SELECT re.C_email

FROM Reservation re)

);

d) SELECT b.reservation_id FROM Bill b

WHERE b.date <> '2019-09-09' AND b.amount < 1000.00;

Exercise II

1 - Invalid as we cannot subtract the values directly between two columns

2 - Invalid as we cannot subtract the values directly between two columns

3 - Invalid as we cannot perform SUM on title column; SUM works on numerals

4 - Valid, returns the average year of titles starting with C

5 - Valid, returns the count of each year

6 - Invalid, before grouping we cannot find the count. It has to be like group by year having count(*)>10.

Kindly Upvote if Helpful :)
HOPE THIS MAY HELP YOU----------------------

------------------------------THANK YOU---------------------------