Reference no: EM132843723

Lab - Interconnecting and Expressing Data

Overview of the Lab

One purpose of this lab it to teach you how to meaningfully relate data and to answer questions using related data, using SQL. In the prior lab you learned the fundamentals creating and using tables to store data, which is a good introduction to SQL. To be effective, however, you need to know how to work with relationships that naturally occur in the data you work with.

Section - Relating Data

1. Create the Pizza and Toppings tables, including all of their columns, datatypes, and constraints, including the foreign key constraint.

2. Insert at least four rows into the Pizza table. Two of the rows should have the values given below.

Pizza 1
name = Plain
date_available = 6/15/2020
price = $9.99
toppings: This pizza has no toppings at all.

Pizza 2
name = Veggie Delight
date_available = 9/20/2020
price = $10.99
toppings: You choose at least two veggie toppings for this pizza.

For the other rows, you insertthe ids, names, dates, prices, and toppings of your choosing (maybe you have some favorite pizzas?). Ensure that these other pizzas you create have at least two toppings.

Lastly, insert an extra topping that is not associated with any pizza, that is, the topping should be an "add-on" which is not included inany pizza's standardtoppings.

Select all rows in both tables to view what you inserted.

3. As an exercise, attempt to insert a topping that references a pizza that doesn't exist. Summarize:
a. why the insertion failed, and
b. how you would interpret the error message from your RDBMS so that you know that the error indicates the Pizza reference is invalid.

Section - Advanced Data Expression

4. Indicate the final values for each of the Boolean expressions below. You must show your work for full credit, by showing the value of each operation step-by-step.
a. (true AND false) OR (true AND true)
b. (true OR false) AND NOT(false OR false) AND (false AND true)
c. NOT((false OR true) AND NOT(true AND true) AND (false OR true))

5. Address the following scenarios.
a. Any pizza matching the following condition is considered a signature pizza for the pizza shop: Any pizza, except for the "Plain" pizza, that is available on or after 5/1/2020, with a price of $9.50 or higher, is a signature pizza.Write a query that shows the name and price of all signature pizzas.
b. The pizza shop also has one flagship pizza that sets the shop apart from other pizza shops. First, define your own conditions for this flagship pizza, making sure the conditions include the name, date, and price. Then write a query that shows the name and price of the flagship pizza. It's fine if you'd like to insert another row of pizzas to become your flagship pizza.

6. Address the following.
a. In your own words, describe the advantages of using a calculated column, when compared to placing the calculation in queries.
b. Define a new generated column named special_price, which gives a lower price for the pizza for when the pizza shop offers specials (such as on holidays or during weekly specials). You determine the percentage or fixed value discount for the special price. Then write a query that lists out the name of all pizzas, along with their regular and special prices.
c. Address #16a again in a different way. First, define a generated column named is_signature on the Pizza table, which indicates whether it's a signature pizza or not. Then write aquery that lists only the signature pizzas. Include relevant columns in the result.

Attachment:- Interconnecting and Expressing Data.rar