Database Management for Business

This document provides study material and solved assignments for Database Management for Business. It includes topics such as entity relationship diagrams, normalization, relational schema, MySQL database, and SQL. The document also includes a case study and references for further reading.

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

Group Members ID:
Group Members Name:
Contents
Part A: Entity Relationship Diagram...........................................................................................................3
Case Study Requirements........................................................................................................................3
ER Diagram.............................................................................................................................................3
Part B: Normalization..................................................................................................................................4
Part C: Relational Schema and MySQL database........................................................................................6
Relational Schema...................................................................................................................................6
MySQL Database..................................................................................................................................11
Database Tables.................................................................................................................................12
Database Records..................................................................................................................................17
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. There may be number of employees in a store.
2. There will be only one manager in every store from the employees of the store.
3. There will be only one supervisor in every department from the employees of the store.
4. Product inventory will be maintained into the database.
5. An employee cannot be employed at more than one store.
6. Employees are stored separately with complete detail like name, address etc.
7. Customers are stored separately with complete detail like name, address etc.
8. Products are stored separately with complete detail like product number, name etc.
9. Stores are stored separately with complete detail store id, store name, address etc.
10. All orders are stored in the database with all products in the orders and customers also.
3 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ER Diagram
(BeginnersBook. 2019)
4 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part B: Normalization
(TechTarget 2019)
5 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
TblEmployee (fldemployeeNumber, fldfirstName, fldlastName, fldaddress, fldmobile, fldemail,
fldTFN, fldjoiningDate)
Normalization: Third Normal Form
TblfullTimeEmployee (fldemployeeNumber, fldannualSalary)
Normalization: Third Normal Form
TblcasualEmployee (fldemployeeNumber, fldhourlyRate)
Normalization: Third Normal Form
TblDepartment (flddepartmentNumber, fldtitle, fldphone, fldemail, fldsupervisorEmpID)
Normalization: Third Normal Form
TblStore (fldstoreID, fldstoreName, fldphone, fldemail, fldfax, fldstreetAddress, fldsuburb,
fldstate, fldpostcode, fldmanagerID)
Normalization: Third Normal Form
TblDepartmentStore (fldstoreID, flddepartmentNumber)
Normalization: Third Normal Form
TblStoreEmployee (fldemployeeNumber , fldstoreID, flddepartmentNumber)
Normalization: Third Normal Form
TblProduct (fldproductNumber, fldtitle, fldbrand, flddescription, fldprice, fldcategory)
Normalization: Third Normal Form
TblSupplier (fldsupplierID, fldfirstName, fldlastName, fldaddress, fldmobile, fldemail)
Normalization: Third Normal Form
TblSupplierProduct (fldsupplierID, fldproductNumber, fldqtyAvailable, fldqtyOrdered)
Normalization: Third Normal Form
TblPatron (fldcustomerNumber, fldfirstName, fldlastName, fldaddress, fldmobile)
Normalization: Third Normal Form
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
TblOrder (fldorderNumber, fldproductNumber, fldorderDate, fldquantity, fldcustomerNumber)
Normalization: Third Normal Form
TblPaySlip (fldpayslipNumber, fldworkedHours, fldemployeeNumber, fldstoried, fldgrossPay)
Normalization: Third Normal Form
Rules of 3 NF-
- There should be no repeated group in the table and all tables have primary key.
- All fields depend upon that primary key completely.
- There is no transitive independency in any table.
The AMC database is following all the mentioned rules. Therefore, it’s all tables are in 3 NF.
(Study.com 2019)
(The Crazy Programmer 2019)
Part C: Relational Schema and MySQL database
Relational Schema
tblEmployee Table
Field Name Data Type Length Key Constraints
fldemployeeNumber integer Primary Key
fldfirstName Varchar 30
fldlastName Varchar 30
fldaddress Varchar 50
fldmobile Varchar 20
fldEmail Varchar 50
fldTFN Varchar 20
fldjoiningDate Datetime
tblFulltimeEmployee Table
Field Name Data Type Length Key Constraints
fldemployeeNumber integer Primary Key
Foreign Key
references
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
tblEmployee
(fldemployeeNumber)
fldannualSalary double
tblCasualEmployee Table
Field Name Data Type Length Key Constraints
fldemployeeNumber integer Primary Key
Foreign Key
references
tblEmployee
(fldemployeeNumber)
fldhourlyRate double
tblDepartment Table
Field Name Data Type Length Key Constraints
fldDepartmentNumbe
r
Integer Primary Key
fldTitle Varchar 30
fldPhone Varchar 20
fldEmail Varchar 50
fldSupervisorEmpID Integer Foreign Key
references
tblEmployee
(fldemployeeNumber)
tblStore Table
Field Name Data Type Length Key Constraints
fldstoreID integer Primary Key
fldstoreName Varchar 40
fldPhone Varchar 20
fldEmail Varchar 50
fldFax Varchar 20
fldstreetAddress Varchar 50
fldsuburb Varchar 20
fldstate Varchar 15
fldpostcode Varchar 6
fldmanagerID integer Foreign Key
references
tblEmployee
8 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
(fldemployeeNumber)
tblDepartmentStore Table
Field Name Data Type Length Key Constraints
fldDepartmentNumbe
r
integer Primary Key
Foreign Key references
tblDepartment
(flddepartmentNumber)
fldStoreID integer Primary Key
Foreign Key references
tblStore (fldstoreID)
tblStoreEmployee Table
Field Name Data Type Length Key Constraints
fldEmployeeNumber integer Primary Key
Foreign Key
references
tblEmployee
(fldemployeeNumber)
fldstoreID integer Foreign Key
references
tblDepartmentStore
(fldstoreID)
fldDepartmentNumbe
r
Integer Foreign Key
references
tblDepartmentStore
(fldStoreID)
tblPayslip Table
Field Name Data Type Length Key Constraints
fldpaySlipNumber integer Primary Key
9 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
fldworkedHours double
fldemployeeNumber integer Foreign Key
references
tblEmployee
(fldemployeeNumber)
fldstoreID integer Foreign Key
references tblStore
(fldstoreID)
fldgrossPay double
tblSupplier Table
Field Name Data Type Length Key Constraints
fldsupplierID integer Primary Key
fldfirstName Varchar 30
fldlastName Varchar 30
fldaddress Varchar 50
fldmobile Varchar 20
fldemail Varchar 50
tblProduct Table
Field Name Data Type Length Key Constraints
fldproductNumber Varchar 10 Primary Key
fldtitle Varchar 30
fldbrand Varchar 30
flddescription Varchar 50
fldprice double
fldcategory Varchar 30
tblSupplierProduct Table
Field Name Data Type Length Key Constraints
fldproductNumber Varchar 10 Primary Key
Foreign Key
references tblProduct
(fldproductNumber)
fldsupplierID integer Primary Key
Foreign Key
references tblSupplier
(fldsupplierID)
10 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
fldqtyAvailable integer
fldqtyOrdered integer
tblPatron Table
Field Name Data Type Length Key Constraints
fldcustomerNumber integer Primary Key
fldfirstName Varchar 30
fldlastName Varchar 30
fldaddress Varchar 50
fldmobile Varchar 20
tblProductOrder Table
Field Name Data Type Length Key Constraints
fldorderNumber integer Primary Key
fldproductNumber Varchar 10 Primary Key
Foreign Key
references tblProduct
(fldproductNumber)
fldorderDate Datetime
fldquantity integer
fldcustomerNumber integer Foreign Key
references
tblCustomer
(fldcustomerNumber)
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 tblEmployee;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| fldemployeeNumber | int(11) | NO | PRI | | |
| fldfirstName | varchar(30) | NO | | | |
| fldlastName | varchar(30) | NO | | | |
| fldaddress | varchar(50) | NO | | | |
| fldmobile | varchar(20) | NO | | | |
| fldTFN | varchar(20) | NO | | | |
| fldemail | varchar(50) | NO | | | |
| fldjoiningDate | datetime | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
describe tblFullTimeEmployee;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| fldemployeeNumber | int(11) | NO | PRI | | |
| fldannualSalary | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
describe tblCasualEmployee;
+-------------------+---------+------+-----+---------+-------+
13 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| fldemployeeNumber | int(11) | NO | PRI | | |
| fldhourlyRate | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
describe tblDepartment;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| flddepartmentNumber | int(11) | NO | PRI | | |
| fldtitle | varchar(30) | NO | | | |
| fldphone | varchar(20) | NO | | | |
| fldemail | varchar(50) | NO | | | |
| fldsupervisorEmpID | int(11) | NO | MUL | | |
+---------------------+-------------+------+-----+---------+-------+
describe tblStore;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| fldstoreID | int(11) | NO | PRI | | |
| fldstoreName | varchar(40) | NO | | | |
| fldphone | varchar(20) | NO | | | |
| fldemail | varchar(50) | NO | | | |
| fldfax | varchar(30) | NO | | | |
| fldstreetAddress | varchar(50) | NO | | | |
| fldsuburb | varchar(20) | NO | | | |
| fldstate | varchar(15) | NO | | | |
| fldpostCode | varchar(6) | NO | | | |
14 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| fldmanagerID | int(11) | NO | MUL | | |
+------------------+-------------+------+-----+---------+-------+
describe tblStoreEmployee;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| fldstoreID | int(11) | NO | MUL | | |
| fldemployeeNumber | int(11) | NO | PRI | | |
| flddepartmentNumber | int(11) | NO | | | |
+---------------------+---------+------+-----+---------+-------+
describe tblDepartmentStore;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| fldstoreID | int(11) | NO | PRI | | |
| flddepartmentNumber | int(11) | NO | PRI | | |
+---------------------+---------+------+-----+---------+-------+
mysql> describe tblPaySlip;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| fldpaySlipNumber | int(11) | NO | PRI | | |
| fldworkedHours | double | NO | | | |
| fldemployeeNumber | int(11) | NO | MUL | | |
| fldstoreID | int(11) | NO | MUL | | |
| fldgrossPay | double | NO | | | |
15 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+-------------------+---------+------+-----+---------+-------+
describe tblSupplier;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| fldsupplierID | int(11) | NO | PRI | | |
| fldfirstName | varchar(30) | NO | | | |
| fldlastName | varchar(30) | NO | | | |
| fldaddress | varchar(50) | NO | | | |
| fldmobile | varchar(20) | NO | | | |
| fldemail | varchar(50) | NO | | | |
+---------------+-------------+------+-----+---------+-------+
describe tblProduct;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| fldproductNumber | varchar(10) | NO | PRI | | |
| fldtitle | varchar(30) | NO | | | |
| fldbrand | varchar(30) | NO | | | |
| flddescription | varchar(50) | NO | | | |
| fldprice | double | NO | | | |
| fldcategory | varchar(30) | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tblSupplierProduct;
+------------------+-------------+------+-----+---------+-------+
16 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| fldsupplierID | int(11) | NO | PRI | | |
| fldproductNumber | varchar(10) | NO | PRI | | |
| fldqtyAvailable | int(11) | NO | | | |
| fldqtyOrdered | int(11) | NO | | | |
+------------------+-------------+------+-----+---------+-------+
describe tblPatron;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| fldcustomerNumber | int(11) | NO | PRI | | |
| fldfirstName | varchar(30) | NO | | | |
| fldlastName | varchar(30) | NO | | | |
| fldaddress | varchar(50) | NO | | | |
| fldmobile | varchar(20) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
describe tblProductOrder;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| fldorderNumber | int(11) | NO | PRI | | |
| fldproductNumber | varchar(10) | NO | PRI | | |
| fldorderDate | datetime | NO | | | |
| fldquantity | int(11) | NO | | | |
| fldcustomerNumber | int(11) | NO | MUL | | |
+-------------------+-------------+------+-----+---------+-------+
17 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Database Records
select * from tblEmployee;
+-------------------+--------------+-------------+---------------+------------
+-
-----------+------------------+---------------------+
| fldemployeeNumber | fldfirstName | fldlastName | fldaddress | fldmobile
|
fldTFN | fldemail | fldjoiningDate |
+-------------------+--------------+-------------+---------------+------------
+-
-----------+------------------+---------------------+
| 1 | Oliver | Wistle | 34 Link Road | 8347595890
|
9804905956 | [e-mail] | 2016-05-19 00:00:00 |
| 2 | Jesse | White | 14 Robert Av. | 9804905959
|
9804905932 | [e-mail] | 2016-03-24 00:00:00 |
| 3 | Joseph | Lee | 56 White Road | 8943579595
|
9804905956 | [e-mail] | 2015-03-02 00:00:00 |
| 4 | Robert | Wadra | 56 Pink Av. | 8347595895
|
3094580460 | [e-mail] | 2016-04-19 00:00:00 |
| 5 | Peter | Smith | 45 Smith Av. | 7895789585
|
8054809406 | [e-mail] | 2014-08-19 00:00:00 |
| 6 | George | Bush | 75 Smith Av. | 8678878788
|
18 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
8934503580 | [e-mail] | 2014-08-19 00:00:00 |
+-------------------+--------------+-------------+---------------+------------
+-
-----------+------------------+---------------------+
select * from tblFullTimeEmployee;
+-------------------+-----------------+
| fldemployeeNumber | fldannualSalary |
+-------------------+-----------------+
| 1 | 120 |
| 2 | 150 |
| 3 | 150 |
+-------------------+-----------------+
select * from tblCasualEmployee;
+-------------------+---------------+
| fldemployeeNumber | fldhourlyRate |
+-------------------+---------------+
| 4 | 60 |
| 5 | 60 |
| 6 | 80 |
+-------------------+---------------+
mysql> select * from tblDepartment;
+---------------------+----------+-------------+--------------------
+-----------
---------+
| flddepartmentNumber | fldtitle | fldphone | fldemail |
fldsupervi
sorEmpID |
+---------------------+----------+-------------+--------------------
+-----------
---------+
19 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1 | finance | 5668783421 | [e-mail] |
3 |
| 2 | sales | 56675783421 | [e-mail] |
4 |
| 3 | accounts | 5682383421 | [e-mail] |
1 |
| 4 | HR | 5668788921 | [e-mail] |
2 |
+---------------------+----------+-------------+--------------------+--------
select * from tblStore;
+------------+--------------+-------------+---------------+-------------
+-------
-----------+-----------+----------+-------------+--------------+
| fldstoreID | fldstoreName | fldphone | fldemail | fldfax |
fldstr
eetAddress | fldsuburb | fldstate | fldpostCode | fldmanagerID |
+------------+--------------+-------------+---------------+-------------
+-------
-----------+-----------+----------+-------------+--------------+
| 1 | Levis | 5668783421 | [e-mail] | 5668783422 | 34
Geo
rge Road | Bankstown | NSW | 2341 | 1 |
| 2 | Madam | 56675783421 | [e-mail] | 56675783422 | 14
Rob
ert Av. | Chatswood | SA | 5623 | 2 |
| 3 | Smith | 5682383421 | [e-mail] | 5682383422 | 56
Whi
te Road | Bankstown | NSW | 9823 | 3 |
| 4 | Zen | 5668788921 | [e-mail] | 5668788922 | 56
Pin
k Av. | Chatswood | SA | 1234 | 4 |
20 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 5 | Wilson | 5668788897 | [e-mail] | 5668788898 | 45
Geo
rge Av. | Bankstown | NSW | 9845 | 5 |
+------------+--------------+-------------+---------------+-------------+----
select * from tblDepartmentStore;
+------------+---------------------+
| fldstoreID | flddepartmentNumber |
+------------+---------------------+
| 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:
mysql> select * from tblStoreEmployee;
+------------+-------------------+---------------------+
| fldstoreID | fldemployeeNumber | flddepartmentNumber |
+------------+-------------------+---------------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 5 | 3 |
| 4 | 6 | 3 |
| 4 | 4 | 4 |
+------------+-------------------+---------------------+
mysql> select * from tblPaySlip;
+------------------+----------------+-------------------+------------
+----------
---+
| fldpaySlipNumber | fldworkedHours | fldemployeeNumber | fldstoreID |
fldgrossP
ay |
+------------------+----------------+-------------------+------------
+----------
---+
| 1 | 12 | 4 | 4 |
7
20 |
| 2 | 12 | 5 | 5 |
7
20 |
22 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 3 | 15 | 6 | 3 |
4
80 |
+------------------+----------------+-------------------+------------
+----------
---+
select * from tblSupplier;
+---------------+--------------+-------------+--------------+-------------
+-----
--------------+
| fldsupplierID | fldfirstName | fldlastName | fldaddress | fldmobile |
flde
mail |
+---------------+--------------+-------------+--------------+-------------
+-----
--------------+
| 1 | Eli | Jackson | 44 Ring Road | 2398734512 |
eli@
gmail.com |
| 2 | Michael | Wilson | 34 Link Road | 23998094512 |
mich
[e-mail] |
| 3 | Micky | Wilson | 34 Link Road | 2398739822 |
mick
[e-mail] |
+---------------+--------------+-------------+--------------+-------------
+-----
--------------+
23 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
select * from tblProduct;
+------------------+-----------------+----------+-----------------+----------
+--
-----------+
| fldproductNumber | fldtitle | fldbrand | flddescription | fldprice |
f
ldcategory |
+------------------+-----------------+----------+-----------------+----------
+--
-----------+
| P101 | Washing Machine | Lloyd | Washing Machine | 120 |
E
lectronics |
| P102 | Shirt | Levis | Levis Shirt | 5 |
C
loth |
| P103 | T-shirt | Levis | Levis Shirt | 6 |
C
loth |
| P104 | Shampoo | Ole | Ole Shampoo | 8 |
B
eauty |
| P105 | Cream | Ole | Ole Cream | 9 |
B
eauty |
+------------------+-----------------+----------+-----------------+----------
+--
-----------+
24 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
select * from tblSupplierProduct;
+---------------+------------------+-----------------+---------------+
| fldsupplierID | fldproductNumber | fldqtyAvailable | fldqtyOrdered |
+---------------+------------------+-----------------+---------------+
| 1 | P101 | 15 | 10 |
| 2 | P102 | 25 | 15 |
| 2 | P104 | 20 | 10 |
| 3 | P103 | 10 | 5 |
| 3 | P105 | 20 | 10 |
+---------------+------------------+-----------------+---------------+
mysql> select * from tblPatron;
+-------------------+--------------+-------------+----------------
+------------+
| fldcustomerNumber | fldfirstName | fldlastName | fldaddress | fldmobile
|
+-------------------+--------------+-------------+----------------
+------------+
| 1 | Harry | Wilson | 14 Robert Av. | 5366378338
|
| 2 | Finn | Brown | 34 George Av. | 5678234167
|
| 3 | Max | Lee | 32 Smith Rd. | 8934579858
|
25 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 4 | Jack | Kevin | 889 Walker Rd. | 4850958959
|
| 5 | Nick | White | 99 Church Rd. | 5909606786
|
+-------------------+--------------+-------------+----------------
+------------+
mysql> select * from tblProductOrder;
+----------------+------------------+---------------------+-------------+-------
------------+
| fldorderNumber | fldproductNumber | fldorderDate | fldquantity | fldcus
tomerNumber |
+----------------+------------------+---------------------+-------------+-------
------------+
| 1001 | P101 | 2017-06-30 00:00:00 | 1 |
1 |
| 1002 | P105 | 2017-09-12 00:00:00 | 2 |
2 |
| 1003 | P102 | 2018-07-11 00:00:00 | 1 |
1 |
| 1004 | P103 | 2017-12-12 00:00:00 | 3 |
3 |
| 1004 | P104 | 2017-12-12 00:00:00 | 3 |
3 |
| 1005 | P104 | 2017-10-15 00:00:00 | 4 |
26 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
4 |
| 1005 | P105 | 2017-10-15 00:00:00 | 4 |
4 |
Part D: SQL
Question 1
select fldcustomerNumber, concat(fldfirstName,' ',fldlastName) 'Customer
Full Name', fldmobile, fldaddress from tblPatron order by fldcustomerNumber
desc
;
+-------------------+--------------------+------------+----------------+
| fldcustomerNumber | Customer Full Name | fldmobile | fldaddress |
+-------------------+--------------------+------------+----------------+
| 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
mysql> select * from tblProduct where fldprice<100;
+------------------+----------+----------+----------------+----------
+----------
---+
| fldproductNumber | fldtitle | fldbrand | flddescription | fldprice |
fldcatego
ry |
+------------------+----------+----------+----------------+----------
+----------
27 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
---+
| P102 | Shirt | Levis | Levis Shirt | 5 | Cloth
|
| P103 | T-shirt | Levis | Levis Shirt | 6 | Cloth
|
| P104 | Shampoo | Ole | Ole Shampoo | 8 | Beauty
|
| P105 | Cream | Ole | Ole Cream | 9 | Beauty
|
+------------------+----------+----------+----------------+----------+-------
Question 3
select tblProductOrder.fldorderNumber, tblProductOrder.fldproductNumber,
tblProduct.fldtitle, tblProductOrder.fldquantity, tblProduct.fldprice,
(tblProdu
ctOrder.fldquantity * tblProduct.fldprice) as TotalAmount from
tblProductOrder
inner join tblProduct on
tblProduct.fldproductNumber=tblProductOrder.fldproductN
umber where fldorderNumber=1005;
+----------------+------------------+----------+-------------+----------
+-------
------+
| fldorderNumber | fldproductNumber | fldtitle | fldquantity | fldprice |
TotalAmount |
+----------------+------------------+----------+-------------+----------
+-------
------+
| 1005 | P104 | Shampoo | 4 | 8 |
32 |
| 1005 | P105 | Cream | 4 | 9 |
28 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
36 |
+----------------+------------------+----------+-------------+----------
+-------
Question 4
select fldstoreName, concat(fldstreetAddress,' ',fldsuburb, ' ', fldstate
, ' ' ,fldpostcode) as Address, fldfax from tblStore where fldstreetAddress
like
'%George%';
+--------------+-----------------------------------+------------+
| fldstoreName | Address | fldfax |
+--------------+-----------------------------------+------------+
| Levis | 34 George Road Bankstown NSW 2341 | 5668783422 |
| Wilson | 45 George Av. Bankstown NSW 9845 | 5668788898 |
+--------------+-----------------------------------+------------+
Question 5
select * from tblProductOrder where fldorderNumber in (select fldorderNum
ber from tblProductOrder group by fldorderNumber having
count(fldproductNumber)>
1);
+----------------+------------------+---------------------+-------------
+-------
------------+
| fldorderNumber | fldproductNumber | fldorderDate | fldquantity |
fldcus
tomerNumber |
+----------------+------------------+---------------------+-------------
+-------
------------+
29 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1004 | P103 | 2017-12-12 00:00:00 | 3 |
3 |
| 1004 | P104 | 2017-12-12 00:00:00 | 3 |
3 |
| 1005 | P104 | 2017-10-15 00:00:00 | 4 |
4 |
| 1005 | P105 | 2017-10-15 00:00:00 | 4 |
4 |
+----------------+------------------+---------------------+-------------
+-------
------------+
Question 6
select fldcustomerNumber, concat(fldfirstName,' ',fldlastName) 'Customer
Name', fldaddress from tblPatron where fldcustomerNumber not in (select
fldcusto
merNumber from tblProductOrder);
+-------------------+---------------+---------------+
| fldcustomerNumber | Customer Name | fldaddress |
+-------------------+---------------+---------------+
| 5 | Nick White | 99 Church Rd. |
+-------------------+---------------+---------------+
Question 7
select tblEmployee.fldemployeeNumber, fldfirstName, fldlastName, fldstore
Name, fldtitle as DepartmentName from tblEmployee inner join tblStoreEmployee
o
n tblEmployee.fldemployeeNumber=tblStoreEmployee.fldemployeeNumber inner join
tb
lStore on tblStoreEmployee.fldstoreID=tblStore.fldstoreID inner join
tblDepartme
nt on tblstoreEmployee.flddepartmentNumber=tblDepartment.flddepartmentNumber;
30 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+-------------------+--------------+-------------+--------------
+---------------
-+
| fldemployeeNumber | fldfirstName | fldlastName | fldstoreName |
DepartmentName
|
+-------------------+--------------+-------------+--------------
+---------------
-+
| 1 | Oliver | Wistle | Levis | finance
|
| 2 | Jesse | White | Madam | sales
|
| 3 | Joseph | Lee | Smith | accounts
|
| 5 | Peter | Smith | Zen | accounts
|
| 6 | George | Bush | Zen | accounts
|
| 4 | Robert | Wadra | Zen | HR
|
+-------------------+--------------+-------------+--------------
+---------------
Question 8
select fldstoreName, count(tblStoreEmployee.fldemployeeNumber) NumberOfE
ployees from tblStore inner join tblStoreEmployee on
tblStore.fldstoreID=tblSto
eEmployee.fldstoreID inner join tblDepartment on
tblStoreEmployee.flddepartment
umber=tblDepartment.flddepartmentNumber group by fldstoreName, fldtitle having
31 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ldtitle='accounts';
+--------------+-------------------+
| fldstoreName | NumberOfEmployees |
+--------------+-------------------+
| Smith | 1 |
| Zen | 2 |
+--------------+-------------------+
Question 9
select * from tblProductOrder where fldorderDate>'2017-06-30' and fldorde
rDate< '2018-07-01';
+----------------+------------------+---------------------+-------------
+-------
------------+
| fldorderNumber | fldproductNumber | fldorderDate | fldquantity |
fldcus
tomerNumber |
+----------------+------------------+---------------------+-------------
+-------
------------+
| 1002 | P105 | 2017-09-12 00:00:00 | 2 |
2 |
| 1004 | P103 | 2017-12-12 00:00:00 | 3 |
3 |
| 1004 | P104 | 2017-12-12 00:00:00 | 3 |
3 |
| 1005 | P104 | 2017-10-15 00:00:00 | 4 |
4 |
| 1005 | P105 | 2017-10-15 00:00:00 | 4 |
4 |
32 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+----------------+------------------+---------------------+-------------
+-------
------------+
Question 10
select fldcustomerNumber, count(fldorderNumber) NumberOfOrders from tblPr
oductOrder group by fldcustomerNumber;
+-------------------+----------------+
| fldcustomerNumber | NumberOfOrders |
+-------------------+----------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+-------------------+----------------+
Question 11
select fldorderNumber, fldorderDate, count(fldproductNumber) as NumberOfP
roducts from tblProductOrder group by fldorderNumber, fldorderDate;
+----------------+---------------------+------------------+
| fldorderNumber | fldorderDate | NumberOfProducts |
+----------------+---------------------+------------------+
| 1001 | 2017-06-30 00:00:00 | 1 |
| 1002 | 2017-09-12 00:00:00 | 1 |
| 1003 | 2018-07-11 00:00:00 | 1 |
| 1004 | 2017-12-12 00:00:00 | 2 |
| 1005 | 2017-10-15 00:00:00 | 2 |
+----------------+---------------------+------------------+
33 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
References
Study.com (2019). What is Normal Form in DBMS? - Types & Examples. [Online]. Available:
https://study.com/academy/lesson/what-is-normal-form-in-dbms-types-examples.html.
[Accessed: 1-June-2019]
The Crazy Programmer (2019). Normalization in DBMS – 1NF, 2NF, 3NF and BCNF. [Online].
Available: https://www.thecrazyprogrammer.com/2017/06/normalization-in-dbms.html.
[Accessed: 1-June-2019]
BeginnersBook. (2019). Entity Relationship Diagram – ER Diagram in DBMS. [Online].
Available: https://beginnersbook.com/2015/04/e-r-model-in-dbms/. [Accessed: 1-June-2019]
TechTarget (2019). Database normalization in MySQL: Four quick and easy steps. [Online].
Available: https://www.computerweekly.com/tutorial/Database-normalization-in-MySQL-Four-
quick-and-easy-steps. [Accessed: 1-June-2019]
34 ISY1002/ISY103 Database Management for Business