A sales system is built using Java.
The database used is MySQL with the DB name 'Sales' and has a database structure which is illustrated with the following Class Diagram:
*On attachment
From the existing Data Class Diagram, please make one
I. Database
Create a 'Sales' Database using MySQL. Field ID using Auto Increment
Enter data into the Customer and Product Tables as follows:
Table Customer
Customer_ID
|
Customer_Name
|
Customer_Add
|
Int
|
Char(150)
|
Char(100)
|
1
|
EFGH, PT
|
XYZ
|
2
|
FGHI, CV
|
LMNO
|
3
|
DEFG
|
IJKL
|
4
|
EFGH
|
MNOP
|
5
|
GHIJ
|
QRST
|
Table Product
Product_ID
|
Product_Name
|
Stock
|
Int
|
Char(50)
|
Int
|
1
|
FFFF
|
2
|
2
|
GGGG
|
7
|
3
|
HHHH
|
8
|
4
|
IIII
|
6
|
5
|
JJJJ
|
5
|
II. GUI
Create a Sales Order Form with the following attached mockup:
*On attachment
*Note due to language differences in the mockup:
Tanggal means Date
Kode means Code
Nama means Name
Tambah Item means Add Item
Hapus Item means Delete Item
Simpan means Save
Keluar means Exit
III. Function
No
|
The function to be made
|
1
|
Create Transaction
Order_ID = 1
Order_Date = 11-2-2021
Customer_ID = 2
Customer_RefNo = 001 / I / S / 2021
When the TextField 'txtCustomerID' is filled and we click the 'View' Button, then the TextField 'txtViewCustomerName' will display the 'Customer_Name' field from the Customer's Table according to the input Customer_ID. If the data is not found, a notification will appear “Customer not registered”.
|
2
|
Fill in the List Combo Box Product_ID by retrieving data from the Product Fied Table Product_ID
|
3
|
If the button ‘Tambah Item’ is clicked, then data from Product_ID, txtProduct_Name, txtQuantity, txtPrice will be saved to the 'Order Detail' Tabke (according to the mockup), then also enter the Sub Total field in the 'Order Detail' table with the calculation of txtQuantity * txtPrice.
|
4
|
When the ‘Hapus’ button is clicked, data from Product_ID, txtProduct_Name, txtQuantity, txtPrice will be deleted.
|
5
|
Display txtGrandTotal based on Sum of Subtotals from 'Order Detail' Table.
|
6
|
Create Order Details with
Item 1: Product_ID = 2, Quantity = 4, Price = 11,000
Item 2: Product_ID = 4, Quantity = 2, Price = 21,000
If you press the 'Simpan' button, the data will be saved to the Order and Order_Detail Tables and stock updates to the product table, with the following mechanism:
Order:
Order_ID = txtOrder_ID, Order_Date = txtOrder_Date, Customer_ID = txtCustomer_ID, Customer_RefNo = txtCustomer_RefNo
Order_Detail:
Order_ID = txtOrder_ID, Product_ID = ID From the Order Details Table, Quantity = Quantity from the Order Details Table, Price = Price from the Order Details Table
Product:
Update Stock added Quantity according to the same Item Code.
If there are 2 items in the Order Detail Table, you must update the stock to the 2 items.
By calculating Stock = Stock - Quantity.
For proof, attach the output stock to the product table before and after the transaction.
|
7
|
If the 'Keluar' button is clicked it will exit the application
|
Please answer the questions in the order of the questions.
Submit Answers in the form of Java Coding and Capture Output Program.
For Database, attach the SQL DDL and DML Syntax and the Capture Database.
Extracted text: SALES ORDER Order Customer - Order ID - Order Date - Customer_ID · Customer_RefNo Char(25) + Create/Add + Read + Update/Edit Delete Int (PK) Order Detail Customer_ID Int (PK) Customer_Name Char(150) Customer Add Char(100) Date Order ID Int (PK) Int (FK) F1.*-1.1-- Product ID Int (FK) Quantity - Price Int -1.1-1,. Int + Create/Add + Read • Update/Edit Delete + Calculate SubTotal + Calculate GrandTotal 1.1 Product - Product_ID Product_Name Char(50) - Stock Int Int + Create/Add + Read + Update/Edit - Delete
Extracted text: Sales txtGrandTotal Purchase ID : txtOrder_ID Tanggal : txtOrder_Date Kode Vendor : txtCustomer_ID View txtViewCustomer_Name Vendor RefNo : txtCustomer_RefNo Product_ID Price txtPrice Nama Product Quantity txtQuantity txtProduct_Name Tambah tem Hapus item ORDER DETAIL ID Nama Product Quantity Price SubTotal Simpan Keluar