IT 240Assignment 2-3
Garden Glory Database
Parts A-F Due as Assignment 2
H-Q Due as Assignment 3
Instructions:
These exercises correspond to the Garden Glory Project found at the end of Chapter
3 in the textbook. You will submit the particular queries listed here. Name your
queries by question number. For example, the query for the first question would be
called Aa-OWNER, followed by Ab-OWNED_PROPERTY, etc. Be sure to read the
book thoroughly to understand the auto-increment and other specific requirements
for these relations. Submit an Access database and any other text files compressed as
a ZIP file.
A. Write CREATE TABLE statements for each of the tables. Omit foreign keys. Hint:
Choose Data Definition type in Access.
a. OWNER
b. OWNED_PROPERTY
c. EMPLOYEE
d. GG_SERVICE
e. PROPERTY_SERVICE
B. Copy each query and add the FK constraints specified in the textbook, included
below. Save the resulting queries as new DDL queries. NOTE: Access will
throw errors for correctly formed queries for this question. I advise
you use a text editor like visual studio code for this question
a. OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER
b. PropertyID in PROPERTY_SERVICE must exist in PropertyID in
OWNED_PROPERTY
c. ServiceID in PROPERTY_SERVICE must exist in ServiceID in
GG_SERVICE
d. EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in
EMPLOYEE
C. Write SQL Statements to insert data into each of the five Garden Glory database
tables. Assume that any surrogate key value will be supplied by the DBMS. Use
the data in the attached Excel sheet.
D. Choose ONE of the tables and write a query to select all columns from that table
E. Write an SQL statement to list LastName, FirstName, and CellPhone for all
employees having an experience level of Master.
F. Write an SQL statement to list LastName, FirstName, and CellPhone for all
employees having an experience level of Master and FirstName that begins with
the letter J.
G.
H. Write an SQL statement to list LastName, FirstName, and CellPhone of
employees who have worked on a property in Seattle. Use a subquery.
I. Answer question H but use a JOIN ON syntax.
J. Write an SQL statement to list LastName, FirstName, and CellPhone of
employees who have worked on a property owned by a corporation. Use a
subquery.
K. Answer the previous question using a JOIN ON syntax.
L. Write an SQL statement to show the LastName, FirstName, CellPhone, and sum
of hours worked for each employee.
M. Write an SQL statement to show the sum of hours worked for each
ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in
descending order.
N. Write an SQL Statement to show the sum of HoursWorked for each type of
OWNER but exclude services of employees who have ExperienceLevel of Junior.
O. Write an SQL statement to modify all EMPLOYEE rows with ExperienceLevel of
Master to SuperMaster (Hint: you may want to take a backup of this database).
P. Write SQL statements to switch the values of ExperienceLevel so that all rows
currently having the value Junior will have the value Senior and all rows
currently having the value Senior will have the value Junior. Hint. Use the value
Unknown as a temporary third value
Q. Given your assumptions about cascading deletions in your answer to part B, write
the fewest number of DELETE statements possible to remove all the data in your
database but leave the table structures intact. Be careful not to run these unless
you have backups so you don’t lose prior work!
OwnerID OwnerName
1 Mary Jones
2 DT Enterprises
3 Sam Douglas
4 UNY Enterprises
5 Doug Samuels
OwnerEmailAddress
Mary.Jones@somewhere.com
DTE@dte.com
Sam.Douglas@somewhere.com
UNYE@unye.com
Doug.Samuels@somewhere.com
OwnerType
Individual
Corporation
Individual
Corporation
Individual
PropertyID
PropertyName
1 Eastlake Building
2 Elm St Apts
3 Jefferson Hill
4 Lake View Apts
5 Kodak Heights Apts
6 Jones House
7 Douglas House
8 Samuels House
PropertyType
Office
Apartments
Office
Apartments
Apartments
Private Residence
Private Residence
Private Residence
Street
City
123 Eastlake
Seattle
4 East Elm
Lynwood
42 West 7th St
Bellevue
1265 32nd Avenue Redmond
65 32nd Avenue
Redmond
1456 48th St
Bellevue
1567 51st St
Bellevue
567 151st St
Redmond
State
WA
WA
WA
WA
WA
WA
WA
WA
ZIP
OwnerID
98119
98223
98007
98052
98052
98007
980007
98052
2
1
2
3
4
1
3
5
EmployeeID LastName
1 Smith
2 Evanston
3 Murray
4 Murphy
5 Fontaine
FirstName
Sam
John
Dale
Jerry
Joan
CellPhone
ExperienceLevel
206-254-1234 Master
206-254-2345 Senior
206-254-3456 Junior
585-545-8765 Master
206-254-4567 Senior
ServiceID
ServiceDescription CostPerHour
1 Mow Lawn
25
2 Plant Annuals
25
3 Weed Garden
30
4 Trim Hedge
45
5 Prune Small Tree
60
6 Trim Medium Tree
100
7 Trim Large Tree
125
PropertyServiceID PropertyID ServiceID
1
1
2
3
3
2
4
6
5
5
6
8
7
4
8
7
9
6
10
5
11
8
12
4
13
7
ServiceDate EmployeeID HoursWorked
2 2019-05-05
1
4.5
2 2019-05-08
3
4.5
1 2019-05-08
2
2.75
1 2019-05-10
5
2.5
4 2019-05-12
4
7.5
1 2019-05-15
4
2.75
4 2019-05-19
1
1
1 2019-05-21
2
2.5
3 2019-06-03
5
2.5
7 2019-06-08
4
10.5
3 2019-06-12
4
2.75
5 2019-06-15
1
5
3 2019-06-19
2
4