Enable auto increment for rental numbers in the RENTAL table. Start the values with $1100.$

Create a stored procedure named prc$\_$new$\_$rentalto insert new rows in the RENTAL table. The procedure should satisfy the following conditions:

The membership number will be provided as a parameter.

Use a Count$()$ function to verify that the membership number exists in the MEMBERSHIP table. If it does not exist, then a message should be

displayed that the membership does not exist and no data should be written to the database.

If the membership does exist, then retrieve the membership balance and display a message that the balance amount is the previous balance.

$($E$.$g$.,$ if the membership has a balance of $$5\mathrm{.}00,$ then display "Previous balance: $$5\mathrm{.}00".)$

Insert a new row in the rental table using the current system date for the RENT$\_$DATE value, and the membership number provided as the value

for MEM$\_$NUM. The rental number should be provided automatically from the auto increment enabled in the previous problem.

Create a stored procedure named prc$\_$new$\_$detailto insert new rows in the DETAILRENTAL table. The procedure should satisfy the following

requirements:

The video number will be provided as a parameter.

Verify that the video number exists in the VIDEO table. If it does not exist, then display a message that the video does not exist, and do not write

any data to the database.

If the video number does exist, then verify that the VID$\_$STATUS for the video is $"$IN$."$ If the status is not $"$IN$,"$ then display a message that the

video's return must be entered before it can be rented again, and do not write any data to the database.

If the status is $"$IN$",$ then retrieve the values of the video's PRICE$\_$RENTFEE, PRICE$\_$DAILYLATEFEE, and PRICE$\_$RENTDAYS from the PRICE

table.

Calculate the due date for the video rental by adding the number of days in PRICE$\_$RENTDAYS to $11$:$59$:$59$ PM $($hours:minutes:seconds$)$ in the

current system date.

Insert a new row in the DETAILRENTAL table using the previous value returned by RENT$\_$NUM$\_$SEQ as the RENT$\_$NUM, the video number

provided in the parameter as the VID$\_$NUM, the PRICE$\_$RENTFEE as the value for DETAIL$\_$FEE, the due date calculated above for the

DETAIL$\_$DUEDATE, PRICE$\_$DAILYLATEFEE as the value for DETAIL$\_$DAILYLATEFEE, and null for the DETAIL$\_$RETURNDATE.