Database Project Report and Presentation

This document provides a complete database project report and presentation for the assessment. It includes an entity relationship diagram, normalization, relational schema, MySQL database, and more. The assignment's main objective is to design and develop the database system of AMC. The logical and physical designs are made in the assignment.

[Database project, report and presentation]
Assessment 3
2019
Student ID:
Student Name:

Group Member ID:
Group Member Name:
Report
Contents
Report..........................................................................................................................................................2
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
Records..................................................................................................................................................16
Referential Integrity Constraint.............................................................................................................22
Part D: SQL...............................................................................................................................................27
References.................................................................................................................................................33
Database Management for Business ISY1002/ISY103 Page 2

Group Member ID:
Group Member Name:
Mission
The assignment’s main objective is to design and develop the database system of AMC. The
logical and physical both designs are made in the assignment. The logical design is used to show
the system requirement while the physical database is implemented on the basis of logical
design. The assignment is showing the database requirements by ER diagram and the database is
implemented in MYSQL database.
Part A: Entity Relationship Diagram
Case Study Requirements
1. There are lots of employees who work in one store.
2. One manager will be appointed for one store from the employees of the store only.
3. One supervisor will be appointed for one department from the employees of the
department only.
4. An employee can work for only one store.
5. The inventory of products will have to be maintained on regular basis.
6. The product details will be saved into the database.
7. The customer details will be saved into the database.
8. The supplier details will be saved into the database.
9. The store details will be saved into the database.
Database Management for Business ISY1002/ISY103 Page 3

Group Member ID:
Group Member Name:
ER Diagram
(Tutorialcup 2015)
(Ziff Davis 2017)
(Smartdraw 2016)
Database Management for Business ISY1002/ISY103 Page 4

Group Member ID:
Group Member Name:
Part B: Normalization
The database is in third normal form if the following rules are applied on the database-
- All tables have primary key and no repeated group.
- All fields in every table depend upon the primary key.
- No table has transitive dependency.
TbllEmployee (flldemployeeNumber, flldfirstName, flldlastName, flldaddress, flldmobile,
flldemail, flldTFN, flldjoiningDate)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllfullTimeEmployee (flldemployeeNumber, flldannualSalary)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllcasualEmployee (flldemployeeNumber, flldhourlyRate)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllDepartment (fllddepartmentNumber, flldtitle, flldphone, flldemail,
flldsupervisorEmpID)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllStore (flldstoreID, flldstoreName, flldphone, flldemail, flldfax, flldstreetAddress,
flldsuburb, flldstate, flldpostcode, flldmanagerID)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllDepartmentStore (flldstoreID, fllddepartmentNumber)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllStoreEmployee (flldemployeeNumber , flldstoreID, fllddepartmentNumber)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
Database Management for Business ISY1002/ISY103 Page 5

Group Member ID:
Group Member Name:
TbllCategory (flldcategory, fllddescription)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllProduct (flldproductNumber, flldtitle, flldbrand, fllddescription, flldprice,
flldcategory)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllSupplier (flldsupplierID, flldfirstName, flldlastName, flldaddress, flldmobile, flldemail)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllSupplierProduct (flldsupplierID, flldproductNumber, flldqtyAvailable,
flldqtyOrdered)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllPatron (flldcustomerNumber, flldfirstName, flldlastName, flldaddress, flldmobile)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllOrder (flldorderNumber, flldproductNumber, flldorderDate, flldquantity,
flldcustomerNumber)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
TbllPaySlip (flldpayslipNumber, flldworkedHours, flldemployeeNumber, flldstoried,
flldgrossPay)
The table is in third normal form because it is satisfying rules of third normal form as mentioned
above.
Database Management for Business ISY1002/ISY103 Page 6

