7BDIN007W DATA REPOSITORIES PRINCIPLES AND TOOLS
7BDIN007W DATA REPOSITORIES PRINCIPLES AND TOOLS
COURSEWORK
Students Name:
Students ID:
Contents
TOC o "1-3" h z u PART A PAGEREF _Toc138632316 h 2A Sub Part PAGEREF _Toc138632317 h 2B Sub Part PAGEREF _Toc138632318 h 6PART B PAGEREF _Toc138632319 h 8A Sub Part PAGEREF _Toc138632320 h 8B Sub Part PAGEREF _Toc138632321 h 8C Sub Part PAGEREF _Toc138632322 h 9D Sub Part PAGEREF _Toc138632323 h 11
PART AA Sub PartDocumentation in tabular format
Entity name: Outlet
Attribute Domain Constraint
Outlet_Code Varchar Primary key
Outlet_Address Varchar Phone_Number Varchar Fax_Number Varchar Outlet_Manager Varchar Entity name: Vehicle
Attribute Domain Constraint
Vehicle_Registration Varchar Primary key
V_Model Varchar V_Make Varchar Reg_Date Date Engine_Size Integer Transmission_Type Varchar Rental_Class Varchar Rental_Category Varchar Number_of_Doors Integer Number_of_Seats Integer Recent_MOT_Date Date Mileage Integer Allocated_Outlet_code Varchar Foreign key
Entity: Personal_client
Attribute Domain Constraint
Client_ID Varchar Primary key
First_Name Varchar Last_Name Varchar Home_Address Varchar Phone_Number Varchar Email_Address Varchar DOB Date Driving_License Varchar Entity: Corporate_Client
Attribute Domain Constraint
Client_ID Varchar Primary key
Business_Name Varchar Business_Address Varchar Phone_Number Varchar Fax_Number Varchar Email_Address Varchar Type_of_Business Varchar Entity: Booking
Attribute Domain Constraint
Booking_code Varchar Primary key
Pick-up_Location Varchar Drop-off_Location Varchar Rental_Start_Date Date/Time Rental_Start_Time Timestamp Rental_Finish_Date Date Rental_Finish_Time Timestamp Transmission_Type Varchar Rental_Class Varchar Rental_Category Varchar Number_of_Doors Integer Number_of_Seats Integer Daily_Rate Integer Insurance_Cover Varchar Booking_Status Varchar Client_ID Varchar Foreign key
Drivers Varchar Additional_Services Varchar Discount_Codes Varchar Payment Integer Entity: Rent_Agreement
Attribute Domain Constraint
Rent_Agreement_Code Varchar Primary key
Client_id Varchar Foreign key
Driver_Details Varchar Vehicle_Registration Varchar Foreign key
Staff_number Varchar Foreign key
Rental_Start_Date Date/Time Rental _Return_Date Date/Time Daily_Hire_Rate Integer Mileage_Included Integer Insurance_Cover Varchar Fuel_Level Varchar Mileage_at_Start Varchar Faults/Dents Varchar Entity:Staff
Attribute Domain Constraint
Staff_Number Integer Primary key
First_Name Varchar Last_Name Varchar Address Varchar Phone_Number Integer Mobile_Phone Varchar Date_of_Birth Date Gender Varchar National_Insurance_Number Varchar Date_Joined Date Job_Title Varchar Salary Integer Type_of_Contract Varchar Entity: MYEZCAR _Membership
Attribute Domain Constraint
Client_Id Varchar Primary key,Foreign key
MYEZCAR_Benefits Varchar Relationships
There is one to many relationship between outlet and vehicle.
There is one to one relationship between booking and client.
There is one to one relationship between rental agreement and client.
There is one to one relationship between client and Myezcar benefits.
There is one to one relationship between staff and outlet.
Conceptual ERD
Figure 1 Shows Conceptual ERD
B Sub PartOutlet(Outlet _Code, Outlet_Address, Phone_Number, Fax_Number, Outlet_Manager)
Vehicle(Vehicle_Registration,V_Model,V_Make, Reg_Date, Engine_Size, Transmission_Type, Rental_Class, Rental_Category, Number_of_Doors, Number_of_Seats, Recent_MOT_Date, Mileage, Allocated_Outlet_code)
Personal_Client(Client_ID, First_Name, Last_Name, Home_Address, Phone_Number, Email_Address, DOB, Driving_License)
Corporate_Client(Client_ID, Business_Name, Business_Address, Phone_Number, Fax_Number, Email_Address, Type_of_Business)
Booking(Booking_code, Pick-up_Location, Drop-off_Location, Rental_Start_Date, Rental_Start_time, Rental_Finish_Date, Rental_Finish_time, Transmission_Type, Rental_Class, Rental_Category, Number_of_Doors, Number_of_Seats, Daily_Rate, Insurance_Cover, Booking_Status, Client_id, Drivers, Additional_Services, Discount_Codes, Payment)
Rental Agreement(Rental_Agreement_Code, Client_id, Drivers_details,Vehicle_registration, Staff_number, Rental_Start_Date, Rental_Return_Date, Daily_Hire_Rate, Mileage_Included, Insurance_Cover, Fuel_Level, Mileage_at_Start, Faults/Dents)
Staff (Staff_Number, First_Name, Last_Name, Address, Phone_Number, Mobile_Phone,Date_of_Birth, National_insurancce_number, Date_Joined, Job_title, Salary, Type_of_Contract)
MYEZCAR_Membership(Client_ID,MYEZCAR_Benefits)
All the entities are determined and their attributes are noted. The primary keys of each of the entities is also identified. The foreign keys are determined for each of the relation to create the logical ERD
PART BA Sub PartThe steps are
Finding the closure set of {B, C}. Closure = {B, C}
The functional dependencies are iterated and new attributes are added to closure set.
B DG
Closure = {B, C, D, G}
CD A
Closure = {A, B, C, D, G}
A E
Closure = {A, B, C, D, E, G}
AC B
Closure = {A, B, C, D, E, G}
ABE C
Closure = {A, B, C, D, E, G}
AG BC
Closure = {A, B, C, D, E, G}
AD EG
Closure = {A, B, C, D, E, G}
Therefore, {, }F+= {A, B, C, D, E, G}
B SubPart{C, E} is not candidate key
Justification
F = {CEA, ABD, BDC}, and we need to find {C,E}+F
For functional dependency, CE->A
{C,E}+F = {A, C,E}
The closure does not contains all the attributes of R therefore, {C, E} is not the candidate key.
{B, C, E} is a candidate key
Justification:
We need to calculate {B,C,E}+F
For functional dependency, CE->A
{B,C,E}+F = {C, E, A}
For functional dependency, ABD
{B,C,E}+F = {A, B, C, E, D}
For functional dependency, BDC
{B,C,E}+F = {A, B, C, D, E}
The closure contains all the attributes of R therefore, {B,C, E} is the key of R.
The subset of {B,C, E} must not be key in order to {B,C, E} become the candidate key. No singleton subset of {B,C, E} is the key and there are no functional dependencies with the determinants that contain only one attribute therefore, {B,C, E} is also candidate key.
C Sub PartLogical ERD
The steps are
Identify all the entities in the ERD. The entities include
Candidate
Assess_Centre
Exam
Certificate
Awarding_body
Enrolment
Pre-Requisite
Assessment
Identification of attributes of each of the entities
Candidate(C_id, c_name, c_addr, c_dob,c_gender, c_email)
Assess_Centre(ac_code,ac_addr)
Exam( ex_code,ex_title)
Certificate(cert_id,cert_title, cert_type, bd_id)
Awarding_body(bd_id,bd_name,bd_addr,bd_tel_no,bd_url)
Enrolment(c_id,cert_id,enrol_no, enrol_dt,completion_dt)
Pre-Requisite(ex_code,pre_ex_code)
Assessment(c_id,ac_code,ex_code,ass_dt,ass_tm,score,outcome)
Identification of the primay keys and making them bold and underline
Candidate(C_id,c_name, c_addr, c_dob,c_gender, c_email)
Assess_Centre(ac_code,ac_addr)
Exam( ex_code,ex_title)
Certificate(cert_id,cert_title, cert_type, bd_id)
Awarding_body(bd_id,bd_name,bd_addr,bd_tel_no,bd_url)
Enrolment(c_id,cert_id,enrol_no, enrol_dt,completion_dt)
Pre-Requisite(ex_code,pre_ex_code)
Assessment(c_id,ac_code,ex_code,ass_dt,ass_tm,score,outcome)
The primary key of the awarding_body relation has been made the foreign key of the certificate relation.
Resolution of M:N relationships
The enrolls for relationship has been resolved by the introduction of the enrolment table which has a relationship with the certificate and candidate.
The primary key of the certificate and candidate relation has been made the foreign key of the enrolment relation.
Recursive relationship
The has pre requisite recursive relationship has been resolved by the introduction of the pre requisite table. and two relationships has and is pre requisite for.
The primary key of the exam relation has been made the foreign key of the pre requisite relation and assessment relation.
Ternary relationship
Registersrelationship has been resolved by introduction of the assessment table. This relation is linked to three entities including exam, assess_centre and candidate.
The primary key of the assess_centre relation has been made the foreign key of the assessment relation
The primary key of the candidate relation has been made the foreign key of the assessment relation.
D Sub PartThe normalized relations are created in following steps.
First normal form
The primary keys are identified based on the given functional dependencies and it comes out to be combination of attributes including job_no, s_no, and task_no.
STAFF_ACC primary keys are {job_no, s_no, task_no}.
Second normal form
The attributes that are dependent on any part of the primary key are removed.
Using fd3 (job_no, task_no task_dt, task_desc, hours)
Relation:
Tasks (job_no, task_no, task_dt, task_descr, hours)
Using fd2 (job_no, s_no s_nm, salary, country, job_dt, job_desc, job_type)
Relation:
STAFF_JOB (job_no, s_no,job_dt, job_desc, job_type)
Using fd1 (s_no s_nm, salary, country)
Relation:
STAFF (s_no,s_nm, salary, country)
Third normal form
There are no transitive dependencies in the non primary attributes of the relation therefore, relations follow the third normal form.
BCNF
The relations are in BCNF as all the functional dependencies have determinants as the part of the primary key.
The normalized relations are
TASKS (job_no, task_no, task_dt, task_descr, hours)
STAFF (s_no, s_nm, salary, country)
STAFF_JOB (job_no, s_no,job_dt, job_desc, job_type)
STAFF_ACC (job_no, s_no, task_no)
The foreign keys are
STAFF_ACC (job_no, s_no) References STAFF_JOB (job_no, s_no)
STAFF_ACC (job_no, task_no) References TASKS(job_no, task_no)
STAFF_JOB (s_no) References STAFF(s_no)
Assumptions
The complete functional dependencies are given.
There are no additional dependencies apart from given dependencies.
INTRODUCTION TO THE CASE STUDYPapajons Pizza is a pizza restaurant chain where customers can choose the varieties of pizza base and the topping(s) with an eat in and home delivery service from several outlets. The recent lockdowns made an impact on the business and revenue for eat in was decreased whereas the revenue for home delivery services got increased. With the current scenario, companys management wish to organise and make home delivery services more efficient. We as a part of IT team have taken the responsibilities to design a data model to process and monitor the requirement mentioned in the case study.
PHASE ITabular Format to Identify Entities, Attributes and RelationshipAfter making careful analysis over the case study, below tabular format displays each entity and its attributes along with the relationship table further in the report.
Outlet
Attributes Domain Name Meaning Domain Definition Constraints
outlet_idOutlet Number The set of all possible outlet numbers Number: Size 10 Primary Key
phone_noPhone Number Possible values of phone numbers Number: Size 10 Unique
postcode Postcode The set of all possible postcodes Character: Size 6 Not Null
address Full Address The set of all possible address across the nation Character: Size 200 Not Null
name Full Name Full name of the outlet with address indicator. Ex: Papajons Wembley or Papajons Westminster Character: Size 50 Not Null
serving_area_postcode [1..*] Serving area postcode The set of all possible postcodes for Papajon's serving area Character: Size 200 Unique
Pizza_ToppingsAttributes Domain Name Meaning Domain Definition Constraints
topping_idTopping ID The set of all possible topping ID Number: Size 10 Primary Key
topping_typeTopping Types The set of all possible topping type Character: Size 20 Not Null
topping_priceTopping Price Possible values of topping price Number: Size 5,2 Not Null
Pizza_baseAttributes Domain Name Meaning Domain Definition Constraints
base_idPizza Base ID The set of all possible pizza base ID Number: Size 10 Primary Key
base_typePizza Base Type The set of all possible base type Character: 20Base Type : thin crust, deep pan, stuffed crust Not Null
base_pricePizza Base Price Possible values of base price Number: Size 5,2 Not Null
Pizza
Attributes Domain Name Meaning Domain Definition Constraints
pizza_idPizza ID The set of all possible pizza ID Number: Size 10 Primary Key
pizza_pricePizza Price Possible values of pizza price Number: Size 5,2 Not Null
qty Pizza Quantity Possible values of pizza quantity Number: Size 10 Not Null
Drivers
Attributes Domain Name Meaning Domain Definition Constraints
driver_idDriver ID The set of all possible driver IDs Number: Size 10 Primary Key
driver_nameDriver Name Full name of the driver Character: Size 20 Not Null
driver_addressDriver Address Full address of the driver Character: Size 200 Not Null
driver_DOBDriver's Date of Birth Possible values of driver's date of birth Date:
DD/MM/YYYY Not Null
driving_license_
number Driver's License Number Possible values of driver's driving license Characters/Number: Size 15 Unique
Customer
Attributes Domain Name Meaning Domain Definition Constraints
cust_noCustomer Number The set of all possible customer ID Number: Size 10 Primary Key
reg_dateDate of registration Possible values of customer registration date Date:
DD/MM/YYYY Not Null
password Password The set of all possible password characters Characters/Number: Size 10 Not Null
mob_numberContact Number Possible values of customer contact number Number: Size 10 Unique
email_idEmail ID Email address of the registered customer Characters/Number: Size 50 Unique
last_nameLast Name Last name of the customer Characters: Size 50 Not Null
first_nameFirst Name First name of the customer Characters: Size 20 Not Null
cust_addressCustomer Full Address The set of all possible address across the nation Characters: Size 100 Not Null
Delivery_TripAttributes Domain Name Meaning Domain Definition Constraints
delivery_idDelivery ID The set of all possible delivery IDs Number: Size 10 Primary Key
departure _time Departure Time Possible values of departure time Time:
HH:MM:SSDate:
DD/MM/YYYY Not Null
arrival_timeArrival Time Possible values of arrival time Time:
HH:MM:SSDate:
DD/MM/YYYY Nullable
Payment
Attributes Domain Name Meaning Domain Definition Constraints
payment_idPayment ID The set of all possible payment IDs Number: Size 10 Primary Key
payment_card_
details Payment card details Possible values of payment card Number: Size 10 Unique
card_numberCard Number Possible values of card number Number: Size 16 Unique
cardholder_nameCardholder Name The name of Cardholder Character: Size 50 Not Null
cardholder_
address Cardholder Address Address of Cardholder Character: Size 200 Not Null
expiry_dateExpiry Date Date of expiry of card Date:
DD/MM/YYYY Not Null
security_numberSecurity Number Security number behind the payment card Number: Size 3 Not Null
card_issue_dateCard Issue Date Card issuing date Date:
DD/MM/YYYY Not Null
card_issue_
number Card issue Number Card issuing number Number: Size 10 Not Null
authorisation_
number Payment Card Authorisation Number Possible set of details for authorisation number Number: Size 15 Not Null
payment_typePayment Type The set of all payment types Character: Size 10(online/cash) Not Null
payment_amountPayment Amount Possible values of final payment amount Number: Size 10 Not Null
payment_amount_
after_discountPayment Amount After Discount Possible values of payment amount after applying discount Number: Size 5,2 Nullable
Discount_VoucherAttributes Domain Name Meaning Domain Definition Constraints
voucher_idVoucher ID The set of all possible voucher ID Number: Size 10 Primary Key
vouchers_codeVoucher Code The set of all vouchers code Character: Size 10 Unique
description Voucher Description The details of the voucher which includes info, T&C Character: Size 100
Not Null
issue_dateVoucher Issue Date Possible values of discount voucher issue date Date:
DD/MM/YYYY Not Null
expire_dateVoucher Expire Date Possible values of discount voucher expiry date Date: DD/MM/YYYY Not Null
Motorbikes
Attributes Domain Name Meaning Domain Definition Constraints
motorbikes_idMotorbike ID The set of all possible motorbikes ID Number: Size 10 Primary Key
registration_noRegistration Number Possible values of bike's registration number Number: Size 10 Unique
engine_sizeEngine Size Possible values of bike's engine size Number: Size 2,2 Not Null
Order
Attributes Domain Name Meaning Domain Definition Constraints
order_idOrder ID The set of all possible order ID Number: Size 10 Primary Key
unreg_cust_nameUnregistered Customer Full Name Full name of unregistered customer Character: Size 20 Not Null
unreg_cust_phone_noUnregistered Customer Phone Number Possible values of unregistered customer contact number Number: Size 10 Unique
order_datetimeOrder Date and Time Possible values of order date and time Time:
HH:MM:SSDate:
DD/MM/YYYY Not Null
order_amtOrder Amount Possible values of order amount Number: Size 5,2 Not Null
order_statusOrder Status Possible set of Order status Character: Size 10 Not Null
collection_timeOrder Collection Time Possible values of order collection time Time:
HH:MM:SSDate:
DD/MM/YYYY Not Null
advance_booking_
datetime Order Advance Booking Date and Time Possible values of advance order date and time Time:
HH:MM:SSDate:
DD/MM/YYYY Nullable
order_modeOrder Mode The set of all possible order mode Number: Size 10 Not Null
delivery_addressOrder Delivery Address Customer full address Character: Size 200 Nullable
delivery_est_timeOrder Delivery Estimated Time Possible values of order estimated delivery time Time:
HH:MM:SSDate:
DD/MM/YYYY
Not Null
order_delivery_timeActual Order Delivery Time Possible values of actual order delivery time Time:
HH:MM:SSDate:
DD/MM/YYYY Not Null
Existing Relationships among Entities
Relationship Name Type Entity A Entity B
places Binary Customer Order
allocate_toBinary Order Outlet
prepares Binary Outlet Pizza
has_aBinary Order Pizza
determines Ternary Pizza_toppingPizza
determines Ternary Pizza_basePizza
determines Ternary Pizza_sizePizza
available_atTernary Pizza_toppingPizza
available_atTernary Pizza_basePizza
available_atTernary Pizza_sizePizza
complimentary_toBinary Discount_voucherCustomer
late_delivery_discountBinary Order Discount_voucherapply_toBinary Discount_voucherPayment
confirms Binary Payment Order
include Binary Delivery_TripOrder
assign_toBinary Delivery_TripDrivers
involve Binary Motorbikes Delivery_tripuse_availableBinary Drivers Motorbikes
Assumptions
Before designing the EERD, below are some assumptions that has been considered
If there is a pizza, there must be a topping.
Outlet Topping (1..*)
At least one topping, one base, one size is required to determine a pizza.
Outlet should have at least one pizza (base, topping, size)
Outlet Pizza (1..*)
One outlet in one service area should have at least one pizza
Order should have at least one pizza
Order Pizza (1..*)
For a pizza available in system has a possibility that there is no order for that pizza.
Pizza Order (0..*)
There is a possibility for order, that there is no delivery. Example: during festive season, with high volume of orders.
No delivery trip is possible without bike and driver.
Possibility of bike and driver not getting any delivery trip reason being they are newly introduced in the system and no delivery assigned to both.
Biker can choose from many available bike options.
Bike should have at least one driver to use, and bike can be used by multiple drivers (not at a same time).
Driver should choose at least one bike or any available bikes.
Each discount voucher is uniquely assigned to the customer.
One outlet should have at least one order.
Conceptual E-ERDThe EERD is designed using Draw.io tool [2]. Relationships are determined in order to relate the entities using lines and labels. More detail is layered by adding key attributes of entities. Cardinality shows the relationship is 0-1,1-1, 1-many or many-to-many.
Figure 1: EERD modelPHASE IIWe have decided to work with Oracle SQL to design relational schema equivalent to Logical EERD (Figure 1). This part will turn the derived logical model into a relational model. The steps to transform a relational model are as follows:
Map Entity to Relation (Relational table)
To convert logical entities to relational tables, the attributes of strong entities are mapped to relational tables and the relational table is named against the entity name. One of the attributes of the strong entities is chosen as the relational table's primary key.
The properties of a weak entity are mapped to a relational table with a primary key and a foreign key, which is generated from the primary key of the associated strong entities.
If the entity has a composite attribute, then only the simple attributes of that composite attribute has been included in the relational table.
After doing a thorough analysis, the following relation tables are derived from the conceptual diagram. The payment entity has a composite attribute which is converted into a simple attribute in the relational table.
-91440923300customers(cust_no, reg_date, password, mob_number, email_id, first_name, last_name, cust_address)
orders(order_id, unreg_cust_name, unreg_cust_phone_no, order_datetime, order_amt, order_status, collection_time, advance_booking_datetime, order_mode, delivery_address, delivery_est_time, order_delivery_time)
outlet(oulet_id, phone_no, postcode, address, name, serving_area_postcode)
pizza(pizza_id, pizza_price, qty)
topping(topping_id, topping_type, topping_price)
base(base_id, base_type, base_price)
size(size_id , size)
payment(payment_id, card_number, cardholder_name, cardholder_address, expiry_date, security_number, card_issue_date, card_issue_number, authorization_number, payment_type, payment_amount, payment_amount_after_discount)
discount_voucher(vourcher_id, vouchers_code, description, issue_date, expiry_date)
delivery_trip (delivery_id, departure_time, arrival_time)
drivers (driver_id, driver_name, driver_address, driver_dob, driving_license_number)
motorbikes (motorbikes_id, registration_no, engine_size)
Map Entity relationship to RelationAfter mapping the Entities to the relational table, the relationship between two entities to the relational tables is mapped as followed:
-70338786400places(cust_no, order_id)
has_a(order_id, pizz_id)
allocate_to(order_id, outlet_id)
prepare(oulet_id, pizza_id)
determines(pizza_id, topping_id, size_id, base_Id)
associate_with(size_id, topping_id )
link_with(size_id, base_Id)
availabile_at(outlet_id, topping_id, size_id, base_Id)
prepare(outlet_id, pizza_id)
confirm(payment_id, order_id)
include(delivery_id, order_id)
apply_to(payment_id, voucher_id)
late_delivery_discount(order_id, voucher_id)
complementary_to(cust_no, voucher_id)
assign_to(delivery_id, driver_id)
involve(delivery_id, motorbikes_id)
use_available(driver_id, motorbikes_id)
Resulting SchemaThe relationships between two rational tables have been be handled as below:
One-to-One relationship: The relational table at the mandatory end of the relation should be amended by passing the primary key of the other table as a foreign key.
One-to-Many Relationship: The relational table at the * (Many) end of the relation should be amended by passing the primary key of the other tables primary key as a foreign key.
Many-to-Many Relationship:The new relational table should be created using the primary keys of both tables and include the required attribute.
-6330523211700Based on the relationship rules, the resulting schema has been derived as follows:
customers (cust_no, reg_date, password, mob_number, email_id, first_name, last_name, cust_address)
orders (order_id, unreg_cust_name, unreg_cust_phone_no, order_datetime, order_amt, order_status, collection_time, advance_booking_datetime, order_mode, delivery_address, delivery_est_time, order_delivery_time, oulet_id, delivery_id, cust_no)
outlet (oulet_id, phone_no, postcode, address, name, serving_area_postcode)
pizza (pizza_id, qty, pizza_price, base _id, size_id)
size (size_id , size)
topping (topping_id, topping_type, topping_price, size_id)
base (base _id, base_type, base_price, size_id)
payment (payment_id, card_number, cardholder_name, cardholder_address, expiry_date, security_number, card_issue_date, card_issue_number, authorization_number, payment_type, payment_amount, payment_amount_after_discount, order_id, , voucher_id)
discount_voucher(voucher_id, vouchers_code, description, issue_date, expiry_date, cust_no, order_id)
delivery_trip (delivery_id, departure_time, arrival_time, orders, driver_id, motorbikes_id)
motorbikes (motorbikes_id, registration_no, engine_size)
drivers (driver_id, driver_name, driver_address, driver_dob, driving_license_number)
driver_motorbikes (d_m_mapping_id, driver_id, motorbikes_id)
pizza_topping (p_t_mapping_id, topping_id, pizza_id)
pizza_outlet (p_out_mapping_id, outlet_id, pizza_id)
pizza_order (p_o_mapping_id, order_id, pizza_id)
available_at (o_t_s_b_id, topping_id, size_id, outlet_id, base_id)
Primary key and foreign keysThe list of primary keys and foreign keys of the resulting schema is explained in the following tables
Table 1: List of Foreign keysReferencing Table Referencing Column (s) Target Table Target Column (s)
orders cust_nocustomers cust_noorders outlet_idoutlet outlet_idorders delivery_iddelivery_tripdelivery_idpizza base_idbase base_idpizza size_idsize size_idtopping size_idsize size_idbase size_idsize size_idpayment order_idorder order_idpayment voucher_iddiscount_vouchervoucher _id
discount_vouchercust_nocustomers cust_nodiscount_voucherorder_idorder order_iddelivery_tripdriver_iddrivers driver_iddelivery_tripmotorbikes_idmotorbikes motorbikes_iddriver_motorbikesdriver_iddrivers driver_iddriver_motorbikesmotorbikes_idmotorbikes motorbikes_idpizza_toppingtopping_idtopping topping_idpizza_toppingpizza_idpizza pizza_idpizza_outlet outlet_id, outlet outlet_id,
pizza_outlet pizza_idpizza pizza_idpizza_order order_idorders order_idpizza_order pizza_idpizza pizza_idavailable_at topping_idtopping topping_idavailable_at size_idsize size_idavailable_at outlet_idoutlet outlet_idavailable_at base_idbase base_idTable 2: List of Primary KeysPrimary Keys
customers cust_noorders order_idoutlet outlet_idpizza pizza_idtopping topping_idbase base_idsize size_idpayment payment_iddiscount_vouchervoucher_iddelivery_tripdelivery_iddrivers driver_idmotorbikes motorbikes_idRelational Diagram
Due to clogging of the content, Figure 2 is added on page 15 of this report. To visualize relational diagram more clearly, PDF attachment is provided in appendix section of this report.
Figure 2: Relation Schema
REFERENCE[1] Oracle.com. 2022. [online] Available at: <https://www.oracle.com/uk/database/sqldeveloper/>
[2] draw.io. [online] Available at: <https://drawio-app.com/>
APPENDIXEERD Drafts
Figure 3: EERD Draft Version 1
Figure 4: EERD Draft Version 2
Figure 5: EERD Draft Version 3
PDF for Relational Diagram
DDL ScriptCREATE TABLE base (
base_id NUMBER(10) NOT NULL,
base_type VARCHAR2(20) NOT NULL,
base_price NUMBER(5, 2) NOT NULL,
size_id NUMBER(10) NOT NULL
);
ALTER TABLE base ADD CONSTRAINT base_pk PRIMARY KEY ( base_id );
CREATE TABLE customer (
cust_no NUMBER(10) NOT NULL,
reg_date DATE NOT NULL,
password VARCHAR2(10) NOT NULL,
mob_number NUMBER(10) NOT NULL,
email_id VARCHAR2(50) NOT NULL,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(50),
cust_address VARCHAR2(100)
);
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( cust_no );
CREATE TABLE delivery_trip (
delivery_id NUMBER(10) NOT NULL,
departure_time DATE NOT NULL,
arrival_time DATE,
driver_id NUMBER(10) NOT NULL,
moterbike_id NUMBER(10) NOT NULL
);
ALTER TABLE delivery_trip ADD CONSTRAINT delivery_trip_pk PRIMARY KEY ( delivery_id );
CREATE TABLE driver_motorbikes (
d_m_mapping_id NUMBER(10) NOT NULL,
driver_id NUMBER(10) NOT NULL,
motorbikes_id NUMBER(10) NOT NULL
);
ALTER TABLE driver_motorbikes ADD CONSTRAINT driver_motorbikes_pk PRIMARY KEY ( d_m_mapping_id ); CREATE TABLE discount_voucher (
voucher_id NUMBER(10) NOT NULL,
vouchers_code VARCHAR2(10) NOT NULL,
description VARCHAR2(100) NOT NULL,
issue_date DATE NOT NULL,
expiry_date DATE,
cust_no NUMBER(10) NOT NULL,
order_id NUMBER(10) NOT NULL
);
ALTER TABLE discount_voucher ADD CONSTRAINT discount_voucher_pk PRIMARY KEY ( voucher_id );
CREATE TABLE drivers (
driver_id NUMBER(10) NOT NULL,
driver_name VARCHAR2(20) NOT NULL,
driver_address VARCHAR2(200) NOT NULL,
driver_dob DATE,
driving_license_number NUMBER(16) NOT NULL
);
ALTER TABLE drivers ADD CONSTRAINT drivers_pk PRIMARY KEY ( driver_id );
CREATE TABLE motorbikes_id (
motorbikes_id NUMBER(10) NOT NULL,
registration_no VARCHAR2(10) NOT NULL,
engine_size NUMBER(2, 2)
);
ALTER TABLE motorbikes_id
ADD CONSTRAINT motorbikes_id_pk PRIMARY KEY ( motorbikes_id ); CREATE TABLE orders (
order_id NUMBER(10) NOT NULL,
unreg_cust_name VARCHAR2(20),
unreg_cust_phone_no NUMBER(10),
order_datetime DATE NOT NULL,
order_amt NUMBER(5, 2),
order_status VARCHAR2(10),
collection_time DATE,
order_outlet NUMBER(10) NOT NULL,
advance_booking_datetime DATE,
order_mode VARCHAR2(10),
delivery_est_time DATE,
delivery_address VARCHAR2(200),
order_delivery_time DATE,
cust_no NUMBER(10) NOT NULL,
outlet_id NUMBER(10) NOT NULL,
delivery_id NUMBER(10)
);
ALTER TABLE orders
ADD CONSTRAINT orders_pk
PRIMARY KEY ( order_id );
CREATE TABLE outlet (
oulet_id NUMBER(10) NOT NULL,
phone_no NUMBER(10) NOT NULL,
postcode VARCHAR2(6) NOT NULL,
address VARCHAR2(200) NOT NULL,
name VARCHAR2(50) NOT NULL,
serving_area_postcode VARCHAR2(200) NOT NULL
);
ALTER TABLE outlet
ADD CONSTRAINT outlet_pk
PRIMARY KEY ( oulet_id ); CREATE TABLE payment (
payment_id NUMBER(10) NOT NULL,
card_number NUMBER(16) NOT NULL,
cardholder_name VARCHAR2(50) NOT NULL,
cardholder_address VARCHAR2(200) NOT NULL,
expiry_date DATE NOT NULL,
security_number INTEGER NOT NULL,
card_issue_date DATE NOT NULL,
card_issue_number NUMBER(10),
authorization_number NUMBER(15) NOT NULL,
payment_type VARCHAR2(10) NOT NULL,
voucher_id NUMBER(10) NOT NULL,
payment_amount NUMBER(5, 2) NOT NULL,
payment_amount_after_discount NUMBER(5, 2) NOT NULL,
order_id NUMBER(10) NOT NULL
);
ALTER TABLE payment ADD CONSTRAINT payment_pk PRIMARY KEY (payment_id);
CREATE TABLE pizza (
pizza_id NUMBER(10) NOT NULL,
pizza_price NUMBER(5, 2) NOT NULL,
qty INTEGER NOT NULL,
base_id NUMBER(10) NOT NULL,
pizza_size_id NUMBER(10) NOT NULL
);
ALTER TABLE pizza ADD CONSTRAINT pizza_pk PRIMARY KEY ( pizza_id ); CREATE TABLE pizza_order (
p_o_mapping_id NUMBER(10) NOT NULL,
order_id NUMBER(10) NOT NULL,
pizza_id NUMBER(10) NOT NULL
);
ALTER TABLE pizza_order ADD CONSTRAINT pizza_order_pk PRIMARY KEY ( p_o_mapping_id );
CREATE TABLE pizza_outlet (
p_out_mapping_id NUMBER(10) NOT NULL,
outlet_id NUMBER(10) NOT NULL,
pizza_id NUMBER(10) NOT NULL
);
ALTER TABLE pizza_outlet ADD CONSTRAINT pizza_outlet_pk PRIMARY KEY ( p_out_mapping_id ); CREATE TABLE pizza_topping (
p_t_mapping_id NUMBER(10) NOT NULL,
topping_id NUMBER(10) NOT NULL,
pizza_id NUMBER(10) NOT NULL
);
ALTER TABLE pizza_topping ADD CONSTRAINT pizza_topping_pk PRIMARY KEY ( p_t_mapping_id );
CREATE TABLE "Size" (
size_id NUMBER(10) NOT NULL,
"size" INTEGER NOT NULL
);
ALTER TABLE "Size" ADD CONSTRAINT size_pk PRIMARY KEY ( size_id ); CREATE TABLE topping (
topping_id NUMBER(10) NOT NULL,
topping_type VARCHAR2(20) NOT NULL,
topping_price NUMBER(5, 2) NOT NULL,
size_id NUMBER(10) NOT NULL
);
ALTER TABLE topping ADD CONSTRAINT topping_pk PRIMARY KEY ( topping_id );
ALTER TABLE base
ADD CONSTRAINT base_size_fk
FOREIGN KEY ( size_id )
REFERENCES "Size" ( size_id ); ALTER TABLE delivery_tripADD CONSTRAINT delivery_trip_drivers_fk FOREIGN KEY ( driver_id )
REFERENCES drivers ( driver_id );
ALTER TABLE delivery_tripADD CONSTRAINT delivery_trip_motorbikes_id_fk
FOREIGN KEY ( moterbike_id )
REFERENCES motorbikes_id ( motorbikes_id ); ALTER TABLE discount_voucherADD CONSTRAINT discount_voucher_customer_fk
FOREIGN KEY ( cust_no )
REFERENCES customer ( cust_no );
ALTER TABLE discount_voucher
ADD CONSTRAINT discount_voucher_orders_fk
FOREIGN KEY ( order_id )
REFERENCES orders ( order_id ); ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk FOREIGN KEY ( cust_no )
REFERENCES customer ( cust_no );
ALTER TABLE orders
ADD CONSTRAINT orders_delivery_trip_fk FOREIGN KEY ( delivery_id )
REFERENCES delivery_trip ( delivery_id ); ALTER TABLE orders
ADD CONSTRAINT orders_outlet_fk FOREIGN KEY ( outlet_id )
REFERENCES outlet ( oulet_id );
ALTER TABLE payment
ADD CONSTRAINT payment_discount_voucher_fk
FOREIGN KEY ( voucher_id )
REFERENCES discount_voucher ( voucher_id ); ALTER TABLE payment
ADD CONSTRAINT payment_orders_fk FOREIGN KEY ( order_id )
REFERENCES orders ( order_id );
ALTER TABLE pizza
ADD CONSTRAINT pizza_base_fk
FOREIGN KEY ( base_id )
REFERENCES base ( base_id );
ALTER TABLE pizza_toppingADD CONSTRAINT table_13_pizza_fk FOREIGN KEY ( pizza_id )
REFERENCES pizza ( pizza_id ); ALTER TABLE pizza
ADD CONSTRAINT pizza_size_fk
FOREIGN KEY ( pizza_size_id )
REFERENCES "Size" ( size_id );
ALTER TABLE pizza_toppingADD CONSTRAINT table_13_topping_fk FOREIGN KEY ( topping_id )
REFERENCES topping ( topping_id );
ALTER TABLE pizza_orderADD CONSTRAINT table_16_orders_fk FOREIGN KEY ( order_id )
REFERENCES orders ( order_id ); ALTER TABLE pizza_orderADD CONSTRAINT table_16_pizza_fk FOREIGN KEY ( pizza_id )
REFERENCES pizza ( pizza_id );
ALTER TABLE pizza_outletADD CONSTRAINT table_23_outlet_fk FOREIGN KEY ( outlet_id )
REFERENCES outlet ( oulet_id ); ALTER TABLE pizza_outletADD CONSTRAINT table_23_pizza_fk FOREIGN KEY ( pizza_id )
REFERENCES pizza ( pizza_id );
ALTER TABLE driver_motorbikesADD CONSTRAINT table_24_drivers_fk FOREIGN KEY ( driver_id )
REFERENCES drivers ( driver_id ); ALTER TABLE driver_motorbikesADD CONSTRAINT table_24_motorbikes_id_fk
FOREIGN KEY ( motorbikes_id )
REFERENCES motorbikes_id ( motorbikes_id );
ALTER TABLE topping
ADD CONSTRAINT topping_size_fk
FOREIGN KEY ( size_id )
REFERENCES "Size" ( size_id ); CREATE TABLE available_at ( o_t_s_b_id NUMBER(10) NOT NULL, "outlet_id " NUMBER(10) NOT NULL, topping_id NUMBER(10) NOT NULL, "size_id " NUMBER(10) NOT NULL, "base_Id " NUMBER(10) NOT NULL)
ALTER TABLE available_at ADD CONSTRAINT available_at_base_fk FOREIGN KEY ( "base_Id " ) REFERENCES base ( base_id ) ALTER TABLE available_at ADD CONSTRAINT available_at_outlet_fk FOREIGN KEY ( "outlet_id " ) REFERENCES outlet ( oulet_id )
ALTER TABLE available_at ADD CONSTRAINT available_at_size_fk FOREIGN KEY ( "size_id " ) REFERENCES "Size" ( size_id ) ALTER TABLE available_at ADD CONSTRAINT available_at_topping_fk FOREIGN KEY ( topping_id ) REFERENCES topping ( topping_id )
