ISY1002/ISY103 Database Management: Relational Schema & MySQL Database

This document presents a solution to a database management project for the ISY1002/ISY103 course, focusing on the requirements of the Australian Manufacturing Company (AMC). The project encompasses several key aspects of database design and implementation, starting with the creation of an Entity Relationship (ER) Diagram to model the database structure based on the provided case study. It then progresses to the normalization of database tables to the Third Normal Form (3NF), ensuring data integrity and reducing redundancy. The solution includes a detailed relational schema defining the tables, fields, data types, and key constraints. Finally, the project demonstrates the implementation of the database using MySQL, including the creation of tables and the insertion of sample records. The document also provides SQL queries for data retrieval and manipulation, showcasing the practical application of database management principles. This comprehensive project serves as a valuable resource for students studying database management.

Database Management for Business
ISY1002/ISY103
Student ID:
Student Name:
6/2/2019

Group Members ID:
Group Members Name:
Contents
Report..........................................................................................................................................................3
Part A: Entity Relationship Diagram...........................................................................................................3
Case Study Requirements........................................................................................................................3
ER Diagram.............................................................................................................................................4
Part B: Normalization..................................................................................................................................5
Part C: Relational Schema and MySQL database........................................................................................7
Relational Schema...................................................................................................................................7
MySQL Database..................................................................................................................................12
Database Tables.................................................................................................................................13
Database Records..................................................................................................................................18
Part D: SQL...............................................................................................................................................26
References.................................................................................................................................................33
2 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Report
Part A: Entity Relationship Diagram
Case Study Requirements
1. A store will have any number of employees.
2. A store will have one manager only.
3. The manager will be the employee of the store.
4. A department will have any number of employees.
5. A department will have one supervisor only.
6. The supervisor will be the employee of the store.
7. An employee can work in only one store.
8. The inventory of all products has to be maintained.
9. The order detail along with date of order has to be maintained.
10. A supplier can deliver any number of products.
11. The ordered quantity of each product till date will be stored into database.
3 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ER Diagram
4 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part B: Normalization
Employee (employeeNumber, firstName, lastName, address, mobile, email, TFN, joiningDate)
Normalization: 3 NF
fullTimeEmployee (employeeNumber, annualSalary)
Normalization: 3 NF
casualEmployee (employeeNumber, hourlyRate)
Normalization: 3 NF
Department (departmentNumber, title, phone, email, supervisorEmpID)
Normalization: 3 NF
Store (storeID, storeName, phone, email, fax, streetAddress, suburb, state, postcode, managerID)
Normalization: 3 NF
DepartmentStore (storeID, departmentNumber)
Normalization: 3 NF
StoreEmployee (employeeNumber , storeID, departmentNumber)
Normalization: 3 NF
Product (productNumber, title, brand, description, price, category)
Normalization: 3 NF
Supplier (supplierID, firstName, lastName, address, mobile, email)
Normalization: 3 NF
SupplierProduct (supplierID, productNumber, qtyAvailable, qtyOrdered)
Normalization: 3 NF
Patron (customerNumber, firstName, lastName, address, mobile)
Normalization: 3 NF
5 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Order (orderNumber, productNumber, orderDate, quantity, customerNumber)
Normalization: 3 NF
PaySlip (payslipNumber, workedHours, employeeNumber, storied, grossPay)
Normalization: 3 NF
All tables are in third normal form because of the following rules-
- All tables are having unique pair of data i.e. there is no repeated group.
- Fields in the tables depend upon the primary key only.
- There is no transitive dependency in any table.
(en.tekstenuitleg.net n.d.)
(hackr.io 2018)
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part C: Relational Schema and MySQL database
Relational Schema
tbEmployee Table
Field Name Data Type Length Key Constraints
flemployeeNumber int Primary Key
flfirstName Varchar 20
fllastName Varchar 20
fladdress Varchar 50
flmobile Varchar 20
flEmail Varchar 50
flTFN Varchar 20
fljoiningDate Datetime
tbFulltimeEmployee Table
Field Name Data Type Length Key Constraints
flemployeeNumber int Primary Key
Foreign Key
references
tbEmployee
(flemployeeNumber)
flannualSalary double
tbCasualEmployee Table
Field Name Data Type Length Key Constraints
flemployeeNumber int Primary Key
Foreign Key
references
tbEmployee
(flemployeeNumber)
flhourlyRate double
tbDepartment Table
Field Name Data Type Length Key Constraints
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
flDepartmentNumber Int Primary Key
flTitle Varchar 30
flPhone Varchar 20
flEmail Varchar 50
flSupervisorEmpID Int Foreign Key
references
tbEmployee
(flemployeeNumber)
tbStore Table
Field Name Data Type Length Key Constraints
flstoreID int Primary Key
flstoreName Varchar 40
flPhone Varchar 20
flEmail Varchar 50
flFax Varchar 20
flstreetAddress Varchar 50
flsuburb Varchar 20
flstate Varchar 15
flpostcode Varchar 6
flmanagerID int Foreign Key
references
tbEmployee
(flemployeeNumber)
tbDepartmentStore Table
Field Name Data Type Length Key Constraints
flDepartmentNumber int Primary Key
Foreign Key
references
tbDepartment
(fldepartmentNumber)
flStoreID int Primary Key
Foreign Key
8 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
references tbStore
(flstoreID)
tbStoreEmployee Table
Field Name Data Type Length Key Constraints
flEmployeeNumber int Primary Key
Foreign Key
references
tbEmployee
(flemployeeNumber)
flstoreID int Foreign Key
references
tbDepartmentStore
(flstoreID)
flDepartmentNumber Int Foreign Key
references
tbDepartmentStore
(flStoreID)
tbPayslip Table
Field Name Data Type Length Key Constraints
flpaySlipNumber int Primary Key
flworkedHours double
flemployeeNumber int Foreign Key
references
tbEmployee
(flemployeeNumber)
flstoreID int Foreign Key
references tbStore
(flstoreID)
flgrossPay double
tbSupplier Table
Field Name Data Type Length Key Constraints
flsupplierID int Primary Key
flfirstName Varchar 20
fllastName Varchar 20
fladdress Varchar 50
9 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
flmobile Varchar 20
flemail Varchar 50
tbProduct Table
Field Name Data Type Length Key Constraints
flproductNumber Varchar 10 Primary Key
fltitle Varchar 30
flbrand Varchar 30
fldescription Varchar 50
flprice double
flcategory Varchar 30
tbSupplierProduct Table
Field Name Data Type Length Key Constraints
flproductNumber Varchar 10 Primary Key
Foreign Key
references tbProduct
(flproductNumber)
flsupplierID int Primary Key
Foreign Key
references tbSupplier
(flsupplierID)
flqtyAvailable int
flqtyOrdered int
tbPatron Table
Field Name Data Type Length Key Constraints
flcustomerNumber int Primary Key
flfirstName Varchar 30
fllastName Varchar 30
fladdress Varchar 50
flmobile Varchar 20
tbProductOrder Table
Field Name Data Type Length Key Constraints
florderNumber int Primary Key
10 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
flproductNumber Varchar 10 Primary Key
Foreign Key
references tbProduct
(flproductNumber)
florderDate Datetime
flquantity int
flcustomerNumber int Foreign Key
references tbCustomer
(flcustomerNumber)
(Janalta Interactive Inc. 2015)
(ApexSQL 2019)
11 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
MySQL Database
12 ISY1002/ISY103 Database Management for Business