Assignment 3 - Stored Procedure

Objectives

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

Assignment

Below is a database - you will need to create this database in your SQL Server. This is a simplified part of a database I wrote for menu management. The entire database is diagrammed in MenuDiagram.pdf

Part A - You will need to create the database tables for this design - you will submit the SQL Script as part of this assignment. You will also need to create the foreign key constraints as part of this script -

MenuGroup.MenuID -> Menu.MenuID

MenuItem.MenuGroupID -> MenuGroup.MenuGroupID

MenuItem.MenuID -> Menu.MenuID

Part B - 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

3. The stored procedure should create a new MenuItem

Submit a word or pdf document with the stored procedure as text and include a screen capture of executing the stored procedure with some data (doc or pdf file). This will be a screen capture of the management studio results of executing the stored procedure.

MenuDiagram.pdf

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.

An example;

MenuTitle - Lunch Menu

MenuGroupText - Sandwiches

MenuItem - California Chicken Sandwich

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

Questions and Answers

External Resources

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.