Lab Assignment- JDBC

This is your fourth laboratory work submission. The Module Learning Outcomes 4-7 will be tested. You are expected to develop a database and a program as outlined below, demonstrate the program during laboratory hours, answer questions, and submit the code electronically.

To submit the Coursework, you need to attend the relevant laboratory sessions and develop the program during laboratory work.

The Task

You will need to develop a JavaFX Application which connects to a Database and makes use of the JDBC API.

The Scenario

See the ERD diagram given in Figure 1. The ERD is for a shopping mall having multiple shops (each shop is a company), employees are linked to a company through the foreign key. This is currently being used in a real scenario so that the shopping mall owner can run statistical reports across all companies.

You are asked to create a Point Of Sale system. The application should be JavaFX based. For database you can use your choice of RDMS (e.g. Java DB, MySQL etc.). The application should have the following functionalities:

1. Create the six tables shown on the diagram. 2. Simulate a point of sale (A transaction header is a sale. A transaction item is an item in a sale). The flow of a sale is as follows: a. Employee enters his id and start transaction b. Employee adds a Product ID. The system will pick the product price according to the effective date. c. Employee will add the quantity. d. System will calculate the sub total. e. System will loop until employee exits (may or may not start new transaction. f. Calculate total g. Transaction header and items are created (you should identify what fields go into this, e.g. customer details, product details, price etc.). h. Record the transaction.

When creating sale you can use SQL Transaction, commit and rollback.
What to submit:
Submit the Java source code and all SQL scripts by end of teaching week 20.
Demonstration: During the session of Week 20.