ISY1002 Database Management for Business Group Assignment Report

This report presents a comprehensive database project undertaken by a student, focusing on the Australian Manufacturing Company (AMC). The project encompasses several key areas, starting with an Entity Relationship (ER) diagram that visually represents the database's structure, detailing entities such as stores, departments, employees, and products, along with their relationships. The report then delves into database normalization, ensuring data integrity and minimizing redundancy by adhering to third normal form principles. Following this, a relational schema is developed, outlining the tables, fields, data types, and constraints, and subsequently implemented in a MySQL database. The report includes detailed descriptions of database tables, including field names, data types, keys, and constraints. Furthermore, the report provides sample data records to illustrate the database's population. The project also involves the creation of SQL queries to retrieve and manipulate data, demonstrating the practical application of database management principles. Finally, references are provided to support the methodologies and concepts discussed throughout the report.

Database Management for Business
May 30
2019
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
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
employeeNumber integer Primary Key
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)
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
storeID integer Primary Key
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
8 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Email Varchar 50
Product Table
Field Name Data Type Length Key Constraints
productNumber Varchar 10 Primary Key
title Varchar 20
brand Varchar 20
description Varchar 50
price double
category Varchar 30
SupplierProduct Table
Field Name Data Type Length Key Constraints
productNumber Varchar 10 Primary Key
Foreign Key
references product
(productNumber)
supplierID integer Primary Key
Foreign Key
references supplier
(supplierID)
qtyAvailable integer
qtyOrdered integer
Patron Table
Field Name Data Type Length Key Constraints
customerNumber integer Primary Key
firstName Varchar 20
lastName Varchar 20
address Varchar 50
mobile Varchar 20
9 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ProductOrder Table
Field Name Data Type Length Key Constraints
orderNumber integer Primary Key
productNumber Varchar 10 Primary Key
Foreign Key
references product
(productNumber)
orderDate Datetime
quantity integer
customerNumber integer Foreign Key
references customer
(customerNumber)
(Holowczak.com n.d.)
(Refsnes Data. 2019)
10 ISY1002/ISY103 Database Management for Business

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

Group Members ID:
Group Members Name:
Database Tables
Describe employee;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | | |
| firstName | varchar(20) | NO | | | |
| lastName | varchar(20) | NO | | | |
| address | varchar(50) | NO | | | |
| mobile | varchar(20) | NO | | | |
| TFN | varchar(20) | NO | | | |
| email | varchar(50) | NO | | | |
| joiningDate | datetime | NO | | | |
+----------------+-------------+------+-----+---------+-------+
Describe fullTime;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | | |
| annualSalary | double | NO | | | |
+----------------+---------+------+-----+---------+-------+
Describe casual;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
12 ISY1002/ISY103 Database Management for Business