Aqua Water Refilling Management System Database Design

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.

If you have any questions or suggestion, please feel free to contact us or you can leave a message below.

1 thought on “Aqua Water Refilling Management System Database Design”

  1. 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

Leave a Comment