Relational Database Project: ISY1002/ISY103 Database Management

This document presents a comprehensive solution to a database management project, likely for the ISY1002/ISY103 course. It includes an Entity Relationship Diagram (ERD) illustrating the database structure based on given case study requirements. The solution details the normalization process, bringing the database to the Third Normal Form (3NF), and provides a relational schema defining the tables, fields, data types, and key constraints. Furthermore, it demonstrates the implementation of the database using MySQL, including database table structures and records. Finally, the document includes SQL queries for data manipulation and retrieval. The case study revolves around the Australian Manufacturing Company (AMC) needing a database to manage stores, employees, products, suppliers, and orders.

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

Group Members ID:
Group Members Name:
Contents
Part A: Entity Relationship Diagram...........................................................................................................3
Case Study Requirements........................................................................................................................3
ER Diagram.............................................................................................................................................3
Part B: Normalization..................................................................................................................................4
Part C: Relational Schema and MySQL database........................................................................................6
Relational Schema...................................................................................................................................6
MySQL Database..................................................................................................................................11
Database Tables.................................................................................................................................12
Database Records..................................................................................................................................17
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. There may be number of employees in a store.
2. There will be only one manager in every store from the employees of the store.
3. There will be only one supervisor in every department from the employees of the store.
4. Product inventory will be maintained into the database.
5. An employee cannot be employed at more than one store.
6. Employees are stored separately with complete detail like name, address etc.
7. Customers are stored separately with complete detail like name, address etc.
8. Products are stored separately with complete detail like product number, name etc.
9. Stores are stored separately with complete detail store id, store name, address etc.
10. All orders are stored in the database with all products in the orders and customers also.
3 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ER Diagram
(BeginnersBook. 2019)
4 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part B: Normalization
(TechTarget 2019)
5 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
TblEmployee (fldemployeeNumber, fldfirstName, fldlastName, fldaddress, fldmobile, fldemail,
fldTFN, fldjoiningDate)
Normalization: Third Normal Form
TblfullTimeEmployee (fldemployeeNumber, fldannualSalary)
Normalization: Third Normal Form
TblcasualEmployee (fldemployeeNumber, fldhourlyRate)
Normalization: Third Normal Form
TblDepartment (flddepartmentNumber, fldtitle, fldphone, fldemail, fldsupervisorEmpID)
Normalization: Third Normal Form
TblStore (fldstoreID, fldstoreName, fldphone, fldemail, fldfax, fldstreetAddress, fldsuburb,
fldstate, fldpostcode, fldmanagerID)
Normalization: Third Normal Form
TblDepartmentStore (fldstoreID, flddepartmentNumber)
Normalization: Third Normal Form
TblStoreEmployee (fldemployeeNumber , fldstoreID, flddepartmentNumber)
Normalization: Third Normal Form
TblProduct (fldproductNumber, fldtitle, fldbrand, flddescription, fldprice, fldcategory)
Normalization: Third Normal Form
TblSupplier (fldsupplierID, fldfirstName, fldlastName, fldaddress, fldmobile, fldemail)
Normalization: Third Normal Form
TblSupplierProduct (fldsupplierID, fldproductNumber, fldqtyAvailable, fldqtyOrdered)
Normalization: Third Normal Form
TblPatron (fldcustomerNumber, fldfirstName, fldlastName, fldaddress, fldmobile)
Normalization: Third Normal Form
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
TblOrder (fldorderNumber, fldproductNumber, fldorderDate, fldquantity, fldcustomerNumber)
Normalization: Third Normal Form
TblPaySlip (fldpayslipNumber, fldworkedHours, fldemployeeNumber, fldstoried, fldgrossPay)
Normalization: Third Normal Form
Rules of 3 NF-
- There should be no repeated group in the table and all tables have primary key.
- All fields depend upon that primary key completely.
- There is no transitive independency in any table.
The AMC database is following all the mentioned rules. Therefore, it’s all tables are in 3 NF.
(Study.com 2019)
(The Crazy Programmer 2019)
Part C: Relational Schema and MySQL database
Relational Schema
tblEmployee Table
Field Name Data Type Length Key Constraints
fldemployeeNumber integer Primary Key
fldfirstName Varchar 30
fldlastName Varchar 30
fldaddress Varchar 50
fldmobile Varchar 20
fldEmail Varchar 50
fldTFN Varchar 20
fldjoiningDate Datetime
tblFulltimeEmployee Table
Field Name Data Type Length Key Constraints
fldemployeeNumber integer Primary Key
Foreign Key
references
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
tblEmployee
(fldemployeeNumber)
fldannualSalary double
tblCasualEmployee Table
Field Name Data Type Length Key Constraints
fldemployeeNumber integer Primary Key
Foreign Key
references
tblEmployee
(fldemployeeNumber)
fldhourlyRate double
tblDepartment Table
Field Name Data Type Length Key Constraints
fldDepartmentNumbe
r
Integer Primary Key
fldTitle Varchar 30
fldPhone Varchar 20
fldEmail Varchar 50
fldSupervisorEmpID Integer Foreign Key
references
tblEmployee
(fldemployeeNumber)
tblStore Table
Field Name Data Type Length Key Constraints
fldstoreID integer Primary Key
fldstoreName Varchar 40
fldPhone Varchar 20
fldEmail Varchar 50
fldFax Varchar 20
fldstreetAddress Varchar 50
fldsuburb Varchar 20
fldstate Varchar 15
fldpostcode Varchar 6
fldmanagerID integer Foreign Key
references
tblEmployee
8 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
(fldemployeeNumber)
tblDepartmentStore Table
Field Name Data Type Length Key Constraints
fldDepartmentNumbe
r
integer Primary Key
Foreign Key references
tblDepartment
(flddepartmentNumber)
fldStoreID integer Primary Key
Foreign Key references
tblStore (fldstoreID)
tblStoreEmployee Table
Field Name Data Type Length Key Constraints
fldEmployeeNumber integer Primary Key
Foreign Key
references
tblEmployee
(fldemployeeNumber)
fldstoreID integer Foreign Key
references
tblDepartmentStore
(fldstoreID)
fldDepartmentNumbe
r
Integer Foreign Key
references
tblDepartmentStore
(fldStoreID)
tblPayslip Table
Field Name Data Type Length Key Constraints
fldpaySlipNumber integer Primary Key
9 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
fldworkedHours double
fldemployeeNumber integer Foreign Key
references
tblEmployee
(fldemployeeNumber)
fldstoreID integer Foreign Key
references tblStore
(fldstoreID)
fldgrossPay double
tblSupplier Table
Field Name Data Type Length Key Constraints
fldsupplierID integer Primary Key
fldfirstName Varchar 30
fldlastName Varchar 30
fldaddress Varchar 50
fldmobile Varchar 20
fldemail Varchar 50
tblProduct Table
Field Name Data Type Length Key Constraints
fldproductNumber Varchar 10 Primary Key
fldtitle Varchar 30
fldbrand Varchar 30
flddescription Varchar 50
fldprice double
fldcategory Varchar 30
tblSupplierProduct Table
Field Name Data Type Length Key Constraints
fldproductNumber Varchar 10 Primary Key
Foreign Key
references tblProduct
(fldproductNumber)
fldsupplierID integer Primary Key
Foreign Key
references tblSupplier
(fldsupplierID)
10 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
fldqtyAvailable integer
fldqtyOrdered integer
tblPatron Table
Field Name Data Type Length Key Constraints
fldcustomerNumber integer Primary Key
fldfirstName Varchar 30
fldlastName Varchar 30
fldaddress Varchar 50
fldmobile Varchar 20
tblProductOrder Table
Field Name Data Type Length Key Constraints
fldorderNumber integer Primary Key
fldproductNumber Varchar 10 Primary Key
Foreign Key
references tblProduct
(fldproductNumber)
fldorderDate Datetime
fldquantity integer
fldcustomerNumber integer Foreign Key
references
tblCustomer
(fldcustomerNumber)
11 ISY1002/ISY103 Database Management for Business

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