28/11/2021 Expense Reporting Database Page 3
Expense Reporting Database Tables
You will use the Expense Reporting Database in assignments 1 to 4. This document describes the business process supported, database diagram with tables and relationships, columns, and populated tables with rows.
Business Processes Supported
The Expense Reporting Database contains tables to track users and expense reports (report summary and items) along with support tables for organizational units, expense categories, assets, and budget limits on expense category spending. Table 1 briefly summarizes the meaning of each table.
Table 1: Tables in the Expense Reports Database
Table Name
Description
Users
Details about users who can submit expense reports
ExpenseReport
Details about summaries of reimbursable items
ExpenseItem
Details about individual items for reimbursement on expense reports
ExpCat
Details about expense categories in which expense items are associated
Asset
Details about company assets that are associated with some expense items
OrgUnit
Details about organizational units in which users are members
BudgetItem
Details about expense budgets by organizational unit and expense category
The Expense Reporting Database supports two business processes, expense reporting and budgeting of expense categories for organizational units. For expense reporting (Figure 1), users provide documentation for expense items and then submit an expense report with all items for approval. Each expense item involves an expense category, an optional asset, and an expense amount. Each expense report has dates for submission and status (pending, approved, or denied) along with the identifiers of the submitting user and approving user. The status defaults to pending on submission but changes to approved or denied upon completion of the review process. The review of an expense report compares amounts of each expense item to limits for associated expense categories.
Figure 1: Expense Reporting Workflow
The expense reporting process involves inserts and updates of the ExpenseReport and ExpenseItem tables with references to the Asset, Users, and ExpCat tables. In the review of an expense report, updates to approved expense amounts (ExpApprAmt) in expense items can be reduced from actual expense amounts (ExpAmt) if limits of associated expense categories are exceeded. Approval of an expense report triggers updates to the BudgetItem table to increase actual amounts of expense categories used for expense items.
For annual budgeting, an organizational unit estimates the amount for each expense category. Each approved expense report increases the actual amount for expense categories referenced in expense items of the expense report. Periodically, a budget analyst in an organizational unit compares the actual amounts to budgeted amounts for selected expense categories. Estimated amounts in a new year can be adjusted based on comparisons of estimated and actual amounts of the current and previous years. The annual budgeting process involves insertions and updates to the BudgetItem table with references to the ExpCat and OrgUnit, tables.
Database Diagram
Figure 2 shows an Oracle database diagram with notation indicating important parts of the database design. In Figure 2, P indicates a primary key, F indicates a foreign key, U/UF indicates unique columns that are not primary keys, and the red dot indicates a required column. In the BudgetItem table, the combination of BIYear (U), OrgNo (UF), and ECNo (UF) is unique. Solid lines indicate required relationships in which foreign keys do not accept null values with Figure 2 showing six required relationships. Dashed lines indicate optional relationships in which the associated foreign keys allow null values with Figure 2 showing three optional relationships. Figure 2 indicates two relationships from Users to ExpenseReport, required SubmitUserNo and optional ApprUserNo. OrgUnit contains an optional self-referencing relationship represented by OrgParentNo indicating the hierarchical structure of organization units. The X by ExpenseItem in the 1-M relationship indicates the CASCADE DELETE for action on referenced rows.
Columns
Tables 2 to 7 describe the columns in each table. The Data Type column indicates the types of values in each column. The Comments column indicates restrictions on columns such as the maximum characters, nulls allowed, and foreign keys. Note that actions on referenced rows are the default RESTRICT except for CASCADE DELETE for ExpenseItem.ERNo.
Figure 2: Oracle Database Diagram for the Expense Reporting Database
Table 2: Columns in the Asset Table
Column
Data Type
Comments
AssetNo
Positive whole number
Primary key
AssetDesc
Variable length string
Max of 255 characters; nulls not allowed
Table 3: Columns in the Users Table
Column Name
Data Type
Comments
UserNo
Whole number
Primary key
UserFirstName
Variable length string
Max of 50 characters; nulls not allowed
UserLastName
Variable length string
Max of 50 characters; nulls not allowed
UserPhone
Variable length string
Max of 20 characters; nulls allowed
UserEmail
Variable length string
Max of 50 characters; unique; nulls not allowed
OrgNo
Positive whole number
Foreign Key to the OrgUnit table; nulls not allowed
Table 4: Columns in the ExpCat Table
Column
Data Type
Comments
ECNo
Positive whole number
Primary key
ECName
Variable length string
Max of 255 characters; nulls not allowed
ECLimit
Dollar Amount
Monetary amount allowed per expense category expanse item; default is 0 (zero); nulls not allowed
Table 5: Columns in the OrgUnit Table
Column
Data Type
Comments
OrgNo
Positive whole number
Primary key
OrgName
Variable length string
Maximum length of 50 characters; nulls not allowed
OrgParentNo
Whole number
Refers to OrgNo; nulls allowed for root organizational units (organizations without a parent organization)
Table 6: Columns in the BudgetItem Table
Column
Data Type
Comments
BINo
Positive whole number
Primary key
BIYear
Whole number
4-digit year. Must be greater than or equal to 2000; default should be current year; use a function to generate the default value; nulls not allowed
BIAmt
Dollar Amount
The budgeted monetary amount for the year; nulls not allowed; default is 0 (zero)
BIActual
Dollar Amount
An ongoing sum of the actual monetary amount of expenses approved for the category/org unit combination in the year; default is 0 (zero)
OrgNo
Whole Number
Foreign Key to the OrgUnit table; nulls not allowed
ECNo
Whole number
Foreign Key to the ExpCat table; nulls not allowed
Table 7: Columns in the ExpenseReport Table
Column
Data Type
Comments
ERNo
Whole number
Primary key
ERDesc
Variable length string
Max of 255 characters; nulls not allowed
ERSubmitDate
Timestamp
Date and time submitted; default should be the current timestamp (use a function to generate the default value); nulls not allowed
ERStatusDate
Timestamp
Date and time when the status changed; defaults to the current timestamp; changes when the expense report is approved or denied; use a function to generate the default value; nulls not allowed
ERStatus
Variable length string
Default value is “PENDING” meaning that the expense report is waiting for approval; this field should be limited to one of the following values “PENDING”, “APPROVED”, or “DENIED”; nulls not allowed
SubmitUserNo
Whole number
Foreign key to the Users table; identifies the user who submitted the expense report; nulls not allowed
ApprUserNo
Whole number
Foreign key to the Users table; identifies the user who approves the expense report; nulls allowed
Table 8: Columns in the ExpenseItem Table
Column
Data Type
Comments
EINo
Whole number
Primary key
ExpDesc
Variable length string
Max of 255 characters; nulls not allowed
ExpDate
Date/Time
Date that the expense was incurred; default should be the current date (use a function to generate the default value)
ExpAmt
Dollar amount
Monetary amount of the expense; nulls not allowed; default is 0 (zero)
ExpApprAmt
Dollar amount
Monetary approved amount of the expense; default is 0 (zero)
ERNo
Positive whole number
Foreign key to the ExpenseReport table; identifies the expense report that contains the item; nulls not allowed; CASCADE DELETE for action on referenced rows
ECNo
Whole number
Foreign key to the ExpCat table; nulls not allowed
AssetNo
Whole number
Foreign key to the Asset table; Nulls allowed
Populated Tables with Rows
Asset
AssetNo
AssetDesc
1
Company Car
2
Company Jet
3
Company Van
4
Company Truck
5
Company Apartment
ExpCat
ECNo
ECName
ECLimit
1
Meal
50.00
2
Car Rental
100.00
3
Snack
25.00
4
Local Transportation
100.00
5
Airfare
5,000.00
6
Entertainment
25.00
7
Lodging
300.00
8
Admission Fee
2,000.00
9
Gas
35.00
OrgUnit
OrgNo
OrgName
OrgParentNo
1
Accounting
2
Sales
3
IT
4
IT Development
3
5
Accounts Receivable
1
6
IT Support
3
7
Accounts Payable
1
Users
UserNo
UserFirstName
UserLastName
UserPhone
UserEmail
OrgNo
1
Sue
Herdon
(206)339-3312
sherdon@org.com
3
2
Bill
Jones
(303)205-8833
bjones@org.com
6
3
Joe
Smith
(303)443-9943
jsmith@org.com
4
4
William
Freed
(303)445-3355
wfreed@org.com
1
5
Sara
Jonson
(303)445-3321
sjonson@org.com
7
6
Tom
Sanchez
(303)445-3322
tsanchez@org.com
5
7
Marry
Blake
(303)445-3333
mblake@org.com
2
8
Jaime
Garcia
(303)357-4566
jgarcia@org.com
3
ExpenseReport
ERNo
ERDesc
ERSubmitdate
ERStatusDate
ERStatus
SubmitUserNo
ApprUserNo
1
Sales Presentation
8/10/2020 10:00
8/26/2020 11:00
APPROVED
3
4
2
Conference
8/16/2020 10:00
8/17/2020 10:00
APPROVED
3
1
3
Training Course
9/23/2020 11:30
9/25/2020 11:00
APPROVED
2
1
4
IT Group Lunch
9/29/2020 10:00
9/29/2020 16:30
APPROVED
1
4
5
Sales Presentation
9/30/2020 10:00
10/1/2020 13:10
APPROVED
7
4
6
Conference
10/3/2020 11:30
10/3/2020 15:10
PENDING
5
7
Entertainment
10/5/2020 10:30
10/15/2020 14:30
DENIED
5
4
ExpenseItem
EINo
ExpDesc
ExpDate
ExpAmt
ExpApprAmt
ERNo
ECNo
AssetNo
1
Car
8/9/2020
150.00
150.00
1
7
2
Hilton
8/9/2020
99.00
99.00
1
7
3
Gas
8/9/2020
35.00
20.00
1
9
1
4
Junk Food
8/10/2020
10.00
10.00
1
3
5
Comfort Inn
8/16/2020
225.00
225.00
2
7
6
Airfare
8/13/2020
325.00
325.00
2
5
7
Dinner
8/15/2020
23.94
23.94
2
1
8
Dinner
8/14/2020
32.50
32.50
2
1
9
Dinner
8/13/2020
19.64
19.64
2
1
10
Concert Tickets
8/15/2020
25.00
25.00
2
6
11
Breakfast
8/15/2020
10.32
10.32
2
3
12
Motel 6
9/23/2020
57.95
57.95
3
7
13
Gas
9/23/2020
15.94
15.94
3
9
3
14
Apartment 1
9/29/2020
299.00
255.00
4
7
5
15
Breakfast
9/29/2020
15.00
13.88
4
1
16
Flight
9/30/2020
3,500.00
3,000.00
5
5
2
17
Airport Bus
9/30/2020
100.00
25.00
5
4
18
Trade Show
10/1/2020
150.00
125.00
5
8
19
Party
10/10/2020
350.00
0
7
1
BudgetItem
BINo
BIYear
OrgNo
ECNo
BIAmt
BIActual
1
2020
1
1
4,000.00
76.08
2
2020
1
2
3,000.00
0.00
3
2020
1
3
2,000.00
10.32
4
2020
1
4
500.00
0.00
5
2020
1
5
10,000.00
325.00
6
2020
1
6
2,000.00
25.00
7
2020
1
7
10,000.00
225.00
8
2020
1
8
6,000.00
0.00
9
2020
1
9
5,000.00
0.00
10
2020
2
1
10,000.00
167.00
11
2020
2
2
7,500.00
98.00
12
2020
2
3
4,000.00
10.75
13
2020
2
4
1,500.00
5.50
14
2020
2
5
15,000.00
650.00
15
2020
2
6
5,000.00
6.79
16
2020
2
7
12,000.00
0.00
17
2020
2
8
7,500.00
0.00
18
2020
2
9
7,500.00
0.00
19
2020
3
1
4,000.00
0.00
20
2020
3
2
3,000.00
0.00
21
2020
3
3
2,000.00
0.00
22
2020
3
4
500.00
24.00
23
2020
3
5
10,000.00
0.00
24
2020
3
6
2,000.00
75.00
25
2020
3
7
10,000.00
0.00
26
2020
3
8
10,000.00
0.00
27
2020
3
9
5,000.00
0.00
28
2020
4
1
1,500.00
0.00
29
2020
4
2
1,200.00
0.00
30
2020
4
3
1,000.00
10.00
31
2020
4
4
250.00
0.00
32
2020
4
5
4,000.00
0.00
33
2020
4
6
1,500.00
0.00
34
2020
4
7
2,000.00
99.00
35
2020
4
8
8,000.00
0.00
36
2020
4
9
650.00
35.00
37
2020
5
1
1,500.00
0.00
38
2020
5
2
1,200.00
0.00
39
2020
5
3
1,000.00
0.00
40
2020
5
4
250.00
0.00
41
2020
5
5
4,000.00
0.00
42
2020
5
6
1,500.00
0.00
43
2020
5
7
2,000.00
0.00
44
2020
5
8
5,500.00
0.00
45
2020
5
9
350.00
0.00
46
2020
6
1
1,500.00
0.00
47
2020
6
2
1,200.00
0.00
48
2020
6
3
1,000.00
2.99
49
2020
6
4
250.00
0.00
50
2020
6
5
4,000.00
0.00
51
2020
6
6
1,500.00
5.95
52
2020
6
7
2,000.00
57.95
53
2020
6
8
5,500.00
1,000.00
54
2020
6
9
350.00
15.94
55
2020
7
1
1,500.00
0.00
56
2020
7
2
1,200.00
0.00
57
2020
7
3
1,000.00
0.00
58
2020
7
4
250.00
0.00
59
2020
7
5
4,000.00
0.00
60
2020
7
6
1,500.00
0.00
61
2020
7
7
2,000.00
0.00
62
2020
7
8
8,000.00
0.00
63
2020
7
9
650.00
0.00
64
2019
1
1
3000
3250.08
65
2019
1
2
4000
3500
66
2019
1
3
2000
2100.32
67
2019
1
4
500
352
68
2019
1
5
9900
9877
69
2019
1
6
2000
1580
70
2019
1
7
10000
10225
71
2019
1
8
6000
6000
72
2019
1
9
5000
6200
73
2019
2
1
10000
9167
74
2019
2
2
7500
7498
75
2019
2
3
4000
3910.75
76
2019
2
4
1500
1635.5
77
2019
2
5
15000
14650
78
2019
2
6
5000
4996.79
79
2019
2
7
12000
12870
80
2019
2
8
7500
6500
81
2019
2
9
7500
7800
82
2019
3
1
4000
3950
83
2019
3
2
3000
2970
84
2019
3
3
2000
2150
85
2019
3
4
500
424
86
2019
3
5
10000
9990
87
2019
3
6
2000
2075
88
2019
3
7
10000
9880
89
2019
3
8
10000
11990
90
2019
3
9
5000
4770
91
2019
4
1
1500
1450
92
2019
4
2
1200
1110
93
2019
4
3
1000
1110
94
2019
4
4
250
255
95
2019
4
5
4000
4440
96
2019
4
6
1500
1390
97
2019
4
7
2000
1899
98
2019
4
8
8000
7440
99
2019
4
9
650
635
100
2019
5
1
1500
1330
101
2019
5
2
1200
1290
102
2019
5
3
1000
960
103
2019
5
4
250
250
104
2019
5
5
4000
3690
105
2019
5
6
1500
1790
106
2019
5
7
2000
1990
107
2019
5
8
5500
5450
108
2019
5
9
350
340
109
2019
6
1
1500
1400
110
2019
6
2
1200
1420
111
2019
6
3
1000
1112.99
112
2019
6
4
250
240
113
2019
6
5
4000
3610
114
2019
6
6
1500
1485.95
115
2019
6
7
2000
2057.95
116
2019
6
8
5500
5000
117
2019
6
9
350
415.94
118
2019
7
1
1500
1450
119
2019
7
2
1200
1110
120
2019
7
3
1000
880
121
2019
7
4
250
260
122
2019
7
5
4000
3990
123
2019
7
6
1500
1450
124
2019
7
7
2000
2001
125
2019
7
8
8000
7960
126
2019
7
9
650
560