Problem 3. (20 points) Understanding of Relational Algebra Consider the followin

Question

Problem 3. (20 points) Understanding of Relational Algebra Consider the following relational schema from which the underlined fields represent the keys to represent information about a hotel, employees, rooms, and guests Guest (questld: integer, name: string, address: string) Hotel guests GuestStay(questld: integer check-in-date: date, check-out-date: date, roomld integer) - Guest reservation history Employee (employeeld:integer, e-name: string) Hotel employees Room (roomld: integer, floornumber: integer, room-capacity: integer, emp - Hotel rooms Receptionist (employeeld: integer, desk-type: string, hours-per-day: double) Hotel receptionists Cleaning-Staff (emploveeld: integer, floornumber: integer, hours-per-day: double)- Assignment of floors to clean Cleaning-Assignment (employeeld integer, roomld: integer); Assignment of rooms to ty: Boolean) clean Using this schema, answer the following questions a) (5 pts) Write a relational expression for the following query Find the name and employee number for all employees who are receptionists b) (5 pts) Write a relational expression for the following query: Find the average capacity of rooms in each floor cleaned by the staff member with employee id 123

Explanation / Answer

a)

e-name,employeeId (Employee Receptionist))

is for projection operation and is for join operation. Join is done on common attribute employeeId

b)

floornumber,avg(room-capacity) ( employeeId = 123 floornumber,avg(room-capacity)(Room Cleaning-Staff Employee))

is grouping operator.

Do ask if any doubt. Please upvote if the answer is helpful.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.