For the following exercises, use the Hotel schema defined at the start of the Exercises at the end of Chapter 4.5.8 Describe the relations that would be produced by the following relational algebra operations:(a) PhotelNo(sprice . 50(Room))(b) sHotel,hotelNo 5 Room.hotelNo(Hotel 3 Room)(c) PhotelName(Hotel 1Hotel.hotelNo 5 Room.hotelNo(sprice . 50 (Room)))(d) Guest 5 (sdateTo $ ‘1-Jan-2007’(Booking))(e) Hotel 2 Hotel.hotelNo 5 Room.hotelNo(sprice . 50 (Room))(f) PguestName, hotelNo(Booking 1 Booking.guestNo 5 Guest.guestNo Guest) 4 PhotelNo(scity 5 ‘London’(Hotel))5.9 Provide the equivalent tuple relational calculus and domain relational calculus expressions for each of the relationalalgebra queries given in Exercise 5.8.5.10 Describe the relations that would be produced by the following tuple relational calculus expressions:(a) {H.hotelName | Hotel(H) Ù H.city 5 ‘London’}(b) {H.hotelName | Hotel(H) Ù ($R) (Room(R) Ù H.hoteINo = R.hoteINo Ù R.price > 50)}(c) {H.hotelName | Hotel(H) Ù ($B) ($G) (Booking(B) Ù Guest(G) Ù H.hoteINo 5 B.hoteINo ÙB.guestNo 5 G.guestNo Ù G.guestName 5 ‘John Smith’)}(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Ù Guest(G) Ù Booking(B1) ÙBooking(B2) Ù H.hoteINo 5 B1.hoteINo Ù G.guestNo 5 B1.guestNo Ù B2.hotelNo 5 B1.hotelNo ÙB2.guestNo 5 B1.guestNo Ù B2.dateFrom B1.dateFrom}5.11 Provide the equivalent domain relational calculus and relational algebra expressions for each of the tuple relationalcalculus expressions given in Exercise 5.10.5.12 Generate the relational algebra, tuple relational calculus, and domain relational calculus expressions for the follow-ing queries:(a) List all hotels.(b) List all single rooms with a price below £20 per night.(c) List the names and cities of all guests.(d) List the price and type of all rooms at the Grosvenor Hotel.(e) List all guests currently staying at the Grosvenor Hotel.(f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if theroom is occupied.(g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Grosvenor Hotel.5.13 Using relational algebra, produce a report of all employees from the IT and planning departments who are bornafter 1990.The following tables form part of a database held in an RDBMS:Employee (empNo, fName, lName, address, DOB, sex, position, deptNo)Department (deptNo, deptName, mgrEmpNo)Project (projNo, projName, deptNo)WorksOn (empNo, projNo, dateWorked, hoursWorked)where Employee contains employee details and empNo is the key.Department contains department details and deptNo is the key. mgrEmpNo identifies theemployee who is the manager of the department. There is only one manager foreach department.Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project).and WorksOn contains details of the hours worked by employees on each project, and empNo/projNo/dateWorked form the key.
Question
For the following exercises, use the Hotel schema defined at the start of the Exercises at the end of Chapter 4.5.8 Describe the relations that would be produced by the following relational algebra operations:(a) PhotelNo(sprice . 50(Room))(b) sHotel,hotelNo 5 Room.hotelNo(Hotel 3 Room)(c) PhotelName(Hotel 1Hotel.hotelNo 5 Room.hotelNo(sprice . 50 (Room)))(d) Guest 5 (sdateTo ‘1-Jan-2007’(Booking))(e) Hotel 2 Hotel.hotelNo 5 Room.hotelNo(sprice . 50 (Room))(f) PguestName, hotelNo(Booking 1 Booking.guestNo 5 Guest.guestNo Guest) 4 PhotelNo(scity 5 ‘London’(Hotel))5.9 Provide the equivalent tuple relational calculus and domain relational calculus expressions for each of the relationalalgebra queries given in Exercise 5.8.5.10 Describe the relations that would be produced by the following tuple relational calculus expressions:(a) {H.hotelName | Hotel(H) Ù H.city 5 ‘London’}(b) {H.hotelName | Hotel(H) Ù (R) (Room(R) Ù H.hoteINo = R.hoteINo Ù R.price > 50)}(c) {H.hotelName | Hotel(H) Ù (G) (Booking(B) Ù Guest(G) Ù H.hoteINo 5 B.hoteINo ÙB.guestNo 5 G.guestNo Ù G.guestName 5 ‘John Smith’)}(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Ù Guest(G) Ù Booking(B1) ÙBooking(B2) Ù H.hoteINo 5 B1.hoteINo Ù G.guestNo 5 B1.guestNo Ù B2.hotelNo 5 B1.hotelNo ÙB2.guestNo 5 B1.guestNo Ù B2.dateFrom B1.dateFrom}5.11 Provide the equivalent domain relational calculus and relational algebra expressions for each of the tuple relationalcalculus expressions given in Exercise 5.10.5.12 Generate the relational algebra, tuple relational calculus, and domain relational calculus expressions for the follow-ing queries:(a) List all hotels.(b) List all single rooms with a price below £20 per night.(c) List the names and cities of all guests.(d) List the price and type of all rooms at the Grosvenor Hotel.(e) List all guests currently staying at the Grosvenor Hotel.(f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if theroom is occupied.(g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Grosvenor Hotel.5.13 Using relational algebra, produce a report of all employees from the IT and planning departments who are bornafter 1990.The following tables form part of a database held in an RDBMS:Employee (empNo, fName, lName, address, DOB, sex, position, deptNo)Department (deptNo, deptName, mgrEmpNo)Project (projNo, projName, deptNo)WorksOn (empNo, projNo, dateWorked, hoursWorked)where Employee contains employee details and empNo is the key.Department contains department details and deptNo is the key. mgrEmpNo identifies theemployee who is the manager of the department. There is only one manager foreach department.Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project).and WorksOn contains details of the hours worked by employees on each project, and empNo/projNo/dateWorked form the key.
Solution
This question is quite complex and involves multiple parts. Here's a step-by-step breakdown:
-
The first part of the question asks you to describe the relations that would be produced by the given relational algebra operations. This would involve understanding the operations and the schema of the Hotel database. For example, operation (a) PhotelNo(sprice . 50(Room)) is selecting rooms with a price less than 50 and projecting the hotelNo attribute.
-
The second part asks for equivalent tuple relational calculus and domain relational calculus expressions for the given relational algebra queries. This would involve translating the operations into the respective calculus expressions.
-
The third part asks you to describe the relations that would be produced by the given tuple relational calculus expressions. Similar to the first part, this involves understanding the expressions and the schema of the Hotel database.
-
The fourth part asks for equivalent domain relational calculus and relational algebra expressions for the given tuple relational calculus expressions. This is similar to the second part but in reverse.
-
The fifth part asks you to generate relational algebra, tuple relational calculus, and domain relational calculus expressions for the given queries. This involves understanding the queries and translating them into the respective expressions.
-
The final part asks you to use relational algebra to produce a report of all employees from the IT and planning departments who are born after 1990. This would involve selecting employees with the specified criteria.
Each of these parts requires a deep understanding of relational algebra and calculus, as well as the schema of the Hotel database.
Similar Questions
The following tables form part of a database held in a relational DBMS:Hotel (hotelNo, hotelName, city)Room (roomNo, hotelNo, type, price)Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)Guest (guestNo, guestName, guestAddress)where Hotel contains hotel details and hotelNo is the primary key;Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key;Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key;Guest contains guest details and guestNo is the primary key.
a) Below is a list of potential entities for a hotel database;– Student– Lecturer– CoursesDraw an ERD diagram showing all possible attributes together with PK and FK.
#9What is the #1 function of any hotel?A. Make a profitB. Decrease turnoverC. Sell conventionsD. Sell roomsE. Provide an outstanding guest experience
•A company provides catering services for clients who need special-occasion, celebratory dinners. For each dinner, a number of dishes are to be offered. The dinner will be held at a venue. The company will provide staff to serve meals at the venue. •The company needs a database to store data related to this business activity a. Identify and list the entities b. Identify pairs of entities where a direct relationship exist c.-For each pair of relationship draw the entity relationship diagram justifying the choice of cardinalities
A company provides catering services for clients who need special-occasion, celebratory dinners. For each dinner, a number of dishes are to be offered. The dinner will be held at a venue. The company will provide staff to serve meals at the venue. •The company needs a database to store data related to this business activity a. Identify and list the entities b. Identify pairs of entities where a direct relationship exist c.-For each pair of relationship draw the entity relationship diagram justifying the choice of cardinalities
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.