Database Implementation: FF4U System Design and SQL Queries

This assignment addresses the database design and implementation for the FF4U (Food for You) system, a food delivery service. The solution begins with user creation, specifically creating a user named MARKERTL with appropriate privileges for database interaction. It then proceeds to define and create a comprehensive set of tables, including Certificate, Suburb, Restaurant, Restaurant_Certification, Customer, Driver, Dish, Orders, and Ordered_Dish, each with specific attributes and constraints to ensure data integrity. Data insertion scripts are also provided to populate the tables with sample data. Furthermore, the assignment includes the creation of ten different SQL views (ViewA through ViewJ), each designed to retrieve specific information from the database, such as order details, dishes meeting certain criteria (e.g., 'Green' EFRating and 'Organic' certification), restaurant details, and order statistics. These views demonstrate the use of joins, filtering, and aggregation functions to extract valuable insights from the database. The solution showcases a practical application of database design principles and SQL query writing for a real-world business scenario.

PART – 3
Introduction
Bill needs to enhance his administration into different rural areas also. He has enlisted more
drivers, with the desire that there will be drivers accessible in every suburb. The clients will have
the capacity to have orders that are delivered from eateries in a similar suburb. Bill has to know
whether his suppers are being delivered on time according to his publicizing, so the database
should have the capacity to give data as required. Bill is satisfied with the way FF4U is creating
and needs to make some further enhancement to the idea. He has chosen to offer an
administration whereby clients will have the capacity to see the carbon impression of the food
they order and have the capacity to pick appropriately. Bill is going to call this administration
'Earth First'. Earth First dishes will give a carbon cost to the dish and an general EF rating from
green (best), through orange to red (the worst). This information is not available by all
restaurants, so just a few dishes will be accessible on the Earth First menu. Bill expects that
Earth First dishes will originate from any of the current type of nourishment he offers (veggie
lover, Halal ensured, noodle house, and so on). At the point when the order/receipt is printed for
the client, the carbon cost and Earth First evaluating will be incorporated along with the other
delivery data.
Implementation Process
User Creation
As according to the given scenario, user MARKERTL needs to be created first with the
privileges of SELECT, UPDATE, DELETE. The commands for creating users are as following:

SQL> Create user MARKERTL identified by admin;
SQL> Grant Connect, Resource to MARKERTL;
SQL> Grant Select, Insert, Update, Delete to MARKERTL;
Table Creation
1. Certificate
SQL> Create table Certificate (CertificateID int primary key, CertificateName varchar2 (50));
2. Suburb
SQL> Create table Suburb (SuburbID int primary key, SuburbName varchar2 (50));

3. Restaurant
SQL> Create table Restaurant (RestaurantID int primary key, RestaurantName varchar2 (50),
RestaurantDetails varchar2 (250), Ethnicity varchar2 (50),
Street varchar2 (250), SuburbID int references Suburb (SuburbID),
State varchar2 (3), Zipcode char (4), PhoneNumber char (10),
Constraint chk_state CHECK (State in ('NSW', 'QLD', 'SA', 'TAS','VIC', 'WA')),
Constraint chk_zip CHECK (Zipcode BETWEEN 0000 AND 9999),
Constraint chk_PHONE CHECK (PhoneNumber BETWEEN 0000000000 AND
9999999999));

4. Restaurant_certification
SQL> Create table Restaurant_Certification (RestaurantID int, CertificateID int,
DateCertification date, constraint pk_id primary key (RestaurantID, CertificateID),
constraint fk_rest foreign key (RestaurantID) references Restaurant
(RestaurantID) ON DELETE CASCADE, constraint fk_cert foreign key (CertificateID)
references Certificate (CertificateID) ON DELETE CASCADE);

5. Customer
SQL> Create table Customer (CustomerID int primary key, CustomerFirstName varchar2(50),
CustomerLastName varchar2(50), PhoneNumber char(10), Street varchar2(250),
SuburbID references Suburb (SuburbID) ON DELETE CASCADE, State varchar2 (3),
Zipcode char(4), Constraint chk_state1 CHECK (State in ('NSW', 'QLD', 'SA',
'TAS','VIC', 'WA')));
6. Driver
SQL> Create table Driver (DriverID int primary key, DriverFirstName varchar2(50),
DriverLastName varchar2(50), Availability varchar2(50), SuburbID references
Suburb (SuburbID) ON DELETE CASCADE, Constraint chk_avail1 CHECK
(Availability in ('Available', 'Out for Delivery', 'Not Available')));

7. Dish
SQL> Create table Dish (DishID int primary key, DishName varchar2(50),
Description varchar2(250), PreparationType varchar2(50),
MainIngredients varchar2(250), CourseType varchar2(50), EFRating varchar2(20),
CarbonCost int, price number (5,2), DeliveryTime varchar2(20), RestaurantID references
Restaurant (RestaurantID) ON DELETE CASCADE, Constraint chk_rate1 CHECK
(EFRating in ('Green', 'Orange', 'Red')), Constraint chk_delivery CHECK (DeliveryTime
in ('Fast', 'Regular', 'Worth the Wait')));

8. Orders
SQL> Create table Orders (OrderID int primary key, DriverID int references Driver (DriverID)
ON DELETE CASCADE, CustomerID int references Customer (CustomerID) ON
DELETE CASCADE, OrderDate date, OrderDeliveryTime int, ActualDeliveryTime int,
CurrentStatus varchar2(20), Constraint chk_status CHECK (CurrentStatus in
('Processing', 'Cancelled', 'Out for Delivery', 'Fulfilled')));
9. Ordered_Dish
SQL> Create table Ordered_Dish (OrderID int, DishID int, constraint pk_order1 primary key
(OrderID, DishID), constraint fk_order1 foreign key (OrderID) references Orders
(OrderID) ON DELETE CASCADE , constraint fk_order2 foreign key (DishID)
references Dish (DishID) ON DELETE CASCADE );

Inserting Data
1. Certificate
2. Suburb

3. Restaurant
4. Restaurant_Certification

5. Customer
6. Driver

7. Dish
8. Orders

9. Ordered_Dish