Calculate Adam McLean's final invoice bill for his family visit started on 2021-08-03. Adam McLean's guest...

 

Transcribed Image Text:

Calculate Adam McLean's final invoice bill for his family visit started on 2021-08-03. Adam McLean's guest ID is G5 and the invoice sales tax percentage is 9%. Follow the steps below to complete this assignment. Read the entire assignment before you work on these steps. Step 1: create an invoice table with the following script (Copy and paste the script to SSMS) CREATE TABLE INVOICE ( InvoiceID InvoiceDate BookID GuestID smallint date Int varchar(4) date identity (1000,1) Primary Key, default getdate(), decimal(7,2) decimal(7,2) decimal(7,2) decimal(5,2) decimal(7,2) Not Null, Not Null, Not Null, StartDate CondoFee ActivityFee Invoice Total default 0, Sales Tax Grand Total default 0, Foreign key (BookID) REFERENCES BOOKING (BookID), Foreign key (GuestID) REFERENCES GUEST (GuestID)); default 0, default 0, default 0, Submit the Select * from invoice query result. (You should see an empty table as shown below) Results | Messages Invoice D Invoice Date BookID Guest ID StartDate Condo Fee ActivityFee Invoice Total Sales Tax Grand Total Step 2: write a select statement to calculate the condo fee for the McLean family. (15 points) Condofee = (No of days * daily rate) of the condo booked by the McLean family on 2021-08-03 1 Total Condo Fee 1015.00 Step 3: write a select statement to calculate the activity fee for the McLean family. (15 points) Activityfee = Numberinparty* PPP (McLean family had two reservations during their visit) 1 Activityfee 275.00 Step 4: Write a stored procedure, Create_Invoice, to insert an invoice record. (30 points) 1. Use TRY CATCH, Transaction Commit/Rollback to insert a new invoice record for the McLean Family. 2. The stored procedure will pass two input parameters, GuestID and StartDate, to avoid hard-coding in step 2 and 3. 3. The invoice record will be populated with invoice ID, invoice date, book ID, GuestID, Start Date, CondoFee, and ActivityFee. The rest of the attributes will contain zeros. Invoice ID and date will be automatically populated. You will have 5 attributes in your insert statement. Make sure the number and position of commas and parentheses are correct to separate these 5 in your insert statement. CREATE PROCEDURE Create_Invoice (input parameters) AS BEGIN Try begin transaction End Try Begin Catch +0 Commit transaction End Catch Enter your insert into statement here............ Select ERROR_NUMBER() AS Error Number, ERROR_MESSAGE() AS ErrorMessage; Rollback 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures Rollback End Catch +0 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures System Stored Procedures dbo.Create_Invoice Step 5: Create 1 trigger, Calculate_Invoice_Total, on the INVOICE table. (20 points) When an invoice record is inserted in step 4, this trigger will 1. Read the inserted temp table from Step 4 to get the Condofee and ActivityFee for invoice calculation 2. Use update statements to calculate and update invoice total, sales tax, and grand total. Invoicetotal = condofee + activityfee Salestax Invoicetotal *0.09 Grandtotal invoice total + sales tax = Submit the Create trigger statement and a screenshot of the created trigger under the INVOICE table folder (see example below). Create trigger Calculate_Invoice_Total Enter your trigger creation statement here.......... dbo.INVOICE Columns Keys 1 Constraints Triggers Calculate_Invoice_Total Indexes Step 6: execute the stored procedure and select the final invoice record for display. The result should look similar to the sample below. (10 points) Exec Create_Invoice 'G5', '2021-08-03'; Select * from invoice; (Your invoice number and invoice date might be different from the example below) InvoiceID Invoice Date BookID GuestID Start Date Condo Fee Activity Fee 1000 2022-07-13 168 G5 2021-08-03 1015.00 275.00 Invoice Total Sales Tax 1290.00 116.10 Grand Total 1406.10 Calculate Adam McLean's final invoice bill for his family visit started on 2021-08-03. Adam McLean's guest ID is G5 and the invoice sales tax percentage is 9%. Follow the steps below to complete this assignment. Read the entire assignment before you work on these steps. Step 1: create an invoice table with the following script (Copy and paste the script to SSMS) CREATE TABLE INVOICE ( InvoiceID InvoiceDate BookID GuestID smallint date Int varchar(4) date identity (1000,1) Primary Key, default getdate(), decimal(7,2) decimal(7,2) decimal(7,2) decimal(5,2) decimal(7,2) Not Null, Not Null, Not Null, StartDate CondoFee ActivityFee Invoice Total default 0, Sales Tax Grand Total default 0, Foreign key (BookID) REFERENCES BOOKING (BookID), Foreign key (GuestID) REFERENCES GUEST (GuestID)); default 0, default 0, default 0, Submit the Select * from invoice query result. (You should see an empty table as shown below) Results | Messages Invoice D Invoice Date BookID Guest ID StartDate Condo Fee ActivityFee Invoice Total Sales Tax Grand Total Step 2: write a select statement to calculate the condo fee for the McLean family. (15 points) Condofee = (No of days * daily rate) of the condo booked by the McLean family on 2021-08-03 1 Total Condo Fee 1015.00 Step 3: write a select statement to calculate the activity fee for the McLean family. (15 points) Activityfee = Numberinparty* PPP (McLean family had two reservations during their visit) 1 Activityfee 275.00 Step 4: Write a stored procedure, Create_Invoice, to insert an invoice record. (30 points) 1. Use TRY CATCH, Transaction Commit/Rollback to insert a new invoice record for the McLean Family. 2. The stored procedure will pass two input parameters, GuestID and StartDate, to avoid hard-coding in step 2 and 3. 3. The invoice record will be populated with invoice ID, invoice date, book ID, GuestID, Start Date, CondoFee, and ActivityFee. The rest of the attributes will contain zeros. Invoice ID and date will be automatically populated. You will have 5 attributes in your insert statement. Make sure the number and position of commas and parentheses are correct to separate these 5 in your insert statement. CREATE PROCEDURE Create_Invoice (input parameters) AS BEGIN Try begin transaction End Try Begin Catch +0 Commit transaction End Catch Enter your insert into statement here............ Select ERROR_NUMBER() AS Error Number, ERROR_MESSAGE() AS ErrorMessage; Rollback 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures Rollback End Catch +0 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures System Stored Procedures dbo.Create_Invoice Step 5: Create 1 trigger, Calculate_Invoice_Total, on the INVOICE table. (20 points) When an invoice record is inserted in step 4, this trigger will 1. Read the inserted temp table from Step 4 to get the Condofee and ActivityFee for invoice calculation 2. Use update statements to calculate and update invoice total, sales tax, and grand total. Invoicetotal = condofee + activityfee Salestax Invoicetotal *0.09 Grandtotal invoice total + sales tax = Submit the Create trigger statement and a screenshot of the created trigger under the INVOICE table folder (see example below). Create trigger Calculate_Invoice_Total Enter your trigger creation statement here.......... dbo.INVOICE Columns Keys 1 Constraints Triggers Calculate_Invoice_Total Indexes Step 6: execute the stored procedure and select the final invoice record for display. The result should look similar to the sample below. (10 points) Exec Create_Invoice 'G5', '2021-08-03'; Select * from invoice; (Your invoice number and invoice date might be different from the example below) InvoiceID Invoice Date BookID GuestID Start Date Condo Fee Activity Fee 1000 2022-07-13 168 G5 2021-08-03 1015.00 275.00 Invoice Total Sales Tax 1290.00 116.10 Grand Total 1406.10