Database Management for Business

This document provides a comprehensive guide to database management for business. It includes an entity relationship diagram, normalization techniques, relational schema, MySQL database, and more. The content covers various topics such as stores, departments, employees, pay slips, products, and patrons in a business setting.

Database Management for Business
May 30
2019
Student ID:
Student Name: ISY1002/ISY103

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........................................................................................6
Relational Schema...................................................................................................................................6
MySQL Database..................................................................................................................................11
Database Tables.................................................................................................................................12
Records in MySQL Database................................................................................................................17
Part D: Relational Schema and MySQL database......................................................................................25
References.................................................................................................................................................31
2 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Report
Part A: Entity Relationship Diagram
Case Study Requirements
1. The complete detail of all stores in AMC will be stored into the database e.g. name,
address, phone etc.
2. The complete detail of all departments in AMC will be stored into the database.
3. The complete detail of all employees will be stored into the database e.g. name, address,
phone etc.
4. A store may have any number of employees.
5. Each store will have the manager.
6. The manager will be one of the employees.
7. Each department will have the supervisor.
8. The supervisor will be one of the employees.
9. The pay slip detail will be stored into the database.
10. The product detail will be stored into the database.
11. The inventory of all products will be stored into the database.
12. The complete detail of all patrons in AMC will be stored into the database e.g. name,
address, phone etc.
13. The complete detail of orders by patrons will be stored into the database e.g. order date,
ordered product, quantity etc.
14. An employee will be employed in one store only.
3 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ER Diagram
(Cinergix Pty Ltd. 2011)
4 ISY1002/ISY103 Database Management for Business

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

Group Members ID:
Group Members Name:
All relations are in third normal form because all are following the rules of third normal form-
- All the tables do not have any repeated group.
- No table has partial dependency i.e. all fields depend upon the primary key only.
- No table has transitive dependency i.e. no field depend upon any other field except
primary key.
(Rouse M. n.d.)
(Dimitri Fontaine 2019)
(Perucci D. n.d.)
Part C: Relational Schema and MySQL database
Relational Schema
Employee Table
Field Name Data Type Length Key Constraints
employeeNumber integer Primary Key
firstName Varchar 20
lastName Varchar 20
address Varchar 50
mobile Varchar 20
Email Varchar 50
TFN Varchar 20
joiningDate Datetime
Fulltime Table
Field Name Data Type Length Key Constraints
employeeNumber integer Primary Key
Foreign Key
references Employee
(employeeNumber)
annualSalary double
Casual Table
Field Name Data Type Length Key Constraints
6 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
employeeNumber integer Primary Key
Foreign Key
references Employee
(employeeNumber)
hourlyRate double
Store Table
Field Name Data Type Length Key Constraints
storeID integer Primary Key
storeName Varchar 40
Phone Varchar 20
Email Varchar 50
Fax Varchar 20
streetAddress Varchar 50
suburb Varchar 20
state Varchar 20
postcode Varchar 6
managerID integer Foreign Key
references Employee
(employeeNumber)
Department Table
Field Name Data Type Length Key Constraints
departmentNumber Integer Primary Key
Title Varchar 20
Phone Varchar 20
Email Varchar 50
supervisorEmpID Integer Foreign Key
references Employee
(employeeNumber)
DepartmentStore Table
Field Name Data Type Length Key Constraints
departmentNumber integer Primary Key
Foreign Key
references
Department
(departmentNumber)
7 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
storeID integer Primary Key
Foreign Key
references Store
(storeID)
StoreEmployee Table
Field Name Data Type Length Key Constraints
EmployeeNumber integer Primary Key
Foreign Key
references Employee
(employeeNumber)
storeID integer Foreign Key
references
DepartmentStore
(storeID)
departmentNumber Integer Foreign Key
references
DepartmentStore
(storeID)
Payslip Table
Field Name Data Type Length Key Constraints
paySlipNumber integer Primary Key
workedHours double
employeeNumber integer Foreign Key
references Employee
(employeeNumber)
storeID integer Foreign Key
references Store
(storeID)
grossPay double
Supplier Table
Field Name Data Type Length Key Constraints
supplierID integer Primary Key
firstName Varchar 20
lastName Varchar 20
address Varchar 50
mobile Varchar 20
8 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Email Varchar 50
Product Table
Field Name Data Type Length Key Constraints
productNumber Varchar 10 Primary Key
title Varchar 20
brand Varchar 20
description Varchar 50
price double
category Varchar 30
SupplierProduct Table
Field Name Data Type Length Key Constraints
productNumber Varchar 10 Primary Key
Foreign Key
references product
(productNumber)
supplierID integer Primary Key
Foreign Key
references supplier
(supplierID)
qtyAvailable integer
qtyOrdered integer
Patron Table
Field Name Data Type Length Key Constraints
customerNumber integer Primary Key
firstName Varchar 20
lastName Varchar 20
address Varchar 50
mobile Varchar 20
9 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
ProductOrder Table
Field Name Data Type Length Key Constraints
orderNumber integer Primary Key
productNumber Varchar 10 Primary Key
Foreign Key
references product
(productNumber)
orderDate Datetime
quantity integer
customerNumber integer Foreign Key
references customer
(customerNumber)
(Holowczak.com n.d.)
(Refsnes Data. 2019)
10 ISY1002/ISY103 Database Management for Business

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

