A realtor leases houses to customers. The realtor has several branches. Each branch has a branch number, an address and a telephone number. Each employee has an employee number, name, address, a salary and holds a position such as manager. Each employee is affiliated to one branch only and manages a number of houses. Each house is managed by a single employee. Each branch has a number of maintenance crews who are responsible for dealing with maintenance issues in the rented homes. These may be tasks such as repairing a leak, fixing a washing machine etc. Each crew has a crew number. Each crew is assigned a maintenance task such as repair a leak at house no 50. Each house for rent has a house number, an address, the square footage of the house, the monthly rent, the number of bedrooms and a status flag indicating whether it is vacant and therefore available for rent. Some houses are apartments, whereas others are single family dwellings. Apartments have a fixed charge for water and electricity. This charge is stored in the database. For a single family house, the renter pays water and electricity based on usage. The area of the land for a single family home is also shown in the database. The database also holds information about the renters, their names, the house they have rented out, their id number and the period of the lease. • Draw an Entity-Relationship diagram • Create the relations • Write relational algebra expressions and expression trees to answer the following queries: i. List all houses that cost less than $500 a month. ii. List the total number of available apartments. iii. List all the crews that have to repair a leak. List the crew numbers. iv. List all the number of customers that are renting apartments and the number of customers that are renting single family homes v. List all the pairs of apartments and houses (a,b) that have both the same number of bedrooms. The first item in the pair should be the apartment. List the house numbers. vi. List the average number of houses managed by an employee. • Express the following constraints in relational algebra i. An apartment with 4 bedrooms should not cost more than $700 per month ii. An employee works at only one branch • Write the following queries in SQL: i. List all the pairs of apartments and houses (a,b) that have both the same number of bedrooms. The first item in the pair should be the apartment. List the house numbers. ii. Find the average rental price of single family homes iii. Find how many apartments have been rented out iv. For each apartment that has been rented out for more than 6 months, reduce the rental price by $50 per month and increase the water rates by $10 a month v. For each employee that manages 5 houses less than the average number of houses managed by an employee, reduce his salary by $5000. • Write the following queries in SQL and each query must use at least one sub-query i. Find the single family home with the highest price ii. Find the lowest priced apartment with 3 bedrooms • Write the following constraints in SQL i. A single family home a land of less than 0.5 acres should have a maximum rent of $2000 per month ii. If an apartment has the same number of bedroom as a single family dwelling, then the apartment must have a lower monthly rental. iii. When updating the rental price of an apartment, check that there is no lower priced apartment with the same number of bedrooms iv. When making any modifications to the rent of single family homes, check that the average rental of single family homes is at least $20,000. • Using SQL create a view that will display the house number, type of house, , number of bedrooms, availability for rent and the monthly rental. • Is this view updateable? Explain. • Write embedded SQL queries to ask a potential renter for a monthly rental and number of bedrooms and find the house whose price is closest to the desired price. Print the type of house
