Database Implementation
This report presents a discussion of the design process of a database application process for the proposed Schafer refreshment Kiosk (SRK) business. SRK wants to start a small kiosk to sell refreshment to golfers in the local golf course. The business will be owned by Abby and Emma who are the owners of the idea of setting up a refreshment Kiosk near the golf course. Their Dad has to give them the startup capital for the business but has insisted there has to be a management system to keep track of the inventory, members and sales on a daily basis.
This reports shows how the database will be implemented to meet the final requirements of the system and how different features of the database application will be used to help manage the business. Potential risks that can originate from the system are discussed while also discussing different controls that can be used to mitigate the risks. The report also discusses allocation of duties in case a third employee is introduced to help manage the business.
Database implementation
The implementation of the database was done using Microsoft Access. The tables were designed to contain all the fields and each field has a specific data type and some fields required to follow a specific format. Relationships between the tables were designed to enforce referential integrity which is important for data consistency and data integrity. The following diagram shows the relationship diagram of the database.
Figure 1:Relationships
According to the screenshot of the relationships shown for the database, its clear that the proposed database is a fully relational database because every table is related to another table. Because of this relationships a number of problems can a rise of the right referential integrity is not enforced. For the SRK database the following referential integrity constraints were enforced;
- Cascade update related fields- This type of referential integrity ensures that each time a parent table is updated all the related records in the child table should also be updated.
- Cascade delete relate fields- This type of referential integrity constraint ensures that each time a record in the parent table is deleted then all the related records in the child table must also be deleted.
The two referential integrity constraints are enforced in all the relationships to main data consistency in the database since it’s a fully relational database.
Forms
After designing and implementing the tables a number of forms were required to populate the database. These forms include;
- Sales Form
This form is used to add records and update records in the sales table. Since every sale is associated with a customer, the customer a combo box is used which is set to be populated using the member ids in the members table. When the user selects the member Id then the first name, last name and address of the users are fetched and updated in the respective fields. Below is a screenshot of the sales form.
- Cash Receipts form
Forms
The cash receipts form is used to add and update records in the cash receipts table. Because a cheque received from a customer can be associated with more than one order this form is designed to have a sub form where the orders that are associated with the cheque are recorded so that incase the user needs to record more than one order then he can add more than one record. Below is a screenshot of the cash receipts form.
From the screenshot shown in figure 3 above, there is a clear demonstration of the concept of a sub form where cash receipt id CR001 belonging to member id MR01SK can have two orders associated with it.
Reports
Reports in Microsoft access can be generate reports that are easy to understand even for users who do not have any knowledge of Microsoft Access. For the SRK database there is a proposal to have 5 reports to be generated for different stakeholders. The following reports will be generated.
- Current inventory count report
- Daily total sales report
- Monthly customer statements report
- Monthly sales report
- Monthly sales and cash receipts report.
- Current inventory count report.
Query Name: Inventory query report
Fields included in query
|
Inventory table |
InventoryID Description InventoryCost InventoryPrice QuantityOnHand |
Purpose of the query
The purpose of the query is to get information that is necessary to generate report for the current inventory count. It gets the inventory id which is the id of the product, description of the product, and cost of buying the product, cost of selling the product and the remaining quantity on hand.
Below is a screenshot of the current inventory count report.
Query Name: Total sales Query
Fields included in the query
|
Member table |
memberID firstName lastName |
|
Sale table |
orderID orderDate |
|
Sale-CashReceipt table |
AmountApplied |
Purpose of the query
The purpose of the query is to generate data that is necessary for generating the daily total sales report that is broken down into sales customer. The query fetches the member id , the first name and last name of the customer, the order id of each sale, the order data and the amount applied for each order. The report has been designed to classify the orders per customer such that it shows a customer with his or her respective orders.
Below is a screen shot of the total sales report?
Monthly customer statements report
Query name: monthly customer statements query
Fields included in the query
|
Member table |
memberID firstName lastName |
|
CashReceipt |
CashReceiptID |
|
Sale-CashReceipt table |
AmountApplied |
Purpose
The purpose of the monthly customer statements query is to fetch the fields that are needed to generate the monthly customer statements report. The query fetches the member id of the customer, the first name and the last name of the customer, cash receipt id of the cash receipt and the total amount for the customer. The report is sent to golf course management to show the statements of the customer. The screenshot below shows the design of the monthly customer statements report.
Monthly sales report
Query Name: Monthly sales query
Fields included in the query
|
Sale table |
orderID orderDate |
|
Inventory table |
inventoryID description inventoryPrice |
|
Member table |
memberID lastName FirstName |
Purpose
The purpose the monthly sales query is to fetch fields that are needed for the generation of the monthly sales report. The query fetches the order id of the sale, the date that the sale happened, the inventory id which is the product id of the product sold in the sale, the description of the product, the inventory price which is the selling price of the product, the member id involved in the sale, their first name and last name. The report has been designed to display each order with its respective member and product. The monthly sales report is sent to the management of the golf course.Below is a screenshot of the monthly sales report.
Monthly sales and cash receipts report
Query Name: Monthly sales and cash receipts query
Fields in the query
|
Member table |
memberID LastName firstName |
|
Sale table |
orderID orderDate |
|
Inventory-sale table |
Quantity |
|
Inventory table |
inventoryID description inventoryPrice |
|
Cashreceipt table |
receiptID |
|
Sale-cashReceipt table |
amount |
Purpose
The purpose of the query is to fetch the fields that are needed to generate the monthly sales and cash receipts report that is sent to the Dad. The query fetches the member id of the customer, last name, first name, the order id of the sale, order date, quantity of the product purchased and the price it was purchased at, the description of the product, the receipt id of the receipt and the total amount for the order. Below is a screenshot of the monthly sales and cash receipts report.
Potential risks
|
Business risk |
Suggested control |
|
Lack of enough training for the users |
Training the users before launching the application for use |
|
Availability- some features are not implemented correctly thus limiting the capability of the system to perform as expected |
Conducting a lot of testing to ensure the system meets all requirements |
|
Unwillingness by the users to use the system where its supposed to be used |
Preparing the users before the system is fully implemented |
|
Data loss due to lack of backups |
Preforming regular backups to ensure there is a point of recovery in case of a data loss. |
|
Lack of support from involved stakeholders. For example the golf course management may not support the idea |
Engaging all stakeholders before implementing or rolling out the system for use. |
|
System does meet all requirements |
Conducting thorough system analysis before implementing the system |
|
Changing requirements- Requirements change after the development of the information system is underway |
Conducting thorough system analysis before implementing the system |
|
Lack of support and maintenance |
Ensuring support and maintenance after the deployment of the system |
Segregation of duties
Segregation of duties is the concept of involving many people to complete a specific task. For example for the SRK business there are three employees; Abby, Emma and Frank. Each of the employees can be assigned a task in the process of making an order such that each employee has a specific role to play in that process.
|
No. |
Entity |
Employee Name (Emma & Abby) |
Employee Name (Emma, Abby & Frank) |
Input – (receives) |
Process/Activity |
Output – (sends) |
|
1 |
Customer |
Orders product/s and quotes member number (orally) |
||||
|
2 |
Sales Dept. – Sales Clerk |
Emma |
Emma |
Receives member number and order |
Checks customer credit rating from paper list and enters customer and order details into computer |
Customer and order details keyed data |
|
3 |
Computer |
Customer and order details keyed data |
Retrieves Customer details from Customer data file and creates sales order. Saves order in Sales Order data file and prints 3 copies of the sales order |
Sends 3 copies of the sales order to Sales Clerk |
||
|
4 |
Production dept |
Abby |
Abby |
Order details |
Prepares the order and records a ledger of the products that have been used |
Sends |
|
5 |
Shipping Dept |
Frank |
Frank |
Products |
Packages the products based on the order of the customer |
Packaged product |
Future improvements
The suggested future improvements of the database are;
- Adding web integration to make it possible to make backups and access the database remotely. This is important because it can make it easy to generate reports. For example the Dad can generate reports instead of relying on the copy that is sent by the staff.
- Adding more functionality- The database can include more functionalities like graphs to which help to give better meaning to data.
- Integrating with excel to take advantage of the business intelligence features of excel. The business is expected to generate a huge amount of data which can be analyzed to give insights to the business.
Watt, A. (n.d.). Database Design:Chapter 11 Functional Dependencies.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
