When figuring out how to split the work for this final project, I propose that since I was familiar with JavaFX, I would make the GUI application for the final application while Alvin will focus on making the database for the final application.
What both of us will do is then when both of our time is available, we would assess on what we have both done together using applications that allows us to both share our screen and we would then stitch both of our progress together by me sending Alvin the java files to then we are going to be connecting the database to the application as well as trying to properly send the data from the database so it can be communicated directly to the GUI application and doing some bug fixing together as well.
The Music Shop is the final result of the collaborative effort from Fadhlan and Alvin for Database Systems application final project. The final application is separated for customers, sellers, and managers.
General
Login Screen
When a user opens up the program, they will be greeted with login form. Existing user would only need to input their username and password while new user would be required to register their account first.
Register Screen
When a new user opens up the register screen by pressing the register button in the login screen, they will the be redirected to the register screen. Here, the user can enter their information for their account.
Customer Side
Catalogue Screen
This is the default screen when a customer logs in to the application. In this screen the customers would have access to different features such as search items, add items to their shipping bin, etc.
Search Function
The customer would be able to input a text in the search box and finds instruments/items that match anything that they input in the search box. The inputted text would be accounted in the searching from the name of the item, category, or the description.
Adding Items to the Shipping Bin
The add button is used to add item to the shipping bin. The remove button is similar with the difference being used to remove an item from the shipping bin.
Order Button
By pressing the order button, the user would then be greeted with the checkout screen where the user can finalize the order by pressing the order now button or cancel their order by pressing the cancel order button.
Receipt Screen
After pressing the order now button, the user would then be able to see their receipt showing their order number as well as their total item price and delivery address.
Edit Profile Button
In the first screen, there is an edit profile button located in the top left of the screen. This will get the user to this screen where customers could see their order list or edit their profile by changing username, password, or other details.
Customer Orders
Edit Profile Button
Employee Side
Login Screen
If a manger of the store already assign a user’s account as an employee account, they could login by checking the employee check box to login to the application as an employee.
Use Kai as the username with 12345 as the password to access as the manager.
Greeting Screen
In the greeting screen, their role would be shown. The roles are separated between the seller role and the manager role. The manager role would have access to the employee database while the seller role would only have access to the instruments database. Both of the roles have their edit access to their respective databases.
Manager Database Screen
When a manager press the employee button, they would have access to the employee database screen. Here, a manager can filter the details of each employee as well as editing or creating new employee. The seller button would be changed to the instrument button in which case it would redirect the scene into the edit instruments database screen.
Create Employee Prompt
Finally both the managers and seller would have access to the show database button. By pressing this button it will show all the databases of the application
Theme: Creating a Database Application using MySQL
The Music Shop
For the 3rd Semesters database systems final project, me (Fadhlan), Rafi, and Alvin were tasked to create an application that utilizes a database created using MySQL. We all decided to create a music store application that initially were only designed for customers to order or buy instruments. However, throughout the project it evolves with us adding more features eventually deciding to create the application that users who login would be directed to different scenes depending on their account. Customers would have their own separate scenes while employees and managers would also have different scenes.
This database is currently in the first stage of normalization. Each table should only have one value and each record must be unique in the first normalization. Examining each record in the database will demonstrate this. We assume that there is no double entry inside the database. The second normalization necessitates the use of a single column primary key that is not operationally reliant on any subset of candidate key relationships. Every table in our database has an id column that serves as the primary key and is independent of other columns. The database must not have any transitive functional dependencies for the third normalization. When a non-key column is changed, it may cause any of the other non-key columns to change, which is known as a transitive functional dependence. The diagram that we have does not have any functional dependencies, as we can see on the ERD. I believe we have done an excellent job of eliminating any inter-dependencies between the attributes as a group.
Relational Schema
Here is a relational schema that we have came up from the ERD that we created.
role(id, role_name, description) Not Null (id, role_name) Unique (id) employee (id, employee_name, join_date, address, date_of_birth, role_id, branch_id, phonenumber, gender, password) Not Null (id, employee_name, join_date, address, date_of_birth, role_id, branch_id, phonenumber, gender, password) Foreign Key role_id references role(id) On Delete Restrict On Update Cascade branch (id, branch_name, location, number_of_employee, number_of_item) Not Null (id, branch_name, location, number_of_employee, number_of_item) Unique (id) manufacturer (id, manufacturer_name, country, description) Not Null (id, manufacturer_name, country) Unique (id) instrument (id, instrument_name, manufacturer_id, category, description) Not Null (id, instrument_name, manufacturer_id, category) Foreign Key manufacturer_id references manufacturer(id) On Delete Restrict On Update Restrict item (id, instrument_id, serial_number, description, year_of_production, price, quantity, branch_id) Not Null (id, instrument_id, serial_number, year_of_production, price, quantity, branch_id) Foreign Key instrument_id references instrument(id) On Delete Restrict On Update Restrict Foreign Key branch_id references branch(id) On Delete Restrict On Update Restrict customer (id, customer_name, email, user_name, password, address) Not Null (id, customer_name, email, user_name, password, address) Unique (id, user_name) customer_order (id, customer_id, delivery_address, order_status, active, total_price) Not Null (id, customer_id, delivery_address, order_status, active, total_price) Foreign Key customer_id references customer(id) On Delete Restrict On Update Restrict order_item (id, customer_order_id, item_id, price, quantity) Not Null (id, customer_order_id, item_id, price, quantity) Foreign Key customer_order_id references customer_order(id) On Delete Restrict On Update Restrict Foreign Key item_id references item(id) On Delete Restrict On Update Restrict
