PRJ2

1

Note: This example is not complete and not perfect. This document is designed to save you time as you prepare your submission but a careful review of the requirements is important if a good score is desired.

Example PRJ2 This document depicts the data design for an AP system.

Data Diagram

Figure 2.1 – provide an appropriate explanatory caption

Explanation of database design Our database design includes the following object, transaction, category and intersection tables.

2

Object Tables: Projects AP_Vendors ……. …….. Transaction Tables: ……………………… …………………….. Include one paragraph for EACH object, transaction, category and intersection table. AP_Vendors table is an object table containing the list of vendors. It is used ……. (a couple of lines) ………………………. Invoices is a transaction table ………………………………………………………………….. For each intersection table, ALSO insert the “script” from the PRJ2 instructions document. You need to fill in the correct field and table names in the script The InvPrj_Allocations intersection table…..

Sample Data The sample data is provided to illustrate how the proposed database design captures needed data.

3

Terms_Codes

Terms_Code_ID Terms_Code Description

1 Unspecified Un-specified Terms

2 210Net30 2% discount within 10 Days Net 30 Days

3 Net 10 Net 10 Days

4 Net 30 Net 30 Days

Table 2.1 Note that an unspecified code is included in this list of data from the Terms_Codes table. Sometimes codes are not required or not available.

Projects

Project_Num Project_Name Description Budget_Amt POIssued_Amt Invoices_Received_Amt Closed

1 OSUBexell-0001456 Overhaul plumbing in Bexell Hall

$300,000.00 $261,291.00 $179,040.50 No

2 ShadyAcresRenovation- 0004532

Design/install new shower system

$382,600.00 $360,288.00 $337,520.00 No

3 CorvallisRemodel-0006987 Install water heater/repair leaky taps

$42,000.00 $39,871.00 $31,096.39 No

4 CHSPlumbing-0001876 Upgrade school plumbing system

$180,000.00 $169,865.00 $151,274.00 No

5 PizzaPipelineSoft-009954 Install water softener $6,940.00 $6,768.00 $6,322.50 Yes

6 Block15Repair-00071234 Repair broken valves and burst piping

$26,300.00 $20,984.00 $16,160.00 No

7 Jubbin'sJuiceInst-0008852 Install dishwasher and taps in break room

$9,500.00 $7,993.00 $7,489.56 No

8 VandelayIndustriesMaint- 0004881

Maintenance check $2,110.00 $1,984.00 $1,780.00 Yes

9 ChilliPalmersRad-0004476 Radiator installation $11,250.00 $10,076.00 $9,735.00 Yes

10 FutonManSupp-0001139 Boiler supply and fitting

$29,750.00 $28,112.00 $27,679.00 No

Table 2.2 Projects track expenditures. The data in this table is key in controlling expenditures and providing accountability for spending authority.

Invoices

Invoice _ID

Vendor _ID

Inv_Ttl_ Amt

Check_N um

Paid_A mt

Vend_Inv _No

Terms_Cod e_ID

Invoice_ Date

Received_ Date

Entered_ Date

Approved_ Date

Paid_Da te

Batch _ID

Entered _By

626 10003 $800.00 0 $0.00 26 2 10/7/201 3

10/12/201 3

10/12/20 13

10/7/2013 1550 1

627 10005 $120.00 223867 $120.00 14 2 10/8/201 3

10/13/201 3

10/13/20 13

10/8/2013 10/20/2 013

1550 1

629 10011 $4,000.0 0

0 $0.00 129 4 10/10/20 13

10/13/201 3

10/13/20 13

10/10/201 3

1550 1

4

Invoices

Invoice _ID

Vendor _ID

Inv_Ttl_ Amt

Check_N um

Paid_A mt

Vend_Inv _No

Terms_Cod e_ID

Invoice_ Date

Received_ Date

Entered_ Date

Approved_ Date

Paid_Da te

Batch _ID

Entered _By

630 10014 $9,000.0 0

223868 $9,000.0 0

2243 3 10/12/20 13

10/14/201 3

10/14/20 13

10/12/201 3

10/20/2 013

1550 1

677 10008 $112,00 0.00

223893 $112,00 0.00

45673 2 12/17/20 13

12/22/201 3

12/28/20 13

12/17/201 3

1/1/201 4

1563 1

Table 2.3 Each invoice receives one line in the Invoices table.

InvPrj_Allocations

InvPrj_ID Project_Num Invoice_ID Amount

1 2 626 $800.00

2 2 627 $120.00

3 2 629 $4,000.00

4 2 630 $9,000.00

5 10 626 $750.00

6 2 638 $250.00

7 2 640 $8,550.00

8 1 643 $15,450.00

9 9 665 $1,585.00

10 5 626 $250.00

11 9 668 $3,700.00

Table 2.4 The InvPrj_Allocations table records which invoices are charged to which projects. Note the many-to-many relationship. For example, Project 2 is charged on multiple invoices - 626, 627, 629, etc. Similarly, invoice 626 is charged to 3 different projects – 2,5, and 10.

 Note how the intersection table example above (Table 2.3) is labelled to explain how it supports the M:M relationship.

Please note: For your submission, data from all tables, with explanations as appropriate will be needed.

 This example does NOT list all the tables in the AP system database. As you can see from the data diagram, nine tables would have been included in a complete version of this documentation.

As you will agree, completing PRJ2 will take some effort. But it is worth a lot of points (75)!