Accounting Information System (AIS) Assignment

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

SQL Command
SELECT tblProjects.ProjectsID, tblProjects.Title, tblEmployee.Name, tblEmployee.Address,
tblEmployee.PhoneNumber, tblEmployee.HighestDegreeAttained, tblEmployee.Expertise
FROM tblEmployee INNER JOIN tblProjects ON tblEmployee.EmployeeID =
tblProjects.EmployeeID;
Query – 4
This query is used to provide the information about projects that are due on December 2018.
It is shown in below.
SQL Command
SELECT tblProjects.ProjectsID, tblProjects.Title, tblProjects.Location, tblProjects.DueDate
FROM tblProjects
WHERE (((tblProjects.DueDate) Between #12/1/2018# And #12/31/2018#));
3.5 Report Creation
Material Lists Report
This report is used to display the all materials that were delivered on November 18
and Supplier name. It is shown below (Bhatia & Bansal, n.d.).
13

Suppliers Report
This report is used to display the supplier’s full information’s. It is shown below.
14

The database file is attached here.
4. IT controls
In database, IT controls needs to three basic ways to do secure database such as flow
control, access control and inference control. These three controls are providing the data
security within the database. It must be ensured with special techniques that do not exist in
unsecure database. These controls are using the cryptographic techniques to provide the data
security and availability. The IT controls are must be understand the value of source controls
for database and learn the database tools, patterns, best practices and standards needs to
manage the database from source controls. It identifies the necessary flow within a team
needed to develop a database.
5. Ethical, Privacy and Security Issues
Database security issues are based on security types and database threats. The security
types are listed below.
Ethical and legal issues
This issue is regarding the right to access the certain information. It does not allow the
unauthorised persons access the database. The ethical issues is prospective the data collection
need to store the data securely and need to be aware of the data type you are gathering.
Policy issues
This issue should not be made publicly available for example credit ratings. Basically
the policies issues at the corporate, governmental and institutional levels.
A database threat includes the following aspects.
 Loss of integrity
 Loss of availability
 Loss of confidentiality
To mitigate the database security issues by using the database security control measures such
as,
 Data encryption
15

 Flow control
 Access control
 Interference control
These measures are provides the secure databases.
References
Bhatia, A., & Bansal, V. Database management system.
CORONEL, C. (2017). DATABASE SYSTEMS. [S.l.]: CENGAGE LEARNING.
16