ISY1002/ISY103 Database Management: Relational Schema & MySQL Database

This document presents a solution to a database management project for the ISY1002/ISY103 course, focusing on the requirements of the Australian Manufacturing Company (AMC). The project encompasses several key aspects of database design and implementation, starting with the creation of an Entity Relationship (ER) Diagram to model the database structure based on the provided case study. It then progresses to the normalization of database tables to the Third Normal Form (3NF), ensuring data integrity and reducing redundancy. The solution includes a detailed relational schema defining the tables, fields, data types, and key constraints. Finally, the project demonstrates the implementation of the database using MySQL, including the creation of tables and the insertion of sample records. The document also provides SQL queries for data retrieval and manipulation, showcasing the practical application of database management principles. This comprehensive project serves as a valuable resource for students studying database management.

Database Management for Business
ISY1002/ISY103
Student ID:
Student Name:
6/2/2019

Group Members ID:
Group Members Name:
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
Database Records..................................................................................................................................18
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. A store will have any number of employees.
2. A store will have one manager only.
3. The manager will be the employee of the store.
4. A department will have any number of employees.
5. A department will have one supervisor only.
6. The supervisor will be the employee of the store.
7. An employee can work in only one store.
8. The inventory of all products has to be maintained.
9. The order detail along with date of order has to be maintained.
10. A supplier can deliver any number of products.
11. The ordered quantity of each product till date will be stored into database.
3 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ER Diagram
4 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part B: Normalization
Employee (employeeNumber, firstName, lastName, address, mobile, email, TFN, joiningDate)
Normalization: 3 NF
fullTimeEmployee (employeeNumber, annualSalary)
Normalization: 3 NF
casualEmployee (employeeNumber, hourlyRate)
Normalization: 3 NF
Department (departmentNumber, title, phone, email, supervisorEmpID)
Normalization: 3 NF
Store (storeID, storeName, phone, email, fax, streetAddress, suburb, state, postcode, managerID)
Normalization: 3 NF
DepartmentStore (storeID, departmentNumber)
Normalization: 3 NF
StoreEmployee (employeeNumber , storeID, departmentNumber)
Normalization: 3 NF
Product (productNumber, title, brand, description, price, category)
Normalization: 3 NF
Supplier (supplierID, firstName, lastName, address, mobile, email)
Normalization: 3 NF
SupplierProduct (supplierID, productNumber, qtyAvailable, qtyOrdered)
Normalization: 3 NF
Patron (customerNumber, firstName, lastName, address, mobile)
Normalization: 3 NF
5 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Order (orderNumber, productNumber, orderDate, quantity, customerNumber)
Normalization: 3 NF
PaySlip (payslipNumber, workedHours, employeeNumber, storied, grossPay)
Normalization: 3 NF
All tables are in third normal form because of the following rules-
- All tables are having unique pair of data i.e. there is no repeated group.
- Fields in the tables depend upon the primary key only.
- There is no transitive dependency in any table.
(en.tekstenuitleg.net n.d.)
(hackr.io 2018)
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part C: Relational Schema and MySQL database
Relational Schema
tbEmployee Table
Field Name Data Type Length Key Constraints
flemployeeNumber int Primary Key
flfirstName Varchar 20
fllastName Varchar 20
fladdress Varchar 50
flmobile Varchar 20
flEmail Varchar 50
flTFN Varchar 20
fljoiningDate Datetime
tbFulltimeEmployee Table
Field Name Data Type Length Key Constraints
flemployeeNumber int Primary Key
Foreign Key
references
tbEmployee
(flemployeeNumber)
flannualSalary double
tbCasualEmployee Table
Field Name Data Type Length Key Constraints
flemployeeNumber int Primary Key
Foreign Key
references
tbEmployee
(flemployeeNumber)
flhourlyRate double
tbDepartment Table
Field Name Data Type Length Key Constraints
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
flDepartmentNumber Int Primary Key
flTitle Varchar 30
flPhone Varchar 20
flEmail Varchar 50
flSupervisorEmpID Int Foreign Key
references
tbEmployee
(flemployeeNumber)
tbStore Table
Field Name Data Type Length Key Constraints
flstoreID int Primary Key
flstoreName Varchar 40
flPhone Varchar 20
flEmail Varchar 50
flFax Varchar 20
flstreetAddress Varchar 50
flsuburb Varchar 20
flstate Varchar 15
flpostcode Varchar 6
flmanagerID int Foreign Key
references
tbEmployee
(flemployeeNumber)
tbDepartmentStore Table
Field Name Data Type Length Key Constraints
flDepartmentNumber int Primary Key
Foreign Key
references
tbDepartment
(fldepartmentNumber)
flStoreID int Primary Key
Foreign Key
8 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
references tbStore
(flstoreID)
tbStoreEmployee Table
Field Name Data Type Length Key Constraints
flEmployeeNumber int Primary Key
Foreign Key
references
tbEmployee
(flemployeeNumber)
flstoreID int Foreign Key
references
tbDepartmentStore
(flstoreID)
flDepartmentNumber Int Foreign Key
references
tbDepartmentStore
(flStoreID)
tbPayslip Table
Field Name Data Type Length Key Constraints
flpaySlipNumber int Primary Key
flworkedHours double
flemployeeNumber int Foreign Key
references
tbEmployee
(flemployeeNumber)
flstoreID int Foreign Key
references tbStore
(flstoreID)
flgrossPay double
tbSupplier Table
Field Name Data Type Length Key Constraints
flsupplierID int Primary Key
flfirstName Varchar 20
fllastName Varchar 20
fladdress Varchar 50
9 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
flmobile Varchar 20
flemail Varchar 50
tbProduct Table
Field Name Data Type Length Key Constraints
flproductNumber Varchar 10 Primary Key
fltitle Varchar 30
flbrand Varchar 30
fldescription Varchar 50
flprice double
flcategory Varchar 30
tbSupplierProduct Table
Field Name Data Type Length Key Constraints
flproductNumber Varchar 10 Primary Key
Foreign Key
references tbProduct
(flproductNumber)
flsupplierID int Primary Key
Foreign Key
references tbSupplier
(flsupplierID)
flqtyAvailable int
flqtyOrdered int
tbPatron Table
Field Name Data Type Length Key Constraints
flcustomerNumber int Primary Key
flfirstName Varchar 30
fllastName Varchar 30
fladdress Varchar 50
flmobile Varchar 20
tbProductOrder Table
Field Name Data Type Length Key Constraints
florderNumber int Primary Key
10 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
flproductNumber Varchar 10 Primary Key
Foreign Key
references tbProduct
(flproductNumber)
florderDate Datetime
flquantity int
flcustomerNumber int Foreign Key
references tbCustomer
(flcustomerNumber)
(Janalta Interactive Inc. 2015)
(ApexSQL 2019)
11 ISY1002/ISY103 Database Management for Business

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

