Restaurant Database Design, Implementation, and View Creation Project

This assignment presents a comprehensive solution for designing and implementing a restaurant database. The solution begins with a revised Entity-Relationship Diagram (ERD) that incorporates entities such as 'Certificate' and 'Suburb', along with their attributes and relationships. The ERD is then translated into a 3rd Normal Form (3NF) relational schema, detailing the tables: CERTIFICATE, RESTAURANT, RESTAURANT_CERTIFICATION, SUBURB, CUSTOMER, DRIVER, DISH, ORDER, and ORDERED_DISH, along with their respective attributes and constraints. A detailed data dictionary is provided, specifying attributes, data types, required fields, constraints, and referential integrity rules for each table. The assignment further includes implementation details, showcasing the SQL code necessary to create and populate the database. Finally, the solution demonstrates the creation of database views to provide specific perspectives on the data.

Part 1: Revised ERD and Schema
The revised ERD is,
Following changes have been made in the revised ERD.
 The new entity Certificate has been added. It contains the details of the certifications of the
restaurants. There is a many to many relationship between Restaurant and Certificate. So, the new
associative entity, RestaurantCertification has been added to the design.
 The suburb entity has been added. As suburb is an important part of the database, so keeping is as
an entity will help in making queries based on suburb, adding relationships with other entities and
reducing the chances of errors in the database.
 Attributes have been added to all entities to add more information.
The 3NF relational schema contains the following relations mapped from the revised ERD.
 CERTIFICATE ( CertificateID, CertificateName)
 RESTAURANT (RestaurantID, RestaurantName, RestaurantDetails, Ethnicity, Street,
SuburbID, State, Zipcode, PhoneNumber)
 RESTAURANT_CERTICATION (RestaurantID, CertificateID, DateCertification)

 SUBURB(SuburbID, SuburbName)
 CUSTOMER (CustomerID, CustomerFirstName, CustomerLastName, PhoneNumber, Street,
SuburbID, State, Zipcode)
 DRIVER(DriverID, DriverFirstName, DriverLastName, Availability, SuburbID)
 DISH(DishID, DishName, Description, PreparationType, MainIngredients, CourseType,
EFRating, CarbonCost, Price, DeliveryTime, RestaurantID)
 ORDER (OrderID, DriverID, CustomerID, OrderDate, OrderDeliveryTime,
ActualDeliveryTime, CurrentStatus)
 ORDERED_DISH (OrderID, DishID)
Part 2: Data Dictionary
The data dictionary is,
Table Attribute Brief Description Datatype
and Size
Required
or Not
Constraint
(s)
Referential
Integrity
Rules (if
Any)
Certificate CertificateID Unique certificate
code.
INT Yes Primary
key
CertificateName Name of
certification.
VARCHA
R2(50)
Yes
Restaurant RestaurantID Unique restaurant
code.
INT Yes Primary
key
RestaurantName Name of a
restaurant.
VARCHA
R2(50)
Yes
RestaurantDetails Short description
about the restaurant.
VARCHA
R2(250)
Yes
Ethnicity Ethnicity of the
restaurant.
VARCHA
R2(50)
Yes
Street Street details. VARCHA
R2(250)
Yes
SuburbID Suburb ID INT Yes Foreign
key
On update
State Statecode VARCHA
R2(3)
Yes {NSW,
QLD, SA,
TAS, VIC,
WA}
Zipcode 4 digit zipcode CHAR(4) Yes 0000-9999
PhoneNumber 10 digit contact
number of the
restaurant.
CHAR(10) Yes
000000000
0-
999999999
9
Restaurant_
Certification
RestaurantID Restaurant code INT Yes Primary
key and
foreign key
On update
CertificateID Certificate code INT Yes Primary
key and
foreign key
On update

DateCertification Date of certification
given to the
restaurant.
DATE Yes
Suburb SuburbID Unique suburb code. INT Yes Primary
key
SuburbName Name of the suburb. VARCHA
R2(50)
Yes
Customer CustomerID Unique customer
code.
INT Yes Primary
key
CustomerFirstNa
me
First name of a
customer.
VARCHA
R2(50)
Yes
CustomerLastNam
e
Last name of a
customer.
VARCHA
R2(50)
Yes
PhoneNumber Phone number of a
customer.
CHAR(10) Yes
Street Street details of a
customer address.
VARCHA
R2(250)
Yes
SuburbID Suburb code. INT Yes Foreign
key
On update
State State of customer
address.
VARCHA
R2(3)
Yes {NSW,
QLD, SA,
TAS, VIC,
WA}
Zipcode 4 digit zip code. CHAR(4) Yes
Driver DriverID Unique driver code. INT Yes Primary
key
DriverFirstName First name of the
driver.
VARCHA
R2(50)
Yes
DriverLastName Last name of the
driver.
VARCHA
R2(50)
Yes
Availability Current availability
of driver.
VARCHA
R2(50)
Yes {Available,
Out for
Delivery,
Not
Available}
SuburbID Suburb code where
the driver delivers
order.
INT Yes Foreign
key
Dish DishID Unique code for a
dish.
INT Yes Primary
key
DishName Name of a dish. VARCHA
R2(50)
Yes
Description Short description of
the dish.
VARCHA
R2(250)
Yes
PreparationType Preparation type of
the dish.
VARCHA
R2(50)
Yes
MainIngredients Main ingredients of
the dish.
VARCHA
R2(250)
Yes
CourseType Course type VARCHA
R2(50)
Yes

EFRating Earth First Rating. VARCHA
R2(20)
Optional {Green,
Orange,
Red}
CarbonCost Carbon cost in kg. INT Optional
Price Price in AUD. Number(5,
2)
Yes
DeliveryTime Delivery time
category.
VARCHA
R2(20)
Yes {Fast,
Regular,
Worth the
Wait}
RestaurantID Restaurant code. INT Yes Foreign
key
On update
Order OrderID Unique Order code. INT Yes Primary
key
DriverID Driver code INT Yes Foreign
key
On update
CustomerID Customer code INT Yes Foreign
key
On update
OrderDate Date of the order DATE Yes
OrderDeliveryTim
e
Expected delivery
time in minutes.
INT Yes 0-120
ActualDeliveryTi
me
Actual delivery time
in minutes.
INT Yes 0-120
CurrentStatus Current status of the
order.
VARCHA
R2(20)
Yes {Processin
g,
Cancelled,
Out for
Delivery,
Fulfilled}
Ordered_Di
sh
OrderID Order code INT Yes Primary
and foreign
key
On update
DishID Dish code INT Yes Primary
and foreign
key
On update
Part 3: Implementation
The code is,
Part 4: Views
The codes are,