DBS201 Assignment Part 2: Normalization of UserViews

Document Sample
DBS201 Assignment Part 2: Normalization of UserViews Powered By Docstoc
					        DBS201 Assignment 1 Ver. 2 (Changes in Blue)
                       - (7 Marks)

               DataBase Implementation CWW Inc.
                    Due Date: Monday Feb 13, 2012

       In this assignment you will:

       1.    Create a DB2 database with several tables.
       2.    Insert data into the database.
       3.    Create views.
       4.    Prepare some reports using tables and views
Please Note:
This assignment is to be done in groups of 2 or 3 students.
Submissions done by a single student will incur a penalty of 20%.
Please include each group member’s names on an: Assignment
Submission Form . Also, beside the person’s name write: Section “X”.
The appearance of a student’s name on this document will be
considered his/her signature.

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


1) Name: _____________________________Student ID:______________

   Work Done:_________________________________________________

2) Name:______________________________Student ID:_____________

   Work Done:_________________________________________________

Assign. 2                                                                 1 of 8
3) Name:______________________________Student ID:_____________

   Work Done:_________________________________________________

Submission Requirements:

This assignment must be handed-in using email.
The Subject line must be: DBS201-Assignment-1.

There are 7 (seven) parts:
Part 1: The Assignment Submission Form
Part 2: The Table Physical Design Sheets. (1 per table)
Part 3: The CREATE TABLE commands. (1 per table)
Part 4: The CREATE VIEW commands. (1 per view)
Part 5: The reports showing at least 3 rows of data. (1 report per table).
Part 6: The reports using the VIEWs (1 report per view)
Part 7: Indicate the i-series account and the collection name
        where your professor can mark your work. The COLLECTION
        name is CWWxxx where xxx is the last 3 characters of your
        dbs201 account on the i-series.

You will develop your SQL commands on the i-series. When you are sure
that your SQL command is working, copy and paste it into an Open Office
Writer document (or a Microsoft Word document). Do the same for all parts
of ths assignment including reports on tables or views. Attach your
document to an email and send it to your professor.

All SQL commands must be properly formatted.
The submission must be business-like. That includes readability,
organization, and naming conventions.

Part 1: In your Writer (or WORD) document Complete and
           Include the Physical Design Sheets - (1 per table)

       When we are ready to create the tables we first prepare a physical
       schema. There we define the attributes, with their datatypes, field
       sizes and constraints. This step allows the designer to concentrate on
       “what” the table contains and what will be needed so that the
       database can support the user’s needs.

       The following relations form part of the database for Canada Wood
       Product Inc.

Assign. 2                                                                  2 of 8
       The COLLECTION name is CWWxxx where xxx is the last 3 characters
       of your dbs201 account on the i-series.

