DAT 560G: Database Design and SQL
Spring 2025, Mini A
Assignment #4: SQL Part 3
Instructions
1. This is an individual assignment. You may not discuss your approach to solving these questions with anyone, other than the instructor or TA.
2. Please include only your Student ID on the submission.
3. The only allowed material is:
a. Class notes
b. Content posted on Canvas
c. Textbook
4. You are not permitted to use other online resources
5. Questions 1 – 6 are auto-graded on Canvas due by 6 am the day of the next lab
6. The other questions are due on Canvas, by the next lab 7. There will be TA office hours. See the schedule on Canvas.
Background
Renting an apartment with roommates is guaranteed to be a new experience. You’ll make memories with your roommates, learn about their culture and hobbies, and learn how you cope with others in different times.
The database here is focused on renting out apartments to people with roommates. The people renting the apartments are one part of the database, but not the important part of it. We are mostly interested in the apartment buildings, and the owners of apartment buildings.
An apartment building has information about the date built and the year built. We also maintain information on address, city, and number of apartments in the building. Each apartment may be rented to one, or more, people. Other information we have is the total monthly rent for all tenants and the value of the apartment building, if it were sold. A property manager runs the individual apartment buildings. We also have the gender of the property manager.
Buildings (Building, DateBuilt, YearBuilt, Address, City, Apartments, TotalRent, Value, PropertyManager, Gender)
The apartment building is owned by a single company, or be several companies. Each company may own one, or more, apartment buildings. They may also only partial ownership of an apartment building. In that case, they would be partners with other companies. The Property attribute in the Ownership relation is identical to the Building attribute in the Buildings relation. (It’s a foreign key.)
For each owner we maintain information about what percent of the building that company owns. To give an example, if a building is owned by 3 companies, each with a different share, the ownership fraction for each company could be 33%, or one company may own a larger share. In another case, only 1 company owns the building. In this case, their ownership share would be 100%.
Information about the owners includes the city they are in, the number of partners, date the company was founded, and the number of times the partners meet each year. We also have information about the total assets owned by this company (in $M). The company has a manager. The manager’s gender is also kept.
Owners (Company, City, Partners, Meetings, DateFounded, Assets, Manager, Gender)
For each owner, in each building, we also know the date they purchased their share of the building. (To clarify, each company may have bought their share at a different time.) We also know what percent of their ownership stake has a mortgage on it. In the same building it could be that one owner has a mortgage of 80%, while another does not have a mortgage.
Ownership (Property, Company, Percent, PurchaseDate, PurchaseYear, Mortgage)
Tenant information in the database includes information about the people renting apartments. Each apartment may have one, or more, tenants. Frequently, roommates rent an apartment together. In addition to the tenants name, gender, and age, we have information about the apartment. This information includes the building name, the apartment number within the building, and the size of the apartment. Information about the lease includes the date this person started to rent the apartment, their monthly rent, and the duration of the lease in months.
Since many of the apartments have sublets, renters in the same apartment may have started their lease at different dates.
Tenants (Building, Tenant, Gender, Age, Apartment, Size, Rent, LeaseStart, Duration)
Database
The E/R Diagram for the database is below.
Relations:
Buildings (Building, DateBuilt, YearBuilt, Address, City, Apartments, TotalRent, Value, PropertyManager, Gender)
Owners (Company, City, Partners, Meetings, DateFounded, Assets, Manager, Gender)
Ownership (Property, Company, Percent, PurchaseDate, PurchaseYear, Mortgage)
Tenants (Building, Tenant, Gender, Age, Apartment, Size, Rent, LeaseStart, Duration)
Each of these questions is 10 points. Submit answers to these online on Canvas, by 6 am the day of the lab. They are automatically graded.
1. How many companies have ownership in more than one property? Sort the result by the number of properties in descending order.
2. How many buildings with more than 10 apartments have less than 2 tenants? Sort the result by the number of tenants in descending order.
3. List all tenants whose lease duration is higher than the average duration of all tenants. List the tenant's name, the building they live in, their rent, and duration. Sort the result by tenant name.
4. List the Companies with the letter ‘n’ in their names, whose assets are lower than the average number of assets among all the companies. Sort the result by decreasing order of the assets.
5. Find all the buildings that were built on 04/13/2010. Sort the results by building name and value.
6. List the tenants whose lease started in April 2021. Find the tenant’s name, age, apartment size, and the building name. Sort by the tenant’s name.
Each of these questions is 5 points. Submit answers to these on Canvas before the next lab session. They will be graded by TAs.
For each question, submit your SQL code and a screen-shot of the results. If the results are too long, partial results are fine. Include relevant attributes for each result, to explain that the result is correct. Do NOT include many unnecessary attributes. Do NOT use SELECT *.
7. List all buildings where both the total rent and the number of apartments are known (not null) and the total rent is higher than twice the number of apartments. Display the building name, city, number of apartments, and total rent. Sort the results in descending order by total rent and show only the top 10 results. DAT 560G – Spring 2025 – Assignment #4: SQL Part 3
8. Find the number of genders and the average size of apartments rented per tenant gender for all buildings except “'Field House'”. Calculate the average size once with AVG function, and once with sum (size)/count. Are they different? Why?
9. Looking for buildings with the letter ‘m’ in their name, find the highest total rent, the lowest total rent, and the difference between these two values. We only want one row with this information.
10. Among buildings with the letter ‘m’ in the file, find the ones with the highest value. Report the building name and value. Also include the city, the built date, and the manager’s name. Use subqueries.
11. For each city, when is the first building built? When is the last building built? How many days passed between the first building built and the last building built in the same city? Sort the results by city name. Do not report Null cities.
12. List all buildings which has part been purchased by a company in 2015 and have a lower value than the average value of buildings with a male property manager. List the building name and its value. Sort the results by descending order of value.
13. For the buildings with more than the average number of tenants, find the owner, city of the owner, and year of founding of the owner company.
14. How much time did you spend on this assignment?