Group Member ID:
Group Member Name:
Part C: Relational Schema and MySQL database
Relational Schema
TbllEmployee Table
Field Name Data Type Length Key Constraints
flldemployeeNumber integer Primary Key
flldfirstName Varchar 20
flldlastName Varchar 20
flldaddress Varchar 40
flldmobile Varchar 20
flldEmail Varchar 30
flldTFN Varchar 20
flldjoiningDate Datetime
TbllFulltimeEmployee Table
Field Name Data Type Length Key Constraints
flldemployeeNumber integer Primary Key
Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldannualSalary double
TbllCasualEmployee Table
Field Name Data Type Length Key Constraints
flldemployeeNumber integer Primary Key
Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldhourlyRate double
Database Management for Business ISY1002/ISY103 Page 7

Group Member ID:
Group Member Name:
TbllDepartment Table
Field Name Data Type Length Key Constraints
flldDepartmentNumbe
r
Integer Primary Key
flldTitle Varchar 30
flldPhone Varchar 20
flldEmail Varchar 30
flldSupervisorEmpID Integer Foreign Key
references
TbllEmployee
(flldemployeeNumber)
TbllStore Table
Field Name Data Type Length Key Constraints
flldstoreID integer Primary Key
flldstoreName Varchar 40
flldPhone Varchar 20
flldEmail Varchar 30
flldFax Varchar 20
flldstreetAddress Varchar 50
flldsuburb Varchar 20
flldstate Varchar 20
flldpostcode Varchar 6
flldmanagerID integer Foreign Key
references
TbllEmployee
(flldemployeeNumber)
TbllDepartmentStore Table
Field Name Data Type Length Key Constraints
flldDepartmentNumbe
r
integer Primary Key
Foreign Key references
TbllDepartment
(fllddepartmentNumber)
flldStoreID integer Primary Key
Foreign Key references
TbllStore (flldstoreID)
Database Management for Business ISY1002/ISY103 Page 8

Group Member ID:
Group Member Name:
TbllStoreEmployee Table
Field Name Data Type Length Key Constraints
flldEmployeeNumber integer Primary Key
Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldstoreID integer Foreign Key
references
TbllDepartmentStore
(flldstoreID)
flldDepartmentNumbe
r
Integer Foreign Key
references
TbllDepartmentStore
(flldStoreID)
TbllPayslip Table
Field Name Data Type Length Key Constraints
flldpaySlipNumber integer Primary Key
flldworkedHours double
flldemployeeNumber integer Foreign Key
references
TbllEmployee
(flldemployeeNumber)
flldstoreID integer Foreign Key
references TbllStore
(flldstoreID)
flldgrossPay double
TbllSupplier Table
Field Name Data Type Length Key Constraints
flldsupplierID integer Primary Key
flldfirstName Varchar 20
flldlastName Varchar 20
flldaddress Varchar 50
flldmobile Varchar 20
Database Management for Business ISY1002/ISY103 Page 9

Group Member ID:
Group Member Name:
flldemail Varchar 30
TbllCategory Table
Field Name Data Type Length Key Constraints
flldcategory Varchar 30 Primary Key
fllddescription Varchar 50
TbllProduct Table
Field Name Data Type Length Key Constraints
flldproductNumber Varchar 10 Primary Key
flldtitle Varchar 30
flldbrand Varchar 30
fllddescription Varchar 50
flldprice double
flldcategory Varchar 30 Foreign Key
references
TbllCategory
(flldcategory)
TbllSupplierProduct Table
Field Name Data Type Length Key Constraints
flldproductNumber Varchar 10 Primary Key
Foreign Key
references
TbllProduct
(flldproductNumber)
flldsupplierID integer Primary Key
Foreign Key
references
TbllSupplier
(flldsupplierID)
flldqtyAvailable integer
flldqtyOrdered integer
Database Management for Business ISY1002/ISY103 Page 10

Group Member ID:
Group Member Name:
TbllPatron Table
Field Name Data Type Length Key Constraints
flldcustomerNumber integer Primary Key
flldfirstName Varchar 20
flldlastName Varchar 20
flldaddress Varchar 50
flldmobile Varchar 20
TbllProductOrder Table
Field Name Data Type Length Key Constraints
flldorderNumber integer Primary Key
flldproductNumber Varchar 10 Primary Key
Foreign Key
references
TbllProduct
(flldproductNumber)
flldorderDate Datetime
flldquantity integer
flldcustomerNumber integer Foreign Key
references
TbllCustomer
(flldcustomerNumber)
(Edugrabs.com 2015)
Database Management for Business ISY1002/ISY103 Page 11

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

