Database Design and Development

Homework $\backslash$#$4$

$11)(40$ points$)$ Create a stored procedure in the TOM schema that meets the following specifications.

$-$ The procedure should be named spScheduleProd

$-$ The user will provide a product number and an assignment number as parameters. The parameters must be in that order.

$-$ Determine if the product is valid $($i$.$e$.,$ the prod$\_$num already exists in the PRODUCT table$).$ If the prod$\_$num is not valid, output a message stating the product cannot be found.

$-$ If the product is valid, determine if the product is already assigned for delivery $($i$.$e$.,$ the product already has an assign$\_$num value$).$ If the product is already assigned for delivery, determine if the existing assignment is the same as the one the user is attempting to make. If the user is attempting to assign the same value as is already assigned for that product, then output a message that the assignment is set $($e$.$g$.,$ "Product $101$ is assigned."$).$

$-$ If product is valid, the product is already assigned for delivery $($i$.$e$.,$ the product already has an assign$\_$num value$),$ and the user is changing the assignment to a new value, then update the assign$\_$num in the PRODUCT table to the new value the user specified. Also, output a message indicating the change using the truck numbers and assignment dates. For example, "Product $101$ changed from Truck $11$ on $2024-09-12$ to Truck $14$ on $2024-09-13".$

$-$ If product is valid and the product is NOT already assigned for delivery $($i$.$e$.,$ the product does not already have an assign$\_$num value$),$ then set the assign$\_$num for that product in the PRODUCT table to the value the user specified. Also, output a message indicating the truck numbers and assignment dates. For example, "Product $101$ assigned to Truck $14$ on $2024-09-13".$ Database Design and Development

Homework $\backslash$#$4$

Top O$\text{'}$ the Morning $($TOM$)$ is a company that provides custom stone countertops for kitchens, bathrooms, and other areas of a home or business. The company is providing you access to sample data for a small area of their overall database. Read the brief description below to help you understand the data. The data were captured in $2024.$

TOM ERD Database Design and Development

Homework $\backslash$#$4$

This assignment is to be completed through your individual effort. All code must be written using SSMS in your own database on the cisora$2$ server $-$ if the instructor cannot confirm the code was written in your own database account, you will not receive credit for it$.$

Write the SQL and$/$or TSQL necessary to complete each question. TSQL is only allowed in question $11.$ Note that you are provided with sample data only. Your code must work even when applied to the full set of data or if the data changes over time. The test data you are using is static $-$ it is not being updated by the users.

$-$ The assignment is individual effort only. You cannot talk to each other or anyone else regarding the problems except the instructor.

$-$ NO CREDIT will be given for code that:

$-$ Is developed using a tool other than SQL Server Management Studio $($SSMS$)$

$-$ Is not developed in your assigned database on the cisora$2$ server

$-$ Uses functions or techniques not covered in this class $($The assignment is a test of your mastery of the material we covered in class.$)$

$-$ SQL statements that return an error when executed

$-$ TSQL programs that will not compile.

$-$ Remember, TSQL is only allowed on the last question.

$-$ You must use only SSMS to write your code.

$-$ You may not use functions or techniques that we have not covered in this class. The assignment is a test of your mastery of the class material that we have covered. There is no credit for statements or programs that use functions or techniques not covered in class in the instructional videos in D$2$L

$-$ While partial credit is possible, you will receive no credit for a stored procedure that does not compile or a SQL statement that returns an error when I attempt to execute it$.$ CHECK YOUR CODE.

$-$ Submission is to be made through Desire$2$Learn $($D$2$L$).$ Upload your submission as a single file with a $.$sql extension to the Homework $4$ dropbox in D$2$L$.$ Do not put the code in Word or a zip file.

$-$ Use comments to number your answers with the appropriate question number. You do not need to re$-$write the question. Do not start your answer on the same line as the comment that numbers the answer, or the command will not execute $($and will therefore receive no credit$).$

$-$ Use a comment at the beginning of the file to place your name and only your name on the first line in the file.

$-$ Remove any extra commands that you used as you were crafting your answers or testing your programs. Notice that none of the questions have a SELECT query as the complete answer, and none of the questions ask for you to execute your stored procedure $--$ only to create it$.$ Points will be deducted for every extra command in your file.