DBS201 Assignment 2

Due Date: March 28, 2008 at 1:30pm

3% of final mark

Your group must consist of 2 or 3 people.Submissions done by a single person will receive a 20% penalty. Your group must develop an Entity Relationship Diagram based on the Information given below.

Required for submission:

1.You are to hand in an ERD diagram.

2.Please ensure that your tables support both the written description of GTA’s operations and the data needed to produce the invoice.

3.You may use Chen or Crow’s Feet notation.

4.Please use Visio, Access, Word, Open Office or other such program to create a printed version.

5.All Many-to-Many relationships must be shown as 2 One-to-Many relationships using a “Bridge” table.

6.An example is included on page 4 of this assignment.

7.You must hand in the Submissions Form (below) with your assignment (one per group).

Student Assignment Submission Form

I/we declare that the attached assignment is my/our own work in accordance with the Seneca Academic Policy. No part of this assignment has been copied manually or electronically from any other source (including web sites) or distributed to other students

.

Name(s) Student ID(s)Signature

1______

2______

3______

PLAGIARIASM and CHEATING

(Refer to section 8.7, page 6 of Seneca's Academic Policy)

(or visit: )

GTA Landscaping Inc.

DescriptionofOperations

GTA LandscapingInc. is a landscaping company that has been in business for over a decade.

GTA Landscaping's business is to offer several services to customers and to sell various products needed for landscaping. Services include "Lawn Cutting", "Garden Planting", fall "Leaf Clearing", and winter "Snow Shoveling". An Invoice contains customer information and may have many services listed. The customer may require the same service repeated several times each year.

GTA Landscaping also sells lawn and garden products to customers. A customer can purchase one or more products on one invoice, and the customer can purchase many of a particular product on one invoice. We usually have several of each product in stock.This allows the sale of the product to several customers. To help GTA Landscaping organize it's products, each product is given a single classification. For example, GTA Landscaping sells the classification of "GT - Garden Tools" which include products such as: "Garden Rakes", "Shovels", "Picks", "Axes", "Pitch Forks", and "Hand Shears". GTA Landscaping also sells a classification of "SH - Shrubs" which include products: "Cedar", "Golden Cedar", "Mulberry", and "Juniper".

The employees who deliver the services offered by GTA Landscaping are organized into teams. Each team can carry out every one of the services. Each employee can belong to only one team. We will also store each employee's OHIP (health card) number in case an employee is injured on the job.

GTA Landscaping: The Invoice

Below is a sample invoice for several services and purchases. We issue an invoice once all services are completed for a customer visit and purchases have been delivered. This invoice will itemizethe details concerningthe services provided and products purchased. Then a summary is shown for the entire invoice including all applicable taxes and the grand total.

Invoice Statement

INVOICE #: / 1355
INVOICE DATE: / 5-Jul-2008 CUSTOMER: / 56 - John Adams
WORK TEAM: / 2 ADDRESS: / 234 Bloor W
Toronto
EQUIP. USED: / E003 - 20 hp John Deer tractor/ mower / M2S 4S3
E057 - 10" tree pruning shears
E022 - 2 hp Johnson grass trimmer
SERVICES:
SERVICE CODE / DESCRIPTION / HOURLY CHARGE / WORK DURATION (hours) / TOTAL CHARGE
LC / Lawn Cutting / $25.00 / 0.75 / $18.75
LW / Lawn Weeding / $35.00 / 1.15 / $40.25
LF / Lawn Fertilizing / $15.00 / 0.25 / $3.75
TG / Tree Pruning / $45.00 / 0.50 / $22.50
PURCHASES:
PRODUCT ID / PRODUCT / QUANTITY / PRICE / TOTAL CHARGE
10 / 6 Foot Rake / 3 / $12.00 / $36.00
110 / Mulberry Sapling / 12 / $15.00 / $180.00
SUBTOTAL: / $301.25
GST (5%): / $15.06
PST (8%): / $24.10
TOTAL DUE: / $340.41

Sample of ERD Diagram for part of Assignment 2

1 to Many

INVOICE-SERVICE
InvoiceNo (PK) (FK)
ServiceCode (PK) (FK)
Hours
SERVICE
ServiceCode (PK)
ServiceDescr
HourlyCharge
INVOICE
InvoiceNo (PK)
InvDate
CustNo (FK)
TeamNo (FK)

1