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)?

Attachments:

Expert's Answer

911 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. 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. The LineItem class will represent an individual

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

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of