Merged Tables for Sport Motors

BTD210

Solution to Assignment 3

Merged Tables for Sport Motors

Color [Colorcode, Colorname]

Item [Itemnum, Itemdesc, Itemsize, Qtyonhand, UnitPrice, Colorcode, suppliercode]

Employee [Empnum, Empfname, Emplname, Empmi, Empdob, Emphiredate,Empcommrate, deptcode]

Category [Catcode, Catdesc]

Subcategory [Subcatcode, Subcatdesc, catcode, itemnum]

Inventory [Inventorydate, Catcode(FK)]

xInventoryCategory [Inventorydate,catcode] – this table is basically a duplicate of the one above and adds nothing to the datbase to be implemented.

Department [Deptcode, Deptname]

Customer [Cust#,Custfname,Custlname, Street, City, State, Zip, Phone]

Order [Ordernum, Orderdate, Cust#(FK), Empnum(FK)]

Invoice [Invoicenum, Custnum(FK), Date, Empnum(FK), PayMethod, CreditCardType, Creditcard#, Expdate]

Supplier [Suppnum, Suppname, Street, City, State, Zip, Phone#, Contactname, Phoneext]

OrderItem[Order#, Productnum, Qtyordered]

InvoicedItem [Invoice#, Item#, Qty]

xProduct [Productcode, productdesc] -this table is not included in the physical schema since the product is also an item of inventory. This table is therefore redundant.

Physical Schema

Table Name: Color

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Colorcode / Int / Y / Y / Y
Colorname / Varchar / 15 / Y

Table Name: Item

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Itemnum / Int / Y / Y / Y
Itemdesc / varchar / 15 / Y
Itemsize / varchar / 2 / S,M,L,XL
Qtyonhand / Int / Y
Unitprice / Decimal / 8,2 / Y
Colorcode / varchar / 20 / Color(color
code) / Y
Suppliercode / Int / Supplier (suppliercode) / Y

Table: Employee

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Empnum / Int / Y / Y / Y
Empfname / varchar / 15 / Y
Emplname / varchar / 15 / Y
Empmi / vchar / 1
Empdob / Datetime / Y
Emphiredate / Datetime / Y
Empcommrate / Decimal / 3,2 / 0.03, 0.05, 0.07
Deptcode / Int / Dept(deptcode) / Y

TableName: Subcategory

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Subcatcode / Int / Y / Y / Y
Subcatdesc / varchar / 15 / Y
Catcode / Int / Category (catcode) / Y
Itemnum / Int / Item(itemnum) / Y

TableName: Inventory

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Inventorydate / Datetime / Y / Y / Y
Categorycode / Int / Category (categorycode) / Y / Y

TableName: Department

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Deptcode / Int / Y / Y / Y
Deptname / varchar / 15 / y

TableName: Customer

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Custnum / Int / Y / Y / Y
Custfname / varchar / 15 / Y
Custlname / Varchar / 20 / Y
Address / varchar / 20 / Y
City / varchar / 15 / Y
State / varchar / 2 / Y
Zip / Int / 5 / Y
Phone / dec / 12,0 / Y

TableName: Order

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Ordernum / Int / Y / Y / Y
Orderdate / Datetime / Y
Custnum / Int / Customer
(custnum) / Y
Empnum / Int / Employee
(Empnum) / Y

TableName: OrderItem

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Ordernum / Int / Y / Order
(Ordernum) / Y / Y
Itemnum / Int / Y / Item
(itemnum) / Y / Y
Qtyordered / Int / y

TableName: InvoicedItem

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Invoicenum / Int / Y / Invoice
(invoicenum) / Y / Y
Itemnum / Int / Y / Item
(itemnum) / Y / Y
Qty / Int / Y

TableName: Invoice

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Invoicenum / Int / Y / Y / Y
Custnum / Int / Customer
(custnum) / Y
Date / Datetime / Y
Ordernum / Int / Order
(ordernum) / Y
Empnum / Int / Employee
(empnum) / Y
Paymethod / varchar / 15 / Y / Cash, Cheque,
Credit Card
Creditcardtype / varchar / 15 / MC, Visa, AE
Creditcardnum / Int / Y
Expdate / Datetime

TableName: Supplier

Column
Name / DataType / Length / Primary
Key
(Y/blank) / ForeignKey
Table/Column
To be referenced / Req’d
Null/Not Null / Unique / Validation
Suppnum / Int / Y / Y / Y
Suppname / varchar / 20 / Y
Street / varchar / 20 / Y
City / varchar / 15 / Y
State / varchar / 2 / Y
Zip / Int / Y
Phonenum / dec / 12,0 / Y
Suppcontactname / varchar / 20 / Y
Phoneext / varchar / 4 / Y

Please note that I have modified user views 3 and 4 so that the product number is the same as the item number in inventory.

Userview 3: - Sport Motor Inc. Order Form

Sport Motor Inc. Order Form
Order # 2
Order Date: March 11, 2007
Customer # 1 / Cust. Name: Robin J. O’Connor
Address: 6775 Lamont Road
Eau Claire, WI 54703
Phone: 715-555-4493
Product Number / Product Description / Qty Ordered / Unit Price
3 / Tour Master 1500 / 1 / 14950.00
10 / Riding Gloves / 2 / 98.00
Taken by: Mark Jones, #3

Userview #4 – Sport Motor Inc. Sales Invoice

Invoice # 1234
Sport Motor Inc.
9876 Anywhere Blvd.
Mythical, Mystate 55555
INVOICE
Customer
Name Robin O’Connor
Address 6775 Lamont Road
City Eau Claire State WI
Zip 54703
Phone: 715-555-4493 / Date: March 15, 2007
Purchase Order No. 2
Employee # 3
Qty / Item Code / Description / Unit Price / Total
1 / 3 / Tour Master 1500 Motorcycle / 14950.00 / 14950.00
2 / 10 / Men’s Leather Riding Gloves / 49.50 / 98.00
Subtotal / 15148.00
7% State Tax / 1060.36
Total / 16208.36
Payment: Method: Cash, Credit Card, Personal Cheque
Credit Card Type: American Express
Credit Card #: 1111 2222 3333 4444
Expiration: 07/11

Merged Tables for Sport Motors