FINAL PROJECT REPORT FOR HCIN 543 1
FINAL PROJECT REPORT FOR HCIN 543-DATABASE DESIGN AND KNOWLEDGE
MANAGEMENT
Submitted to:
Dr. Thidarat Tinnakornsrisuphap
Prepared by:
Christopher Conrad Korycki
MS in Health Care Informatics
August 28th, 2018
FINAL PROJECT REPORT FOR HCIN 543 2
Executive Summary
This database illustrates Database Design Language (DBDL), SQL, Queries, Forms and Reports
all created in Microsoft Access. This particular database illustrates the relation between a
company’s employees and their assigned project.
1.0 Introduction
This project calls for the creation of a basic functioning database of our choosing. I have chosen
a Company Database that has two tables: an Employee Table and a Project Table.
2.0 Methodology
• Tables
o Explain information about the tables that you created (number of tables,
attributes, etc.) and data source.
• Employee (EmployeeID, EmployeeLastName, EmployeeFirstName, ProjectName)
• Project (ProjectName, EmployeeLastName, EmployeeFirstName, EmployeeID)
• Project Name in the Employee Table is the Foreign Key
o List all the tables in Database Design Language (DBDL) format
• Employee (EmployeeID, LastName, FirstName, Street, City, WageRate, SocSecNum,
ProjectName)
• AK SocSecNum
• SK LastName
• FK ProjectName ->Project
•
• Project (ProjectName, LastName, FirstName, EmployeeID)
• AK LastName
FINAL PROJECT REPORT FOR HCIN 543 3
• SK FirstName
• FK EmployeID -> Employee
Data Source is from professional experience.
The Tables:
Employee Table
EmployeeID LastName FirstName Street City State WageRate SocSecNum ProjectNam
1 Smith Brian 123 Anystreet Chicago IL $25.00 123456789 Marketing
2 Jones Tim 234 Maple Chicago IL $23.00 987654320 SEO
3 Roberts Kim 345 Oak Chicago IL $30.50 124678543 Advertising
4 Doe John 456 Willow Chicago IL $25.50 918273645 PR
5 McGinnis Shamus 6879 Burr Chicago IL $30.00 198473827 Web Design
Project Table
ProjectName LastName FirstName EmployeeID
Advertising Roberts Kim 3
Marketing Smith Brain 1
PR Doe John 4
SEO Jones Tim 2
Web Design McGinnis Shamus 5
o The relationship between the two tables is one-to-one. Each project is assigned to
one employee.
• Forms
FINAL PROJECT REPORT FOR HCIN 543 4
o Each form is used to access and enter data in a secure and controlled fashion.
• Queries
• Query1 Description-When EmployeID is selected their respective ProjectName also
appears.
• Query1 SQL:
SELECT [Link], [Link]
Query 1 DatasheetView:
EmployeeID ProjectName
1 Marketing
2 SEO
3 Advertising
4 PR
5 Web Design
FROM Employee;
• Query2 Description-When ProjectName is selected the EmployeeID also appears.
• Query2 SQL View: SELECT [Link], [Link]
• FROM Project;
• Query2 Datasheet View:
ProjectName EmployeeID
Advertising 3
Marketing 1
PR 4
SEO 2
Web Design 5
•
3.0 Results
• Reports
FINAL PROJECT REPORT FOR HCIN 543 5
• Query1 Report:
• Query 2 Report:
FINAL PROJECT REPORT FOR HCIN 543 6
• 4.0 Conclusions and Recommendations
This database shows the relationship between an employee (EmloyeeID) and the project
(ProjectName) they are assigned to work on in a company.
FINAL PROJECT REPORT FOR HCIN 543 7