Computer Science Database SQL Project
Ericka9225
Project.pdf
CS 346 ‐ Database Management Systems Fall 2021
Final Project
A company is planning to create a database for the expense reports submitted by employees from
various departments within the company. Each department has many employees and each employee
belongs to one department only. Each department has a department manager who can manage one
department only. When an employee spends money for business purposes, they submit an expense
report with the details of the items they spent money for. Each expense report is submitted by one
employee only. An employee may submit more than one expense report. There are employees who
never submit any expense reports. Each expense report contains one or more expense items. The
company maintains a list of all the expense items that could be reimbursed. Each item in this list may be
included in more than one expense report. Some items may exist in the list even if they have never been
included in any expense report yet. Expense items are grouped into categories. Each expense item
belongs to one category only and each category contains many items. A sample report with expense
reports submitted by employees from three departments is attached.
Given the information mentioned above and the attached report, design and implement the database
that will be used for the automated Expense Report System. To achieve your goal, complete the
following tasks:
1‐ Identify all the entities that will be captured in the database. Submit a list of these entities.
2‐ Analyze the information provided to extract the business rules that will be used to drive the
design of the database. Submit a list of the business rules.
3‐ Draw the Entity Relationship Diagram (ERD) for the database using the Crow’s Foot Notation.
4‐ Review the data in the attached report to identify all the data elements of the database.
5‐ Use the data in the report and follow the normalization steps discussed in class to group the
data items into tables normalized to the 4NF.
6‐ Optimize the 4NF database by introducing any necessary surrogate keys that will improve the
overall database design.
7‐ Submit a list of the 4NF tables and the attributes in each table. Indicate the Primary Key for
each table as wel las any Foreign Keys that might exist in the table.
8‐ Revise your Entity Relationship Diagram to see if any updates are needed after the
normalization and optimization steps. Submit your final Entity Relationship Diagram.
9‐ Create the database with all the tables in the MS SQL Server. Submit your MS SQL Server
Database File (YourLastName_DB.mdf).
Department Name Engineering
Manager Name CAMPBELL, ROGER
Manager Phone 281‐317‐7484
Manager Emaill [email protected]
EmployeeName Employee_Phone Employee_Email ExpenseReportNumber ExpenseReportDate ExpenseReportTotal ExpenseReportStatus Category ExpenseItem ExpenseDate ExpenseAmount
MOORE, FRANCES 281‐519‐9163 [email protected] 91034 8/3/2020 $745.00 Reimbursed Professional Development Training Fees 7/27/2020 $745.00
Pens 6/19/2020 $34.76
Printer Papers 6/19/2020 $245.39
SMITH, AMY 713‐539‐4539 [email protected] 91032 7/30/2020 $35.30 Reimbursed Meals & Entertainment Resturant Meal 7/20/2020 $35.30
MORRISON, WANDA 832‐602‐7850 [email protected] 91036 8/18/2020 $125.23 Reimbursed Professional Development Professional Journal Subscription 8/12/2020 $125.23
Air Ticket 9/14/2020 $345.98
Hotel 9/16/2020 $215.45
91023 5/28/2020 $650.00 Reimbursed Professional Development Conference Registration Fees 5/18/2020 $650.00
Air Ticket 9/14/2020 $345.98
Hotel 9/16/2020 $215.45
Laundry & Dry Cleaning 9/15/2020 $8.23
Department Name Finance
Manager Name PETERS, SHERRY
Manager Phone 832‐652‐5305
Manager Emaill [email protected]
EmployeeName Employee_Phone Employee_Email ExpenseReportNumber ExpenseReportDate ExpenseReportTotal ExpenseReportStatus Category ExpenseItem ExpenseDate ExpenseAmount
Meals & Entertainment Water 6/19/2020 $45.21
Paper Clips 6/19/2020 $23.65
Pens 6/19/2020 $34.56
Printer Papers 6/19/2020 $322.34
Stapler 6/19/2020 $6.78
HERBERT, ELIZABETH 832‐507‐7339 [email protected] 91030 7/27/2020 $28.69 Reimbursed Meals & Entertainment Resturant Meal 7/20/2020 $28.69
BROWN, ALBERT 832‐652‐5763 [email protected] 91035 8/17/2020 $654.90 Reimbursed Meals & Entertainment Food Catering 8/6/2020 $654.90
Department Name Marketing
Manager Name RICHMOND, DANNY
Manager Phone 713‐357‐6538
Manager Emaill [email protected]
EmployeeName Employee_Phone Employee_Email ExpenseReportNumber ExpenseReportDate ExpenseReportTotal ExpenseReportStatus Category ExpenseItem ExpenseDate ExpenseAmount
HOFFMAN, JOAN 832‐339‐8767 [email protected] 91029 6/29/2020 $749.94 Reimbursed Office Supplies Printer Papers 6/19/2020 $749.94
Air Ticket 9/14/2020 $345.98
Hotel 9/16/2020 $215.45
91025 6/24/2020 $45.29 Reimbursed Office Supplies Notepades 6/19/2020 $45.29
91031 7/28/2020 $150.00 Reimbursed Meals & Entertainment Event Ticket 7/20/2020 $150.00
91024 5/29/2020 $650.00 Reimbursed Professional Development Conference Registration Fees 5/18/2020 $650.00
91026 6/25/2020 $432.54 Reimbursed Meals & Entertainment Food Catering 6/19/2020 $432.54
Alcoholic Beverage 7/20/2020 $25.34
Event Ticket 7/20/2020 $150.00
Resturant Meal 7/20/2020 $39.56
Air Ticket 9/14/2020 $345.98
Hotel 9/16/2020 $215.45
Laundry & Dry Cleaning 9/15/2020 $12.34
Parking 9/15/2020 $10.00
Rental Car 9/14/2020 $143.76
Travel
Office Supplies
Travel
Travel
Office Supplies
Travel
Meals & Entertainment$214.90 Reimbursed
91040 9/25/2020 $727.53 Pending
DAVIS, GLORIA 713‐357‐2341 [email protected]
91038 9/23/2020
91033 7/31/2020
$561.43 Pending
FIELDS, ANDREW 281‐370‐5294 [email protected]
SYLVIA, ROBERTA 832‐345‐5616 [email protected]
Reimbursed
JOHNSON, BRAD 281‐317‐7388 [email protected]
RODGERS, BARBARA 281‐534‐8298 [email protected]
9/25/2020 $561.43
91028 6/26/2020 $432.54
PendingMORRISON, FRANK
713‐612‐7502 [email protected]
91037 9/23/2020 $569.66 Pending
91027 6/25/2020 $280.15 ReimbursedMICHAEL, TERRY
832‐282‐6558 [email protected] 91039
