Creating a Database Application for a Project Management Company

This assignment details the creation of a database application for a project management company using Microsoft Access. The solution includes the design of tables to store project, supplier, material, and employee information, along with an ER diagram to visualize relationships. The assignment further covers the creation of forms for data input and manipulation, and SQL queries to retrieve specific data. Reports are designed to display material lists and supplier information. The solution addresses IT controls for database security, including flow control, access control, and inference control, alongside a discussion of ethical, privacy, and security issues relevant to database management, referencing concepts like data encryption and security measures to mitigate potential risks and ensure data integrity and confidentiality. The database file is also attached.

ACCOUNTING
INFORMATION SYSTEM

Table of Contents
1. Introduction...................................................................................................................................3
2. Use of Database.............................................................................................................................3
3. Database Design............................................................................................................................3
3.1 Table’s Creation....................................................................................................................4
3.2 ER Diagram...........................................................................................................................5
3.3 Form Creation........................................................................................................................8
3.4 Query Creation....................................................................................................................11
3.5 Report Creation....................................................................................................................13
4. IT controls...................................................................................................................................15
5. Ethical, Privacy and Security Issues............................................................................................15
References...........................................................................................................................................16
2

1. Introduction
You are the coordinator for a project management company. You will be handling the
various ongoing projects. You also track the commercial project and employee activities and
employee participation in the each project. The project management company needs to create
the new database application to maintain the Project and employee information. So, you need
to create the new database applications by using the Microsoft access. The New database
applications needs to save the team members information, supplier’s information, material
information and project information. These databases also retrieve, delete and update the
information. The database application will be created.
2. Use of Database
The new database application is used to store the following information.
 Project information
 Supplier’s information
 Purchased materials information
 Employee Information
This database also retrieves the project, supplier and employee information. The new
database applications also do update, delete and insert operations. It provides the data
security. The database application was only accessed by the company administrators.
3. Database Design
The New database application creation is done by using the Microsoft Access. It is shown
below (CORONEL, 2017). The Database creation includes the,
 Table’s creation
 ER Diagram
 Query
 Form
 Report
3

3.1 Table’s Creation
Employee table
Material Lists Table
Project Description Table
4

Project Table
Suppliers Table
3.2 ER Diagram
The Entity Relationship diagram is shown in below.
5Phone Number

ER Diagram
6
Employee
Projects
SupplierMaterial List
Project Description
Address Highest Degree Attained
Name
Employee ID
Project Description ID
List of Associated Activities
Material Lists ID
Actual Delivery Date
Expected Delivery Date
Order Date
Cost
Supplier ID
Name
Address
ABN
Due Date
Project ID
Estimated Budget
Expertise
Location
Title
Contact Number
Include
Has

3.3 Form Creation
Employee Form
7
tblEmployee
EmployeeID
Name
Address
PhoneNumber
HighestDegreeAttained
Expertise
tblMaterialLists
MaterialListsID
Cost
OrderDate
ExpectedDeliveryDate
ActualDeliveryDate
ProjectDescriptionID
ListofAssociatedActivi
ties
MaterialListsID
SupplierID
ProjectID
Title
Location
DueDate
Estimated Budget
EmployeeID
ProjectDescriptionID
SupplierID
Name
Address
ABN
ContactNumber
tblProjects
tblSupplier
tblProjectDescription

This Employee form is used to add the new employee information’s and also read,
delete and write the existing employee information in the database. The employee Form
design is shown below.
Project Form
8

This Projects form is used to add the new Projects information’s and also read, delete and
write the existing projects information in the database. The Projects Form design is shown
below.
9

Supplier’s Form
This Supplier’s form is used to add the new Supplier’s information’s and also read, delete and
write the existing Supplier’s information in the database. The Supplier’s Form design is
shown below.
10

3.4 Query Creation
Query – 1
The below query is used to list the project information’s. It is shown in below.
SQL Command
SELECT tblProjects.ProjectsID, tblProjects.Title, tblProjects.Location,
tblProjects.EstimatedBudget, tblProjects.DueDate,
tblProjectDescription.ListofAssociatedActivities, tblEmployee.Name, tblEmployee.Expertise,
tblMaterialLists.MaterialListsID, tblMaterialLists.Cost, tblMaterialLists.ActualDeliveryDate
FROM tblMaterialLists INNER JOIN (tblEmployee INNER JOIN (tblProjectDescription
INNER JOIN tblProjects ON tblProjectDescription.ProjectDescriptionID =
tblProjects.ProjectDescriptionID) ON tblEmployee.EmployeeID = tblProjects.EmployeeID)
ON tblMaterialLists.MaterialListsID = tblProjectDescription.MaterialListsID;
11

Query – 2
This query is used to provide the project information like project ID, project Name,
Project due date and project activities. It is shown in below.
SQL Command
SELECT tblProjects.Title, tblProjects.DueDate, tblProjects.DueDate,
tblProjectDescription.ListofAssociatedActivities
FROM tblProjectDescription INNER JOIN tblProjects ON
tblProjectDescription.ProjectDescriptionID = tblProjects.ProjectDescriptionID;
Query – 3
This query is used to provide the information about the team members of each project.
It is shown below.
12