I am severely struggling in this class, and need to get a C- to graduate. I need homework help. If I could get some help with this, I may not rip out my hair.

ITM 315 — Individual Assignment #5 (78 pts) ERD & SQL Exercises
1) ER Diagram (50 pts)
Using ERwin, create the ERD necessary to capture the following information. Once you
have completed your diagram, cut and paste it into this grade sheet.
Squirrels Unlimited Yard Service (SUYS) does basic yardwork (mowing,
trimming, weeding, etc.) as well as more complex tasks (e.g., removing trees, installing
sprinkler systems, etc.) for customers in Boise. They have many long-term customers as
well as one-time customers. Some customers own more than one property which they
have SUYS maintain. They would like to start tracking their business more carefully
through a database system.
Customers receive bills for any services performed after the fact. Therefore SUYS
must record the customer?s name, address, and phone number. SUYS also likes to
record the date on which a customer first uses their services.
Each property has an address and SUYS also wants to record if the property is a
residential or commercial property.
When contracting with SUYS for services, some customers wish a one-time only
service, such as removing a tree. Each of those services is priced based on the
specific job.
Many other customers wish a repeating package of services (i.e., mowing and
trimming every week in the summer), and still others wish a comprehensive
package of services such that all yard care needs are handled by SUYS regardless
of the season (i.e., mowing in the summer, raking in the fall, trimming of trees
and shrubs in the spring, etc.). SUYS offers a number of packages for these
repeating customers. For each package SUYS has a unique code and a description
of the services included (e.g., Pkg A includes mowing and trimming around
fences, trees, etc. during the summer months only, Pkg B is a year-round service).
For the packages, there is a monthly fee determined for that particular customer.
Business Rules:
Each customer owns at least one property, but may own more than one property.
Each property is owned by one and only one customer
Each property may have had many services performed on it, or may have had no
services performed. Each service has been performed for one and only one
property.
Each package SUYS offers may be performed for many properties, or may not
have been performed at all. Each property may have had many packages, or may
have had none.
Be sure to:
Convert all many-to-many relationships to associative entities (also called many-tomany entities).
Convert all multi-valued attributes to entities.
Specify the maximum and minimum cardinality for each relationship.
Add as few extra attributes as possible.

1 of 2

2) SQL (28 pts)
Answer parts a through f of question 2 on page 152 of your textbook. The database is
called Chapter 6, available on Blackboard. Also, write the SQL query which will return
all employee first names with the department name they manage (if they manage a
department).* Each problem must be answered in a single query.
Grading for Assignment # 5
Possible Points
Part A) (50 pts)
25
Entities and relationships

Points Received
_____

10

Primary Keys

_____

15

Attributes

_____

Part B) (28 pts)
4

Query a

_____

4

Query b

_____

4

Query c

_____

4

Query d

_____

4

Query e

_____

4

Query f

_____

4

Query from above*

_____

Total

_____

78

2 of 2