ISY1002/ISY103 Database Project: Design, Implementation & SQL

This document presents a comprehensive database project, including an Entity Relationship Diagram (ERD) based on given case study requirements. It covers the normalization of database tables to the third normal form (3NF), justifying the absence of partial and transitive dependencies. The project further details the relational schema, specifying field names, data types, lengths, and key constraints for each table. A MySQL database implementation is showcased with table structures and data entries. Finally, the project includes SQL queries for data manipulation and retrieval. Desklib provides a platform to explore this and many other solved assignments.

Student ID:
Student Name:

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
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........................................................................................7
Relational Schema...................................................................................................................................7
MySQL Database..................................................................................................................................12
Database Tables.................................................................................................................................13
Data in the Tables..................................................................................................................................18
Part D: SQL...............................................................................................................................................26
References.................................................................................................................................................33
2 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Report
Part A: Entity Relationship Diagram
Case Study Requirements
1. Number of employees work in a store.
2. Only one manager manages the store.
3. Only one supervisor supervises the department.
4. An employee can work only in one store.
5. The manager is the employee of the store.
6. The supervisor is the employee of the store.
7. Order detail will be stored into the database.
8. Inventory of products will be maintained into the database.
9. Complete customer detail will be maintained into the database.
10. Complete employee detail will be maintained into the database.
11. Complete supplier detail will be maintained into the database.
12. All store’s and its department’s detail will be stored into the database.
3 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
ER Diagram
4 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Part B: Normalization
TEmployee (femployeeNumber, ffirstName, flastName, faddress, fmobile, femail, fTFN,
fjoiningDate)
Table Employee is in third normal form because there is no partial dependency and transitive
dependency in the Employee table and all fields depend upon the primary key only.
TfullTimeEmployee (femployeeNumber, fannualSalary)
Table FullTimeEmployee is in third normal form because there is no partial dependency and
transitive dependency in the FullTimeEmployee table and all fields depend upon the primary key
only.
TcasualEmployee (femployeeNumber, fhourlyRate)
Table CasualEmployee is in third normal form because there is no partial dependency and
transitive dependency in the CasualEmployee table and all fields depend upon the primary key
only.
TDepartment (fdepartmentNumber, ftitle, fphone, femail, fsupervisorEmpID)
Table Department is in third normal form because there is no partial dependency and transitive
dependency in the Department table and all fields depend upon the primary key only.
TStore (fstoreID, fstoreName, fphone, femail, ffax, fstreetAddress, fsuburb, fstate,
fpostcode, fmanagerID)
Table Store is in third normal form because there is no partial dependency and transitive
dependency in the Store table and all fields depend upon the primary key only.
TDepartmentStore (fstoreID, fdepartmentNumber)
Table DepartmentStore is in third normal form because there is no partial dependency and
transitive dependency in the DepartmentStore table and all fields depend upon the primary key
only.
TStoreEmployee (femployeeNumber , fstoreID, fdepartmentNumber)
5 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Table StoreEmployee is in third normal form because there is no partial dependency and
transitive dependency in the StoreEmployee table and all fields depend upon the primary key
only.
TProduct (fproductNumber, ftitle, fbrand, fdescription, fprice, fcategory)
Table Product is in third normal form because there is no partial dependency and transitive
dependency in the Product table and all fields depend upon the primary key only.
TSupplier (fsupplierID, ffirstName, flastName, faddress, fmobile, femail)
Table Supplier is in third normal form because there is no partial dependency and transitive
dependency in the Supplier table and all fields depend upon the primary key only.
TSupplierProduct (fsupplierID, fproductNumber, fqtyAvailable, fqtyOrdered)
Table SupplierProduct is in third normal form because there is no partial dependency and
transitive dependency in the SupplierProduct table and all fields depend upon the primary key
only.
TPatron (fcustomerNumber, ffirstName, flastName, faddress, fmobile, femail)
Table Patron is in third normal form because there is no partial dependency and transitive
dependency in the Patron table and all fields depend upon the primary key only.
TProductOrder (forderNumber, fproductNumber, forderDate, fquantity,
fcustomerNumber)
Table ProductOrder is in third normal form because there is no partial dependency and transitive
dependency in the ProductOrder table and all fields depend upon the primary key only.
TPaySlip (fpayslipNumber, fworkedHours, femployeeNumber, fstoried, fgrossPay)
Table PaySlip is in third normal form because there is no partial dependency and transitive
dependency in the PaySlip table and all fields depend upon the primary key only.
(SQL World n.d.)
(Datanamic Solutions n.d.)
6 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Part C: Relational Schema and MySQL database
Relational Schema
tEmployee Table
Field Name Data Type Length Key Constraints
femployeeNumber integer Primary Key
ffirstName Varchar 20
flastName Varchar 20
faddress Varchar 50
fmobile Varchar 20
fTFN Varchar 20
fEmail Varchar 30
fjoiningDate Datetime
tFulltimeEmployee Table
Field Name Data Type Length Key Constraints
femployeeNumber integer Primary Key
Foreign Key
references tEmployee
(femployeeNumber)
fannualSalary double
tCasualEmployee Table
Field Name Data Type Length Key Constraints
femployeeNumber integer Primary Key
Foreign Key
references tEmployee
(femployeeNumber)
fhourlyRate double
tDepartment Table
7 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Field Name Data Type Length Key Constraints
fDepartmentNumber Integer Primary Key
fTitle Varchar 30
fPhone Varchar 20
fEmail Varchar 30
fSupervisorEmpID Integer Foreign Key
references tEmployee
(femployeeNumber)
tStore Table
Field Name Data Type Length Key Constraints
fstoreID integer Primary Key
fstoreName Varchar 40
fPhone Varchar 20
fEmail Varchar 30
fFax Varchar 20
fstreetAddress Varchar 50
fsuburb Varchar 20
fstate Varchar 15
fpostcode Varchar 6
fmanagerID integer Foreign Key
references tEmployee
(femployeeNumber)
tDepartmentStore Table
Field Name Data Type Length Key Constraints
fDepartmentNumber integer Primary Key
Foreign Key
references
tDepartment
(fdepartmentNumber)
fStoreID integer Primary Key
Foreign Key
references tStore
(fstoreID)
tStoreEmployee Table
8 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Field Name Data Type Length Key Constraints
fEmployeeNumber integer Primary Key
Foreign Key
references tEmployee
(femployeeNumber)
fstoreID integer Foreign Key
references
tDepartmentStore
(fstoreID)
fDepartmentNumber Integer Foreign Key
references
tDepartmentStore
(fStoreID)
tPayslip Table
Field Name Data Type Length Key Constraints
fpaySlipNumber integer Primary Key
fworkedHours double
femployeeNumber integer Foreign Key
references tEmployee
(femployeeNumber)
fstoreID integer Foreign Key
references tStore
(fstoreID)
fgrossPay double
tSupplier Table
Field Name Data Type Length Key Constraints
fsupplierID integer Primary Key
ffirstName Varchar 30
flastName Varchar 30
faddress Varchar 50
fmobile Varchar 20
femail Varchar 30
tProduct Table
Field Name Data Type Length Key Constraints
9 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
fproductNumber Varchar 10 Primary Key
ftitle Varchar 30
fbrand Varchar 30
fdescription Varchar 50
fprice double
fcategory Varchar 30
tSupplierProduct Table
Field Name Data Type Length Key Constraints
fproductNumber Varchar 10 Primary Key
Foreign Key
references tProduct
(fproductNumber)
fsupplierID integer Primary Key
Foreign Key
references tSupplier
(fsupplierID)
fqtyAvailable integer
fqtyOrdered integer
tPatron Table
Field Name Data Type Length Key Constraints
fcustomerNumber integer Primary Key
ffirstName Varchar 30
flastName Varchar 30
faddress Varchar 50
fmobile Varchar 20
femail Varchar 30
10 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
tProductOrder Table
Field Name Data Type Length Key Constraints
forderNumber integer Primary Key
fproductNumber Varchar 10 Primary Key
Foreign Key
references tProduct
(fproductNumber)
forderDate Datetime
fquantity integer
fcustomerNumber integer Foreign Key
references tCustomer
(fcustomerNumber)
11 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
MySQL Database
12 ISY1002/ISY103 Database Management for Business