- Open a new Microsoft Word document and answer the following question.
- Save the file on your computer with your last name in the file name. (Example: Case_Exercises_1 _Jones.doc)
- Click the Choose File button to find and select your saved document.
StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice.
- For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address.
- For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate.
- For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.
ADDRESS CITY OFFICE OFFICE_NUM OFFICE_NAME AREA STATE ZIP_CODE StayWell-Columbia City 1135 N. Wells Avenue Columbia City Seattle WA StayWell-Georgetown 956 S. Madison Rd Georgetown FIGURE 1-4 Sample data for StayWell offices 98118 Seattle WA 98108 Cole OWNER OWNER_NUM LAST_NAME FIRST_NAME ADDRESS CITY MO100 Moore Elle-May 8006 W. Newport Ave. Reno PA101 Patel Makesh 7337 Sheffield St. Seattle AK 102 Aksoy Ceyda 411 Griffin Rd. Seattle CO103 Meerab 9486 Circle Ave. Olympia KO104 Kowalczyk Jakub 7431 S. Bishop St. Bellingham SI105 Sims Tinydon 527 Primrose Rd. Portland BU106 Burke Ernest 613 Old Pleasant St. Twin Falls RE107 Redman 7681 Fordham St. Seattle 1.0108 Lopez Janine 9856 Pumpkin Hill Ln. Everett B1109 Bianchi Nicole 7990 Willow Dr. New York JO110 Jones Ammarah 730 Military Ave Seattle FIGURE 1-5 Sample data for the owners of StayWell properties STATE ZIP_CODE NV 89508 WA 98119 WA 98131 WA 98506 WA 98226 OR 97203 ID 83303 WA 98119 WA 98213 NY 10005 WA 98126 Seth PROPERTY PROPERTY ID OFFICE NUM ADDRESS SQR FT BDRMS FLOORS MONTHLY RENT OWNER NUM 30 West Thomas Rd 1,600 3 1 1,400 BU106 782 Queen In 2,100 4 2 1,900 AK102 1 9800 Sunbeam Ave. 1,0052 1 1,200 B1109 4 1 105 North Illinois Rd. 1,750 3 1 1,650 KO104 1 887 Vine Rd 1,125 2 1 1,160 SI 105 1 8 Laurel Dr. 2,125 4 2 2,050 MO100 2 447 Goldfield St. 1,6753 2 1.700 CO103 S 2 594 Leatherwood Dr. 2,700 5 2 2.750 RO104 2 504 Windsor Ave 700 2 1 1,050 PA101 2 891 Alton Dr. 1,300 3 1 1,600 L0108 9531 Sherwood Rd. 1,0752 1 1,100 JO110 12 2 2 Bow Ridge Ave 1,400 3 2 1,700 RE107 FIGURE 1-6 Sample data for StayWell properties 6 SERVICE_CATEGORY CATEGORY_NUM CATEGORY_DESCRIPTION 1 Plumbing Heating 3 Painting Electrical systems 5 Carpentry Furniture replacement FIGURE 1-7 Sample category data for StayWell maintenance services EST SPENT NEXT HOURS SERVICE DATE 1 11/01/2019 10/02/2019 11/0W2019 10/01/2019 SERVICE REQUEST SERVICE PROPERTY_CATEGORY_OFFICE DESCRIPTION STATUS ID ID NUMBER NUM HOURS 11 2 The second Problem has bedroom been confirmed upstairs is not Central heating heating up at enginoer has night. been scheduled A new strip light Scheduled is needed for the kitchen 5 1 The bathroom Service rep has 3 door does not confirmed issue close properly Scheduled to be refitted New outlet has been scheduled requested for the first upstairs bedroom. There is currently no outlet) 8 3 2 New paint job Open 10 requested for the common area (lounge) Shower is Problem dripping confirmed when not in use. Plumbers boun scheduled 2 1 Teating unit in Service rep the entrance confirmed the smells like issue to be dust it's burning in the heating unit To be cleaned. Kitchen sink Problem 6 does not drain confirmed properly Plumber scheduled 12 New sofa requested. Open FIGURE 1-8 Sample service request category 0 2 10/07/2019 0 10/0W2019 2 11/12/2019 RESIDENTS RESIDENT_ID FIRST_NAME SURNAME PROPERTY_ID Albie O'Ryan 1 Tariq Khan 1 3 Ismail Salib 1 Callen Beck 2 5 Milosz Polansky 2 6 Ashanti Lucas 2 Randy Woodrue 2 8 Aislinn Lawrence 3 Monique French 3 10 Amara Dejsuwan 4 12 Rosalie Blackmore 4 13 Carina Britton 4 14 Valentino Ortega 5 15 Kaylem Kent 5 16 Alessia Wagner 6 17 Tyrone Galvan 6 18 Constance Fleming 6 19 Eamonn Bain 6 20 Misbah Yacob 7 21 Gianluca Esposito 7 22 Elinor Lake 7 23 Ray Rosas 8 24 Damon Caldwell 8 Dawood Busby 8 26 Dora Harris 8 27 Leroy Stokes 8 28 Tamia Hess 9 29 Amelia Sanders 9 30 Zarah Byers 10 31 Sara Farrow 10 32 Delilah Roy 33 Dougie McDaniel 11 34 Tahir Halabi 11 35 Mila Zhikin 12 36 Glenn Donovan 12 Zayn Fowler 12 FIGURE 1-9 Sample data for StayWell residents 25 10 37 ADDRESS CITY OFFICE OFFICE_NUM OFFICE_NAME AREA STATE ZIP_CODE StayWell-Columbia City 1135 N. Wells Avenue Columbia City Seattle WA StayWell-Georgetown 956 S. Madison Rd Georgetown FIGURE 1-4 Sample data for StayWell offices 98118 Seattle WA 98108 Cole OWNER OWNER_NUM LAST_NAME FIRST_NAME ADDRESS CITY MO100 Moore Elle-May 8006 W. Newport Ave. Reno PA101 Patel Makesh 7337 Sheffield St. Seattle AK 102 Aksoy Ceyda 411 Griffin Rd. Seattle CO103 Meerab 9486 Circle Ave. Olympia KO104 Kowalczyk Jakub 7431 S. Bishop St. Bellingham SI105 Sims Tinydon 527 Primrose Rd. Portland BU106 Burke Ernest 613 Old Pleasant St. Twin Falls RE107 Redman 7681 Fordham St. Seattle 1.0108 Lopez Janine 9856 Pumpkin Hill Ln. Everett B1109 Bianchi Nicole 7990 Willow Dr. New York JO110 Jones Ammarah 730 Military Ave Seattle FIGURE 1-5 Sample data for the owners of StayWell properties STATE ZIP_CODE NV 89508 WA 98119 WA 98131 WA 98506 WA 98226 OR 97203 ID 83303 WA 98119 WA 98213 NY 10005 WA 98126 Seth PROPERTY PROPERTY ID OFFICE NUM ADDRESS SQR FT BDRMS FLOORS MONTHLY RENT OWNER NUM 30 West Thomas Rd 1,600 3 1 1,400 BU106 782 Queen In 2,100 4 2 1,900 AK102 1 9800 Sunbeam Ave. 1,0052 1 1,200 B1109 4 1 105 North Illinois Rd. 1,750 3 1 1,650 KO104 1 887 Vine Rd 1,125 2 1 1,160 SI 105 1 8 Laurel Dr. 2,125 4 2 2,050 MO100 2 447 Goldfield St. 1,6753 2 1.700 CO103 S 2 594 Leatherwood Dr. 2,700 5 2 2.750 RO104 2 504 Windsor Ave 700 2 1 1,050 PA101 2 891 Alton Dr. 1,300 3 1 1,600 L0108 9531 Sherwood Rd. 1,0752 1 1,100 JO110 12 2 2 Bow Ridge Ave 1,400 3 2 1,700 RE107 FIGURE 1-6 Sample data for StayWell properties 6 SERVICE_CATEGORY CATEGORY_NUM CATEGORY_DESCRIPTION 1 Plumbing Heating 3 Painting Electrical systems 5 Carpentry Furniture replacement FIGURE 1-7 Sample category data for StayWell maintenance services EST SPENT NEXT HOURS SERVICE DATE 1 11/01/2019 10/02/2019 11/0W2019 10/01/2019 SERVICE REQUEST SERVICE PROPERTY_CATEGORY_OFFICE DESCRIPTION STATUS ID ID NUMBER NUM HOURS 11 2 The second Problem has bedroom been confirmed upstairs is not Central heating heating up at enginoer has night. been scheduled A new strip light Scheduled is needed for the kitchen 5 1 The bathroom Service rep has 3 door does not confirmed issue close properly Scheduled to be refitted New outlet has been scheduled requested for the first upstairs bedroom. There is currently no outlet) 8 3 2 New paint job Open 10 requested for the common area (lounge) Shower is Problem dripping confirmed when not in use. Plumbers boun scheduled 2 1 Teating unit in Service rep the entrance confirmed the smells like issue to be dust it's burning in the heating unit To be cleaned. Kitchen sink Problem 6 does not drain confirmed properly Plumber scheduled 12 New sofa requested. Open FIGURE 1-8 Sample service request category 0 2 10/07/2019 0 10/0W2019 2 11/12/2019 RESIDENTS RESIDENT_ID FIRST_NAME SURNAME PROPERTY_ID Albie O'Ryan 1 Tariq Khan 1 3 Ismail Salib 1 Callen Beck 2 5 Milosz Polansky 2 6 Ashanti Lucas 2 Randy Woodrue 2 8 Aislinn Lawrence 3 Monique French 3 10 Amara Dejsuwan 4 12 Rosalie Blackmore 4 13 Carina Britton 4 14 Valentino Ortega 5 15 Kaylem Kent 5 16 Alessia Wagner 6 17 Tyrone Galvan 6 18 Constance Fleming 6 19 Eamonn Bain 6 20 Misbah Yacob 7 21 Gianluca Esposito 7 22 Elinor Lake 7 23 Ray Rosas 8 24 Damon Caldwell 8 Dawood Busby 8 26 Dora Harris 8 27 Leroy Stokes 8 28 Tamia Hess 9 29 Amelia Sanders 9 30 Zarah Byers 10 31 Sara Farrow 10 32 Delilah Roy 33 Dougie McDaniel 11 34 Tahir Halabi 11 35 Mila Zhikin 12 36 Glenn Donovan 12 Zayn Fowler 12 FIGURE 1-9 Sample data for StayWell residents 25 10 37
