ISYS6169001 – Database Systems

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.

ERD

https://miro.com/app/board/uXjVOXRUrM8=/

This project will be called The Music Shop.

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