how to Create two or more MySQL VIEWS using the Pizza Store Database. Each of the Views must have a SELECT Query that contains two or more tables in the SELECT statements and the attributes of two more tables in the attribute list/projection of the Resultset. **THE REST OF THE CREATE TABLES CODE ARE IN THE PIC** **THE DB: DROP DATABASE IF EXISTS PizzaDelivery_BD; CREATE DATABASE IF NOT EXISTS PizzaDelivery_BD; USE PizzaDelivery_BD; CREATE TABLE IF NOT EXISTS PERSON ( pNum VARCHAR(10), pNume VARCHAR(50) NOT NULL, pAddress VARCHAR(50), isCustomer BOOLEAN, isDelivery BOOLEAN, isOrderStaff BOOLEAN, CONSTRAINT Person_PK PRIMARY KEY (pnum) ); CREATE TABLE IF NOT EXISTS Customer ( phone CHAR(10), email VARCHAR(50), pnum_FK VARCHAR(10), CONSTRAINT Customer_PK PRIMARY KEY (email,pnum_FK ), CONSTRAINT Customer_FK1 FOREIGN KEY (pnum_FK) REFERENCES Person(pnum) ); CREATE TABLE IF NOT EXISTS Order_Staff ( empID VARCHAR(5), pnum_FK VARCHAR(10), CONSTRAINT Order_Staff_PK PRIMARY KEY (empID, pNum_FK), CONSTRAINT Order_Staff_FK1 FOREIGN KEY (pnum_FK) REFERENCES Person(pnum) ); INSERT INTO Person (pnum, pname, paddress, iscustomer, isorderstaff, isdelivery) VALUES ("PID100", "James Kildare", "23 Shewbury court", 1,0,0), ("PID101", "Ben Casey", "281 s 52nd street", 1,0,0), ("PID102", "mARCUS Welby", "5808 Vine Street", 1,0,0), ("PID103", "Leonard Bones McCoy", "16 s. Bank Street", 1,0,0), ("PID104", "Hawkeye Pierce McCoy", "7311 Miller Avenue", 1,0,0), ("PID105", "pHILLIP cHANDLER", "206 Locust Street", 1,0,0), ("PID106", "HEATHCLIFF hUXTABLE", "6600 Broad Street", 1,0,0), ("PID107", "Greg House", "1920 Heather Circle", 0,1,0), ("PID108", "Derek Shepherd", "101 philadelphia Pike", 0,1,0), ("PID109", "Miranda Bailey", "1010 Flourence Circle", 0,1,0), ("PID110", "Barry Sanders", "100 Ering Lane", 0,0,1), ("PID111", "O.J. Simpson, ", "101 broad Street", 0,0,1), ("PID112", "LaDainian Tomlinson", "400 Picatoway Drive", 0,0,1), ("PID113", "Terry Bradshaw", "52 Cheese Head Road", 0,0,1) ; INSERT INTO Customer(pnum_FK, email, phone) VALUES ("PID100", "James@craigle.com", "2155551234"), ("PID101", "Ben@craigle.com", "2155559876"), ("PID102", "Marcus@craigle.com", "2155553456"), ("PID103", "Leonard@craigle.com", "2155558765"), ("PID104", "Hawk@craigle.com", "3025551234"), ("PID105", "Phillip@craigle.com", "2155559876"), ("PID106", "Heat@craigle.com", "3025554567") ; INSERT INTO Order_Staff (pnum_FK, empid) VALUES ("PID107", "T100"), ("PID108", "T200"), ("PID109", "T300") ; INSERT INTO Delivery_Person(pnum_FK, phone, model) VALUES ("PID110", "6105559876", "Civic"), ("PID111", "6105556789", "Beetle"), ("PID112", "6105556754", "F-150"), ("PID113", "2155556567", "Talon") ; INSERT INTO Pizza (item_num, size, price) VALUES ("1", "small",4.99 ), ("2", "medium",5.99 ), ("3", "large",6.99 ), ("4", "exlarge",7.99 ) ; INSERT INTO Topping (item_num, tname, price) VALUES ("10", "Sauge", 1.00), ("T11", "Mushrooms", .50), ("T12", "Onions", .50), ("T13", "Peppers", .50), ("T14", "Perperoni", 1.00), ("T15", "NONE", 0.00) ; INSERT INTO Pizza_order (order_num, odate, otype, empid_FK, cust_FK, deliveryPerson_FK) VALUES ("INV#0001", "2020/03/26", "Delivery", "T100", "PID100", "PID110"), ("INV#0002", "2020/03/26", "Delivery", "T200", "PID101", "PID111"), ("INV#0003", "2020/03/26", "Delivery", "T300", "PID102", "PID112"), ("INV#0004", "2020/03/27", "Delivery", "T100", "PID103", "PID113"), ("INV#0005", "2020/03/27", "Delivery", "T200", "PID104", "PID110"), ("INV#0006", "2020/03/27", "Delivery", "T300", "PID105", "PID111"), ("INV#0007", "2020/03/27", "Delivery", "T100", "PID106", "PID112"), ("INV#0008", "2020/03/28", "Delivery", "T200", "PID104", "PID113"), ("INV#0009", "2020/03/28", "Delivery", "T300", "PID105", "PID110"), ("INV#0010", "2020/03/28", "Delivery", "T300", "PID106", "PID111") ; INSERT INTO Order_Item ( order_num_FK, item_num_FK, autogen) VALUES ("INV#0001", "2", 1), ("INV#0001", "1", 2), ("INV#0002", "2", 3), ("INV#0003", "3", 4), ("INV#0004", "4", 5), ("INV#0005", "1", 6), ("INV#0006", "2", 7), ("INV#0007", "3", 8), ("INV#0008", "4", 9), ("INV#0009", "1", 10), ("INV#0010", "2", 11) ; INSERT INTO Pizza_Toppings (autogen_FK, item_num_FK) VALUES (1,"I10"), (1,"I12"), (2,"I14"), (3,"I11"), (3,"I12"), (3,"I13"), (4,"I12"), (4,"I13"), (5,"I14"), (6,"I14"), (7,"I10"), (8,"I10"), (9,"I10"), (10,"I10"), (10,"I11"), (10,"I12"), (10,"I13"), (10,"I14"), (11,"I15")
how to Create two or more MySQL VIEWS using the Pizza Store Database. Each of the Views must have a SELECT Query that contains two or more tables in the SELECT statements and the attributes of two more tables in the attribute list/projection of the Resultset. **THE REST OF THE CREATE TABLES CODE ARE IN THE PIC** **THE DB: DROP DATABASE IF EXISTS PizzaDelivery_BD; CREATE DATABASE IF NOT EXISTS PizzaDelivery_BD; USE PizzaDelivery_BD; CREATE TABLE IF NOT EXISTS PERSON ( pNum VARCHAR(10), pNume VARCHAR(50) NOT NULL, pAddress VARCHAR(50), isCustomer BOOLEAN, isDelivery BOOLEAN, isOrderStaff BOOLEAN, CONSTRAINT Person_PK PRIMARY KEY (pnum) ); CREATE TABLE IF NOT EXISTS Customer ( phone CHAR(10), email VARCHAR(50), pnum_FK VARCHAR(10), CONSTRAINT Customer_PK PRIMARY KEY (email,pnum_FK ), CONSTRAINT Customer_FK1 FOREIGN KEY (pnum_FK) REFERENCES Person(pnum) ); CREATE TABLE IF NOT EXISTS Order_Staff ( empID VARCHAR(5), pnum_FK VARCHAR(10), CONSTRAINT Order_Staff_PK PRIMARY KEY (empID, pNum_FK), CONSTRAINT Order_Staff_FK1 FOREIGN KEY (pnum_FK) REFERENCES Person(pnum) ); INSERT INTO Person (pnum, pname, paddress, iscustomer, isorderstaff, isdelivery) VALUES ("PID100", "James Kildare", "23 Shewbury court", 1,0,0), ("PID101", "Ben Casey", "281 s 52nd street", 1,0,0), ("PID102", "mARCUS Welby", "5808 Vine Street", 1,0,0), ("PID103", "Leonard Bones McCoy", "16 s. Bank Street", 1,0,0), ("PID104", "Hawkeye Pierce McCoy", "7311 Miller Avenue", 1,0,0), ("PID105", "pHILLIP cHANDLER", "206 Locust Street", 1,0,0), ("PID106", "HEATHCLIFF hUXTABLE", "6600 Broad Street", 1,0,0), ("PID107", "Greg House", "1920 Heather Circle", 0,1,0), ("PID108", "Derek Shepherd", "101 philadelphia Pike", 0,1,0), ("PID109", "Miranda Bailey", "1010 Flourence Circle", 0,1,0), ("PID110", "Barry Sanders", "100 Ering Lane", 0,0,1), ("PID111", "O.J. Simpson, ", "101 broad Street", 0,0,1), ("PID112", "LaDainian Tomlinson", "400 Picatoway Drive", 0,0,1), ("PID113", "Terry Bradshaw", "52 Cheese Head Road", 0,0,1) ; INSERT INTO Customer(pnum_FK, email, phone) VALUES ("PID100", "James@craigle.com", "2155551234"), ("PID101", "Ben@craigle.com", "2155559876"), ("PID102", "Marcus@craigle.com", "2155553456"), ("PID103", "Leonard@craigle.com", "2155558765"), ("PID104", "Hawk@craigle.com", "3025551234"), ("PID105", "Phillip@craigle.com", "2155559876"), ("PID106", "Heat@craigle.com", "3025554567") ; INSERT INTO Order_Staff (pnum_FK, empid) VALUES ("PID107", "T100"), ("PID108", "T200"), ("PID109", "T300") ; INSERT INTO Delivery_Person(pnum_FK, phone, model) VALUES ("PID110", "6105559876", "Civic"), ("PID111", "6105556789", "Beetle"), ("PID112", "6105556754", "F-150"), ("PID113", "2155556567", "Talon") ; INSERT INTO Pizza (item_num, size, price) VALUES ("1", "small",4.99 ), ("2", "medium",5.99 ), ("3", "large",6.99 ), ("4", "exlarge",7.99 ) ; INSERT INTO Topping (item_num, tname, price) VALUES ("10", "Sauge", 1.00), ("T11", "Mushrooms", .50), ("T12", "Onions", .50), ("T13", "Peppers", .50), ("T14", "Perperoni", 1.00), ("T15", "NONE", 0.00) ; INSERT INTO Pizza_order (order_num, odate, otype, empid_FK, cust_FK, deliveryPerson_FK) VALUES ("INV#0001", "2020/03/26", "Delivery", "T100", "PID100", "PID110"), ("INV#0002", "2020/03/26", "Delivery", "T200", "PID101", "PID111"), ("INV#0003", "2020/03/26", "Delivery", "T300", "PID102", "PID112"), ("INV#0004", "2020/03/27", "Delivery", "T100", "PID103", "PID113"), ("INV#0005", "2020/03/27", "Delivery", "T200", "PID104", "PID110"), ("INV#0006", "2020/03/27", "Delivery", "T300", "PID105", "PID111"), ("INV#0007", "2020/03/27", "Delivery", "T100", "PID106", "PID112"), ("INV#0008", "2020/03/28", "Delivery", "T200", "PID104", "PID113"), ("INV#0009", "2020/03/28", "Delivery", "T300", "PID105", "PID110"), ("INV#0010", "2020/03/28", "Delivery", "T300", "PID106", "PID111") ; INSERT INTO Order_Item ( order_num_FK, item_num_FK, autogen) VALUES ("INV#0001", "2", 1), ("INV#0001", "1", 2), ("INV#0002", "2", 3), ("INV#0003", "3", 4), ("INV#0004", "4", 5), ("INV#0005", "1", 6), ("INV#0006", "2", 7), ("INV#0007", "3", 8), ("INV#0008", "4", 9), ("INV#0009", "1", 10), ("INV#0010", "2", 11) ; INSERT INTO Pizza_Toppings (autogen_FK, item_num_FK) VALUES (1,"I10"), (1,"I12"), (2,"I14"), (3,"I11"), (3,"I12"), (3,"I13"), (4,"I12"), (4,"I13"), (5,"I14"), (6,"I14"), (7,"I10"), (8,"I10"), (9,"I10"), (10,"I10"), (10,"I11"), (10,"I12"), (10,"I13"), (10,"I14"), (11,"I15")
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Concept explainers
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Question
how to Create two or more MySQL VIEWS using the Pizza Store
**THE REST OF THE CREATE TABLES CODE ARE IN THE PIC**
**THE DB:
DROP DATABASE IF EXISTS PizzaDelivery_BD;
CREATE DATABASE IF NOT EXISTS PizzaDelivery_BD;
USE PizzaDelivery_BD;
CREATE TABLE IF NOT EXISTS PERSON
(
pNum VARCHAR(10),
pNume VARCHAR(50) NOT NULL,
pAddress VARCHAR(50),
isCustomer BOOLEAN,
isDelivery BOOLEAN,
isOrderStaff BOOLEAN,
CONSTRAINT Person_PK PRIMARY KEY (pnum)
);
CREATE TABLE IF NOT EXISTS Customer
(
phone CHAR(10),
email VARCHAR(50),
pnum_FK VARCHAR(10),
CONSTRAINT Customer_PK PRIMARY KEY (email,pnum_FK ),
CONSTRAINT Customer_FK1 FOREIGN KEY (pnum_FK)
REFERENCES Person(pnum)
);
CREATE TABLE IF NOT EXISTS Order_Staff
(
empID VARCHAR(5),
pnum_FK VARCHAR(10),
CONSTRAINT Order_Staff_PK PRIMARY KEY (empID, pNum_FK),
CONSTRAINT Order_Staff_FK1 FOREIGN KEY (pnum_FK)
REFERENCES Person(pnum)
);
INSERT INTO Person (pnum, pname, paddress, iscustomer, isorderstaff, isdelivery)
VALUES ("PID100", "James Kildare", "23 Shewbury court", 1,0,0),
("PID101", "Ben Casey", "281 s 52nd street", 1,0,0),
("PID102", "mARCUS Welby", "5808 Vine Street", 1,0,0),
("PID103", "Leonard Bones McCoy", "16 s. Bank Street", 1,0,0),
("PID104", "Hawkeye Pierce McCoy", "7311 Miller Avenue", 1,0,0),
("PID105", "pHILLIP cHANDLER", "206 Locust Street", 1,0,0),
("PID106", "HEATHCLIFF hUXTABLE", "6600 Broad Street", 1,0,0),
("PID107", "Greg House", "1920 Heather Circle", 0,1,0),
("PID108", "Derek Shepherd", "101 philadelphia Pike", 0,1,0),
("PID109", "Miranda Bailey", "1010 Flourence Circle", 0,1,0),
("PID110", "Barry Sanders", "100 Ering Lane", 0,0,1),
("PID111", "O.J. Simpson, ", "101 broad Street", 0,0,1),
("PID112", "LaDainian Tomlinson", "400 Picatoway Drive", 0,0,1),
("PID113", "Terry Bradshaw", "52 Cheese Head Road", 0,0,1)
;
INSERT INTO Customer(pnum_FK, email, phone)
VALUES ("PID100", "James@craigle.com", "2155551234"),
("PID101", "Ben@craigle.com", "2155559876"),
("PID102", "Marcus@craigle.com", "2155553456"),
("PID103", "Leonard@craigle.com", "2155558765"),
("PID104", "Hawk@craigle.com", "3025551234"),
("PID105", "Phillip@craigle.com", "2155559876"),
("PID106", "Heat@craigle.com", "3025554567")
;
INSERT INTO Order_Staff (pnum_FK, empid)
VALUES ("PID107", "T100"),
("PID108", "T200"),
("PID109", "T300")
;
INSERT INTO Delivery_Person(pnum_FK, phone, model)
VALUES ("PID110", "6105559876", "Civic"),
("PID111", "6105556789", "Beetle"),
("PID112", "6105556754", "F-150"),
("PID113", "2155556567", "Talon")
;
INSERT INTO Pizza (item_num, size, price)
VALUES ("1", "small",4.99 ),
("2", "medium",5.99 ),
("3", "large",6.99 ),
("4", "exlarge",7.99 )
;
INSERT INTO Topping (item_num, tname, price)
VALUES ("10", "Sauge", 1.00),
("T11", "Mushrooms", .50),
("T12", "Onions", .50),
("T13", "Peppers", .50),
("T14", "Perperoni", 1.00),
("T15", "NONE", 0.00)
;
INSERT INTO Pizza_order (order_num, odate, otype, empid_FK, cust_FK, deliveryPerson_FK)
VALUES ("INV#0001", "2020/03/26", "Delivery", "T100", "PID100", "PID110"),
("INV#0002", "2020/03/26", "Delivery", "T200", "PID101", "PID111"),
("INV#0003", "2020/03/26", "Delivery", "T300", "PID102", "PID112"),
("INV#0004", "2020/03/27", "Delivery", "T100", "PID103", "PID113"),
("INV#0005", "2020/03/27", "Delivery", "T200", "PID104", "PID110"),
("INV#0006", "2020/03/27", "Delivery", "T300", "PID105", "PID111"),
("INV#0007", "2020/03/27", "Delivery", "T100", "PID106", "PID112"),
("INV#0008", "2020/03/28", "Delivery", "T200", "PID104", "PID113"),
("INV#0009", "2020/03/28", "Delivery", "T300", "PID105", "PID110"),
("INV#0010", "2020/03/28", "Delivery", "T300", "PID106", "PID111")
;
INSERT INTO Order_Item ( order_num_FK, item_num_FK, autogen)
VALUES ("INV#0001", "2", 1),
("INV#0001", "1", 2),
("INV#0002", "2", 3),
("INV#0003", "3", 4),
("INV#0004", "4", 5),
("INV#0005", "1", 6),
("INV#0006", "2", 7),
("INV#0007", "3", 8),
("INV#0008", "4", 9),
("INV#0009", "1", 10),
("INV#0010", "2", 11)
;
INSERT INTO Pizza_Toppings (autogen_FK, item_num_FK)
VALUES (1,"I10"),
(1,"I12"),
(2,"I14"),
(3,"I11"),
(3,"I12"),
(3,"I13"),
(4,"I12"),
(4,"I13"),
(5,"I14"),
(6,"I14"),
(7,"I10"),
(8,"I10"),
(9,"I10"),
(10,"I10"),
(10,"I11"),
(10,"I12"),
(10,"I13"),
(10,"I14"),
(11,"I15")
;
Transcribed Image Text:41
CREATE TABLE IF NOT · EXISTS isDelivery_Person
42
43
- VARCHAR (20) NOT NULL,
CHAR(10),
44
model
45
phone
46
pnum_FK VARCHAR (10),
47
48
..CONSTRAINT Delivery_Person_PK PRIMARY KEY (phone, pnum_FK),
49
CONSTRAINT - Delivery_Person_FK1 · FOREIGN-KEY (pnum_FK),
REFERENCES Person(pnum)
50
51
52
);
53
54
CREATE TABLE IF NOT EXISTS Pizza_Order
55
56
odate-... DATE
VARCHAR (5),
CHAR(3),
VARCHAR (5),
.-• VARCHAR (50),
CHAR (10),
57
order_num
58
otype
empid_FK
Email_FK
59
60
61
phone_FK
62
63
... CONSTRAINT · Pizza_order_PK PRIMARY - KEY (order_num),
64
CONSTRAINT Pizza_order_FK1 FOREIGN-KEY (order_FK)
-REFERENCES Order_Staff (empid),
65
66
67
CONSTRAINT Pizza_order_FK1·FOREIGN- KEY (empid),
REFERENCES Order_Staff (empid),
68
69
70
CONSTRAINT - Piza_order_FK2 · FOREIGN - KEY (email_FK)
REFERENCES Customer(email),
71
72
73
CONSTRAINT Pizza_order_FK3 · FOREIGN- KEY (phone_FK)
REFERENCES Delivery_Person(phone)
74
75
76
77
);
78
79
CREATE TABLE IF NOT - EXISTS Pizza
80
CHAR (5),
VARCHAR(2),
81
item_num
82
size
83
price-.... DOUBLE,
84
85
..CONSTRAINT Pizza_PK · PRIMARY-KEY (item_num)
86
87
);
88
89
CONSTRAINT · TABLE IF NOT · EXISTS Order_Item
90
autogen--....
order_num_FK
int auto_increment,
VARCHAR (5),
CHAR (5),
91
92
93
item_num_FK
94
CONSTRAINT order_item_pk PRIMARY KEY(autogen),
REFERENCES Pizza_Order(order_num),
95
96
97
CONSTRAINT order_item_FK2 FOREIGN KEY(item_num_FK)
REFERENCES Pizza(item_num)
98
99
100
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education