Faculty of Engineering - Popular Pizza Database Management Project

This assignment presents a detailed solution for a database management project centered around a pizza ordering system. It begins with a requirement specification that covers data requirements, transaction requirements, and business rules. The core of the assignment includes the creation of an Enhanced Entity Relationship (EER) diagram and a comprehensive data dictionary. The EER model is then mapped to a relational model, documented in Database Description Language (DBDL). The relational schema undergoes normalization to Boyce-Codd Normal Form (BCNF), ensuring data integrity and efficiency. The document includes a complete SQL script for database creation, along with SQL statements demonstrating various database operations. The solution also provides input data examples and example queries to illustrate the database functionality. The project covers concept database design, logic database design, and physical database design, as well as normalization of the relational schema. This project is a complete database design solution for the Popular Pizza project.

Running head: DATABASE MANAGEMENT
DATABASE MANAGEMENT
Name of the Student
Name of the University
Author Note

1
DATABASE MANAGEMENT
Table of Contents
Requirement Specification...............................................................................................................2
EER Diagram...................................................................................................................................4
Data Dictionary................................................................................................................................5
EER to Relational model (DBDL).................................................................................................20
Normalization................................................................................................................................22
SQL Script.....................................................................................................................................22
SQL Statements.............................................................................................................................32
Input Data..................................................................................................................................32
Queries.......................................................................................................................................37

2
DATABASE MANAGEMENT
Requirement Specification
Data requirement Analysis is done with the help of the Pizza database scenario. The
Processes of Ordering, Employee management and Menu management helped in identifying the
data which is required for the database. Similarly the Transaction Requirements is analyzed after
identification of the data requirements. Transactions defines the resources and data required to
process in the database. Business process is the main factor of analysis part. Business rules for
the Pizza database are listed below:
i. A customer can book 0 or many orders.
ii. Each order is associated with one and only one customer.
iii. Each order can be associated to 0 or one walk-in bookings.
iv. Each walk-in bookings are related to one and only one order.
v. Each order can be associated with 0 or one phone orders.
vi. Each phone orders are an order.
vii. Each order is associated with one or more Payment.
viii. Each payment is associated with one and only one order.
ix. Each phone order can be either pickup or delivery.
x. Each order is related with one or many order has items.
xi. Each order has items is associated with one and only one order and one and only one
item.
xii. Each item can be present in one or many orders.
xiii. Each item can use one or many ingredients.
xiv. Each ingredients can be used in one or many items.

3
DATABASE MANAGEMENT
xv. Each ingredient can be supplied by one or many suppliers.
xvi. Each supplier can supply one or many ingredients.
xvii. Each employee can take one or more orders.
xviii. Each employee is either driver staff or in-store staff.
xix. Each driver staff can deliver one or more order deliveries.
xx. Each delivery is associated with one and only one driver.
xxi. Each employee have one and only one bank.
xxii. Each bank can have one or more employees.
xxiii. Each employee have one or more one payment.
xxiv. Each payment is associated with one and only one employee.

4
DATABASE MANAGEMENT
EER Diagram
Figure: Enhanced Entity Relationship Diagram of Pizza database
Source: created by author

5
DATABASE MANAGEMENT
Data Dictionary
bank
Column Type Null Default Links to
bankCode (Primary
)
varchar(5) No
Name varchar(45) Yes NULL
AccountNumber int(11) Yes NULL
Indexes
Keyname Type Unique Packed Column Cardinality
Collatio
n
Null
PRIMARY
BTRE
E
Yes No bankCode 6 A No
customer
Column Type Null Default Links to
CustID (Primary
)
varchar(5) No
firstname varchar(45) Yes NULL

6
DATABASE MANAGEMENT
lastname varchar(45) No
Address varchar(45) Yes NULL
Phone int(11) Yes NULL
Indexes
Keyname Type Unique
Packe
d
Column
Cardinalit
y
Collation Null
PRIMARY
BTRE
E
Yes No CustID 4 A No
delivery
Column Type Null Default Links to
deliveryTime (Primary
)
varchar(10
)
No
Address
varchar(45
)
Yes NULL
OrderID (Primary) varchar(5) No
phoneorder ->
OrderID
DriverID (Primary) varchar(10 No driver -> DriverID

7
DATABASE MANAGEMENT
)
Indexes
Keyname Type
Uniqu
e
Packe
d
Column
Cardinalit
y
Collatio
n
Nul
l
PRIMARY
BTRE
E
Yes No
deliveryTim
e
3 A No
OrderID 3 A No
DriverID 3 A No
fk_Delivery_PhoneOrder1_i
dx
BTRE
E
No No OrderID 3 A No
fk_Delivery_Driver1_idx
BTRE
E
No No DriverID 3 A No
driver
Column Type Null Default Links to
LicenseNumber
varchar(45
)
Yes NULL

8
DATABASE MANAGEMENT
DriverID (Primary
)
varchar(10
)
No
employees ->
EmpNumber
Indexes
Keyname Type Unique
Packe
d
Column Cardinality Collation Null
PRIMARY
BTRE
E
Yes No DriverID 2 A No
employees
Column Type Null Default Links to
EmpNumber (Primary
)
varchar(10) No
Firstname varchar(45) Yes NULL
Lastname varchar(45) Yes NULL
PostalAddress varchar(45) Yes NULL
Contact int(11) Yes NULL
tfnNumber varchar(45) Yes NULL
bankCode varchar(5) No bank -> bankCode

9
DATABASE MANAGEMENT
Indexes
Keyname Type
Uniqu
e
Packe
d
Column
Cardinalit
y
Collatio
n
Nul
l
PRIMARY
BTRE
E
Yes No
EmpNumbe
r
6 A No
fk_Employees_Bank1_id
x
BTRE
E
No No bankCode 6 A No
emppayment
Column Type Null Default Links to
PayNumber (Primary
)
varchar(10
)
No
GrossPay int(11) Yes NULL
Date date Yes NULL
taxWithHeld int(11) Yes NULL
TotalAmountPaid int(11) Yes NULL
PstartDate date Yes NULL
Penddate date Yes NULL

10
DATABASE MANAGEMENT
EmpNumber
varchar(10
)
No
employees ->
EmpNumber
Indexes
Keyname Type
Uniqu
e
Packe
d
Column
Cardinalit
y
Collatio
n
Nul
l
PRIMARY
BTRE
E
Yes No
PayNumbe
r
6 A No
fk_EmpPayment_Employees1_
idx
BTRE
E
No No
EmpNumb
er
6 A No
ingredient
Column Type Null Default Links to
IngCode (Primary
)
varchar(10) No
Name varchar(45) Yes NULL
type varchar(45) Yes NULL
Description varchar(45) Yes NULL
StockLevel varchar(45) Yes NULL

11
DATABASE MANAGEMENT
LastStockDate date Yes NULL
ReorderLevel varchar(45) Yes NULL
Indexes
Keyname Type Unique
Packe
d
Column
Cardinalit
y
Collation Null
PRIMARY
BTRE
E
Yes No IngCode 4 A No
ingredientorder
Column Type Null Default Links to
OrderNumber (Primary
)
varchar(10
)
No
IngCode (Primary)
varchar(10
)
No
ingredient ->
IngCode
SupplierID (Primary)
varchar(10
)
No
supplier ->
SupplierID
Dateoforder date Yes NULL
DateRecieved date Yes NULL