How to Use the Code Editor

Select the "Run Code" button to execute the program.

Select the "Calculate Grade" button to generate a score based on the completed tasks.

Continue to modify, run, and calculate your code until you are happy with the grade.

Select the "Submit" button to turn in the assignment to your instructor.

MySQL Lab Instructions

Before running queries, check the selected database matches the database indicated in the instructions.

The "Run Code" button will run the contents of the query.sql file only.

Complete each task in order and press the Run Test button for that specific task before moving on$.$

Only select $$Calculate Grade$$ when you are satisfied with your answer.

Database Model: StayWell

Staywell finds and manages accommodation for owners of student accommodation in the Seattle Area. The company rents out and helps to maintain $15-$bedroom properties located in two main areas in the city, Columbia City and Georgetown. This is done on behalf of property owners based both in the local area and throughout the United States. Each location is administrated by a different office, StayWell$-$Columbia City and StayWell$-$Georgetown.

StayWell wishes to expand its business. The current model relies on advertisements in student and university publications in print and online, but prospective owners and renters need to contact the offices and speak to an administrator on all matters relating to renting of properties. The office organizes maintenance services for a fee, which is also currently done via email or direct communication.

StayWell has decided that the best way to increase efficiency and move toward an e$-$commerce$-$based business model is to store all the data about the properties, owners, tenants and services in databases. This will mean that the information can be easily accessed. StayWell hopes that these databases can then be used in future projects such as mobile apps and online booking systems.

The data is split into several tables, shown below:

The OFFICE table shows the office number, office name, address, area, city, state, and ZIP code.

OFFICE table

StayWell stores information about the owners of each property in the OWNER table. Each owner is identified by a unique owner number that consists of two uppercase letters followed by a three$-$digit number. For each owner, the table also includes the last name, first name, address, city, state, and ZIP code. Notice the owners are from across the United States. Although some apartments may be owned by a couple or a family, only the primary contact is given.

OWNER table

Each property at each location is identified by a property ID$,$ as seen in the PROPERTY table. Each property also includes the office number that manages the property, address, floor size, the number of bedrooms, the number of floors, monthly rent per property, and the owner number. The property ID is an integer unique for each property.

PROPERTY table

The RESIDENTS table includes details about the residents living in each property. The RESIDENTS table includes the first name and last name for each of the residents, along with a resident ID$.$ The PROPERTY$\_$ID is the unique identification number of the property in which they are staying.