Group Member ID:
Group Member Name:
Database Tables
describe TbllEmployee;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| flldemployeeNumber | int(11) | NO | PRI | | |
| flldfirstName | varchar(20) | NO | | | |
| flldlastName | varchar(20) | NO | | | |
| flldaddress | varchar(40) | NO | | | |
| flldmobile | varchar(20) | NO | | | |
| flldTFN | varchar(20) | NO | | | |
| flldemail | varchar(30) | NO | | | |
| flldjoiningDate | datetime | NO | | | |
+--------------------+-------------+------+-----+---------+-------+
describe TbllFullTimeEmployee;
+--------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| flldemployeeNumber | int(11) | NO | PRI | | |
| flldannualSalary | double | NO | | | |
+--------------------+---------+------+-----+---------+-------+
describe TbllCasualEmployee;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| flldemployeeNumber | int(11) | NO | PRI | | |
| flldhourlyRate | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
describe TbllDepartment;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| fllddepartmentNumber | int(11) | NO | PRI | | |
| flldtitle | varchar(30) | NO | | | |
| flldphone | varchar(20) | NO | | | |
| flldemail | varchar(30) | NO | | | |
| flldsupervisorEmpID | int(11) | NO | MUL | | |
+----------------------+-------------+------+-----+---------+-------+
Database Management for Business ISY1002/ISY103 Page 13

Group Member ID:
Group Member Name:
describe TbllStore;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| flldstoreID | int(11) | NO | PRI | | |
| flldstoreName | varchar(40) | NO | | | |
| flldphone | varchar(20) | NO | | | |
| flldemail | varchar(30) | NO | | | |
| flldfax | varchar(20) | NO | | | |
| flldstreetAddress | varchar(50) | NO | | | |
| flldsuburb | varchar(20) | NO | | | |
| flldstate | varchar(20) | NO | | | |
| flldpostCode | varchar(6) | NO | | | |
| flldmanagerID | int(11) | NO | MUL | | |
+-------------------+-------------+------+-----+---------+-------+
describe TbllStoreEmployee;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| flldstoreID | int(11) | NO | MUL | | |
| flldemployeeNumber | int(11) | NO | PRI | | |
| fllddepartmentNumber | int(11) | NO | | | |
+---------------------+---------+------+-----+---------+-------+
describe TbllDepartmentStore;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| flldstoreID | int(11) | NO | PRI | | |
| fllddepartmentNumber | int(11) | NO | PRI | | |
+---------------------+---------+------+-----+---------+-------+
describe TbllPaySlip;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| flldpaySlipNumber | int(11) | NO | PRI | | |
| flldworkedHours | double | NO | | | |
| flldemployeeNumber | int(11) | NO | MUL | | |
| flldstoreID | int(11) | NO | MUL | | |
| flldgrossPay | double | NO | | | |
+-------------------+---------+------+-----+---------+-------+
Database Management for Business ISY1002/ISY103 Page 14

Group Member ID:
Group Member Name:
describe TbllSupplier;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| flldsupplierID | int(11) | NO | PRI | | |
| flldfirstName | varchar(20) | NO | | | |
| flldlastName | varchar(20) | NO | | | |
| flldaddress | varchar(50) | NO | | | |
| flldmobile | varchar(20) | NO | | | |
| flldemail | varchar(30) | NO | | | |
+----------------+-------------+------+-----+---------+-------+
describe tbllCategory;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| flldcategory | varchar(30) | NO | PRI | | |
| fllddescription | varchar(50) | NO | | | |
+-----------------+-------------+------+-----+---------+-------+
describe TbllProduct;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| flldproductNumber | varchar(10) | NO | PRI | | |
| flldtitle | varchar(30) | NO | | | |
| flldbrand | varchar(30) | NO | | | |
| fllddescription | varchar(50) | NO | | | |
| flldprice | double | NO | | | |
| flldcategory | varchar(30) | NO | MUL | | |
+-------------------+-------------+------+-----+---------+-------+
describe TbllSupplierProduct;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| flldsupplierID | int(11) | NO | PRI | | |
| flldproductNumber | varchar(10) | NO | PRI | | |
| flldqtyAvailable | int(11) | NO | | | |
| flldqtyOrdered | int(11) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
Database Management for Business ISY1002/ISY103 Page 15

