For our final project prototype, we used Visual Basic to make the program and MySQL for the database. For what our program is, it is a prototype program for a store that records transactions and also displays the items that exist in the shop or the details of the item and also the details of employee working in the store.

The ERD

The type table refers to the category of item. The source table is the company the item is obtained from. User table is the username and password for the employee to log in. Transaction table stores the data of the transactions and we can which transaction is being handled by which employee.

Relational Schema

Type(type_id, type_name)

Source(code, name, address, phone_number)

Item(item_id, name, price, quantity, type, source)
Foreign Key type References Type(type_id)
Foreign Key source References Source(code)

Employee(id, first_name, last_name, salary, position)

User(username, password, employee_id)
Foreign Key employee_id References Employee(id)

Transaction(id, item_id, qty, date, employee_id)
Foreign Key item_id References Item(item_id)
Foreign Key employee_id References Employee(id)

Normalization

UNF:

Transaction(transaction_id, item_id, item_name, item, item_quantity, item_price, item_type_id, item_type_name, item_source_code, source_name, source_address, source_phone, transaction_qty, date, employee_id, first_name, last_name, salary, position, username, password)

1NF:

Transaction(id, item_id, qty, employee_id)

Item(item_id, name, price, quantity, type_id, type_name, source_code, source_name, source_address, source_phone)

Employee(id, first_name, last_name, salary, position, username, password)

2NF:

Transaction(id, item_id, qty, employee_id)

Item(item_id, name, price, quantity, type, source)

Type(type_id, type_name)

Source(code, name, address, phone_number)

Employee(id, first_name, last_name, salary, position)

User(username, password, employee_id) <– because username cannot be redundant

For the user instructions:

At first, the user is given a login form. The user logs in with the appropriate username and password that exists in the database. The login form checks the user’s position in the database and gives the appropriate form to the user. If the user is an admin, they will be given admin form. If the user is a cashier, the user will be given cashier form. If the user is the stock manager, they will be given the form to add stocks of the items.

For the admin form, the user will be shown the transaction history. If the user wishes to look at other tables, they can check another radio button at the bottom to show the table they want to view. The update button is grayed out for every table and it will be available for clicking if the user clicks on a row in the table which shows the data in empty text boxes.

The Add button is to add a new record into the database. When clicked, the user will be given a new form to fill in.

The report button is to show the current state of the shop such as the income and also the number of sales.

However, the admin cannot add records into the transaction table because the cashier is the one responsible for all the transactions and they also cannot update the quantity of the items because the stock manager is responsible for it.

For the cashier form, the table shows the transactions the user had done previously. The cashier can only put in item ID and the quantity to be bought. The date will be automatically set as today.

The stock update form is the same like cashier, except it updates the number of items in stock and the table they view is the items and the quantity available right now.

For the search item ID field, the user can input any part of the item’s ID into the field when the button is clicked, all of the item containing what was typed into the text box will be shown.

The settings button in all the forms is for the user to change their username and password to their own choice of username and password.

The log out button is for the user to return to the login form.

Comments are closed.