Group Members ID:
Group Members Name:
Database Tables
describe tbEmployee;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| flemployeeNumber | int(11) | NO | PRI | | |
| flfirstName | varchar(20) | NO | | | |
| fllastName | varchar(20) | NO | | | |
| fladdress | varchar(50) | NO | | | |
| flmobile | varchar(20) | NO | | | |
| flTFN | varchar(20) | NO | | | |
| flemail | varchar(50) | NO | | | |
| fljoiningDate | datetime | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tbFullTimeEmployee;
+------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| flemployeeNumber | int(11) | NO | PRI | | |
| flannualSalary | double | NO | | | |
+------------------+---------+------+-----+---------+-------+
describe tbCasualEmployee;
+------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| flemployeeNumber | int(11) | NO | PRI | | |
| flhourlyRate | double | NO | | | |
13 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+------------------+---------+------+-----+---------+-------+
describe tbDepartment;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| fldepartmentNumber | int(11) | NO | PRI | | |
| fltitle | varchar(30) | NO | | | |
| flphone | varchar(20) | NO | | | |
| flemail | varchar(50) | NO | | | |
| flsupervisorEmpID | int(11) | NO | MUL | | |
+---------------------+-------------+------+-----+---------+-------+
describe tbStore;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| flstoreID | int(11) | NO | PRI | | |
| flstoreName | varchar(40) | NO | | | |
| flphone | varchar(20) | NO | | | |
| flemail | varchar(50) | NO | | | |
| flfax | varchar(30) | NO | | | |
| flstreetAddress | varchar(50) | NO | | | |
| flsuburb | varchar(20) | NO | | | |
| flstate | varchar(15) | NO | | | |
| flpostCode | varchar(6) | NO | | | |
| flmanagerID | int(11) | NO | MUL | | |
+------------------+-------------+------+-----+---------+-------+
14 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
describe tbStoreEmployee;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| flstoreID | int(11) | NO | MUL | | |
| flemployeeNumber | int(11) | NO | PRI | | |
| fldepartmentNumber | int(11) | NO | | | |
+---------------------+---------+------+-----+---------+-------+
describe tbDepartmentStore;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| flstoreID | int(11) | NO | PRI | | |
| fldepartmentNumber | int(11) | NO | PRI | | |
+---------------------+---------+------+-----+---------+-------+
mysql> describe tbPaySlip;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| flpaySlipNumber | int(11) | NO | PRI | | |
| flworkedHours | double | NO | | | |
| flemployeeNumber | int(11) | NO | MUL | | |
| flstoreID | int(11) | NO | MUL | | |
| flgrossPay | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
15 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
describe tbSupplier;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| flsupplierID | int(11) | NO | PRI | | |
| flfirstName | varchar(20) | NO | | | |
| fllastName | varchar(20) | NO | | | |
| fladdress | varchar(50) | NO | | | |
| flmobile | varchar(20) | NO | | | |
| flemail | varchar(50) | NO | | | |
+---------------+-------------+------+-----+---------+-------+
describe tbProduct;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| flproductNumber | varchar(10) | NO | PRI | | |
| fltitle | varchar(30) | NO | | | |
| flbrand | varchar(30) | NO | | | |
| fldescription | varchar(50) | NO | | | |
| flprice | double | NO | | | |
| flcategory | varchar(30) | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tbSupplierProduct;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| flsupplierID | int(11) | NO | PRI | | |
| flproductNumber | varchar(10) | NO | PRI | | |
16 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| flqtyAvailable | int(11) | NO | | | |
| flqtyOrdered | int(11) | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tbPatron;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| flcustomerNumber | int(11) | NO | PRI | | |
| flfirstName | varchar(30) | NO | | | |
| fllastName | varchar(30) | NO | | | |
| fladdress | varchar(50) | NO | | | |
| flmobile | varchar(20) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
describe tbProductOrder;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| florderNumber | int(11) | NO | PRI | | |
| flproductNumber | varchar(10) | NO | PRI | | |
| florderDate | datetime | NO | | | |
| flquantity | int(11) | NO | | | |
| flcustomerNumber | int(11) | NO | MUL | | |
+-------------------+-------------+------+-----+---------+-------+
17 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Database Records
select * from tbEmployee;
+------------------+-------------+------------+------------------------
+--------
----+------------+------------------+---------------------+
| flemployeeNumber | flfirstName | fllastName | fladdress |
flmobil
e | flTFN | flemail | fljoiningDate |
+------------------+-------------+------------+------------------------
+--------
----+------------+------------------+---------------------+
| 1 | Sebastian | Wheel | 7 Fir St |
5673467
467 | 9804905956 | [e-mail] | 2018-08-11 00:00:00 |
| 2 | Riley | Brown | 26 Victoria Ave |
9804905
959 | 7867483678 | [e-mail] | 2019-02-14 00:00:00 |
| 3 | Adam | Smith | Spring Valley Hume Hwy |
8943579
595 | 3487578575 | [e-mail] | 2018-01-01 00:00:00 |
| 4 | Leo | Klinton | 12 Moresby St |
8347595
895 | 8756785755 | [e-mail] | 2016-08-11 00:00:00 |
| 5 | Edward | White | 17 Saltau St |
7895789
585 | 8945895855 | [e-mail] | 2015-01-19 00:00:00 |
| 6 | Jacob | Lee | 214 Enmore Rd |
8678878
788 | 4575785785 | [e-mail] | 2016-09-09 00:00:00 |
+------------------+-------------+------------+------------------------
+--------
18 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
select * from tbFullTimeEmployee;
+------------------+----------------+
| flemployeeNumber | flannualSalary |
+------------------+----------------+
| 1 | 130 |
| 2 | 100 |
| 3 | 100 |
+------------------+----------------+
select * from tbCasualEmployee;
+------------------+--------------+
| flemployeeNumber | flhourlyRate |
+------------------+--------------+
| 4 | 50 |
| 5 | 70 |
| 6 | 90 |
+------------------+--------------+
select * from tbDepartment;
+---------------------+----------+-------------+--------------------
+-----------
---------+
| fldepartmentNumber | fltitle | flphone | flemail | flsupervi
sorEmpID |
+--------------------+----------+---------------+------------------
+------------
-------+
| fldepartmentNumber | fltitle | flphone | flemail |
flsuperviso
rEmpID |
+--------------------+----------+---------------+------------------
+------------
19 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
-------+
| 1 | finance | 5477677676 | [e-mail] |
3 |
| 2 | sales | 56765787667 | [e-mail] |
4 |
| 3 | accounts | 7687687756 | [e-mail] |
1 |
| 4 | HR | 6567576676675 | [e-mail] |
2 |
+--------------------+----------+---------------+------------------
+------------
-------+
select * from tbStore;
+-----------+-------------+-------------+----------------+-------------
+--------
---------+-----------+---------+------------+-------------+
| flstoreID | flstoreName | flphone | flemail | flfax |
flstree
tAddress | flsuburb | flstate | flpostCode | flmanagerID |
+-----------+-------------+-------------+----------------+-------------
+--------
---------+-----------+---------+------------+-------------+
| 1 | Lee | 5867868756 | [e-mail] | 6798989899 | 55
Geor
ge Road | Winthrop | WA | 4589 | 1 |
| 2 | White | 76757779786 | [e-mail] | t6769888775 | 26
Vict
oria Ave | Dalyellup | SA | 6564 | 2 |
| 3 | Spark | 8768687877 | [e-mail] | 3556455455 | 12
More
sby St | Rosebery | TAS | 6545 | 3 |
20 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 4 | Jones | 6789697888 | [e-mail] | 8543654545 | 214
Enm
ore Rd | Rosebery | TAS | 5734 | 4 |
| 5 | Klinten | 6546798788 | [e-mail] | 6588765486 | 78
Geor
ge Av. | Winthrop | WA | 8712 | 5 |
+-----------+-------------+-------------+----------------+-------------+-----
select * from tbDepartmentStore;
+-----------+--------------------+
| flstoreID | fldepartmentNumber |
+-----------+--------------------+
| 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 |
| 4 | 4 |
+-----------+--------------------+
21 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
select * from tbStoreEmployee;
+-----------+------------------+--------------------+
| flstoreID | flemployeeNumber | fldepartmentNumber |
+-----------+------------------+--------------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 5 | 3 |
| 4 | 6 | 3 |
| 4 | 4 | 4 |
+-----------+------------------+--------------------+
select * from tbPaySlip;
mysql> select * from tbPaySlip;
+-----------------+---------------+------------------+-----------+------------
+
| flpaySlipNumber | flworkedHours | flemployeeNumber | flstoreID | flgrossPay
|
+-----------------+---------------+------------------+-----------+------------
+
| 1 | 10 | 4 | 4 | 500
|
| 2 | 10 | 5 | 5 | 500
|
| 3 | 10 | 6 | 3 | 500
|
+-----------------+---------------+------------------+-----------+------------
+
22 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
select * from tbSupplier;
+--------------+-------------+------------+------------------------
+------------
-+----------------+
| flsupplierID | flfirstName | fllastName | fladdress | flmobile
| flemail |
+--------------+-------------+------------+------------------------
+------------
-+----------------+
| 1 | Rose | Lee | 6 Binar Way |
4765767686
| [e-mail] |
| 2 | Noah | White | 35 Barrett-Lennard Pde |
87678688777
| [e-mail] |
| 3 | Levi | Black | 6 Binar Way |
6786897787
| [e-mail] |
+--------------+-------------+------------+------------------------
+------------
-+----------------+
select * from tbProduct;
+-----------------+-------------+---------+-------------------+---------
+-------
------+
| flproductNumber | fltitle | flbrand | fldescription | flprice |
flcate
gory |
+-----------------+-------------+---------+-------------------+---------
+-------
------+
23 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| Prod101 | AC | Toshiba | AC | 120 |
Electr
onics |
| Prod102 | Cap | KingGee | KingGee Capt | 2 |
Cloth
|
| Prod103 | Hair Cream | Spark | Spark Hair Cream | 5 |
Beauty
|
| Prod104 | Hair Gel | Spark | Spark Hair Gel | 7 |
Beauty
|
| Prod105 | Body Lotion | Spark | Spark Body Lotion | 10 |
Beauty
|
+-----------------+-------------+---------+-------------------+---------+----
select * from tbSupplierProduct;
+--------------+-----------------+----------------+--------------+
| flsupplierID | flproductNumber | flqtyAvailable | flqtyOrdered |
+--------------+-----------------+----------------+--------------+
| 1 | Prod101 | 10 | 8 |
| 2 | Prod102 | 15 | 10 |
| 2 | Prod104 | 20 | 12 |
| 3 | Prod103 | 30 | 15 |
| 3 | Prod105 | 40 | 10 |
+--------------+-----------------+----------------+--------------+
select * from tbPatron;
+--------------+-----------------+----------------+--------------+
| flsupplierID | flproductNumber | flqtyAvailable | flqtyOrdered |
+--------------+-----------------+----------------+--------------+
24 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1 | Prod101 | 10 | 8 |
| 2 | Prod102 | 15 | 10 |
| 2 | Prod104 | 20 | 12 |
| 3 | Prod103 | 30 | 15 |
| 3 | Prod105 | 40 | 10 |
+--------------+-----------------+----------------+--------------+
select * from tbProductOrder;
+---------------+-----------------+---------------------+------------
+----------
--------+
| florderNumber | flproductNumber | florderDate | flquantity |
flcustome
rNumber |
+---------------+-----------------+---------------------+------------
+----------
--------+
| 1001 | Prod101 | 2017-06-30 00:00:00 | 1 |
1 |
| 1002 | Prod105 | 2017-10-10 00:00:00 | 2 |
2 |
| 1003 | Prod102 | 2018-06-19 00:00:00 | 1 |
1 |
| 1004 | Prod103 | 2017-11-19 00:00:00 | 3 |
3 |
| 1004 | Prod104 | 2017-10-17 00:00:00 | 3 |
3 |
| 1005 | Prod104 | 2017-12-13 00:00:00 | 4 |
4 |
| 1005 | Prod105 | 2017-12-14 00:00:00 | 4 |
25 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
4 |
+---------------+-----------------+---------------------+------------
+----------
--------+
Part D: SQL
Question 1
select flcustomerNumber, concat(flfirstName,' ',fllastName) 'Customer Ful
l Name', flmobile, fladdress from tbPatron order by flcustomerNumber desc;
+------------------+--------------------+------------+----------------+
| flcustomerNumber | Customer Full Name | flmobile | fladdress |
+------------------+--------------------+------------+----------------+
| 5 | Nick White | 5909606786 | 99 Church Rd. |
| 4 | Jack Kevin | 4850958959 | 889 Walker Rd. |
| 3 | Max Lee | 8934579858 | 32 Smith Rd. |
| 2 | Finn Brown | 5678234167 | 34 George Av. |
| 1 | Harry Wilson | 5366378338 | 14 Robert Av. |
+------------------+--------------------+------------+----------------Question
2
select * from tbProduct where flprice<100;
+-----------------+-------------+---------+-------------------+---------
+-------
-----+
| flproductNumber | fltitle | flbrand | fldescription | flprice |
flcate
gory |
+-----------------+-------------+---------+-------------------+---------
+-------
-----+
26 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| Prod102 | Cap | KingGee | KingGee Capt | 2 |
Cloth
|
| Prod103 | Hair Cream | Spark | Spark Hair Cream | 5 |
Beauty
|
| Prod104 | Hair Gel | Spark | Spark Hair Gel | 7 |
Beauty
|
| Prod105 | Body Lotion | Spark | Spark Body Lotion | 10 |
Beauty
|
+-----------------+-------------+---------+-------------------+---------
+-------
-----+
Question 3
select tbProductOrder.florderNumber, tbProductOrder.flproductNumber, tbPr
oduct.fltitle, tbProductOrder.flquantity, tbProduct.flprice,
(tbProductOrder.flq
uantity * tbProduct.flprice) as TotalAmount from tbProductOrder inner join
tbPr
oduct on tbProduct.flproductNumber=tbProductOrder.flproductNumber where
florderN
umber=1005;
+---------------+-----------------+-------------+------------+---------
+--------
-----+
| florderNumber | flproductNumber | fltitle | flquantity | flprice |
TotalAm
ount |
+---------------+-----------------+-------------+------------+---------
+--------
-----+
27 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1005 | Prod104 | Hair Gel | 4 | 7 |
28 |
| 1005 | Prod105 | Body Lotion | 4 | 10 |
40 |
+---------------+-----------------+-------------+------------+---------
+--------
-----+
Question 4
select flstoreName, concat(flstreetAddress,' ',flsuburb, ' ', flstate, '
' ,flpostcode) as Address, flfax from tbStore where flstreetAddress like
'%Georg
e%';
+-------------+---------------------------------+------------+
| flstoreName | Address | flfax |
+-------------+---------------------------------+------------+
| Lee | 55 George Road Winthrop WA 4589 | 6798989899 |
| Klinten | 78 George Av. Winthrop WA 8712 | 6588765486 |
+-------------+---------------------------------+------------+
Question 5
select * from tbProductOrder where florderNumber in (select florderNumber
from tbProductOrder group by florderNumber having count(flproductNumber)>1);
+---------------+-----------------+---------------------+------------
+----------
--------+
| florderNumber | flproductNumber | florderDate | flquantity |
flcustome
rNumber |
+---------------+-----------------+---------------------+------------
+----------
--------+
28 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1004 | Prod103 | 2017-11-19 00:00:00 | 3 |
3 |
| 1004 | Prod104 | 2017-10-17 00:00:00 | 3 |
3 |
| 1005 | Prod104 | 2017-12-13 00:00:00 | 4 |
4 |
| 1005 | Prod105 | 2017-12-14 00:00:00 | 4 |
4 |
+---------------+-----------------+---------------------+------------
+----------
--------+
Question 6
select flcustomerNumber, concat(flfirstName,' ',fllastName) 'Customer Nam
e', fladdress from tbPatron where flcustomerNumber not in (select
flcustomerNumb
er from tbProductOrder);
+------------------+---------------+---------------+
| flcustomerNumber | Customer Name | fladdress |
+------------------+---------------+---------------+
| 5 | Nick White | 99 Church Rd. |
+------------------+---------------+---------------+
Question 7
mysql> select tbEmployee.flemployeeNumber, flfirstName, fllastName,
flstoreName,
fltitle as DepartmentName from tbEmployee inner join tbStoreEmployee on
tbEmpl
oyee.flemployeeNumber=tbStoreEmployee.flemployeeNumber inner join tbStore on
tbS
toreEmployee.flstoreID=tbStore.flstoreID inner join tbDepartment on
tbstoreEmplo
29 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
yee.fldepartmentNumber=tbDepartment.fldepartmentNumber;
+------------------+-------------+------------+-------------+----------------+
| flemployeeNumber | flfirstName | fllastName | flstoreName | DepartmentName |
+------------------+-------------+------------+-------------+----------------+
| 1 | Sebastian | Wheel | Lee | finance |
| 2 | Riley | Brown | White | sales |
| 3 | Adam | Smith | Spark | accounts |
| 5 | Edward | White | Jones | accounts |
| 6 | Jacob | Lee | Jones | accounts |
| 4 | Leo | Klinton | Jones | HR |
+------------------+-------------+------------+-------------+----------------+
Question 8
select flstoreName, count(tbStoreEmployee.flemployeeNumber) NumberOfEmplo
yees from tbStore inner join tbStoreEmployee on
tbStore.flstoreID=tbStoreEmploye
e.flstoreID inner join tbDepartment on
tbStoreEmployee.fldepartmentNumber=tbDepa
rtment.fldepartmentNumber group by flstoreName, fltitle having
fltitle='accounts
';
+-------------+-------------------+
| flstoreName | NumberOfEmployees |
+-------------+-------------------+
| Jones | 2 |
| Spark | 1 |
+-------------+-------------------+
Question 9
select * from tbProductOrder where florderDate>'2017-06-30' and florderDa
te< '2018-07-01';
30 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+---------------+-----------------+---------------------+------------
+----------
--------+
| florderNumber | flproductNumber | florderDate | flquantity |
flcustome
rNumber |
+---------------+-----------------+---------------------+------------
+----------
--------+
| 1002 | Prod105 | 2017-10-10 00:00:00 | 2 |
2 |
| 1003 | Prod102 | 2018-06-19 00:00:00 | 1 |
1 |
| 1004 | Prod103 | 2017-11-19 00:00:00 | 3 |
3 |
| 1004 | Prod104 | 2017-10-17 00:00:00 | 3 |
3 |
| 1005 | Prod104 | 2017-12-13 00:00:00 | 4 |
4 |
| 1005 | Prod105 | 2017-12-14 00:00:00 | 4 |
4 |
+---------------+-----------------+---------------------+------------
+----------
--------+
Question 10
select flcustomerNumber, count(florderNumber) NumberOfOrders from tbProdu
ctOrder group by flcustomerNumber;
+------------------+----------------+
31 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| flcustomerNumber | NumberOfOrders |
+------------------+----------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+------------------+----------------+
Question 11
select florderNumber, florderDate, count(flproductNumber) as NumberOfProd
ucts from tbProductOrder group by florderNumber, florderDate;
+---------------+---------------------+------------------+
| florderNumber | florderDate | NumberOfProducts |
+---------------+---------------------+------------------+
| 1001 | 2017-06-30 00:00:00 | 1 |
| 1002 | 2017-10-10 00:00:00 | 1 |
| 1003 | 2018-06-19 00:00:00 | 1 |
| 1004 | 2017-10-17 00:00:00 | 1 |
| 1004 | 2017-11-19 00:00:00 | 1 |
| 1005 | 2017-12-13 00:00:00 | 1 |
| 1005 | 2017-12-14 00:00:00 | 1 |
+---------------+---------------------+------------------+
32 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
References
Janalta Interactive Inc. (2015). Functional Dependency. [Online]. Available:
http://www.techopedia.com/definition/19504/functional-dependency. [Accessed: 1-June-2019]
ApexSQL (2019). What is Database Normalization in SQL Server? Online. Available:
https://www.sqlshack.com/what-is-database-normalization-in-sql-server/. [Accessed: 1-June-
2019]
hackr.io (2018). DBMS Normalization: 1NF, 2NF, 3NF and BCNF with Examples. [Online].
Available: https://hackr.io/blog/dbms-normalization/. [Accessed: 1-June-2019]
en.tekstenuitleg.net (n.d.). Database Normalization. [Online]. Available:
http://en.tekstenuitleg.net/articles/software/database-design-tutorial/database-normalization.html.
[Accessed: 1-June-2019]
33 ISY1002/ISY103 Database Management for Business