DBS201 Assignment 2

Changes in Plum

Due Wednesday Nov 19, 2008 (23:59)

7% of final mark

Physical Database Design and Implementation

Your group must consist of 2 or 3 people You perform the physical design of a database, create the six tables and insert the data shown in the Weekly Sales Report and the Customer List Report. Submissions done by a single person will receive a 20% penalty.

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: http://cs.senecac.on.ca/bullets/cheating.html )


ENG-A-PLUS

An engineering firm named ENG-A-PLUS provides design and build services to government and private organizations. The Weekly Sales Report and the Customer List Report are attached. In order to support these reports, six separate tables are necessary.

Note that one invoice can have more than one piece of equipment and that one particular piece of equipment can be sold on more than one invoice. For example: Invoice number EAP-44720 has Equipment numbers P100 and CMT22. Also note that Equipment number P100 is sold on Invoice EAP-44720 and EAP-44721.

The report shows the Customer number which relates to a CUSTOMER table with attributes given below. Any particular Federal GST License Number can occur only once in the CUSTOMER table as every customer must have a different GST License Number. The only attributes that can be skipped are CustAddress, CustPhone and Invoice Date. The system will insert today’s date if the user skips InvoiceDate. The system inserts blanks into SalesPersonName when skipped. Federal_GST_LicenseNumber must be greater than or equal to 100000000 and less than 999999999.

ENG-A-PLUS
Weekly Sales Report
Equip
Class / Class
Description / Equip
Num / Equipment
Description / Charge / Qty / Inv.
Num. / Inv.
Date / Sales-Person
Person / Cust
No
OFF / Office Copier / P100 / Kodac Photocopier / $420.00 / 2 / EAP-44720 / 05-10-17 / 23-Oliv Gardenor / 103
MIX / Concrete Mix / CMT2 / Concrete Mixer / $4000.00 / 20 / EAP-44720 / 05-10-17 / 23-Oliv Gardenor / 103
TRU / Wood Trusses / A100 / 50’ x 16’ Roofing / $1200.00 / 48 / EAP-44721 / 05-10-18 / 13-Walter Chan / 83
FRM / Framing / A137 / 2 x 4 x 8 Poplar / $7500.00 / 1000 / EAP-44721 / 05-10-18 / 13-Walter Chan / 83
OFF / Office Copier / P100 / Kodac Photocopier / $840.00 / 4 / EAP-44721 / 05-10-18 / 13-Walter Chan / 83
STF / Steel Framing / STL10 / Front Steel Loader / $1500.00 / 3 / EAP-44721 / 05-10-18 / 13-Walter Chan / 83
MIX / Concrete Mix / ST22 / Stone Crusher / $950.00 / 1 / EAP-44722 / 05-10-18 / 23-Oliv Gardenor / 46
FLD / Front Loader / FL660 / Front Loading Device / $450 / 1 / EAP-44722 / 05-10-18 / 23-Oliv Gardenor / 46
CRA / Overhead
Lift / CRA-11 / 120’ Overhead Crane / $9000 / 3 / EAP-44723 / 05-10-18 / 20-Joe Shoelly / 52
ENG-A-PLUS
Customer List
Customer Number / First Name / LastName / Address / Phone
46 / Laurie / Wells / 112 Apple Rd, Toronto / (416) 289- 4417
52 / Ronald / MacDonn / 1 Big Mac Rd, Toronto / (800) 448- 9000
83 / Tom / Duley / 3 Hanghead Dr. Markham / (416) 229- 4104
103 / Jeanne / Jeanne / 2 Youngalive St. Toronto / (416) 664- 3121


To Hand In:

1. An EMAIL : The subject line must be: DBS201-Assign 2

A. Student Assignment Submission Form

B. Six “Physical Database Descriptions” – one for each of the six tables shown below.

C. You will create the database on a group member's account. Submit the account name and password of the account you wish to be marked for your group's assignment.

------

For part B, be sure to indicate all constraints in the Physical Database Descriptions:

1. PRIMARY KEY

2. FOREIGN KEY

3. NOT NULL (Required)

4. UNIQUE (a value can occur only once in a column)

5. CHECK (validation)

A sample “Physical Database Description” from another assignment might look like this:

TABLE: INVOICE

Column / Type / Length / PK / FK / Reqd / Unique / Validation
Invoice# / char / 4 / Y
InvDate / Date / Y
Cust# / char / 6 / CUSTOMER(Cust#) / Y / >= 100
PreparedBy / char / 6 / EMPLOYEE(EmpID) / Y

------

For part C, your group is to create a DB2 database to implement the 6 tables whose DBDL descriptions are given below.

a) Choose a group member i-Series account and create the tables.

b) Insert the sample data found in the Weekly Sales Report (first four rows) and the Customer List (all rows). For this you will use Create Table... , Insert Into …. You may invent data for any fields that are missing. Attribute names must be the same as those shown below:

Table Name Attributes

1. INVOICE [Inv#(PK) , InvoiceDate, SalesPerson#(FK), Cust#(FK)]

2. INVOICE_EQUIPMENT [Inv#(PK)(FK), Eqp#(PK)(FK), Qty, Charge]

3. EQUIP_CLASS [EqpClass#(PK), EqpClassDesc]

4. SALESPERSON [SalesPerson#(PK), SalesPersonName]

5. EQUIPMENT [Eqp#(PK), EqpDesc, EqpClass#(FK)]

6. CUSTOMER [Cust#(PK), CLName, CFName, CustAddress, CustPhone, Federal_GST_LicenseNumber]

DBS201 – 053 Page 3 Due Friday Nov 25, 2005