PARTS            [PartCode(PK) , PartDesc, Inventory, PartCost, UnitPrice,

CUSTOMER         [Cust#(PK), CustName, CustAddress, CustPhone,
                 ContactName, DateLast, ShipInst, Terms_Id (FK)]

TERMS            [Terms_Id(PK), PaymentTerms]

INVOICE          [ Inv#(PK), Cust#(FK), Order# (FK) ]

INVOICE-PART     [Inv# (PK)(FK1), PartCode(PK) (FK2), Qty ]

ORDERS           [Order#(PK), DateOrder]

SUPPLIER         [ SuppNum(PK) , SupName ]

Note: the PartCost on the PARTS table is what CWW pays to buy the
part, but UnitPrice is what CWW charges for the part.

Assign. 2                                                                    3 of 8
     You are to complete and include in an Open Office Writer file or an MS
     WORD file the physical design for each table. Complete one form for each

     Table Physical Design Sheet – One per table

     Table Name: _____________________

Column                                           NOT
Name        Data Type Length PK FK               NULL Unique Check              Default

     Assign. 2                                                            4 of 8
Part 2: In your Writer or WORD document, provide the Syntax
          for Table Creation Commands for the 7 Tables:

Choose a group member Series-i account and create a collection named
CWWxxx (where xxx is the last 3 characters of your userid). Create the
seven tables defined above. Column names must be the same as those
shown in the physical schema. Use proper formatting(indenting).

Part 3: In your Writer or WORD document, provide the Syntax to
          Create the 2 “Views”

   1. A view called INVOICEINFO which will contain the attributes shown on
      Invoices (shown in the appendix) for all orders.
   2. A view called INVOICE55 which can be used to produce Invoice
      Number 2012-55 . Use proper formatting(indenting).

Part 4: In your Writer or WORD document, provide the Syntax to
           Insert Sample Rows (minimum 3) for each Table

You may use data from the User Views. If necessary you may invent some

Part 5: In your Writer or WORD document, provide screen shots
           of the contents of each table (all Rows)

Part 6 : In your Writer or WORD document, provide screen shots
           when you run a “SELECT * from ‘viewname’” command
           for both Views created above.

Assign. 2                                                                5 of 8
             Appendix A: Sample Reports

The following inventory report is used in the assembly process to check the
availability of parts.

                     Canada Wood Products Inc.
                        Inventory Report
                               Feb 15, 2012

PartCode Description               Inventory Supplier                   Cost
A200-05 Arches tall with 5 shelves    100    12 -CWW Inc.              $60.00
BD60     Rectangular shelf 60cm        12    13 -Domtar Lumber Co.      $1.56
BD80     Rectangular shelf 80cm        5     13 -Domtar Lumber Co.      $1.86
BDM100 Rectangular side 100cm          6     13 -Domtar Lumber Co.      $1.92
BDM200 Rectangular side 200cm          7     13 -Domtar Lumber Co.      $2.40
BDT100 Triangular side 100cm           6     14 -Abitibi Boards Ltd.    $1.92
BDT200 Triangular side 200cm           7     14-Abitibi Boards Ltd.     $2.40
BK10060 Back Panel:100x60cm            3     15 -Millwood, Inc.         $2.49
BK10080 Back Panel:100x80cm            4     15 -Millwood, Inc.         $2.55
BK20060 Back Panel:200x60cm            5     15 -Millwood, Inc.         $2.76
BK20080 Back Panel:200x80cm            5     15 -Millwood, Inc.         $2.94
CAP08    #8 screw cap                 600    16 -Acme Hardware, Ltd     $0.04
M200-05 Mission tall and narrow
         with 5 shelves               350    12 -CWW Inc.              $60.00
M200-06 Mission tall and narrow
         with 6 shelves               420    12 -CWW Inc.              $120.00
P100-03 Pyramid short with 3
         shelves                      615    12 -CWW Inc.              $53.40
P200-06 Pyramid tall with 6
         shelves                      525    12 -CWW Inc.              $120.00
PIN12    #12 shelf support pin        250    17 –Ace Supplies, Ltd.     $0.09
SCR08    #8 flat-head screw           500    16 -Acme Hardware, Ltd     $0.07

Assign. 2                                                                        6 of 8
                      Canada Wood Products Inc.
                         Customer Report
                                  Feb 15, 2012

Cust   Name      Address          Phone   Contact    Pay-     Date of   Shipping
No                                        Name       ment-    last      Instructions
                                                     Terms    order
10     Walart    1050             (905)   Susan      Net 30   2012-     Deliver to rear
                 Hurontario St.   878-    Chow       Days     06-05     receiving dock
                 Mississauga,     1234
                 M5W 1Y2
16     The       12345            (416)   Martin     C.O.D.   2012-     Deliver prior
       Brick     Markham Rd       691-    Singh               06-14     to 11 am.
                 Scarborough,     4321
                 M8X 3T3
25     Walart    661 Dixie Rd     (905)   Irene      Net 30   2012-     Deliver to
                 Brampton,        446-    Martinez   Days     07-02     receiving dock
                 Ont              7654                                  No. 2
                 N4P 9J8
49     Sears     87 Ontario St    (905)   Greg       Net 30   2008-     Please call
                 St.              937-    Murphy     Days     11-30     ahead for a
                 Catharines,      9977                                  delivery time
                 L8V 1V1
85     Zellers   7878             (905)   Roger      Net 30   2012-     Deliver to rear
                 Trafalger Rd     876-    Ahmed      Days     06-12     receiving dock
                 Oakville, Ont    5432
                 M8M 4R4
101    Leons     435677 Yonge     (905)   Chuck      C.O.D.   2012-     Deliver using
                 St.              853-    Borgh               06-27     Cabano
                 Newmarket,       8686                                  Trucking
                 L3Y 2T2
125    Sears     12347            (416)   Simone     Net 30   2012-     Prefers
                 Markham Rd.      691-    Vis        Days     07-08     Kingsway
                 Scarborough,     1234                                  Transport
                 M8X 3T5

Note: This report may be used by the shipping employee to find the
customer address and to review any special shipping instructions the
customer may have. It may also be used by the Sales Manager to see which
customers have not ordered anything for quite some time.

Assign. 2                                                                                 7 of 8

                   Canada Wood Products Inc.
Customer No: 444 - Furniture R Us             Invoice Number: 2012-55

Address: 2780 Warden Avenue,
         Scarborough, ON.
         R4T 1Z7

Order Date: 2012/02/15
This Invoice For Your Order Number: 15762
Terms: Amount Due upon receipt of Invoice.

QTY     PartCode    Description                   Unit       Total
10      P100-03     Pyramid short with 3           89.00     $890.00
75      M200-05     Mission tall and narrow       100.00     7500.00
                    with 5 shelves
50      A200-05     Arches tall with 5 shelves    100.00     5000.00
40      P200-06     Pyramid tall with 6           200.00     8000.00
20      M200-06     Mission tall and narrow       200.00     4000.00
                    with 6 shelves

        GST (5%)                                             1269.50

                    TOTAL                                    $26659.50

Note: this is the bill which is sent to the customer as soon as the order has
been shipped to them. (When the truck leaves our dock).

Assign. 2                                                                 8 of 8

Shared By: