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 - 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.
|
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. |
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.
| Stored Procedure Variable/Field |
Table Field |
If Exists |
If Not Exists |
| MenuTitle varchar |
Menu.MenuTitle |
Get Menu.MenuID |
Insert, Get Identity Menu.MenuID |
| MenuGroupText varchar |
MenuGroup.MenuGroupText |
Get MenuGroup.MenuGroupID |
Insert, Get Identity MenuGroup.MenuGroupID |
| MenuItem varchar | MenuItem.MenuItemTitle | Do Not Insert | Insert |
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
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/
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.