Group Member ID:
Group Member Name:
describe TbllPatron;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| flldcustomerNumber | int(11) | NO | PRI | | |
| flldfirstName | varchar(20) | NO | | | |
| flldlastName | varchar(20) | NO | | | |
| flldaddress | varchar(50) | NO | | | |
| flldmobile | varchar(20) | NO | | | |
+--------------------+-------------+------+-----+---------+-------+
describe TbllProductOrder;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| flldorderNumber | int(11) | NO | PRI | | |
| flldproductNumber | varchar(10) | NO | PRI | | |
| flldorderDate | datetime | NO | | | |
| flldquantity | int(11) | NO | | | |
| flldcustomerNumber | int(11) | NO | MUL | | |
+--------------------+-------------+------+-----+---------+-------+
Records
select * from tbllEmployee;
+--------------------+---------------+--------------+---------------
+-----------
-+------------+-------------------+---------------------+
| flldemployeeNumber | flldfirstName | flldlastName | flldaddress |
flldmobile
| flldTFN | flldemail | flldjoiningDate |
+--------------------+---------------+--------------+---------------
+-----------
-+------------+-------------------+---------------------+
| 1 | Christial | White | 14 Peter Rd |
4785858555
| 4785858555 | [e-mail] | 2016-05-11 00:00:00 |
| 2 | Patrick | Brown | 11 Daniel Av. |
4785858009
| 4785858111 | [e-mail] | 2015-01-14 00:00:00 |
| 3 | Archer | Black | 15 Smith Rd |
4785858223
| 4785858589 | [e-mail] | 2019-06-06 00:00:00 |
Database Management for Business ISY1002/ISY103 Page 16

Group Member ID:
Group Member Name:
| 4 | Jaxon | Smith | 46 Zebra Rd |
4785858887
| 4785858590 | [e-mail] | 2016-07-10 00:00:00 |
| 5 | Louis | Robert | 12 White St. |
4785858453
| 4785858533 | [e-mail] | 2018-02-18 00:00:00 |
| 6 | Harley | Smith | 78 Brown St. |
4785858123
| 4785859755 | [e-mail] | 2014-09-27 00:00:00 |
+--------------------+---------------+--------------+---------------
+-----------
-+------------+-------------------+---------------------+
select * from TbllFullTimeEmployee;
+--------------------+------------------+
| flldemployeeNumber | flldannualSalary |
+--------------------+------------------+
| 1 | 130 |
| 2 | 120 |
| 3 | 160 |
+--------------------+------------------+
select * from TbllCasualEmployee;
+--------------------+----------------+
| flldemployeeNumber | flldhourlyRate |
+--------------------+----------------+
| 4 | 50 |
| 5 | 50 |
| 6 | 60 |
+--------------------+----------------+
select * from TbllDepartment;
+----------------------+-----------+------------+---------------------
+---------
------------+
| fllddepartmentNumber | flldtitle | flldphone | flldemail |
flldsupe
rvisorEmpID |
+----------------------+-----------+------------+---------------------
+---------
------------+
| 1 | finance | 4557757667 | [e-mail] |
3 |
| 2 | sales | 4557654367 | [e-mail] |
4 |
| 3 | accounts | 4557690667 | [e-mail] |
Database Management for Business ISY1002/ISY103 Page 17

