StayWell focuses on finding and managing accommodation for owners of student accommodation in the Seattle area. Currently, it operates in two main areas of the city with separate administrative offices: Colombia City and Georgetown. StayWell rents out $1$ to $5-$bedroom properties to students on behalf of property owner $7.$ You are assigned, as the database administrator, to collect and manage transactional data of the StayWell operations. You will begin by creating the database and tables for StayWell and will transfer the collected data. Then, you will need to execute further database operations as StayWell$$s operations expand.

The OFFICE table contains information about the two StayWell office locations, including the office number, office name, and address.

OFFICE table

OFFICE table

The SERVICE$\_$CATEGORY table contains information regarding the different service categories, such as their numerical designation and description.

SERVICE$\_$CATEGORY table

SERVICE$\_$CATEGORY table

The SERVICE$\_$REQUEST table contains information on service requests for each property. The SERVICE$\_$REQUEST table stores the service ID$,$ property ID$,$ service category number, the managing office ID$,$ request description, the request's status, estimated hours, hours spent, and the service date.

SERVICE$\_$REQUEST

SERVICE$\_$REQUEST table

The RESIDENTS table contains the information on each resident, such as their resident ID$,$ first name, surname $($last name$),$ and property ID$.$

RESIDENTS table

RESIDENTS table

The OWNER table contains information on each property owner, such as their owner number $($ID$),$ last name, first name, and address.

OWNER table

OWNER table

The PROPERTY table contains information on each property, including the property ID$,$ managing office number, address, square feet, bedrooms, floors, monthly rent, and the owner.

PROPERTY table

PROPERTY table

Project Objectives

Create, use, and drop views

Update the data over views instead of tables

Create and test constraints such as foreign keys

Use mathematical functions over numerical data

Calculate dates with SQL functions

Create triggers before inserting data into tables

Best practices to follow:

Write detailed comments for SQL statements.

Organize and structure SQL statements for readability.

Your tasks are as follows:

Task $1$: The property management team of StayWell is considering separating the database operations of its Colombia City and Georgetown offices. Therefore, you need to create a COLOMBIA$\_$CITY$\_$PROPERTY VIEW to filter the properties where OFFICE$\_$NUM equals to $1.$ Be sure to show all the PROPERTY table information on each property except for the managing office number $($OFFICE$\_$NUM$).$

Task $2$: Colombia office employees started using COLOMBIA$\_$CITY$\_$PROPERTY and they want to update the MONTHLY$\_$RENT to $1200$ for properities where their PROPERTY$\_$ID equals to $1.$ You need to update the rent information over the VIEW.

Task $3$: The property management team wants to continue property database operations by using the common PROPERTY table. Therefore, they no longer require the COLOMBIA$\_$CITY$\_$PROPERTY VIEW, and they want you to remove it from the database.

Task $4$: The property management team wants to ensure that the property and owner information always correspond. In other words, they do not want any property in the database where the OWNER$\_$NUM information does not match with anyone in the OWNER table. You need to alter the PROPERTY table by changing the OWNER$\_$NUM field to refer to the OWNER table.

Task $5$: The marketing team wants to print out branded name tags for the properties with the StayWell logo and resident names. They require the first and last names of the residents combined and converted to uppercase for printing. You need to send them a single$-$column table with the column name of LABEL.

Task $6$: The property management team wants to learn and track the average size of the properties in StayWell. You need to return the average square feet of each property, calculated and rounded to an integer value, displayed as AVG$\_$SQUARE$\_$FEET.

Task $7$: The maintenance team wants to send SMS reminders in relation to upcoming service operations. Therefore, they request the SERVICE$\_$ID $1$ day prior to the planned service dates of the service requests as the REMINDER column. However, they only require this information for those service requests where the NEXT$\_$SERVICE$\_$DATE is set.

Task $8$: The development team of StayWell wants to ensure that all the STATE fields of the owners are uppercase in the database. Create the state$\_$insert trigger prior to incorporation in the OWNER table to ensure that this requirement is satisfied. From the textbook, A Guide to SQL $10$th edition