| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Get control of your email attachments. Connect all your Gmail accounts and in less than 2 minutes, Dokkio will automatically organize your file attachments. You can also connect Dokkio to Drive, Dropbox, and Slack. Sign up for free.

View
 

COP4709 Assignment 3A - Stored Procedures

Page history last edited by Dr. Ron Eaglin 3 years, 5 months ago

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.

 

 

MenuDiagram.pdf

 

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.

Comments (0)

You don't have permission to comment on this page.