Group Member ID:
Group Member Name:
1 |
| 4 | HR | 4557658867 | [e-mail] |
2 |
+----------------------+-----------+------------+---------------------+------
select * from TbllStore;
+-------------+---------------+------------+-----------------+------------
+-----
--------------+------------+-----------+--------------+---------------+
| flldstoreID | flldstoreName | flldphone | flldemail | flldfax |
flld
streetAddress | flldsuburb | flldstate | flldpostCode | flldmanagerID |
+-------------+---------------+------------+-----------------+------------
+-----
--------------+------------+-----------+--------------+---------------+
| 1 | Zara | 4656654799 | [e-mail] | 4656654799 | 98
G
eorge Road | Zeeland | NSW | 5643 | 1 |
| 2 | Levis | 4656654722 | [e-mail] | 4656654722 | 22
S
tuart St. | Chatswood | VIC | 4423 | 2 |
| 3 | Peter England | 4656654711 | [e-mail] | 4656654711 | 55
S
mith Rd | Bankstown | NSW | 1967 | 3 |
| 4 | Benten | 4656654790 | [e-mail] | 4656654790 | 66
M
ax Rd. | Chatswood | VIC | 5543 | 4 |
| 5 | Lilliput | 4656654777 | [e-mail] | 4656654777 | 77
G
eorge Rd. | Zeeland | NSW | 9987 | 5 |
+-------------+---------------+------------+-----------------+------------+--
select * from TbllDepartmentStore;
+------------+---------------------+
| flldstoreID | fllddepartmentNumber |
+------------+---------------------+
| 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 |
Database Management for Business ISY1002/ISY103 Page 18

Group Member ID:
Group Member Name:
+------------+---------------------+
select * from TbllStoreEmployee;
+-------------+--------------------+----------------------+
| flldstoreID | flldemployeeNumber | fllddepartmentNumber |
+-------------+--------------------+----------------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 5 | 3 |
| 4 | 6 | 3 |
| 4 | 4 | 4 |
+-------------+--------------------+----------------------+
select * from TbllPaySlip;
+-------------------+-----------------+--------------------+-------------
+------
--------+
| flldpaySlipNumber | flldworkedHours | flldemployeeNumber | flldstoreID |
flldg
rossPay |
+-------------------+-----------------+--------------------+-------------
+------
--------+
| 1 | 10 | 4 | 4 |
500 |
| 2 | 10 | 5 | 5 |
600 |
| 3 | 10 | 6 | 3 |
500 |
+-------------------+-----------------+--------------------+-------------+---
select * from TbllSupplier;
+----------------+---------------+--------------+----------------+------------
+-
---------------+
| flldsupplierID | flldfirstName | flldlastName | flldaddress | flldmobile
|
flldemail |
+----------------+---------------+--------------+----------------+------------
+-
---------------+
Database Management for Business ISY1002/ISY103 Page 19

Group Member ID:
Group Member Name:
| 1 | Rose | Rober | 98 George Road | 5766876877
|
[e-mail] |
| 2 | Max | Lee | 98 George Road | 5656876877
|
[e-mail] |
| 3 | Noah | Smith | 18 Link Road | 5766876878
|
[e-mail] |
+----------------+---------------+--------------+----------------+------------
+-
---------------+
select * from tbllCategory;
+--------------+-----------------+
| flldcategory | fllddescription |
+--------------+-----------------+
| Beauty | Beauty |
| Cloth | Cloth |
| Electronics | Electronics |
+--------------+-----------------+
select * from TbllProduct;
+-------------------+-------------+---------------+---------------------
+-------
----+--------------+
| flldproductNumber | flldtitle | flldbrand | fllddescription |
flldpr
ice | flldcategory |
+-------------------+-------------+---------------+---------------------
+-------
----+--------------+
| P1 | TV | Max | TV |
104 | Electronics |
| P2 | Pant | Peter England | Peter England Pant |
2 | Cloth |
| P3 | Shirt | Peter England | Peter England Shirt |
3 | Cloth |
| P4 | Face Powder | Max | Max Face Powder |
9 | Beauty |
| P5 | Hair Cream | Max | Max Hair Cream |
4 | Beauty |
+-------------------+-------------+---------------+---------------------
+-------
Database Management for Business ISY1002/ISY103 Page 20

