Assignment 3 – Stored Procedures

Assignment 3 – Stored Procedure

Objectives

To learn to create stored procedures and programmable elements in a database.

Note: You will probably be using stored procedures from this point on – they are one of the most powerful elements of modern databases.

Assignment

Below is a database script. Use this for the assignment (or any other database you might be using with n:n join tables. Note – I did NOT include the Identity designation on the PK fields. You WILL need to do this (a little research will make this very easy). You can also do this after you run the script, but it is an easy add to the script and will take about 1 minute to add. 

USE MenuDatabase

CREATE TABLE [dbo].[Menu](
[ID] INT PRIMARY KEY,
[MenuTitle] [nvarchar](50) NULL,
[MenuDescriptionText] [nvarchar](50) NULL)

CREATE TABLE [dbo].[MenuGroups](
[ID] INT PRIMARY KEY,
[MenuID] INT NOT NULL,
[MenuGroupText] [nvarchar](50) NOT NULL,
[MenuGroupDescriptionText] [nvarchar](50) NULL, 
FOREIGN KEY (MenuID) REFERENCES Menu(ID))

CREATE TABLE [dbo].[MenuItems](
[ID] INT PRIMARY KEY,
[MenuGroupsID] INT NOT NULL,
[MenuItemTitle] [nvarchar](50) NOT NULL,
[MenuItemDescriptionText] [nvarchar](50) NULL,
FOREIGN KEY (MenuGroupsID) REFERENCES MenuGroups(ID))

CREATE TABLE [dbo].[Ingredients](
[ID] INT PRIMARY KEY,
[IngredientTitleText] [NVARCHAR](50) NOT NULL,
[IngredientDescriptionText] [NVARCHAR](200) NULL
)

CREATE TABLE [dbo].[IngredientsInItems](
[ID] INT PRIMARY KEY,
[IngredientsID] INT NOT NULL,
[MenuItemID] INT NOT NULL, 
[DescriptionText] [NVARCHAR](200), 
FOREIGN KEY (IngredientsID) REFERENCES Ingredients(ID),
FOREIGN KEY (MenuItemID) REFERENCES MenuItems(ID))

You will create a stored procedure to insert a menu item into the database. It must check to see if data elements that are unique exist in the database. Here are the steps;

sp_InsertNewMenuItem

1. The stored procedure should accept as arguments (at a minimum) MenuTitle, MenuGroupText, MenuItemTitle

2. The stored procedure should check to see if the MenuTitle exists, if not create a new Menu, if so use the ID

3. The stored procedure should check to see if the MenuGroupText exists, if not create a new MenuGroup, if so use the ID

4. The stored procedure should create a new MenuItem

5. Create a stored procedure sp_InsertIngredient that INSERTS an ingredient to and places it in a Menu Item.

Test Data:

Create a Basic Taco (often called the American Taco on menus. Tacos should be in the MenuGroup “Tacos” which will have multiple tacos in it. One of your Tacos should have the Ingredients;

Ground Beef
Tomatoes
Onions
Lettuce
Salsa

Create a second taco with your own ingredients.

Note: If you have your own database with the same level of complexity and write insertion stored procedures (and include diagrams and description of your database) I will also accept the assignment. Please note that you will be expected to post a description and design of your database to the discussion boards if you choose to do this.

The level of complexity expected is that the table has at least 1 1:n relations and 1 n:n relations with a join table.

Information

What you need to do for this stored procedure very closely follows the example in the lectures. I recommend you create multiple stored procedures for entering artists too (you will need to do this for later assignments). It will not hurt also to practice entering more data into your database also.

Estimated Completion Time

Watching the lectures and completing this assignment should take 1-2 hours for experienced database users, 5-10 hours if this is your first stored procedure.

Supporting Lectures

Lectures and materials for Week 3 support this assignment. COP4709 Course Lectures By Week

The topics which cover this are Topic – Stored Procedures Part 1 and Topic – Stored Procedures Part 2

Questions and Answers

I will post the results of Q & A from the discussion boards here.

External Resources

Stored procedures open an entirely new world of database programming to you. Now you can create incredibly more complex queries and perform tasks that you could not perform before. This is all with the added bonus of them being stored in the database. Some tutorial resources are below (if you find good tutorials and post to the discussion board – I will add them here).

https://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure-tutorial/

WiseOwl Video – Stored Procedure Basics – https://www.youtube.com/watch?v=fjNsRV4zLdc

WiseOwl Video – Stored Procedure Parameters – https://www.youtube.com/watch?v=Vs-atxMs4mw

Grading Criteria

A complete working stored procedure is worth 10 points. You will create a Word Document which will have (1) the text of the stored procedure and (2) a screen capture of you executing the stored procedure in SQL Manager. A successful working stored procedure is worth 10 points.