Entity Relationship Diagram and Normalization

This document provides a detailed explanation of Entity Relationship Diagram (ERD) and normalization in database management. It covers the requirements, creation of ER diagram, and normalization of tables. The document also includes a relational schema and MySQL database for reference.

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

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Database Tables
describe tEmployee;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| femployeeNumber | int(11) | NO | PRI | | |
| ffirstName | varchar(20) | NO | | | |
| flastName | varchar(20) | NO | | | |
| faddress | varchar(50) | NO | | | |
| fmobile | varchar(20) | NO | | | |
| fTFN | varchar(20) | NO | | | |
| femail | varchar(30) | NO | | | |
| fjoiningDate | datetime | NO | | | |
+-----------------+-------------+------+-----+---------+-------+
describe tFullTimeEmployee;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| femployeeNumber | int(11) | NO | PRI | | |
| fannualSalary | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
describe tCasualEmployee;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
13 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| femployeeNumber | int(11) | NO | PRI | | |
| fhourlyRate | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
describe tDepartment;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| fdepartmentNumber | int(11) | NO | PRI | | |
| ftitle | varchar(30) | NO | | | |
| fphone | varchar(20) | NO | | | |
| femail | varchar(50) | NO | | | |
| fsupervisorEmpID | int(11) | NO | MUL | | |
+---------------------+-------------+------+-----+---------+-------+
describe tStore;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| fstoreID | int(11) | NO | PRI | | |
| fstoreName | varchar(40) | NO | | | |
| fphone | varchar(20) | NO | | | |
| femail | varchar(30) | NO | | | |
| ffax | varchar(20) | NO | | | |
| fstreetAddress | varchar(50) | NO | | | |
| fsuburb | varchar(20) | NO | | | |
| fstate | varchar(15) | NO | | | |
| fpostCode | varchar(6) | NO | | | |
| fmanagerID | int(11) | NO | MUL | | |
14 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
+------------------+-------------+------+-----+---------+-------+
describe tStoreEmployee;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| fstoreID | int(11) | NO | MUL | | |
| femployeeNumber | int(11) | NO | PRI | | |
| fdepartmentNumber | int(11) | NO | | | |
+---------------------+---------+------+-----+---------+-------+
describe tDepartmentStore;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| fstoreID | int(11) | NO | PRI | | |
| fdepartmentNumber | int(11) | NO | PRI | | |
+---------------------+---------+------+-----+---------+-------+
mysql> describe tPaySlip;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| fpaySlipNumber | int(11) | NO | PRI | | |
| fworkedHours | double | NO | | | |
| femployeeNumber | int(11) | NO | MUL | | |
| fstoreID | int(11) | NO | MUL | | |
| fgrossPay | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
describe tSupplier;
15 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 | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| fsupplierID | int(11) | NO | PRI | | |
| ffirstName | varchar(30) | NO | | | |
| flastName | varchar(30) | NO | | | |
| faddress | varchar(50) | NO | | | |
| fmobile | varchar(20) | NO | | | |
| femail | varchar(30) | NO | | | |
+---------------+-------------+------+-----+---------+-------+
describe tProduct;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| fproductNumber | varchar(10) | NO | PRI | | |
| ftitle | varchar(30) | NO | | | |
| fbrand | varchar(30) | NO | | | |
| fdescription | varchar(50) | NO | | | |
| fprice | double | NO | | | |
| fcategory | varchar(30) | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tSupplierProduct;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| fsupplierID | int(11) | NO | PRI | | |
| fproductNumber | varchar(10) | NO | PRI | | |
16 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| fqtyAvailable | int(11) | NO | | | |
| fqtyOrdered | int(11) | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tPatron;
mysql> describe tPatron;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| fcustomerNumber | int(11) | NO | PRI | | |
| ffirstName | varchar(30) | NO | | | |
| flastName | varchar(30) | NO | | | |
| faddress | varchar(50) | NO | | | |
| fmobile | varchar(20) | NO | | | |
| femail | varchar(30) | NO | | | |
+-----------------+-------------+------+-----+---------+-------+
describe tProductOrder;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| forderNumber | int(11) | NO | PRI | | |
| fproductNumber | varchar(10) | NO | PRI | | |
| forderDate | datetime | NO | | | |
| fquantity | int(11) | NO | | | |
| fcustomerNumber | int(11) | NO | MUL | | |
+-------------------+-------------+------+-----+---------+-------+
17 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
Data in the Tables
select * from tEmployee;
+-----------------+------------+-----------+-------------------+------------
+---
---------+-------------------+---------------------+
| femployeeNumber | ffirstName | flastName | faddress | fmobile |
fT
FN | femail | fjoiningDate |
+-----------------+------------+-----------+-------------------+------------
+---
---------+-------------------+---------------------+
| 1 | William | Brown | 11 Church Rd. | 6234876474 |
63
47864764 | [e-mail] | 2014-01-11 00:00:00 |
| 2 | Lucas | Smith | 65 Banking Rd. | 7468734674 |
67
84784747 | [e-mail] | 2017-02-21 00:00:00 |
| 3 | Jacob | White | 44 Nick Rd. | 6874678474 |
67
84387474 | [e-mail] | 2017-01-01 00:00:00 |
| 4 | Xavier | Kelvin | 33 Beach Rd. | 7467847474 |
74
74747747 | [e-mail] | 2016-01-14 00:00:00 |
| 5 | Adam | Lee | 78 Sea Sight Ave. | 6487678474 |
78
48748763 | [e-mail] | 2017-07-17 00:00:00 |
18 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 6 | Oscar | Bush | 67 Ride St. | 4787847474 |
68
74747737 | [e-mail] | 2015-08-11 00:00:00 |
+-----------------+------------+-----------+-------------------+------------
+---
---------+-------------------+---------------------+
select * from tFullTimeEmployee;
+-----------------+---------------+
| femployeeNumber | fannualSalary |
+-----------------+---------------+
| 1 | 100 |
| 2 | 120 |
| 3 | 130 |
+-----------------+---------------+
select * from tCasualEmployee;
+-----------------+-------------+
| femployeeNumber | fhourlyRate |
+-----------------+-------------+
| 4 | 50 |
| 5 | 40 |
| 6 | 70 |
+-----------------+-------------+
select * from tDepartment;
+-------------------+----------+-------------+--------------------
+-------------
-----+
| fdepartmentNumber | ftitle | fphone | femail |
fsupervisorE
mpID |
19 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
+-------------------+----------+-------------+--------------------
+-------------
-----+
| 1 | accounts | 8734678477 | [e-mail] |
1 |
| 2 | finance | 8748948488 | [e-mail] |
3 |
| 3 | sales | 74378474777 | [e-mail] |
4 |
| 4 | HR | 6437874374 | [e-mail] |
2 |
+-------------------+----------+-------------+--------------------
+-------------
select * from tStore;
+----------+------------+-------------+-------------------+-------------
+-------
----------+----------+--------+-----------+------------+
| fstoreID | fstoreName | fphone | femail | ffax |
fstree
tAddress | fsuburb | fstate | fpostCode | fmanagerID |
+----------+------------+-------------+-------------------+-------------
+-------
----------+----------+--------+-----------+------------+
| 1 | Elijah | 5632672468 | [e-mail] | 734678346 | 114
Ge
orge Road | Sydney | NSW | 7612 | 1 |
| 2 | Jake | 67823479848 | [e-mail] | 87498848444 | 101
Pi
zza Ave. | Sydney | NSW | 9823 | 2 |
| 3 | Charles | 7634874744 | [e-mail] | 6934798574 | 23
Spa
20 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
rkle Rd. | Adelaide | SA | 9812 | 3 |
| 4 | Owen | 7643787575 | [e-mail] | 3847583457 | 11
Ape
x Rd. | Sydney | NSW | 8723 | 4 |
| 5 | Arlo | 8798585855 | [e-mail] | 8374589785 | 5
Geor
ge Av. | Sydney | NSW | 5312 | 5 |
+----------+------------+-------------+-------------------+-------------
+-------
----------+----------+--------+-----------+------------+
select * from tDepartmentStore;
+------------+---------------------+
| fstoreID | fdepartmentNumber |
+------------+---------------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 4 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
21 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 4 | 4 |
+------------+---------------------+
select * from tStoreEmployee;
+----------+-----------------+-------------------+
| fstoreID | femployeeNumber | fdepartmentNumber |
+----------+-----------------+-------------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 5 | 3 |
| 4 | 6 | 3 |
| 4 | 4 | 4 |
+----------+-----------------+-------------------+
select * from tPaySlip;
+----------------+--------------+-----------------+----------+-----------+
| fpaySlipNumber | fworkedHours | femployeeNumber | fstoreID | fgrossPay |
+----------------+--------------+-----------------+----------+-----------+
| 1 | 10 | 4 | 4 | 500 |
| 2 | 10 | 5 | 5 | 400 |
| 3 | 10 | 6 | 3 | 700 |
+----------------+--------------+-----------------+----------+-----------+
select * from tSupplier;
+-------------+------------+-----------+----------------+------------
+----------
--------+
22 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| fsupplierID | ffirstName | flastName | faddress | fmobile | femail
|
+-------------+------------+-----------+----------------+------------
+----------
--------+
| 1 | Jasper | White | 12 Sparkle Rd. | 3246728464 |
jasper@gm
ail.com |
| 2 | Jasper | Smith | 11 White Ave | 2764784757 |
jasper@gm
ail.com |
| 3 | Carter | White | 12 Smith Rd. | 8374958578 |
carter@gm
ail.com |
+-------------+------------+-----------+----------------+------------
+----------
--------+
select * from tProduct;
+----------------+------------+---------+--------------+--------+-------------
+
| fproductNumber | ftitle | fbrand | fdescription | fprice | fcategory
|
+----------------+------------+---------+--------------+--------+-------------
+
| 1 | Juiser | Samsung | Juiser | 102 | Electronics
|
| 2 | Shirt | Peter | Peter Shirt | 5 | Cloth
|
| 3 | Pant | Peter | Peter Pant | 6 | Cloth
|
23 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 4 | Face Cream | Loreal | Loreal Cream | 8 | Beauty
|
| 5 | Body Cream | Loreal | Loreal Cream | 9 | Beauty
|
+----------------+------------+---------+--------------+--------+-------------
+
select * from tSupplierProduct;
+-------------+----------------+---------------+-------------+
| fsupplierID | fproductNumber | fqtyAvailable | fqtyOrdered |
+-------------+----------------+---------------+-------------+
| 1 | 1 | 10 | 1 |
| 2 | 2 | 20 | 2 |
| 2 | 4 | 10 | 10 |
| 3 | 3 | 9 | 5 |
| 3 | 5 | 10 | 9 |
+-------------+----------------+---------------+-------------+
select * from tPatron;
+-----------------+------------+-----------+-----------------+------------
+-----
-------------+
| fcustomerNumber | ffirstName | flastName | faddress | fmobile |
fema
il |
+-----------------+------------+-----------+-----------------+------------
+-----
-------------+
24 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 1 | Toby | White | 12 Sparkle Rd. | 6487274777 |
toby
@gmail.com |
| 2 | Angus | Brown | 12 Sparkle Rd. | 7236478474 |
angu
[e-mail] |
| 3 | Caleb | Black | 12 Smith Rd. | 7894784744 |
cale
[e-mail] |
| 4 | Marcus | White | 114 George Road | 7348437744 |
marc
[e-mail] |
| 5 | Nathan | Black | 114 George Road | 7347698347 |
nath
[e-mail] |
+-----------------+------------+-----------+-----------------+------------
+-----
-------------+
select * from tProductOrder;
+--------------+----------------+---------------------+-----------
+-------------
----+
| forderNumber | fproductNumber | forderDate | fquantity |
fcustomerNum
ber |
+--------------+----------------+---------------------+-----------
+-------------
----+
| 1001 | 1 | 2017-05-10 00:00:00 | 1 |
1 |
| 1002 | 5 | 2017-09-11 00:00:00 | 2 |
25 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
2 |
| 1003 | 2 | 2018-05-12 00:00:00 | 1 |
1 |
| 1004 | 3 | 2017-11-13 00:00:00 | 3 |
3 |
| 1004 | 4 | 2017-11-14 00:00:00 | 3 |
3 |
| 1005 | 4 | 2017-12-15 00:00:00 | 4 |
4 |
| 1005 | 5 | 2017-09-16 00:00:00 | 4 |
4 |
+--------------+----------------+---------------------+-----------
+-------------
----+
Part D: SQL
Question 1
select fcustomerNumber, concat(ffirstName,' ',flastName) 'Customer Full N
ame', fmobile, faddress from tPatron order by fcustomerNumber desc;
+-----------------+--------------------+------------+-----------------+
| fcustomerNumber | Customer Full Name | fmobile | faddress |
+-----------------+--------------------+------------+-----------------+
| 5 | Nathan Black | 7347698347 | 114 George Road |
| 4 | Marcus White | 7348437744 | 114 George Road |
| 3 | Caleb Black | 7894784744 | 12 Smith Rd. |
| 2 | Angus Brown | 7236478474 | 12 Sparkle Rd. |
| 1 | Toby White | 6487274777 | 12 Sparkle Rd. |
26 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
+-----------------+--------------------+------------+-----------------+
Question 2
select * from tProduct where fprice<100;
+----------------+------------+--------+--------------+--------+-----------+
| fproductNumber | ftitle | fbrand | fdescription | fprice | fcategory |
+----------------+------------+--------+--------------+--------+-----------+
| 2 | Shirt | Peter | Peter Shirt | 5 | Cloth |
| 3 | Pant | Peter | Peter Pant | 6 | Cloth |
| 4 | Face Cream | Loreal | Loreal Cream | 8 | Beauty |
| 5 | Body Cream | Loreal | Loreal Cream | 9 | Beauty |
+----------------+------------+--------+--------------+--------+-----------+
Question 3
select tProductOrder.forderNumber, tProductOrder.fproductNumber, tProduct
.ftitle, tProductOrder.fquantity, tProduct.fprice, (tProductOrder.fquantity *
tP
roduct.fprice) as TotalAmount from tProductOrder inner join tProduct on
tProduc
t.fproductNumber=tProductOrder.fproductNumber where forderNumber=1005;
+--------------+----------------+------------+-----------+--------
+-------------
+
| forderNumber | fproductNumber | ftitle | fquantity | fprice |
TotalAmount
|
+--------------+----------------+------------+-----------+--------
+-------------
+
| 1005 | 4 | Face Cream | 4 | 8 |
32
|
27 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 1005 | 5 | Body Cream | 4 | 9 |
36
|
+--------------+----------------+------------+-----------+--------
+-------------
+
Question 4
select fstoreName, concat(fstreetAddress,' ',fsuburb, ' ', fstate, ' ' ,f
postcode) as Address, ffax from tStore where fstreetAddress like '%George%';
+------------+---------------------------------+------------+
| fstoreName | Address | ffax |
+------------+---------------------------------+------------+
| Elijah | 114 George Road Sydney NSW 7612 | 734678346 |
| Arlo | 5 George Av. Sydney NSW 5312 | 8374589785 |
+------------+---------------------------------+------------+
Question 5
select * from tProductOrder where forderNumber in (select forderNumber fr
om tProductOrder group by forderNumber having count(fproductNumber)>1);
+--------------+----------------+---------------------+-----------
+-------------
----+
| forderNumber | fproductNumber | forderDate | fquantity |
fcustomerNum
ber |
+--------------+----------------+---------------------+-----------
+-------------
----+
| 1004 | 3 | 2017-11-13 00:00:00 | 3 |
3 |
28 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 1004 | 4 | 2017-11-14 00:00:00 | 3 |
3 |
| 1005 | 4 | 2017-12-15 00:00:00 | 4 |
4 |
| 1005 | 5 | 2017-09-16 00:00:00 | 4 |
4 |
+--------------+----------------+---------------------+-----------
+-------------
Question 6
select fcustomerNumber, concat(ffirstName,' ',flastName) 'Customer Name',
faddress from tPatron where fcustomerNumber not in (select fcustomerNumber
from
tProductOrder);
+-----------------+---------------+-----------------+
| fcustomerNumber | Customer Name | faddress |
+-----------------+---------------+-----------------+
| 5 | Nathan Black | 114 George Road |
+-----------------+---------------+-----------------+
Question 7
select tEmployee.femployeeNumber, ffirstName, flastName, fstoreName, fti
tle as DepartmentName from tEmployee inner join tStoreEmployee on
tEmployee.femp
loyeeNumber=tStoreEmployee.femployeeNumber inner join tStore on
tStoreEmployee.f
storeID=tStore.fstoreID inner join tDepartment on
tstoreEmployee.fdepartmentNumb
er=tDepartment.fdepartmentNumber;
+-----------------+------------+-----------+------------+----------------+
| femployeeNumber | ffirstName | flastName | fstoreName | DepartmentName |
29 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
+-----------------+------------+-----------+------------+----------------+
| 1 | William | Brown | Elijah | accounts |
| 2 | Lucas | Smith | Jake | finance |
| 3 | Jacob | White | Charles | sales |
| 5 | Adam | Lee | Owen | sales |
| 6 | Oscar | Bush | Owen | sales |
| 4 | Xavier | Kelvin | Owen | HR |
+-----------------+------------+-----------+------------+----------------+
Question 8
select fstoreName, count(tStoreEmployee.femployeeNumber) NumberOfEmployee
s from tStore inner join tStoreEmployee on
tStore.fstoreID=tStoreEmployee.fstore
ID inner join tDepartment on
tStoreEmployee.fdepartmentNumber=tDepartment.fdepar
tmentNumber group by fstoreName, ftitle having ftitle='accounts';
+------------+-------------------+
| fstoreName | NumberOfEmployees |
+------------+-------------------+
| Elijah | 1 |
+------------+-------------------+
Question 9
mysql> select * from tProductOrder where forderDate>'2017-06-30' and
forderDate<
'2018-07-01';
+--------------+----------------+---------------------+-----------
+-------------
----+
30 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| forderNumber | fproductNumber | forderDate | fquantity |
fcustomerNum
ber |
+--------------+----------------+---------------------+-----------
+-------------
----+
| 1002 | 5 | 2017-09-11 00:00:00 | 2 |
2 |
| 1003 | 2 | 2018-05-12 00:00:00 | 1 |
1 |
| 1004 | 3 | 2017-11-13 00:00:00 | 3 |
3 |
| 1004 | 4 | 2017-11-14 00:00:00 | 3 |
3 |
| 1005 | 4 | 2017-12-15 00:00:00 | 4 |
4 |
| 1005 | 5 | 2017-09-16 00:00:00 | 4 |
4 |
+--------------+----------------+---------------------+-----------
+-------------
----+
Question 10
mysql> select fcustomerNumber, count(forderNumber) NumberOfOrders from
tProductO
rder group by fcustomerNumber;
+-----------------+----------------+
| fcustomerNumber | NumberOfOrders |
+-----------------+----------------+
| 1 | 2 |
31 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+-----------------+----------------+
Question 11
mysql> select forderNumber, forderDate, count(fproductNumber) as NumberOfProduct
s from tProductOrder group by forderNumber, forderDate;
+--------------+---------------------+------------------+
| forderNumber | forderDate | NumberOfProducts |
+--------------+---------------------+------------------+
| 1001 | 2017-05-10 00:00:00 | 1 |
| 1002 | 2017-09-11 00:00:00 | 1 |
| 1003 | 2018-05-12 00:00:00 | 1 |
| 1004 | 2017-11-13 00:00:00 | 1 |
| 1004 | 2017-11-14 00:00:00 | 1 |
| 1005 | 2017-09-16 00:00:00 | 1 |
| 1005 | 2017-12-15 00:00:00 | 1 |
+--------------+---------------------+------------------+
32 ISY1002/ISY103 Database Management for Business

180219: Robindeep Singh bal 180262 : Sarpreet Singh Sangha 170176: Gurpreet Singh
180079 : Jagbir Singh gill 180261 : Sahildeep Singh
References
SQL World (n.d.). Database Normalization with Examples. [Online]. Available:
http://www.complexsql.com/database-normalization/. [Accessed: 2-June-2019]
Datanamic Solutions (n.d.). Database Normalization. Retrieved From:
https://www.datanamic.com/support/database-normalization.html. [Accessed: 2-June-2019]
33 ISY1002/ISY103 Database Management for Business