A realtor leases houses to customers. The realtor has several branches. Each branch has a branch number, an address and…
• 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
Computer Science homework help
Save your time - order a paper!
Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlinesOrder Paper Now