Group Members ID:
Group Members Name:
Database Tables
Describe employee;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | | |
| firstName | varchar(20) | NO | | | |
| lastName | varchar(20) | NO | | | |
| address | varchar(50) | NO | | | |
| mobile | varchar(20) | NO | | | |
| TFN | varchar(20) | NO | | | |
| email | varchar(50) | NO | | | |
| joiningDate | datetime | NO | | | |
+----------------+-------------+------+-----+---------+-------+
Describe fullTime;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | | |
| annualSalary | double | NO | | | |
+----------------+---------+------+-----+---------+-------+
Describe casual;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
12 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+----------------+---------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | | |
| hourlyRate | double | NO | | | |
+----------------+---------+------+-----+---------+-------+
Describe department;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| departmentNumber | int(11) | NO | PRI | | |
| title | varchar(20) | NO | | | |
| phone | varchar(20) | NO | | | |
| email | varchar(50) | NO | | | |
| supervisorEmpID | int(11) | NO | MUL | | |
+------------------+-------------+------+-----+---------+-------+
Describe store;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| storeID | int(11) | NO | PRI | | |
| storeName | varchar(40) | NO | | | |
| phone | varchar(20) | NO | | | |
| email | varchar(50) | NO | | | |
| fax | varchar(20) | NO | | | |
| streetAddress | varchar(50) | NO | | | |
| suburb | varchar(20) | NO | | | |
| state | varchar(20) | NO | | | |
| postCode | varchar(6) | NO | | | |
| managerID | int(11) | NO | MUL | | |
13 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+---------------+-------------+------+-----+---------+-------+
Describe storeEmployee;
+------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| storeID | int(11) | NO | MUL | | |
| employeeNumber | int(11) | NO | PRI | | |
| departmentNumber | int(11) | NO | | | |
+------------------+---------+------+-----+---------+-------+
Describe departmentStore;
+------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| storeID | int(11) | NO | PRI | | |
| departmentNumber | int(11) | NO | PRI | | |
+------------------+---------+------+-----+---------+-------+
Describe paySlip;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| paySlipNumber | int(11) | NO | PRI | | |
| workedHours | double | NO | | | |
| employeeNumber | int(11) | NO | MUL | | |
| storeID | int(11) | NO | MUL | | |
| grossPay | double | NO | | | |
+----------------+---------+------+-----+---------+-------+
Describe supplier;
+------------+-------------+------+-----+---------+-------+
14 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| supplierID | int(11) | NO | PRI | | |
| firstName | varchar(20) | NO | | | |
| lastName | varchar(20) | NO | | | |
| address | varchar(50) | NO | | | |
| mobile | varchar(20) | NO | | | |
| email | varchar(50) | NO | | | |
+------------+-------------+------+-----+---------+-------+
Describe product;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| productNumber | varchar(10) | NO | PRI | | |
| title | varchar(20) | NO | | | |
| brand | varchar(20) | NO | | | |
| description | varchar(50) | NO | | | |
| price | double | NO | | | |
| category | varchar(30) | NO | | | |
+---------------+-------------+------+-----+---------+-------+
Describe supplierProduct;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| supplierID | int(11) | NO | PRI | | |
| productNumber | varchar(10) | NO | PRI | | |
| qtyAvailable | int(11) | NO | | | |
| qtyOrdered | int(11) | NO | | | |
15 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+---------------+-------------+------+-----+---------+-------+
Describe patron;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | | |
| firstName | varchar(20) | NO | | | |
| lastName | varchar(20) | NO | | | |
| address | varchar(50) | NO | | | |
| mobile | varchar(20) | NO | | | |
+----------------+-------------+------+-----+---------+-------+
Describe productOrder;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int(11) | NO | PRI | | |
| productNumber | varchar(10) | NO | PRI | | |
| orderDate | datetime | NO | | | |
| quantity | int(11) | NO | | | |
| customerNumber | int(11) | NO | MUL | | |
+----------------+-------------+------+-----+---------+-------+
16 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Records in MySQL Database
SELECT * FROM employee;
+----------------+-----------+----------+----------------+------------
+---------
---+----------------+---------------------+
| employeeNumber | firstName | lastName | address | mobile | TFN
| email | joiningDate |
+----------------+-----------+----------+----------------+------------
+---------
---+----------------+---------------------+
| 1001 | John | Smith | 101 Corol Rd. | 5647281342 |
56347892
34 | [e-mail] | 2017-06-29 00:00:00 |
| 1002 | Nick | Jonas | 12 George Rd. | 7343587575 |
87637855
75 | [e-mail] | 2016-05-21 00:00:00 |
| 1003 | Max | White | 105 Church Rd. | 8398495855 |
34987958
58 | [e-mail] | 2019-01-21 00:00:00 |
| 1004 | Noah | Brown | 131 Joseph Rd. | 8938489585 |
34959858
58 | [e-mail] | 2016-01-29 00:00:00 |
| 1005 | Rose | Kelvin | 101 George Rd. | 7365785785 |
35709585
85 | [e-mail] | 2015-06-19 00:00:00 |
+----------------+-----------+----------+----------------+------------
+---------
---+----------------+---------------------+
SELECT * FROM fullTime;
+----------------+--------------+
| employeeNumber | annualSalary |
17 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+----------------+--------------+
| 1001 | 100 |
| 1002 | 120 |
| 1003 | 110 |
+----------------+--------------+
SELECT * FROM casual;
+----------------+------------+
| employeeNumber | hourlyRate |
+----------------+------------+
| 1004 | 50 |
| 1005 | 60 |
+----------------+------------+
SELECT * FROM department;
+------------------+----------+------------+------------------
+-----------------
+
| departmentNumber | title | phone | email |
supervisorEmpID
|
+------------------+----------+------------+------------------
+-----------------
+
| 1 | accounts | 4523178234 | [e-mail] |
1001
|
| 2 | HR | 7478477427 | [e-mail] |
1002
|
| 3 | finance | 637636736 | [e-mail] |
1003
18 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
|
| 4 | sales | 363737736 | [e-mail] |
1004
|
+------------------+----------+------------+------------------
+-----------------
SELECT * FROM store;
+---------+-----------+------------+------------------+------------
+------------
----+-----------+------------+----------+-----------+
| storeID | storeName | phone | email | fax |
streetAddre
ss | suburb | state | postCode | managerID |
+---------+-----------+------------+------------------+------------
+------------
----+-----------+------------+----------+-----------+
| 1 | George | 4564666456 | [e-mail] | 5678912897 | 101
George
Rd. | Bankstown | NSW | 7865 | 1001 |
| 2 | Joseph | 3546686354 | [e-mail] | 4580812389 | 101
George
Rd. | Bankstown | SA | 6754 | 1002 |
| 3 | City | 5698734298 | [e-mail] | 7864538675 | 55 Church
R
d. | Manly | NSW | 8965 | 1003 |
| 4 | Max | 5643298756 | [e-mail] | 2348798675 | 11 Joseph
R
d. | Cronulla | Queensland | 4532 | 1004 |
| 5 | Corrol | 3450986753 | [e-mail] | 3452387645 | 161
Corrol
Rd. | Chatswood | SA | 2343 | 1005 |
+---------+-----------+------------+------------------+------------
+------------
19 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
----+-----------+------------+----------+-----------+
SELECT * FROM departmentStore;
+---------+------------------+
| storeID | departmentNumber |
+---------+------------------+
| 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 |
+---------+------------------+
SELECT * FROM storeEmployee;
+---------+----------------+------------------+
| storeID | employeeNumber | departmentNumber |
+---------+----------------+------------------+
| 1 | 1001 | 1 |
| 2 | 1002 | 2 |
20 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 3 | 1003 | 3 |
| 4 | 1004 | 4 |
| 4 | 1005 | 4 |
+---------+----------------+------------------+
SELECT * FROM paySlip;
+---------------+-------------+----------------+---------+----------+
| paySlipNumber | workedHours | employeeNumber | storeID | grossPay |
+---------------+-------------+----------------+---------+----------+
| 1 | 10 | 1004 | 4 | 500 |
| 2 | 10 | 1005 | 5 | 600 |
| 3 | 10 | 1003 | 3 | 300 |
+---------------+-------------+----------------+---------+----------+
SELECT * FROM supplier;
+------------+-----------+----------+---------------+-------------
+-------------
-----+
| supplierID | firstName | lastName | address | mobile | email
|
+------------+-----------+----------+---------------+-------------
+-------------
-----+
| 1 | Oliver | Bush | 101 Corol Rd. | 5647281342 |
oliver@gmail
.com |
| 2 | Jack | Klinton | 55 Church Rd. | 8935795955 |
jack@gmail.c
om |
| 3 | Neel | Smith | 11 Joseph Rd. | 94598598555 |
neel@gmail.c
om |
21 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
+------------+-----------+----------+---------------+-------------
+-------------
-----+
SELECT * FROM product;
+---------------+--------------+---------+------------------+-------
+-----------
--+
| productNumber | title | brand | description | price | category
|
+---------------+--------------+---------+------------------+-------
+-----------
--+
| P1 | Refrigerator | Samsung | Refrigerator | 110 |
Electronic
s |
| P2 | Jacket | Levis | Levis Jacket | 10 | Cloth
|
| P3 | Jeans | Levis | Levis Jeans | 5 | Cloth
|
| P4 | Face Wash | Loreal | Loreal Face Wash | 3 | Beauty
|
| P5 | Body Wash | Loreal | Loreal Body Wash | 4 | Beauty
|
+---------------+--------------+---------+------------------+-------
+-----------
--+
SELECT * FROM supplierProduct;
+------------+---------------+--------------+------------+
| supplierID | productNumber | qtyAvailable | qtyOrdered |
+------------+---------------+--------------+------------+
22 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1 | P1 | 1 | 10 |
| 2 | P2 | 2 | 20 |
| 2 | P4 | 2 | 20 |
| 3 | P3 | 1 | 20 |
| 3 | P5 | 2 | 20 |
+------------+---------------+--------------+------------+
SELECT * FROM patron;
+----------------+-----------+----------+---------------+------------+
| customerNumber | firstName | lastName | address | mobile |
+----------------+-----------+----------+---------------+------------+
| 1001 | Harley | Robert | 101 George Rd | 5366378338 |
| 1002 | Jesse | White | 11 Church Rd | 6532767464 |
| 1003 | Spencer | Wilson | 12 Ring Rd | 6487478784 |
| 1004 | Marcus | Brown | 15 Smith Rd | 7984747747 |
| 1005 | Felix | Smith | 101 George Rd | 7236874247 |
+----------------+-----------+----------+---------------+------------+
SELECT * FROM productOrder;
+-------------+---------------+---------------------+----------
+----------------
+
| orderNumber | productNumber | orderDate | quantity |
customerNumber
|
+-------------+---------------+---------------------+----------
+----------------
+
| 1001 | P1 | 2017-07-01 00:00:00 | 1 |
1001
|
23 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1002 | P5 | 2017-08-11 00:00:00 | 2 |
1002
|
| 1003 | P2 | 2018-06-01 00:00:00 | 1 |
1001
|
| 1004 | P3 | 2017-11-11 00:00:00 | 3 |
1003
|
| 1004 | P4 | 2017-11-11 00:00:00 | 3 |
1003
|
| 1005 | P4 | 2017-09-05 00:00:00 | 4 |
1003
|
+-------------+---------------+---------------------+----------
+----------------
24 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Part D: Relational Schema and MySQL database
Question 1
select customerNumber, concat(firstName,' ',lastName) 'Customer Full Name
', mobile, address from Patron order by customerNumber desc;
+----------------+--------------------+------------+---------------+
| customerNumber | Customer Full Name | mobile | address |
+----------------+--------------------+------------+---------------+
| 1005 | Felix Smith | 7236874247 | 101 George Rd |
| 1004 | Marcus Brown | 7984747747 | 15 Smith Rd |
| 1003 | Spencer Wilson | 6487478784 | 12 Ring Rd |
| 1002 | Jesse White | 6532767464 | 11 Church Rd |
| 1001 | Harley Robert | 5366378338 | 101 George Rd |
+----------------+--------------------+------------+---------------+
Question 2
select * from Product where price<100;
+---------------+-----------+--------+------------------+-------+----------+
| productNumber | title | brand | description | price | category |
+---------------+-----------+--------+------------------+-------+----------+
| P2 | Jacket | Levis | Levis Jacket | 10 | Cloth |
| P3 | Jeans | Levis | Levis Jeans | 5 | Cloth |
| P4 | Face Wash | Loreal | Loreal Face Wash | 3 | Beauty |
| P5 | Body Wash | Loreal | Loreal Body Wash | 4 | Beauty |
+---------------+-----------+--------+------------------+-------+----------+
Question 3
select ord.orderNumber, ord.productNumber, prod.title, ord.quantity, prod
.price, (ord.quantity * prod.price) as TotalAmount from productOrder ord
inner
25 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
join product prod on prod.productNumber=ord.productNumber where
orderNumber=1005
;
+-------------+---------------+-----------+----------+-------+-------------+
| orderNumber | productNumber | title | quantity | price | TotalAmount |
+-------------+---------------+-----------+----------+-------+-------------+
| 1005 | P4 | Face Wash | 4 | 3 | 12 |
+-------------+---------------+-----------+----------+-------+-------------+
Question 4
select storeName, concat(streetAddress,' ',suburb, ' ', state, ' ' ,postc
ode) 'Address', fax from store where streetAddress like '%George%';
+-----------+-----------------------------------+------------+
| storeName | Address | fax |
+-----------+-----------------------------------+------------+
| George | 101 George Rd. Bankstown NSW 7865 | 5678912897 |
| Joseph | 101 George Rd. Bankstown SA 6754 | 4580812389 |
+-----------+-----------------------------------+------------+
Question 5
select * from productOrder where orderNumber in (select orderNumber from
productOrder group by orderNumber having count(productNumber)>1);
+-------------+---------------+---------------------+----------
+----------------
+
| orderNumber | productNumber | orderDate | quantity |
customerNumber
|
+-------------+---------------+---------------------+----------
+----------------
+
26 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1004 | P3 | 2017-11-11 00:00:00 | 3 |
1003
|
| 1004 | P4 | 2017-11-11 00:00:00 | 3 |
1003
|
+-------------+---------------+---------------------+----------
+----------------
Question 6
select customerNumber, concat(firstName,' ',lastName) 'Customer Full Name
', address from Patron where customerNumber not in (select customerNumber from
productOrder);
+----------------+--------------------+---------------+
| customerNumber | Customer Full Name | address |
+----------------+--------------------+---------------+
| 1004 | Marcus Brown | 15 Smith Rd |
| 1005 | Felix Smith | 101 George Rd |
+----------------+--------------------+---------------+
Question 7
select Employee.employeeNumber, firstName, lastName, storeName, title as
DepartmentName from Employee inner join storeEmployee on
Employee.employeeNumbe
r=storeEmployee.employeeNumber inner join Store on
storeEmployee.storeID=Store.S
toreID inner join Department on
storeEmployee.departmentNumber=Department.depart
mentNumber;
+----------------+-----------+----------+-----------+----------------+
| employeeNumber | firstName | lastName | storeName | DepartmentName |
+----------------+-----------+----------+-----------+----------------+
| 1001 | John | Smith | George | accounts |
27 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1002 | Nick | Jonas | Joseph | HR |
| 1003 | Max | White | City | finance |
| 1004 | Noah | Brown | Max | sales |
| 1005 | Rose | Kelvin | Max | sales |
+----------------+-----------+----------+-----------+----------------+
Question 8
select storeName, count(StoreEmployee.employeeNumber) 'Number of Employee
s' from Store inner join StoreEmployee on Store.storeID=StoreEmployee.storeID
in
ner join department on
StoreEmployee.departmentNumber=Department.departmentNumbe
r group by storeName, title having title='accounts';
+-----------+---------------------+
| storeName | Number of Employees |
+-----------+---------------------+
| George | 1 |
+-----------+---------------------+
Question 9
select * from productOrder where orderDate>'2017-06-30' and orderDate< '2
018-07-01';
+-------------+---------------+---------------------+----------
+----------------
+
| orderNumber | productNumber | orderDate | quantity |
customerNumber
|
+-------------+---------------+---------------------+----------
+----------------
+
28 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
| 1001 | P1 | 2017-07-01 00:00:00 | 1 |
1001
|
| 1002 | P5 | 2017-08-11 00:00:00 | 2 |
1002
|
| 1003 | P2 | 2018-06-01 00:00:00 | 1 |
1001
|
| 1004 | P3 | 2017-11-11 00:00:00 | 3 |
1003
|
| 1004 | P4 | 2017-11-11 00:00:00 | 3 |
1003
|
| 1005 | P4 | 2017-09-05 00:00:00 | 4 |
1003
|
+-------------+---------------+---------------------+----------
+----------------
+
Question 10
select customerNumber, count(orderNumber) 'Number of Orders' from product
Order group by customerNumber;
+----------------+------------------+
| customerNumber | Number of Orders |
+----------------+------------------+
| 1001 | 2 |
| 1002 | 1 |
| 1003 | 3 |
+----------------+------------------+
29 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
Question 11
select orderNumber, orderDate, count(productNumber) as 'Number of Product
s' from productOrder group by orderNumber, orderDate;
+-------------+---------------------+--------------------+
| orderNumber | orderDate | Number of Products |
+-------------+---------------------+--------------------+
| 1001 | 2017-07-01 00:00:00 | 1 |
| 1002 | 2017-08-11 00:00:00 | 1 |
| 1003 | 2018-06-01 00:00:00 | 1 |
| 1004 | 2017-11-11 00:00:00 | 2 |
| 1005 | 2017-09-05 00:00:00 | 1 |
+-------------+---------------------+--------------------+
30 ISY1002/ISY103 Database Management for Business

Group Members ID:
Group Members Name:
References
Rouse M. (n.d.). Database Normalization. Online. Available:
https://searchsqlserver.techtarget.com/definition/normalization. [Accessed: 30-May-2019]
Dimitri Fontaine (2019). Database Normalization and Primary Keys. Online. Available:
https://tapoueh.org/blog/2018/03/database-normalization-and-primary-keys/. [Accessed: 30-
May-2019]
Holowczak.com (n.d.). Database Normalization. [Online]. Available:
http://holowczak.com/database-normalization/. [Accessed: 30-May-2019]
Perucci D. (n.d.). Pros and Cons of Database Normalization. Online. Available:
https://dzone.com/articles/pros-and-cons-of-database-normalization. [Accessed: 30-May-2019]
Cinergix Pty Ltd. (2011). Ultimate Guide to ER Diagrams, [Online].
Available:http://creately.com/blog/diagrams/er-diagrams-tutorial/. [Accessed: 30-May-2019]
Refsnes Data. (2019). SQL Data Types for MySQL, SQL Server, and MS Access. [Online].
Available: https://www.w3schools.com/sql/sql_datatypes.asp. [Accessed: 30-May-2019]
31 ISY1002/ISY103 Database Management for Business