Data Management And AnalysisQuestion Three [30 marks]Radisson Blue Hotels stores information about their hotels in different countries. Every hotel is identified by hotel id, hotel name, hotel address, city and URL. Every country is identified by country code, country name and country currency. Guests staying in the hotel is identified by guest number, guest name, guest address and guest city. Every room in the hotel is identified by room number, and room floor. Every room booking is identified by booking id, starting date, and ending date. There are different types of rooms in the hotel. Each room type is identified by room type code, room standard rate, and description. In each country, there may be no hotels or more than one hotel. A hotel may have one or more rooms. A guest can book one or more rooms and a room may be booked by one or more guests. A hotel may have one or more bookings and each booking is associated with exactly one hotel. Each room type may have one or more rooms and a room belongs to one room type.1) Write the schema of the appropriate relations for the above scenario. Identify the primary key and the foreign key in each of the relations. [4 marks]How did you identify the primary key and foreign key? Explain. [2 Marks]2) Use the designed relations to write SQL statements for the following questions: – Explain the working of each query in detail.a. Display all the rooms having the same rate in the ascending order of room rate. [5 marks]b. List the number of hotels in each country. Only include countries with more than 4 hotels. [4 marks]c. Display the details of hotels that is not yet included in any booking. [4 marks]d. Display all the details of guests who booked hotels on ‘2021-05-01’. [4 marks]e. Display the room number, room standard rate and the hotel id. [4 marks]f. Display the details of countries with country name starting with letters other than any letter within ‘A’ and ‘P’. [3 marks]
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more