Database Management Analysis 2022

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

DATABASE MANAGEMENT
1
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

DATABASE MANAGEMENT
2
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.

DATABASE MANAGEMENT
3
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.

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

DATABASE MANAGEMENT
5
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 Collation 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
lastname varchar(45) No
Address varchar(45) Yes NULL

DATABASE MANAGEMENT
6
Phone int(11) Yes NULL
Indexes
Keyname Type Unique
Packe
d
Column
Cardinalit
y
Collation Null
PRIMARY BTREE 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
Indexes

DATABASE MANAGEMENT
7
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
DriverID (Primary
)
varchar(10
)
No
employees ->
EmpNumber
Indexes