Exercise: Build-It Pizzeria

Build-It Pizzeria lets customers build their own pizza: a size, a crust and any toppings. Its orders are still on paper slips. You'll design the database, build it with a script, fill it with the first orders, and answer the owner's questions.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-build-it-pizzeria, also linked from the Exercise box beside the lesson. Click Code, then Download ZIP, and unzip it into your bootcamp folder. Or clone it with Git.

What to do

1. Design. Sketch the tables on paper or in a drawing tool: one subject per table, a primary key in each, a foreign key for every link. Watch for the many-to-many: a pizza can have many toppings, and a topping can be on many pizzas.
2. Script. In BuildItPizzeria.sql, create the database and its tables, with the right types, NOT NULL, IDENTITY, defaults, and primary key, foreign key, UNIQUE and CHECK constraints.
3. Data. At the end of the script, insert the menu, the customers and the four orders.
4. Questions. Run the script, then answer each question in Questions.sql with a query, and compare with its Expect line.
5. Diagram. Make a database diagram of your finished database in SSMS, and compare it with your sketch.

Hints

• Normalize the slips. Each slip mixes facts about a customer, an order, its pizzas and their toppings. Each kind of fact gets a table of its own.
• Prices live in the menu tables. A pizza line stores its size, crust and quantity; its cost is worked out from the menu.
• Constraints that fit the menu: a size's name is unique, a quantity is more than zero, a delivery fee is never negative.
• Start the script with USE master and DROP DATABASE IF EXISTS, so you can fix and run it as often as you like. SQL Server can't drop a database that's in use, so close any other window that's using BuildItPizzeria first.
• A pizza with no toppings has no rows in the topping link table: use a LEFT JOIN when adding up topping prices.

When you're done

Your script builds the database from nothing, every answer matches its Expect line, and your diagram shows the tables and links you sketched. Run the script once more to be sure it starts fresh cleanly.

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

1. On GitHub, click +, then New repository. Name it (for example exercise-build-it-pizzeria), choose Public, and click Create repository. Leave out the README: your folder already has one.

2. In a terminal in your exercise folder (Git Bash on Windows), send your work to your new repo. If you downloaded the ZIP:

   git init -b main
   git add .
   git commit -m "My solution"
   git remote add origin https://github.com/your-name/exercise-build-it-pizzeria.git
   git push -u origin main
   

   If you cloned the starter instead, point your copy at your own repo, then commit and push:

   git remote set-url origin https://github.com/your-name/exercise-build-it-pizzeria.git
   git add .
   git commit -m "My solution"
   git push -u origin main
   

3. Paste your repo's address, such as https://github.com/your-name/exercise-build-it-pizzeria, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

From a stack of paper slips to a working database: requirements, design, script, data and questions. That's the whole path every app's database takes, and next you'll put a C# app on top of one.

Key terms

• Normalization
• Many-to-Many Relationship
• Junction Table
• Constraint

Related

• Create Database Scripts in T-SQL
• Normalization
• Database Diagrams in SSMS