Database Management for Business

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