Group Member ID:
Group Member Name:
select * from TbllSupplierProduct;
+----------------+-------------------+------------------+----------------+
| flldsupplierID | flldproductNumber | flldqtyAvailable | flldqtyOrdered |
+----------------+-------------------+------------------+----------------+
| 1 | P1 | 12 | 10 |
| 2 | P2 | 20 | 15 |
| 2 | P4 | 30 | 10 |
| 3 | P3 | 20 | 5 |
| 3 | P5 | 10 | 10 |
+----------------+-------------------+------------------+----------------+
select * from TbllPatron;
+--------------------+---------------+--------------+---------------
+-----------
-+
| flldcustomerNumber | flldfirstName | flldlastName | flldaddress |
flldmobile
|
+--------------------+---------------+--------------+---------------
+-----------
-+
| 1 | Zain | Lee | 24 Church St. |
3264264747
|
| 2 | Ellora | White | 77 Smith St. |
3264264744
|
| 3 | John | Lee | 78 White Rd. |
3264264733
|
| 4 | Harry | Kevin | 19 Lee Rd. |
3264264722
|
| 5 | Henry | Smith | 99 Lee Rd. |
3264264711
|
+--------------------+---------------+--------------+---------------+--------
select * from TbllProductOrder;
+-----------------+-------------------+---------------------+--------------
+----
Database Management for Business ISY1002/ISY103 Page 21

Group Member ID:
Group Member Name:
----------------+
| flldorderNumber | flldproductNumber | flldorderDate | flldquantity |
fll
dcustomerNumber |
+-----------------+-------------------+---------------------+--------------
+----
----------------+
| 1001 | P1 | 2017-05-22 00:00:00 | 1 |
1 |
| 1002 | P5 | 2017-10-12 00:00:00 | 2 |
2 |
| 1003 | P2 | 2018-08-11 00:00:00 | 1 |
1 |
| 1004 | P3 | 2017-11-12 00:00:00 | 3 |
3 |
| 1004 | P4 | 2017-11-12 00:00:00 | 3 |
3 |
| 1005 | P4 | 2017-12-15 00:00:00 | 4 |
4 |
| 1005 | P5 | 2017-12-15 00:00:00 | 4 |
4 |
+-----------------+-------------------+---------------------+--------------+-
Referential Integrity Constraint
TbllCasualEmployee Table
TbllFullTimeEmployee Table
Database Management for Business ISY1002/ISY103 Page 22

Group Member ID:
Group Member Name:
TbllDepartment Table
TbllDepartmentStore Table
Database Management for Business ISY1002/ISY103 Page 23

Group Member ID:
Group Member Name:
TbllPayslip Table
TbllProduct Table
Database Management for Business ISY1002/ISY103 Page 24

Group Member ID:
Group Member Name:
TbllProductOrder Table
TbllStore Table
Database Management for Business ISY1002/ISY103 Page 25

Group Member ID:
Group Member Name:
TbllStoreEmployee Table
TbllSupplierProduct Table
Database Management for Business ISY1002/ISY103 Page 26

Group Member ID:
Group Member Name:
Part D: SQL
Question 1
SQL select flldcustomerNumber, concat(flldfirstName,' ',flldlastName)
'Customer Full Name', flldmobile, flldaddress from TbllPatron
order by flldcustomerNumber desc;
Output +--------------------+--------------------+------------
+---------------+
| flldcustomerNumber | Customer Full Name | flldmobile |
flldaddress |
+--------------------+--------------------+------------
+---------------+
| 5 | Henry Smith | 3264264711 | 99 Lee
Rd. |
| 4 | Harry Kevin | 3264264722 | 19 Lee
Rd. |
| 3 | John Lee | 3264264733 | 78 White
Rd. |
| 2 | Ellora White | 3264264744 | 77 Smith
St. |
| 1 | Zain Lee | 3264264747 | 24
Church St. |
+--------------------+--------------------+------------
Database Management for Business ISY1002/ISY103 Page 27

