ISY1002/ISY103 Database Project: AMC System Design and SQL Queries

This assignment focuses on the design and development of a database system for the Australian Manufacturing Company (AMC). It includes both the logical and physical database designs, with the logical design illustrating the system requirements through an Entity Relationship (ER) diagram and the physical database implemented in MySQL. The project covers key aspects such as normalization to the third normal form, relational schema design detailing tables, fields, data types, and key constraints, and the implementation of referential integrity constraints. SQL queries are provided to demonstrate data manipulation and retrieval, showcasing practical application of database management principles. The assignment also includes database tables with records and their descriptions, offering a comprehensive overview of the database system's structure and functionality. Students can find similar solved assignments and past papers on Desklib.

[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

Group Member ID:
Group Member Name:
TbllStoreEmployee Table
Field Name Data Type Length Key Constraints
flldEmployeeNumber integer Primary Key
Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldstoreID integer Foreign Key
references
TbllDepartmentStore
(flldstoreID)
flldDepartmentNumbe
r
Integer Foreign Key
references
TbllDepartmentStore
(flldStoreID)
TbllPayslip Table
Field Name Data Type Length Key Constraints
flldpaySlipNumber integer Primary Key
flldworkedHours double
flldemployeeNumber integer Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldstoreID integer Foreign Key
references TbllStore
(flldstoreID)
flldgrossPay double
TbllSupplier Table
Field Name Data Type Length Key Constraints
flldsupplierID integer Primary Key
flldfirstName Varchar 20
flldlastName Varchar 20
flldaddress Varchar 50
flldmobile Varchar 20
Database Management for Business ISY1002/ISY103 Page 9

Group Member ID:
Group Member Name:
flldemail Varchar 30
TbllCategory Table
Field Name Data Type Length Key Constraints
flldcategory Varchar 30 Primary Key
fllddescription Varchar 50
TbllProduct Table
Field Name Data Type Length Key Constraints
flldproductNumber Varchar 10 Primary Key
flldtitle Varchar 30
flldbrand Varchar 30
fllddescription Varchar 50
flldprice double
flldcategory Varchar 30 Foreign Key
references
TbllCategory
(flldcategory)
TbllSupplierProduct Table
Field Name Data Type Length Key Constraints
flldproductNumber Varchar 10 Primary Key
Foreign Key
references
TbllProduct
(flldproductNumber)
flldsupplierID integer Primary Key
Foreign Key
references
TbllSupplier
(flldsupplierID)
flldqtyAvailable integer
flldqtyOrdered integer
Database Management for Business ISY1002/ISY103 Page 10

Group Member ID:
Group Member Name:
TbllPatron Table
Field Name Data Type Length Key Constraints
flldcustomerNumber integer Primary Key
flldfirstName Varchar 20
flldlastName Varchar 20
flldaddress Varchar 50
flldmobile Varchar 20
TbllProductOrder Table
Field Name Data Type Length Key Constraints
flldorderNumber integer Primary Key
flldproductNumber Varchar 10 Primary Key
Foreign Key
references
TbllProduct
(flldproductNumber)
flldorderDate Datetime
flldquantity integer
flldcustomerNumber integer Foreign Key
references
TbllCustomer
(flldcustomerNumber)
(Edugrabs.com 2015)
Database Management for Business ISY1002/ISY103 Page 11

Group Member ID:
Group Member Name:
MySQL Database
Database Management for Business ISY1002/ISY103 Page 12