Database Management for Business

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