Excel & SQL Server to create the appropriate tables and lists

Assignment1-GardenGlory.docx

Home >Computer Science homework help > Excel & SQL Server to create the appropriate tables and lists

Assignment 1 – Garden Glory [100 pts]

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.

Extra credit if you create the tables in SQL Server

PropertyName	PropertyType	Address	OwnerName	OwnerPhone	BillingAddress
Jones Home	Residence	Elm St	E.J. Jones	223-1111	2nd Street
Eastlake Office	Office	Elm St	E.J. Jones	223-1111	2nd Street
Samish	Park	East Park	City	222-0030	City Hall
Westview	Park	West Park	City	222-0030	City Hall

A. Create a sample list of 5 owners and 8 properties. Your list will be similar in structure to that in the Figure above. Use Excel to create the list [10]

B. Describe with data problems that are likely to occur if Garden Glory attempts to maintain the list in a spreadsheet as you have done in A. [15]

C. 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 a property and an owner. Demonstrate that the modification problems you identified in part B have been eliminated. Use Excel to create the lists Create the same with SQL Server. [20]

PropertyName	Type	Address	OwnerName	OwnerPhone	BillingAddress	Date	Desc	Charge
Jones Home	Residence	Elm St	E.J. Jones	223-1111	2nd Street	2/2/14	…	125.00
Eastlake Office	Office	Elm St	E.J. Jones	223-1111	2nd Street	2/7/14	…	78.44
Samish	Park	East Park	City	222-0030	City Hall	2/2/14	…	99.50
Westview	Park	West Park	City	222-0030	City Hall	3/7/14	…	224.99
Eastlake Office	Office	Elm St	E.J. Jones	223-1111	2nd Street	2/14/14	…	78.44
Eastlake Office	Office	Elm St	E.J. Jones	223-1111	2nd Street	2/22/14	…	100.00
Westview	Park	West Park	City	222-0030	City Hall	3/14/14	…	224.99

D. Create a sample list of owners, properties, and services as shown above. Fill in the description for services as you create the list. Use Excel to create the lists [15]

E. Illustrate modification problems that are likely to occur if Garden Glory attempts to maintain the list from part D in a spreadsheet. Are these problems worse than what you found in B. [10]

F. Split the list from part D into tables such that each has only one theme. Create appropriate ID columns. Use linking columns to represent relationships. Demonstrate that the modification problems you identified in part E have been eliminated. Use Excel to create the lists and SQL Server to create the appropriate tables. [30]

Assignment 1

–

Garden Glory

[100 pts]

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 maintena

nce services.

Extra credit if you create the tables in SQL Server

PropertyName

Property

Type

Address

OwnerName

OwnerPhone

BillingAddress

Jones Home

Residence

Elm St

E.J. Jones

223

1111

Street

Eastlake Office

Office

Elm St

E.J. Jones

223

1111

Street

Samish

Park

East Park

City

222

0030

City Hall

Westview

Park

West Park

City

222

0030

City Hall

Create a sample list of

owners and

properties. Your list will be similar in structure to

that in

the

Figure

above.

Use Excel to create the list

[10]

Describe

with data

problems that are likely to occur if Garden Glory attempts to maintain

the list in a spreadsheet

as you have done in A.

[15]

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 a property and an

owner. Demonstrate that the modification problems you identified in part B have been

eliminated.

Use Excel to create the list

Create

the

same with SQL Server.

[20]

PropertyName

Type

Address

OwnerName

OwnerPhone

BillingAd

dress

Date

Desc

Charge

Jones Home

Residence

Elm St

E.J. Jones

223

1111

Street

2/2/

…

125.00

Eastlake

Office

Elm St

E.J. Jones

223

1111

Street

2/7/

…

78.44

Samish

Park

East

Park

City

222

0030

City Hall

2/2/

…

99.50

Westview

Park

West

Park

City

222

0030

City Hall

3/7/

…

224.99

Eastlake

Office

Elm St

E.J. Jones

223

1111

Street

2/14/

…

78.44

Eastlake

Office

Elm St

E.J. Jones

223

1111

Street

2/22/

…

100.00

Westview

Park

West

Park

City

222

0030

City Hall

3/14/

…

224.99

Assignment 1 – Garden Glory [100 pts]

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.

Extra credit if you create the tables in SQL Server

PropertyName PropertyType Address OwnerName OwnerPhone BillingAddress

Jones Home Residence Elm St E.J. Jones 223-1111 2

Street

Eastlake Office Office Elm St E.J. Jones 223-1111 2

Street

Samish Park East Park City 222-0030 City Hall

Westview Park West Park City 222-0030 City Hall

A. Create a sample list of 5 owners and 8 properties. Your list will be similar in structure to

that in the Figure above. Use Excel to create the list [10]

B. Describe with data problems that are likely to occur if Garden Glory attempts to maintain

the list in a spreadsheet as you have done in A. [15]

C. 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 a property and an

owner. Demonstrate that the modification problems you identified in part B have been

eliminated. Use Excel to create the lists Create the same with SQL Server. [20]

PropertyName Type Address OwnerName OwnerPhone BillingAddress Date Desc Charge

Jones Home Residence Elm St E.J. Jones 223-1111 2

Street 2/2/14 … 125.00

Eastlake

Office

Office Elm St E.J. Jones 223-1111 2

Street 2/7/14 … 78.44

Samish Park East

Park

City 222-0030 City Hall 2/2/14 … 99.50

Westview Park West

Park

City 222-0030 City Hall 3/7/14 … 224.99

Eastlake

Office

Office Elm St E.J. Jones 223-1111 2

Street 2/14/14 … 78.44

Eastlake

Office

Office Elm St E.J. Jones 223-1111 2

Street 2/22/14 … 100.00

Westview Park West

Park

City 222-0030 City Hall 3/14/14 … 224.99