Group Member ID:
Group Member Name:
+---------------+
Question 2
SQL select * from TbllProduct where flldprice<100;
Output | flldproductNumber | flldtitle | flldbrand |
fllddescription | flldpr
ice | flldcategory |
+-------------------+-------------+---------------
+---------------------+-------
----+--------------+
| P2 | Pant | Peter England | Peter
England Pant |
2 | Cloth |
| P3 | Shirt | Peter England | Peter
England Shirt |
3 | Cloth |
| P4 | Face Powder | Max | Max Face
Powder |
9 | Beauty |
| P5 | Hair Cream | Max | Max Hair
Cream |
4 | Beauty |
+-------------------+-------------+---------------
+---------------------+----
Question 3
SQL select tbllProductOrder.flldorderNumber,
tbllProductOrder.flldproductNumb
er, tbllProduct.flldtitle, tbllProductOrder.flldquantity,
tbllProduct.flldprice,(tbllProductOrder.flldquantity *
tbllProduct.flldprice) as TotalAmount from tbllProductOrder inner
join tbllProduct on tbllProduct.flldproductNumber=tbllProdu
ctOrder.flldproductNumber where flldorderNumber=1005;
Output +-----------------+-------------------+-------------+--------------
+-----------+
-------------+
| flldorderNumber | flldproductNumber | flldtitle | flldquantity |
flldprice |
TotalAmount |
+-----------------+-------------------+-------------+--------------
+-----------+
-------------+
| 1005 | P4 | Face Powder | 4 |
9 |
36 |
| 1005 | P5 | Hair Cream | 4 |
4 |
16 |
+-----------------+-------------------+-------------+--------------
+-----------+
Database Management for Business ISY1002/ISY103 Page 28

Group Member ID:
Group Member Name:
-------------+
Question 4
SQL select flldstoreName, concat(flldstreetAddress,' ',flldsuburb, '
', flldstate
, ' ' ,flldpostcode) as Address, flldfax from TbllStore where
flldstreetAddress like
'%George%';
Output +---------------+---------------------------------+------------+
| flldstoreName | Address | flldfax |
+---------------+---------------------------------+------------+
| Zara | 98 George Road Zeeland NSW 5643 | 4656654799 |
| Lilliput | 77 George Rd. Zeeland NSW 9987 | 4656654777 |
+---------------+---------------------------------+------------+
Question 5
SQL select * from TbllProductOrder where flldorderNumber in (select
flldorderNum
ber from TbllProductOrder group by flldorderNumber having
count(flldproductNumber)>1);
Output +-----------------+-------------------+---------------------
+--------------+----
----------------+
| flldorderNumber | flldproductNumber | flldorderDate |
flldquantity | fll
dcustomerNumber |
+-----------------+-------------------+---------------------
+--------------+----
----------------+
| 1004 | P3 | 2017-11-12 00:00:00 |
3 |
3 |
| 1004 | P4 | 2017-11-12 00:00:00 |
3 |
3 |
| 1005 | P4 | 2017-12-15 00:00:00 |
4 |
4 |
| 1005 | P5 | 2017-12-15 00:00:00 |
4 |
4 |
+-----------------+-------------------+---------------------
+--------------+
Question 6
Database Management for Business ISY1002/ISY103 Page 29

Group Member ID:
Group Member Name:
SQL select flldcustomerNumber, concat(flldfirstName,' ',flldlastName)
'Customer
Name', flldaddress from TbllPatron where flldcustomerNumber not in
(select flldcustomerNumber from TbllProductOrder);
Output +--------------------+---------------+-------------+
| flldcustomerNumber | Customer Name | flldaddress |
+--------------------+---------------+-------------+
| 5 | Henry Smith | 99 Lee Rd. |
+--------------------+---------------+-------------+
Question 7
SQL select TbllEmployee.flldemployeeNumber, flldfirstName, flldlastName,
flldstore Name, flldtitle as DepartmentName from TbllEmployee inner
join TbllStoreEmployee on
TbllEmployee.flldemployeeNumber=TbllStoreEmployee.flldemployeeNumber
inner join tblStore on
TbllStoreEmployee.flldstoreID=TbllStore.flldstoreID inner join
TbllDepartment on
TbllstoreEmployee.fllddepartmentNumber=TbllDepartment.fllddepartmentN
umber;
Output +--------------------+---------------+--------------+---------------
+-----------
-----+
| flldemployeeNumber | flldfirstName | flldlastName | flldstoreName |
Department
Name |
+--------------------+---------------+--------------+---------------
+-----------
-----+
| 1 | Christial | White | Zara |
finance
|
| 2 | Patrick | Brown | Levis |
sales
|
| 3 | Archer | Black | Peter England |
accounts
|
| 4 | Jaxon | Smith | Benten |
HR
|
| 5 | Louis | Robert | Benten |
accounts
|
| 6 | Harley | Smith | Benten |
accounts
|
+--------------------+---------------+--------------+---------------
+--------
Database Management for Business ISY1002/ISY103 Page 30

