Ryan Roe, Kaitlin Paasch, Wesley Hudson, and Patrick Karuri

ISYS 317

Professor Memory Nyandoro

3rd June 2023

Garden Glory Project

Problem Description:

• Garden Glory is a partnership that provides gardening and yard maintenance services to

individuals and organizations. Garden Glory is owned by two partners. They employ two office

administrators and a number of full- and part-time gardeners. Garden Glory will provide one-

time garden services, but it specializes in ongoing service and maintenance. Many of its

customers have multiple buildings, apartments, and rental houses that require gardening and

lawn maintenance services.

Consider the sample list below of owners, properties, and services. Submit Answers to the

following Questions:

1) Describe modification problems (with Examples) that are likely to occur if Garden Glory

attempts to maintain this list in a spreadsheet.

A) The first problem we identified within the Garden Glory spreadsheet is the redundancy of

data when entering information about owners, properties, and services. For example,

when looking at the spreadsheet, if multiple services are provided to the same owner or

their property, then the owner data is entered repeatedly.

B) In addition, another problem persistent within the original spreadsheet is the lack of

scalability. As the business grows, the spreadsheet should be able to grow with the

company. In this instance, the spreadsheet hinders growth. Ultimately, it will become
 unmanageable to maintain this spreadsheet with multiple owners, properties, and

services.

C) Deleting one row removes data across multiple themes in the current spreadsheet. There

is no place to record the owner if there is no property attached to the owner. There is no

place to record multiple services for the same property. There is no clear way to identify

multiple owners of the same property. Changing the information of an owner requires

edits of multiple rows, increasing the chance for human error.

2) Split the list into tables such that each has only one theme. Create appropriate ID

columns. Use a linking column to represent the relationship between entities. Demonstrate

that the modification problems you identified in step 1 have been eliminated. Ensure that

your final tables are in correct database notation.

A) Table 1:

OWNERS (OwnerID, OwnerName, OwnerEmail, OwnerType)

OwnerID OwnerName OwnerEmail OwnerType

1 DT Enterprises DTE@dte.com Corporation

2 Mary Jones Mary.Jones@somewhere.com Individual

3 UNY Enterprises UNYE@unye.com Corporation

4 Doug Samuels Doug.Samuels@somewhere.com Individual

5 Sam Douglas Sam.Douglas@somewhere.com Individual

Description

The OWNERS table includes the various owners associated with the properties being

serviced. The table captures the owners’ information in one dataset, allowing for a more

accessible review of the data related to the owners. The dataset includes owner IDs,
 owner names, owner emails, and owner type. Furthermore, the table is a key for the

additional tables, allowing users to quickly reference the OWNERS table to track who

belongs to which property and the services being provided.

Keys

Primary Key - OwnerID:

The OwnerID serves as the primary key as the data is significant and is used to link

information between subsequent tables from the pool of data/themes.

B) Table 2:

PROPERTIES (PropertyID, OwnerID, PropertyName, PropertyType, Street, City, State,

Zip)

PropertyID OwnerID PropertyName PropertyTy Street City State Zip

1 1 Eastlake Office 123 Eastlake Seattle WA 98119

2 1 JeffersonHill Office 42 West 7th Bellevue WA 98007

3 2 ElmStApts Apartments 4 East Elm Lynwood WA 98223

4 2 JonesHouse Private 1456 48th St Bellevue WA 98007

5 3 KodakHeights Apartments 65 32nd Redmond WA 98052

6 4 Samuels House Private 567 151st St Redmond WA 98052

7 5 LakeView Apts Apartments 1265 32nd Redmond WA 98052

8 5 DouglasHouse Private 1567 51st St Bellevue WA 98007

Description

The PROPERTIES table includes the properties associated with their owners and

geographical location. The table captures the properties dataset, allowing for a clear

overview of the properties, types of properties, and their owners. The dataset includes

property IDs, owner IDs, property name, property type, and the property address.

Furthermore, the table bridges the information between the OWNERS and

PROPERTIES, allowing for easier manipulation and modifications without disturbing

the data associated with the property owners.

Keys

Primary Key - PropertyID:

The PropertyID serves as the primary key as the data is significant and is used to link

information between the OWNERS and SERVICES table.

Foreign Key - OwnerID:

The OwnerID serves as the foreign key as the data feeds into the PROPERTIES table

from the OWNERS table.

C) Table 3:

SERVICES (ServiceID, PropertyID, OwnerID, ServiceType, ServiceDate, Charge)

ServiceID PropertyID OwnerID ServiceType ServiceDate Charge

2 2 1 Plant Annuals 2023-05-08 $75

3 3 2 Mow Lawn 2023-05-08 $60

4 4 2 Mow Lawn 2023-05-10 $60

5 5 3 Trim Hedge 2023-05-12 $35

6 6 4 Mow Lawn 2023-05-15 $60

7 7 5 Trim Hedge 2023-05-19 $35

8 8 5 Mow Lawn 2023-05-21 $60

9 4 2 Mow Lawn 2023-06-03 $60

10 4 4 Mow Lawn 2023-06-10 $60

11 5 3 Trim Large 2023-06-08 $25

12 6 4 Weed Garden 2023-06-12 $30

13 7 5 Prune Small 2023-06-15 $20

14 8 5 Weed Garden 2023-06-19 $30

Description

The SERVICES table includes the services associated with the owners and their

properties. The table captures the services dataset, giving a clear overview of the service

IDs, types of services, and their date with cost information. The dataset includes service

ID, property ID, owner ID, service type, service date, and the cost for service.

Furthermore, the table connects the info between the OWNERS and PROPERTIES

datasets and their related services. With the Services table, users can quickly identify the

properties and owners with their IDs and the services they receive.
Keys

Primary Key - ServiceID:

The ServiceID serves as the primary key as the data is significant and identifies the theme

of the table. Additionally, the ServiceID allows for users to reference the types of service

in additional tables when required.

Foreign Key - PropertyID, OwnerID

The PropertyID and OwnerID serves as the foreign keys as the data feeds into the

SERVICES table from the OWNERS and PROPERTIES table.