DAT 560G – Fall 2021 – Practice Final Exam [1] WASHINGTON UNIVERSITY IN ST. LOUIS OLIN BUSINESS SCHOOL DAT 560G: Database Design and SQL Fall 2021, Mini A Practice Final Exam A description of the database, sample questions, and answers 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. We will only answer clarification questions. 2. Please write your SID at the bottom of this page. 3. You are not permitted to use any online resources. 4. You are supposed to have only MySQL, and Ms. Word open on your laptop. A browser is necessary at the last moments to submit your work on Canvas. 5. Submit the pdf file on Canvas page. Keep track of your time. 6. Don’t worry if you can’t complete this exam. Grades in the course will be curved. GOOD LUCK 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 maintina 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) DAT 560G – Fall 2021 – Practice Final Exam [2] 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. DAT 560G – Fall 2021 – Practice Final Exam [3] 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) 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 the number of rows returned. You may include a few additional attributes, but do not include many useless attributes. The attributes returned in the query must make it clear that the result is correct. Please write your SID before Question 1: Student ID: ___________________________ Each of these questions is 10 points, unless noted otherwise A) List owners of apartment buildings in St. Louis whose mortgage is more than 55%. Also include the year the building was built, the year the company purchased their part in the building, and their share of the building. Sort the results by building name and then by purchase year. USE Apartments; SELECT Building, YearBuilt, City, Company, Percent, PurchaseYear, Mortgage FROM Buildings JOIN Ownership ON Building = Property DAT 560G – Fall 2021 – Practice Final Exam [4] WHERE City = 'St. Louis' AND Mortgage > 55 ORDER BY Building, PurchaseYear; /* 15 rows returned B) For each apartment building built before 1955, identify all the current owners, the ownership percent for each owner, and the total assets each of these owners holds. SELECT Building, Property, YearBuilt, Company, Percent, Assets FROM Buildings LEFT JOIN Ownership ON Building = Property LEFT JOIN Owners USING(Company) WHERE YearBuilt < 1955; /* 10 rows returned DAT 560G – Fall 2021 – Practice Final Exam [5] C) For each apartment building built before 1965, list the number of apartments in the building, total rent for the building, the number of tenants included the this database, total rent paid by these tenants, and the average duration of leases. Sort the results by rent paid by the tenants in the database. SELECT Building, MIN(YearBuilt), MIN(Apartments), MIN(TotalRent), COUNT(Tenant), SUM(Rent) AS ActualRent, AVG(Duration) FROM Buildings LEFT JOIN Tenants USING(Building) WHERE YearBuilt < 1965 GROUP BY Building ORDER BY ActualRent; /* 16 rows returned DAT 560G – Fall 2021 – Practice Final Exam [6] D) For each apartment building built before 1965, calculate density in the building. Density is defined as the ratio of total tenants to the number of apartments in the building. Sort the results increasing in year built and decreasing in density. SELECT Building, MIN(YearBuilt), MIN(Apartments), COUNT(Tenant), COUNT(Tenant)/MIN(Apartments) AS Density FROM Buildings LEFT JOIN Tenants USING(Building) WHERE YearBuilt < 1965 GROUP BY Building ORDER BY YearBuilt ASC, Density DESC; /* 16 rows returned DAT 560G – Fall 2021 – Practice Final Exam [7]
欢迎咨询51作业君
