Execute sql in MySQL and confirm that the resulting database contains nine populated tables. Create an ER diagram and corresponding relational schema

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Instructions

This document constitutes the final project for the course. If you are doing Assignment 4, you only have to complete Parts 1 – 3 below, and this project will be worth 30% of your course grade. If you are doing the extended version of the project, complete all parts, you will not have to do Assignment 4, and this extended project will be worth 40% of your course grade.

• Execute sql in MySQL and confirm that the resulting database contains nine populated tables.
• Create an ER diagram and corresponding relational schema based on the following:
  1. First, use rows 5 – 77 of sql for the tables, columns, primary keys and foreign keys. Note that some modifications will have to be made in Part b.
  2. Second, use the following information to add to/update the results of Part a. Note that some of the following may be different than what is indicated in sql (e.g.; nullable columns).
     • Corporate client industry is
     • There can be multiple corporate client locations for a given corporate
     • A manager’s full name is composed of his/her first and last
     • A manager’s age is derived from his/her birthdate and the current
     • Not every manager will have a
     • Apartment numbers are partially unique (unique within a building).
     • Each manager manages at least one building, but each building has only one manager.
     • Inspectors track the date of the last inspection and date of the next
     • An apartment can be leased to at most one corporate client, and each corporate client leases at least one apartment.
     • Each apartment is cleaned by at least 2 staff members, and each staff member cleans between 4 and 7
     • A manager resides in one and only one building, but a building can have zero or one managers residing in
     • Each inspector inspects between 3 and 5 buildings, and each building is inspected by at least one inspector.
     • Each building has at least one apartment, and each apartment is in one and only one building.
     • Each corporate client can refer zero or one other clients, and each referred client may have been referred by only one other client or no

 

• Using only the data in the hafh_project database (i.e,; disregard Part 2) write queries that answer/perform the following and save them in a single .sql file. Label each query with a commented-out line indicating which question is being answered. You do not have to save the outputs of the queries, but I will execute your script on my machine to verify it performs the tasks as expected. You can only use one query per
  1. What is the most common apartment number?
  2. How many different apartment numbers are there?
  3. How many different apartments (number and building) are there?
  4. Which apartments (number and building) have no clients in them?
  5. What is the average rent for two-bedroom apartments?
  6. How many buildings are there?
  7. Which building(s) has the widest range in rent prices?
  8. List all the managers (first and last names) with their salaries in order of oldest to youngest.
  9. Which manager(s) (first and last names) received the largest bonus? What was it?
  10. Which manager(s) manage(s) the most buildings? How many?
  11. Who are the manager(s) and their phone numbers of the buildings that were inspected by Bianca?
  12. Which corporate client(s) referred at least one other client, how many did each refer, and which building and apartment do they live in?
  13. Which corporate clients were referred by no one?
  14. What is the name(s) of the staff member(s) who cleans the fewest apartments?
  15. For the staff member(s) who cleans the fewest apartments, which apartments (number and building) does (s)he clean? Include the name(s) of the staff
• Complete this question only if you are doing the extended version of the project. Using only the data in the hafh_project database (i.e,; disregard Part 2) write queries that answer/perform the following and add them to the same .sql file you created for Part 3. Label each query with a commented-out line indicating which question is being You do not have to save the outputs of the queries, but I will execute your script on my machine to verify it performs the tasks as expected. You can only use one query per question.
  1. What are the first and last names of the manager(s), their respective ages (use the CURDATE or similar function) and bonuses, who manage the building(s) with the most corporate clients?
  2. Repeat Part a for managers who manage only one
  3. Which corporate client(s), staff member(s), inspector(s) and manager(s) are associated with the building that had the most recent inspection?
  4. For the apartment(s) (number and building) that has/have the lowest average rent-per- bedroom, who are the associated inspector(s), manager(s), corporate client(s) and staff member(s)?

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme