Database Management for Business

Database
Managem
ent for
Business
May 30
201
9
Student ID:
Student Name:
ISY1002/
ISY103

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......................................................................6
Relational Schema........................................................................................................ 6
MySQL Database....................................................................................................... 11
Database Tables...................................................................................................... 12
Records in MySQL Database......................................................................................... 17
Part D: Relational Schema and MySQL database....................................................................25
References................................................................................................................... 31
2 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Report
Part A: Entity Relationship Diagram
Case Study Requirements
1. The complete detail of all stores in AMC will be stored into the database e.g. name,
address, phone etc.
2. The complete detail of all departments in AMC will be stored into the database.
3. The complete detail of all employees will be stored into the database e.g. name, address,
phone etc.
4. A store may have any number of employees.
5. Each store will have the manager.
6. The manager will be one of the employees.
7. Each department will have the supervisor.
8. The supervisor will be one of the employees.
9. The pay slip detail will be stored into the database.
10. The product detail will be stored into the database.
11. The inventory of all products will be stored into the database.
12. The complete detail of all patrons in AMC will be stored into the database e.g. name,
address, phone etc.
13. The complete detail of orders by patrons will be stored into the database e.g. order date,
ordered product, quantity etc.
14. An employee will be employed in one store only.
3 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ER Diagram
(Cinergix Pty Ltd. 2011)
4 ISY1002/ISY103 Database Management for Business

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

Group Members ID:
Group Members Name:
All relations are in third normal form because all are following the rules of third normal form-
- All the tables do not have any repeated group.
- No table has partial dependency i.e. all fields depend upon the primary key only.
- No table has transitive dependency i.e. no field depend upon any other field except
primary key.
(Rouse M. n.d.)
(Dimitri Fontaine 2019)
(Perucci D. n.d.)
Part C: Relational Schema and MySQL database
Relational Schema
Employee Table
Field Name Data Type Length Key Constraints
employeeNumber integer Primary Key
firstName Varchar 20
lastName Varchar 20
address Varchar 50
mobile Varchar 20
Email Varchar 50
TFN Varchar 20
joiningDate Datetime
Fulltime Table
Field Name Data Type Length Key Constraints
employeeNumber integer Primary Key
Foreign Key
references Employee
(employeeNumber)
annualSalary double
Casual Table
Field Name Data Type Length Key Constraints
employeeNumber integer Primary Key
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Foreign Key
references Employee
(employeeNumber)
hourlyRate double
Store Table
Field Name Data Type Length Key Constraints
storeID integer Primary Key
storeName Varchar 40
Phone Varchar 20
Email Varchar 50
Fax Varchar 20
streetAddress Varchar 50
suburb Varchar 20
state Varchar 20
postcode Varchar 6
managerID integer Foreign Key
references Employee
(employeeNumber)
Department Table
Field Name Data Type Length Key Constraints
departmentNumber Integer Primary Key
Title Varchar 20
Phone Varchar 20
Email Varchar 50
supervisorEmpID Integer Foreign Key
references Employee
(employeeNumber)
DepartmentStore Table
Field Name Data Type Length Key Constraints
departmentNumber integer Primary Key
Foreign Key
references Department
(departmentNumber)
storeID integer Primary Key
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Foreign Key
references Store
(storeID)
StoreEmployee Table
Field Name Data Type Length Key Constraints
EmployeeNumber integer Primary Key
Foreign Key
references Employee
(employeeNumber)
storeID integer Foreign Key
references
DepartmentStore
(storeID)
departmentNumber Integer Foreign Key
references
DepartmentStore
(storeID)
Payslip Table
Field Name Data Type Length Key Constraints
paySlipNumber integer Primary Key
workedHours double
employeeNumber integer Foreign Key
references Employee
(employeeNumber)
storeID integer Foreign Key
references Store
(storeID)
grossPay double
Supplier Table
Field Name Data Type Length Key Constraints
supplierID integer Primary Key
firstName Varchar 20
lastName Varchar 20
address Varchar 50
mobile Varchar 20
Email Varchar 50
8 ISY1002/ISY103 Database Management for Business