Assignment 3 - Stored Procedure

Objectives

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

Assignment

IN Assignment 1 you designed a music database. I your database you should have designed the ability store information about musicians, including the instruments played on a particular song. Here is what you need to do - you must write a single stored procedure that allows you to enter a musician and link the musician to the instrument played on a song for a particular musical release.

Input Parameters

Musician

Instrument

Song

Musical Release

Business Rules

1. If the Musical Release exists in the database, link the song correctly to the release (like a song on a CD)

2. If the Musical Release does not exist - create (you will link the song to it)

3. If the song exists in the database, link the musician and instrument to the song. You must follow the design you came up with in Assignment 1 - I recommend linking the instrument to the song and the musician to the instrument - but this will vary.

4. If the song does not exist in the database create it (you will need to link this to the musical release)

5. You can decide to have a separate table for instruments or keep these with the musician - however you must retain the relationships that a musician can play multiple instruments on the same song or different songs.

You may make changes to your table design and I highly recommend you discuss your design options and approach to the stored procedure on the bulletin board. Here is some sample data that you may want to try (based on the example given in the video).

I am using this data because Tommy (The Who) and Goodbye Yellow Brick Road (Elton John) are the first two albums I owned. You can get detailed information on these albums here;

http://en.wikipedia.org/wiki/Goodbye_Yellow_Brick_Road 

http://en.wikipedia.org/wiki/Tommy_%28album%29 

Musician: Dee Murray

Instrument: Bass Guitar

Song: Candle in the Wind

Musical Release: Goodbye Yellow Brick Road

Artist: Elton John

You are going to use the database you create in later assignments when we generate reports, so these stored procedures will become important.

Submit the stored procedure as a text file (use txt extension - my reader gets confused with the sql extension). And include a screen capture of executing the stored procedure with some data (do or pdf file).

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

To read the reading for week 3, watch the lectures and complete this assignment should take 5-10 hours.

Supporting Lectures 

Lectures and materials for Week 3 support this assignment.

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.