Aqua Water Refilling Management System Database Design
The purpose of Aqua Water Refilling Management System Database Design is to overcome difficulties in manual operation in refilling station. The difficulty in the manual system is one of the reasons why the efficiency in availing services of the clients is not satisfying and keeping of records is often misplaced and not secure.
This system will be programmed using java that can enable the user to record things that are being purchased by the clients and it is created using MySQL database.
This system manages to display the data to be filled by the user according to the information of the customer in organize manner, such that their personal details, and the services they want to avail as well as the payment on the transaction they purchased. The system keeps the information of the customer and the details of what they purchased.
The system coordinates the arrangement on the delivery of products. It consists all the records for the location of the clients, date of transaction, schedule of delivery, contact number and the person assigned to deliver and the payment of customer to the quantity of product that about to deliver.
The system also views the information about the availability of the products as well as the containers. The system views the available containers to provide stocks again.
This system also manages the information of the employees that a refilling station must have just like a front liner, cashier, technical assistant, and delivery an. It stores the information in organizing so that it easy for the owner to access the detail of his/her employee.
Upon having this system, it will provide the capacity to the owner and clients to transact without spending time and effort.
Basic features:
- Manage user (add, list updates)
- Delivery (confirm, cancel)
- Products (add, delete, update).
DATA DICTIONARIES for Water Refilling Management System Database Design
The table below provides the entire database tables details such as field names, descriptions, data type and character length.
TABLE 1. EMPLOYEE
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| EMPLOYEE_ID(pk) | EMPLOYEE ID NUMBER | INT | 11 |
| F_NAME | EMPLOYEE FIRST NAME | VARCHAR | 50 |
| M_NAME | EMPLOYEE MIDDLE NAME | VARCHAR | 50
|
| L_NAME | EMPLOYEE LAST NAME | VARCHAR | 50 |
| AGE | AGE OF EMPLOYEE | VARCHAR | 11 |
| ADDRESS | LOCATION OF EMPLOYEE | VARCHAR | 50 |
| CONTACT_# | NUMBER OF employees | VARCHAR | 11 |
Table 2. Customer
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| CUSTOMER_ID(PK) | CUSTOMER ID NUMBER | INT | 11 |
| F_NAME | CUSTOMER FIRST NAME | VARCHAR | 50 |
| M_NAME | CUSTOMER MIDDLE NAME | VARCHAR | 50
|
| L_NAME | CUSTOMER LAST NAME | VARCHAR | 50 |
| ADDRESS | LOCATION OF CUSTOMER | VARCHAR | 50 |
| CONTACT_# | NUMBER OF customers | VARCHAR | 11 |
| DATE | DATE OF TRANSACTION | DATE TIME | 11 |
TABLE 3. PRODUCT
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| PRODUCT_ID(PK) | PRODUCT ID NUMBER | INT | 11 |
| PRODUCT_NAME
| PRODUCT NAME | VARCHAR | 50 |
| QUANTITY
| QUANTITY OF PRODUCTS | INT | 11
|
| UNIT_PRICE
| PRICE OF PRODUCTS | VARCHAR | 50 |
| DATE | DATE | DATE | 11 |
TABLE 4. DELIVERY
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| DELIVERY_ID(PK)
| DELIVERY ID NUMBER | INT | 11 |
| CUSTOMER_ID(FK)
| CUSTOMER ID NUMBER | INT | 11 |
| PRODUCT_ID(FK)
| PRODUCT ID NUMBER | INT | 11
|
| TRANSACTION_ID | TRANSACTION ID NUMBER | INT | 11 |
| QUANTITY | QUANTITY OF PRODUCTS | INT | 11 |
| DELIVERY_MAN | DELIVERY MAN | VARCHAR | 50 |
| DATE_DELIVER
| DATE TO DELIVER | DATE TIME | 11 |
TABLE5.CONTAINERS
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| CONTAINER_ID | CONTAINER ID NUMBER | INT | 11 |
| CONTAINER_NO
| CONTROL NUMBER | INT | 50 |
| QUANTITY | QUANTITY ORDER OF CUSTOMER | INT | 20 |
| ON_HAND
| AVAILABLE CONTAINERS | VARCHAR | 50 |
| UNIT_PRICE | PRICE OF CONTAINERS | VARCHAR | 11 |
TABLE 6.PAYMENT
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| PAYMENT_ID | PRODUCT ID NUMBER | INT | 11 |
| CUSTOMER_ID
| CUSTOMER ID NUMBER | INT | 50 |
| TRANSACTION_ID
| DATE OF TRANSACTION | DATE TIME | 11
|
| TOTAL | TOTAL OF PURCHASED PRODUCT | INT | 11 |
| AMOUNT_RECEIVED
| AMOUNT RECEIVED | INT | 11 |
| CHANGED | CHANGED | INT | 11 |
TABLE 7.TRANSACTION
| FIELD NAMES | DESCRIPTION | TYPE | LENGTH |
| TRANSACTION_ID | PRODUCT ID NUMBER | INT | 11 |
| PRODUCT_ID
| PRODUCT ID NUMBER | INT | 50 |
| TRANS_TYPE
| TYPE OF TRANSACTION | VARCHAR | 11
|
| UNIT_PRICE | PRICE OF PRODUCT | VARCHAR | 50 |
| QUANTITY | QUANTITY OF PRODUCT | INT | 11 |
| TOTAL
| TOTAL COST OF TRANSACTION | INT | 11 |
| DATE | DATE | DATE | 11 |
ENTITY RELATIONSHIP DIAGRAM
Figure 1 Proposed ER Diagram for water refilling management system shows the system entity relationship in each entity and their supposed function in each relationship.
Figure 1.Aqua Water Refilling Management System Entity Relationship Diagram
Based on figure 1, the proposed ER diagram for water refilling management system show the entity of the proposed Water Refilling Management System Database Design, which is represented by tables, the tables are made to meet the required specification of the system and provide any specific details of each entity within the system.
Download AQUA Water Refilling Management System Database Design Document pdf. WATER REFILLING SYSTEM
For another free material on database design.
- ER Diagram for Sales and Inventory System Database Design
- Ordering System Database Design with ERD
- ER Diagram for Water Refilling Station System Database Design
If you have any questions or suggestion, please feel free to contact us or you can leave a message below.
I am new to database, trying to learn how the relationships, PK and FK works. I don’t understand how you connect 2 tables when there is no FK used in those e g how the employee and the customer tables are connected without using Customer ID or Employee ID in respective tables. Same i saw salon management system, there too, employee table was connected to customers and transactions table in diagram but no FK was used in it. Confusing how can you connect tables without using FK