Database Project Report and Presentation

[Database project, report and presentation]
Assessment 3
2019
Student ID:
Student Name:

Group Member ID:
Group Member Name:
Report
Contents
Report..........................................................................................................................................................2
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
Records..................................................................................................................................................16
Referential Integrity Constraint.............................................................................................................22
Part D: SQL...............................................................................................................................................27
References.................................................................................................................................................33
Database Management for Business ISY1002/ISY103 Page 2

Group Member ID:
Group Member Name:
Mission
The assignment’s main objective is to design and develop the database system of AMC. The
logical and physical both designs are made in the assignment. The logical design is used to show
the system requirement while the physical database is implemented on the basis of logical
design. The assignment is showing the database requirements by ER diagram and the database is
implemented in MYSQL database.
Part A: Entity Relationship Diagram
Case Study Requirements
1. There are lots of employees who work in one store.
2. One manager will be appointed for one store from the employees of the store only.
3. One supervisor will be appointed for one department from the employees of the
department only.
4. An employee can work for only one store.
5. The inventory of products will have to be maintained on regular basis.
6. The product details will be saved into the database.
7. The customer details will be saved into the database.
8. The supplier details will be saved into the database.
9. The store details will be saved into the database.
Database Management for Business ISY1002/ISY103 Page 3

Group Member ID:
Group Member Name:
ER Diagram
(Tutorialcup 2015)
(Ziff Davis 2017)
(Smartdraw 2016)
Database Management for Business ISY1002/ISY103 Page 4

Group Member ID:
Group Member Name:
Part B: Normalization
The database is in third normal form if the following rules are applied on the database-
- All tables have primary key and no repeated group.
- All fields in every table depend upon the primary key.
- No table has transitive dependency.
TbllEmployee (flldemployeeNumber, flldfirstName, flldlastName, flldaddress, flldmobile,
flldemail, flldTFN, flldjoiningDate)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllfullTimeEmployee (flldemployeeNumber, flldannualSalary)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllcasualEmployee (flldemployeeNumber, flldhourlyRate)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllDepartment (fllddepartmentNumber, flldtitle, flldphone, flldemail,
flldsupervisorEmpID)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllStore (flldstoreID, flldstoreName, flldphone, flldemail, flldfax, flldstreetAddress,
flldsuburb, flldstate, flldpostcode, flldmanagerID)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllDepartmentStore (flldstoreID, fllddepartmentNumber)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllStoreEmployee (flldemployeeNumber , flldstoreID, fllddepartmentNumber)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
Database Management for Business ISY1002/ISY103 Page 5

Group Member ID:
Group Member Name:
TbllCategory (flldcategory, fllddescription)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllProduct (flldproductNumber, flldtitle, flldbrand, fllddescription, flldprice,
flldcategory)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllSupplier (flldsupplierID, flldfirstName, flldlastName, flldaddress, flldmobile, flldemail)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllSupplierProduct (flldsupplierID, flldproductNumber, flldqtyAvailable,
flldqtyOrdered)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllPatron (flldcustomerNumber, flldfirstName, flldlastName, flldaddress, flldmobile)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllOrder (flldorderNumber, flldproductNumber, flldorderDate, flldquantity,
flldcustomerNumber)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllPaySlip (flldpayslipNumber, flldworkedHours, flldemployeeNumber, flldstoried,
flldgrossPay)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
Database Management for Business ISY1002/ISY103 Page 6

Group Member ID:
Group Member Name:
Part C: Relational Schema and MySQL database
Relational Schema
TbllEmployee Table
Field Name Data Type Length Key Constraints
flldemployeeNumber integer Primary Key
flldfirstName Varchar 20
flldlastName Varchar 20
flldaddress Varchar 40
flldmobile Varchar 20
flldEmail Varchar 30
flldTFN Varchar 20
flldjoiningDate Datetime
TbllFulltimeEmployee Table
Field Name Data Type Length Key Constraints
flldemployeeNumber integer Primary Key
Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldannualSalary double
TbllCasualEmployee Table
Field Name Data Type Length Key Constraints
flldemployeeNumber integer Primary Key
Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldhourlyRate double
Database Management for Business ISY1002/ISY103 Page 7

Group Member ID:
Group Member Name:
TbllDepartment Table
Field Name Data Type Length Key Constraints
flldDepartmentNumbe
r
Integer Primary Key
flldTitle Varchar 30
flldPhone Varchar 20
flldEmail Varchar 30
flldSupervisorEmpID Integer Foreign Key
references
TbllEmployee
(flldemployeeNumber)
TbllStore Table
Field Name Data Type Length Key Constraints
flldstoreID integer Primary Key
flldstoreName Varchar 40
flldPhone Varchar 20
flldEmail Varchar 30
flldFax Varchar 20
flldstreetAddress Varchar 50
flldsuburb Varchar 20
flldstate Varchar 20
flldpostcode Varchar 6
flldmanagerID integer Foreign Key
references
TbllEmployee
(flldemployeeNumber)
TbllDepartmentStore Table
Field Name Data Type Length Key Constraints
flldDepartmentNumbe
r
integer Primary Key
Foreign Key references
TbllDepartment
(fllddepartmentNumber)
flldStoreID integer Primary Key
Foreign Key references
TbllStore (flldstoreID)
Database Management for Business ISY1002/ISY103 Page 8