Staywell finds and manages accommodation for owners of student accommodation in the Seattle Area. The company rents out and helps to maintain 1-5-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.

 

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 surname (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.

  

RESIDENTS table

The SERVICE_REQUEST table shows requests that residents have put into the offices for maintenance. Each row contains a unique service ID number, the property ID, the category number associated with the type of work, the office managing the property, a description of the request, the current status of the request, the estimated hours to complete the request, the hours spent on the request, and the scheduled service date.

 

SERVICE_REQUEST table

 

The SERVICE_CATEGORY table includes details of these services. The CATEGORY_NUM provides a unique number for the service, and CATEGORY_DESCRIPTION stores a description of what the service is.

 

 

SERVICE_CATEGORY table

 

Task 1: Create  view named SMALL_PROPERTY. It consists of the property ID, office number, bedrooms, floor, monthly rent, and owner number for every property whose square footage is less than 1,250 square feet.

 

Task 2: Write and execute the command to retrieve the office number, property ID, and monthly rent for every property in the SMALL_PROPERTY view with a monthly rent of $1150 or more.

 

Task 3: Without using the SMALL_PROPERTY VIEW, retrieve the office number, property ID, and monthly rent for every property whose square footage is less than 1,250 square feet and a monthly rent of $1150 or less.

 

Task 4: Create a view named PROPERTY_OWNERS. It consists of the property ID, office number, square footage, bedrooms, floors, monthly rent, and owner's last name for every property in which the number of bedrooms is three.

 

Task 5: Write and execute the command to retrieve all information for a property for every property in the PROPERTY_OWNERS view with a monthly rent of less than $1675.

 

Task 6: Repeat Task 5 without using the PROPERTY_OWNERS VIEW.

 

Task 7: Create a view named MONTHLY_RENTS. It consists of three columns: the first is the number of bedrooms, the second is the average square feet, and the third is the average monthly rent for all properties in the PROPERTY table that have that number of bedrooms. Use BEDROOMS, AVG_SQUARE_FEET, and AVG_MONTHLY_RENT as the column names. Group and order the rows by number of bedrooms.

 

Task 8: Write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is greater than $1,400.

 

Task 9: Without using the MONTHLY_RENTS VIEW, write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is less than $1,400.

 

Task 10: User Oliver must be able to retrieve data from the PROPERTY table.

Task 11: Users Crandall and Perez must be able to add new owners and properties to the database.

 

 

Task 12: Users Johnson and Klein must be able to change the monthly rent of any property.

 

Task 13: All users must be able to retrieve the office number, monthly rent, and owner number for every property.

Task 14: User Klein must be able to add and delete service categories.

 

Task 15: User Adams must be able to create an index on the SERVICE_REQUEST table.

 

Task 16: Users Adams and Klein must be able to change the structure of the PROPERTY table.

 

Task 17: User Klein must have all privileges on the OFFICE, OWNER, and PROPERTY tables.

 

task 18: Revoke all privileges from user Adams.

 

 

Task 19: Create an index named OWNER_INDEX1 on the STATE column in the OWNER table.

 

 

 

Task 20: Create an index named OWNER_INDEX2 on the LAST_NAME column in the OWNER table.

 

 

 

Task 21: Create an index named OWNER_INDEX3 on the STATE and CITY columns in the OWNER table. List the states in descending order.

 

 

 

Task 22: Delete the OWNER_INDEX3 index from the OWNER table.

 

 

 

Task 23: List every column in the PROPERTY table and its associated data type.

 

 

 

Task 24: List every table that contains a column named OWNER_NUM. The output table should display one column named TABLE_NAME.

 

 

 

Task 25: Add the OWNER_NUM column as a foreign key in the PROPERTY table.

 

OFFICE_NUM 1 2 OFFICE_NAME StayWell-Columbia City StayWell-Georgetown ADDRESS 1135 N. Wells Avenue 986 S. Madison Rd AREA Columbia City Georgetown CITY Seattle Seattle STATE WA WA ZIP CODE 98118 98108