Group Member ID:
Group Member Name:
Question 8
SQL select flldstoreName, count(TbllStoreEmployee.flldemployeeNumber)
NumberOfE
ployees from TbllStore inner join TbllStoreEmployee on
TbllStore.flldstoreID=TbllStoeEmployee.flldstoreID inner join
TbllDepartment on TbllStoreEmployee.fllddepartment
umber=TbllDepartment.fllddepartmentNumber group by flldstoreName,
flldtitle having flldtitle='accounts';
Output +---------------+-------------------+
| flldstoreName | NumberOfEmployees |
+---------------+-------------------+
| Benten | 2 |
| Peter England | 1 |
+---------------+-------------------+
Question 9
SQL select * from TbllProductOrder where flldorderDate>'2017-06-30'
and flldorde
rDate< '2018-07-01';
Output +-----------------+-------------------+---------------------
+--------------+----
----------------+
| flldorderNumber | flldproductNumber | flldorderDate |
flldquantity | fll
dcustomerNumber |
+-----------------+-------------------+---------------------
+--------------+----
----------------+
| 1002 | P5 | 2017-10-12 00:00:00 |
2 |
2 |
| 1004 | P3 | 2017-11-12 00:00:00 |
3 |
3 |
| 1004 | P4 | 2017-11-12 00:00:00 |
3 |
3 |
| 1005 | P4 | 2017-12-15 00:00:00 |
4 |
4 |
| 1005 | P5 | 2017-12-15 00:00:00 |
4 |
4 |
+-----------------+-------------------+---------------------
+--------------+-
Database Management for Business ISY1002/ISY103 Page 31

Group Member ID:
Group Member Name:
Question 10
SQL select flldcustomerNumber, count(flldorderNumber) NumberOfOrders
from TbllPr
oductOrder group by flldcustomerNumber;
Output +--------------------+----------------+
| flldcustomerNumber | NumberOfOrders |
+--------------------+----------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+--------------------+----------------+
Question 11
SQL select flldorderNumber, flldorderDate, count(flldproductNumber) as
NumberOfP
roducts from TbllProductOrder group by flldorderNumber,
flldorderDate;
Output +-----------------+---------------------+------------------+
| flldorderNumber | flldorderDate | NumberOfProducts |
+-----------------+---------------------+------------------+
| 1001 | 2017-05-22 00:00:00 | 1 |
| 1002 | 2017-10-12 00:00:00 | 1 |
| 1003 | 2018-08-11 00:00:00 | 1 |
| 1004 | 2017-11-12 00:00:00 | 2 |
| 1005 | 2017-12-15 00:00:00 | 2 |
+-----------------+---------------------+------------------+
Database Management for Business ISY1002/ISY103 Page 32

Group Member ID:
Group Member Name:
References
Tutorialcup (2015), ER Data Model [online] Available from:
https://www.tutorialcup.com/dbms/er-data-model.htm [Accessed: 3 June 2019]
Ziff Davis (2017), Understanding Relationships in E-R Diagrams [online] Available from:
http://it.toolbox.com/blogs/enterprise-solutions/understanding-relationships-in-er-diagrams-
14310 [Accessed: 3 June 2019]
Smartdraw (2016). Entity Relationship Diagram. [online] Available from:
https://www.smartdraw.com/entity-relationship-diagram/. [Accessed: 3 June 2019]
Edugrabs.com (2015), Types of Attributes in DBMS with Example (ER MODEL – Part 2),
[online] Available from: http://www.edugrabs.com/type-of-attributes-in-dbms/. [Accessed: 3
June 2019]
Database Management for Business ISY1002/ISY103 Page 33