Accounting Information System Report: Database Application Overview

This report provides a comprehensive overview of an Accounting Information System (AIS) designed for the Schafer Refreshment Kiosk Company. It begins with an Entity Relationship (ER) diagram, illustrating the relationships between various entities such as employees, members, sales, inventory, and customers. The report then details the tables used in the database, including their attributes and primary/foreign keys, covering tables for employees, inventory, members, cash receipts, sales, inventory sales, and customers. Data types for each table's attributes are also specified. The report includes several SQL queries demonstrating how to retrieve and manipulate data from the database, such as retrieving invoice details, customer information, extra descriptions, inventory details, and inventory sale quantities. It further details the design of forms for data input and the creation of reports for customer information, sales data, and cash receipts. The report also identifies potential business risks and outlines corresponding business controls. A structured narrative table explains the flow of information and activities within the system. Finally, the report suggests future improvements to the database application, emphasizing the benefits of using Microsoft Access for improved data sharing, security, and overall system performance.

ACCOUNTING INFORMATION SYSTEM

Table of Contents
ER Diagram.................................................................................................................................................3
Tables..........................................................................................................................................................4
Data types....................................................................................................................................................7
Relationships.............................................................................................................................................10
Query.........................................................................................................................................................11
Form..........................................................................................................................................................15
Report........................................................................................................................................................17
Potential Risks...........................................................................................................................................20
Structured Narrative Table........................................................................................................................21
Future Improvements.................................................................................................................................23
2

ER Diagram
The ER diagram is shown in below.
The Entity Relationship Diagram contains the following entities employee, member, sale
cash receipt, cash receipt, sale, inventory sale, inventory, customer and Extras. The Sale table
connecting the employee, member, sale cash receipt, customer and inventory sale. The Inventory
sale connecting the Inventory and extras table. The cash receipt table is connected with employee
and member table. Entity relationship diagram shows the full database application for the
Schafer refreshment Kiosk Company.
3

Tables
The Overall data application entities are shown in below.
For Employee Table,
The employee table consists of employee ID, first name, last name, address, suburb,
postcode phone number and email address. Here Employee ID is the primary key.
For Inventory Table,
The Inventory table consists of Inventory ID, Product ID, description, Inventory Cost,
Inventory Price, and Quality on hand. Here the Inventory ID is the primary key and product ID is
the foreign key.
4

For Member table,
The member table consists of Member ID, first name, last name, address, suburb, post
code, phone number, email address and credit limit. Here the member ID is the primary key.
For Cash Receipt table,
The cash receipt table consists of cash receipt ID, data, Cheque Number, Member ID and
Employee ID. Here the Cash receipt ID is the primary key. The Member ID and Employee ID is
the foreign key.
5

For Sale table,
The Sale table consists of Order ID, date, Member ID, employee ID, description,
Quantity Ordered, inventory Price, extension, Order Total, Inventory sale ID and sale cash
Receipt ID. Here the Order ID is the primary key. The inventory sale ID and sale cash receipt ID
is the foreign Key.
For Sale cash Receipt table,
The Sale cash receipt table consists of Cash receipt ID, order ID, amount applied and sale
cash receipt ID. The Sale Cash receipt ID is the primary key and cash receipt ID is the foreign
key.
6

For Inventory Sale Table,
The Inventory sale table consists of Inventory Sale ID, order ID, Product ID, quality
ordered and Inventory ID. The Inventory Sale ID is the primary key and Inventory ID is the
foreign key.
For Customer Table,
The Customer table consists of customer ID, First name, last name, address, phone
number and Order ID. The Customer ID is primary key.
Data types
For Inventory Table,
7

For Customer Table,
For Inventory Sale Table,
For Sale Table,
8

For Employee Table,
For Member Table,
For Cash Receipt Table,
For Sale Cash Receipt Table,
9

Relationships
10
Sale Customer
Sale Inventory
Sale
Inventory
Sale
Inventory
Inventory
Sale
Extras
Sale Employee
Sale Member
Cash
Receipt
Employee
Based on
Involves
Involves
Includes
Depends on
Includes
Provided by

Query
Query – 1
In this query is used to display the Invoice number, invoice date and order ID by using
the Sale table.
SQL Command for Query – 1
SELECT tblSale.InvoiceNo, tblSale.InvoiceDate, tblSale.OrderID
FROM tblSale;
11
Cash
Receipt
Member
Sale Sale cash
Receipt
Provided by
Provided by

Query – 2
The below query is used to provide the Customer ID, first name, last name, phone
number and Order ID by using the Customer table and sale table. These two tables are joined
together after provide the below query. It display the customer full details.
SQL Command for Query – 2
SELECT tblCustomer.CustomerID, tblCustomer.FirstName, tblCustomer.LastName,
tblCustomer.PhoneNumber, tblCustomer.OrderID
FROM tblCustomer;
Query – 3
This query is used to display the extra description and extra price by using the extras table.
12