Suppose you have the following relational database scheme for a real estate agency:
Real Estate Office (officeID, street, city, postalCode)
Employee (employeeID, name, surname, position, gender, salary, officeID)
Rental Property (propertyID, street, city, postalCode, type, number of rooms, rent, property ownerID, employeeID, officeID)
Customer (customerID, name, surname, phone number, maxRent)
Property Owner (Property OwnerID, name, surname, address, teephone number)
Property show (clientID, propertyID, show time comment)
Record (customerID, officeID, employeeID, recordDate)
First, determine the appropriate primary key for each relationship. Then create the appropriate queries using SQL.
1. Relational Algebra Queries
List the ID, street, city and rent information of the properties managed by the employees in the office located on "Galata Cad" street.
b. List all cities with offices but no rental properties.
List the names and comments of all customers who have viewed a rental property.
d.List the first and last names of all customers viewing three-room properties.
e. List customers who are registered customers who have never been shown a property.
2.SQL Queries
List the ID, street, city and rent information of the properties managed by the employees in the office located on "Galata Cad" street.
b.List all cities with offices but no rental properties
c. List customers who are registered customers who have never been shown a property.
d. List all employees whose salaries are higher than the average salary.
Find the number of properties managed by each employee