Part 1: APPLICATION’S 3NF RELATIONS (17 Total)
1)CUSTOMER [ Cust#(PK), CustName, CustAddress, CustEmail, CustHPhone, CustCPhone ]
2)INVENTORY_CHECK [ Date(PK), PreparedByEmpID, ApprovedBySuper#]
3)INVENTORY_PRODUCT [ Date(PK), Product#(PK), QtyOnHand ]
4)INVOICE [Invoice#(PK), InvDate, Cust#, PreparedByEmpID]
5)WORKORDER [WorkOrder#(PK), WODate, Invoice#,PreparedByEmpID, Customer#, EquipMake, EquipModel, EquipSerial#, EstComplDate, InspDate, InspBySuper#, PickupNotificationDate ] NOTE: Because WorkOrder can apply to only one invoice, I have added Invoice# to WORKORDER so INVOICE_WORKORDER relation not required, and removed Invoice# from PK of INVOICE_WORKORDER_SERVICE
6)WORKORDER_SERVICE [WorkOrder#(PK), Service#(PK), DateAssigned ]
7)INVOICE_PRODUCT [Invoice#(PK), ProductCode(PK), Qty ]
8)PRODUCT [Product#(PK), ProdName, UnitCost, MnfrProdID, ReorderPt, ReorderQty ]
9)PROGYR [ProgYear(PK), PayRate ]
10)RECEIPT [ Receipt#(PK), ReceiptDate, Invoice#, PaymentAmt, PayMethod, Acct#, AcctExpDate, ReceivedByEmpID]
11)SERVICE [ Service#(PK), Desc, Type, Charge, EstTime ]
12)SUPERVISOR[Super#(PK), SuperName, PhoneExt]
13)STUDENT [ EmpID(PK), Name, Hphone, Cphone, Student#, WeekDay, ProgYear]
14)WORKDAY [ Date(PK)]
15)WORKDAY_PERIOD [ Date(PK), TimePeriod(PK), Super#]
16)WORKDAY_DESK [ Date(PK), DeskType(PK)]
17)WORKDAY_DESKASST [ Date(PK), DeskType(PK), ProgYear(PK), EmpID, HrsWorked]
Part 2: DATABASE TABLES (14 Total)
1)CUSTOMER [ Cust#(PK), CustName, CustAddress, CustEmail, CustHPhone, CustCPhone ]
2)INVOICE [Invoice#(PK), InvDate, Cust#, PreparedByEmpID]
3)WORKORDER [WorkOrder#(PK), WODate, Invoice#,PreparedByEmpID, Customer#, EquipMake, EquipModel, EquipSerial#, EstComplDate, InspDate, InspBySuper#, PickupNotificationDate ] NOTE: Because WorkOrder can apply to only one invoice, I have added Invoice# to WORKORDER so INVOICE_WORKORDER relation not required, and removed Invoice# from PK of INVOICE_WORKORDER_SERVICE
4)WORKORDER_SERVICE [WorkOrder#(PK), Service#(PK), DateAssigned ]
5)INVOICE_PRODUCT [Invoice#(PK), ProductCode(PK), Qty ]
6)PRODUCT [Product#(PK), ProdName, UnitCost, MnfrProdID, ReorderPt, ReorderQty, QtyOnHand ] NOTE: Since historical information on QtyOnHand is not required I have added QtyOnHand to PRODUCT and not included INVENTORY_CHECK and INVENTORY_PRODUCT
7)PROGYR [ProgYear(PK), PayRate ]
8)RECEIPT [ Receipt#(PK), ReceiptDate, Invoice#, PaymentAmt, PayMethod, Acct#, AcctExpDate, ReceivedByEmpID]
9)SERVICE [ Service#(PK), Desc, Type, Charge, EstTime ]
10)SUPERVISOR[Super#(PK), SuperName, PhoneExt]
11)STUDENT [ EmpID(PK), Name, Hphone, Cphone, Student#, WeekDay, ProgYear]
12)WORKDAY [ Date(PK) ]
13)WORKDAY_PERIOD [ Date(PK), TimePeriod(PK), Super#]
14)WORKDAY_DESKASST [ Date(PK), DeskType(PK), ProgYear(PK), EmpID, HrsWorked] NOTE: constraints for this table mean WORKDAY_DESK no longer required
TABLE: CUSTOMER
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationCust# / Int / Y
Fname / Char / 25 / Y
Lname / Char / 25 / Y
Address / Char / 30 / Y
Email / Char / 30
Hphone / Int / 10 / Y / >0
Cphone / Int / 10 / >0
TABLE: INVOICE
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationInvoice# / Int / Y
InvDate / Date / WORKDAY(WorkDate) / Y
Cust# / Int / CUSTOMER(Cust#) / Y
PreparedBy / Int / EMPLOYEE(EmpID) / Y
TABLE: WORKORDER
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationWorkOrder# / Int / Y
WODate / Date / WORKDAY(WorkDate) / Y
Invoice# / Int / INVOICE(Invoice#) / Y
PreparedBy / Int / EMPLOYEE(EmpID) / Y
Cust# / Int / CUSTOMER(Cust#) / Y
EquipMake / Char / 30 / Y
EquipModel / Char / 30 / Y
Serial# / Char / 30
EstComplDate / Date / Y
InspDate / Date
InspBy / Int / SUPERVISOR(Super#)
PickupNotDate / Date
TABLE: WORKORDER_SERVICE
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationWorkOrder# / Int / Y / WORKORDER (WorkOrder#)
Service# / Int / Y / SERVICE (Service#)
DateAssigned / Date
TABLE: INVOICE_PRODUCT
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationInvoice# / Int / Y / INVOICE(Invoice#)
Product# / Int / Y / PRODUCT(Product#)
ProductQty / Int / 2 / Y / >0
TABLE: PRODUCT
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationProduct# / Int / Y
ProductName / Char / 50 / Y
UnitCost / Decimal / 4.2 / Y / >0.00
MnfrProdID / Char / 20
ReorderPt / Int / Y / >=0
ReorderQty / Int / Y / >0
QtyOnHand / Int / Y / >=0
TABLE: PROGYR
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationProgYr / Int / Y / < 4
PayRate / Decimal / 4.2 / Y / >= 0, < 15
TABLE: RECEIPT
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationReceipt# / Int / Y
RecDate / Date / WORKDAY(WorkDate) / Y
Invoice# / Int / INVOICE(Invoice#) / Y
PaymentAmt / Decimal / 6,2 / Y / >= 0
PayMethod / Char / 1 / Y / C or M
Acct# / Char / 20
AcctExpDate / Date
ReceivedBy / Int / EMPLOYEE(EmpID) / Y
TABLE: SERVICE
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationService# / Int / Y / >0
Desc / Char / 50 / Y / Y
Charge / Decimal / 5,2 / Y / >=0
Type / Char / 1 / Y / H or S
EstTime / Int / 3 / Y / >0
TABLE: SUPERVISOR
Column / Data Type / Length / PK / FK / Required / Unique / ValidationSuper# / Int / Y
FName / Char / 25 / Y
LName / Char / 25 / Y
Ext / Int / 4 / >0
TABLE: EMPLOYEE
Column / Data Type / Length / PK / FK / Reqd / Unique / ValidationEmpID / Int / Y
FName / Char / 25 / Y
LName / Char / 25 / Y
Hphone / Int / Y / >0
Cphone / Int / >0
Student# / Int / 10 / Y / Y
WeekDay / Char / 1 / Y / M,T,W,R,F
ProgYr / Int / 1 / PROG_YR(ProgYr) / Y
TABLE: WORKDAY
Column / Data Type / Length / PK / FK / Required / Unique / ValidationWorkDate / Date / Y
TABLE: WORKDAY_PERIOD
Column / Data Type / Length / PK / FK / Required / Unique / ValidationDate / Date / 6 / Y / WORKDAY(WorkDate)
TimePeriod / Int / Y / 1 or 2
Super# / Int / SUPERVISOR(Super#) / Y
TABLE: WORKDAY_DESKASST
Column / Data Type / Length / PK / FK / Required / Unique / ValidationDate / Date / Y / WORKDAY(WorkDate)
DeskType / Char / 1 / Y / H or S
ProgYr / Int / Y / PROGYR(ProgYr)
EmpID / Int / EMPLOYEE(EmpID)
HrsWorked / Decimal / 2,1 / Between 0 and 5