Computer Science Database SQL Project

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 ROGER.CAMPBELL@TigerOne.com

EmployeeName Employee_Phone Employee_Email ExpenseReportNumber ExpenseReportDate ExpenseReportTotal ExpenseReportStatus Category ExpenseItem ExpenseDate ExpenseAmount

MOORE, FRANCES 281‐519‐9163 FRANCES.MOORE@TigerOne.com 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 AMY.SMITH@TigerOne.com 91032 7/30/2020 $35.30 Reimbursed Meals & Entertainment Resturant Meal 7/20/2020 $35.30

MORRISON, WANDA 832‐602‐7850 WANDA.MORRISON@TigerOne.com 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 SHERRY.PETERS@TigerOne.com

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 ELIZABETH.HERBERT@TigerOne.com 91030 7/27/2020 $28.69 Reimbursed Meals & Entertainment Resturant Meal 7/20/2020 $28.69

BROWN, ALBERT 832‐652‐5763 ALBERT.BROWN@TigerOne.com 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 DANNY.RICHMOND@TigerOne.com

EmployeeName Employee_Phone Employee_Email ExpenseReportNumber ExpenseReportDate ExpenseReportTotal ExpenseReportStatus Category ExpenseItem ExpenseDate ExpenseAmount

HOFFMAN, JOAN 832‐339‐8767 JOAN.HOFFMAN@TigerOne.com 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 GLORIA.DAVIS@TigerOne.com

91038 9/23/2020

91033 7/31/2020

$561.43 Pending

FIELDS, ANDREW 281‐370‐5294 ANDREW.FIELDS@TigerOne.com

SYLVIA, ROBERTA 832‐345‐5616 ROBERTA.SYLVIA@TigerOne.com

Reimbursed

JOHNSON, BRAD 281‐317‐7388 BRAD.JOHNSON@TigerOne.com

RODGERS, BARBARA 281‐534‐8298 BARBARA.RODGERS@TigerOne.com

9/25/2020 $561.43

91028 6/26/2020 $432.54

PendingMORRISON, FRANK

713‐612‐7502 TERRY.MICHAEL@TigerOne.com

91037 9/23/2020 $569.66 Pending

91027 6/25/2020 $280.15 ReimbursedMICHAEL, TERRY

832‐282‐6558 FRANK.MORRISON@TigerOne.com 91039