sql

CS669_Lab4ExplanationForSQLServer.pdf

Home >Computer Science homework help >sql

MET CS 669 Database Design and Implementation for Business Lab 4 Explanation: Procedural SQL

Lab 4 Explanations For SQL Server This explanation document illustrates how to correctly execute each SQL construct step‐by‐step for Lab 4, and

explains important theoretical and practical details. Before completing a step, read its explanation here first.

Use this explanations document only if you are using SQL Server. If you are using Oracle or PostgreSQL, explanations for those DBMS’ are available in a different document in the assignments section of the course.

Page 2 of 34

Table of Contents Section One – Aggregating Data ............................................................................................................................. 4

STEP 1 .................................................................................................................................................................. 4

Code: Creating Customer Order Schema ........................................................................................................ 5

Screenshot: Creating the Customer Order Schema ........................................................................................ 6

Code: Inserting Customer Order Data ............................................................................................................ 7

Screenshot: Inserting Customer Order Data .................................................................................................. 8

STEP 2 .................................................................................................................................................................. 9

Code: Creating add_customer_harry Procedure ............................................................................................ 9

Screenshot: Creating add_customer_harry Procedure ................................................................................ 10

Code: Executing add_customer_harry Procedure ........................................................................................ 10

Screenshot: Executing add_customer_harry Procedure .............................................................................. 11

Screenshot: Customer Table After Execution ............................................................................................... 11

STEP 3 ................................................................................................................................................................ 12

Screenshot: Second Execution of add_customer_harry .............................................................................. 12

STEP 4 ................................................................................................................................................................ 13

Code: Creating add_customer Procedure .................................................................................................... 13

Screenshot: Creating add_customer Procedure ........................................................................................... 14

Code: Executing add_customer Procedure .................................................................................................. 14

Screenshot: Executing add_customer Procedure ......................................................................................... 14

Screenshot: Listing Customer Table After Add ............................................................................................. 15

STEP 5 ................................................................................................................................................................ 16

Pseudocode for Basic Variable Use ............................................................................................................... 16

Code: Creating add_item Procedure ............................................................................................................ 17

Screenshot: Compiling and Executing add_item .......................................................................................... 18

Screenshot: Listing Item Table ...................................................................................................................... 18

STEP 6 ................................................................................................................................................................ 20

Code: ADD_LINE_ITEM procedure ................................................................................................................ 20

Screenshot: Compiling and Executing add_line_item .................................................................................. 21

Screenshot: Listing Line_item After Execution ............................................................................................. 22

STEP 7 ................................................................................................................................................................ 23

Code: DELETE_CUSTOMER procedure .......................................................................................................... 24

Screenshot: Deleting Customer John Smith ................................................................................................. 25

Screenshot: Listing John Smith After Deletion ............................................................................................. 25

Section Two – Triggers .......................................................................................................................................... 26

STEP 8 ................................................................................................................................................................ 26

Code: No Negative Balance Validation Trigger ............................................................................................. 26

Page 3 of 34

Screenshot: Compile the No Negative Balance Trigger ................................................................................ 28

Screenshot: Test the No Negative Balance Trigger ...................................................................................... 28

STEP 9 ................................................................................................................................................................ 29

Code: Correct Line Price Validation Trigger .................................................................................................. 29

Screenshot: Test the No Negative Balance Trigger ...................................................................................... 30

STEP 10 .............................................................................................................................................................. 31

Code: Create the Item_price_history Table .................................................................................................. 32

Code: The Item_price_history Trigger .......................................................................................................... 32

Screenshot: Compilation and Update for Item_price_history ..................................................................... 33

Screenshot: Listing Item_price_history ........................................................................................................ 33

Page 4 of 34

Section One – Aggregating Data

To help demonstrate how to complete the commands in this section, we work with simplified customer order schema which tracks customers and their orders of items. The schema itself is illustrated below.

The Item table contains items that can be purchased, with a primary key, a description of the item, a price, and an item_code which is an identifier by which the item can be referenced. The Customer table contains basic information on customers such as their first and last name, and a total balance which they owe. The Customer_order table contains basic information about an order itself, including a reference to the customer who placed the order, the sum total for the order, and the date the order was placed. The Line_item table contains information on individual lines in the order, including a reference to the item that was purchased, the quantity that was purchased, and the total amount for that line (for example, if an item costs $10 and 2 of them were purchased, the total amount for the line would be $20). We create the customer odder schema illustrated above in its entirety, including all primary and foreign key constraints, with the SQL below. Here’s the code we used for creating the schema.

STEP 1

Create the tables in the social networking schema, including all of their columns, datatypes, and constraints. Populate the tables with data, ensuring that there are at least 5 people, at least 8 posts, and at least 4 likes. Most of the fields are self‐ explanatory. As far as the “content” field in Post, make them whatever you like, such as “Take a look at these new pics” or “Just arrived in the Bahamas”, and set the summary as the first 12 characters of the content, followed by “…”.

Page 5 of 34

Below are screenshots of creating the schema.

CREATE TABLE Customer( customer_id DECIMAL(12) NOT NULL, customer_first VARCHAR(32), customer_last VARCHAR(32), customer_total DECIMAL(12, 2), PRIMARY KEY (customer_ID)); CREATE TABLE Item( item_id DECIMAL(12) NOT NULL, description VARCHAR(64) NOT NULL, price DECIMAL(10, 2) NOT NULL, item_code VARCHAR(4) NOT NULL, PRIMARY KEY (item_id)); CREATE TABLE Customer_order ( order_id DECIMAL(12) NOT NULL, customer_id DECIMAL(12) NOT NULL, order_total DECIMAL(12,2) NOT NULL, order_date DATE NOT NULL, PRIMARY KEY (ORDER_ID), FOREIGN KEY (CUSTOMER_ID) REFERENCES customer); CREATE TABLE Line_item( order_id DECIMAL(12) NOT NULL, item_id DECIMAL(12) NOT NULL, item_quantity DECIMAL(10) NOT NULL, line_price DECIMAL(12,2) NOT NULL, PRIMARY KEY (ORDER_ID, ITEM_ID), FOREIGN KEY (ORDER_ID) REFERENCES customer_order, FOREIGN KEY (ITEM_ID) REFERENCES item);

Code: Creating Customer Order Schema

Page 6 of 34

Screenshot: Creating the Customer Order Schema

SQL Server

We then insert the following made up data into the tables, making sure to relate them properly with primary and foreign keys.

Page 7 of 34

Below are screenshot snippets of inserting the values. There are too many commands to fit in one screenshot, and screenshotting them all is not important in this context.

--Customers INSERT INTO customer VALUES(1,'John','Smith',0); INSERT INTO customer VALUES(2,'Mary','Berman',0); INSERT INTO customer VALUES(3,'Elizabeth','Johnson',0); INSERT INTO customer VALUES(4,'Peter','Quigley',0); INSERT INTO customer VALUES(5,'Stanton','Hurley',0); INSERT INTO customer VALUES(6,'Yvette','Presley',0); INSERT INTO customer VALUES(7,'Hilary','Marsh',0); --Items INSERT INTO item VALUES(1,'Plate',10, 'P001'); INSERT INTO item VALUES(2,'Bowl',11, 'B002'); INSERT INTO item VALUES(3,'Knife',5, 'K003'); INSERT INTO item VALUES(4,'Fork',5, 'F004'); INSERT INTO item VALUES(5,'Spoon',5, 'S005'); INSERT INTO item VALUES(6,'Cup',12, 'C006'); --Orders INSERT INTO customer_order VALUES(1,1,506,CAST('18-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(2,1,1000,CAST('17-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(3,3,15,CAST('19-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(4,3,15,CAST('20-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(5,2,1584,CAST('18-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(6,4,100,CAST('17-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(7,5,40,CAST('18-DEC-2005' AS DATE)); INSERT INTO customer_order VALUES(8,1,10,CAST('19-DEC-2005' AS DATE)); --Line Items INSERT INTO line_item VALUES(1,1,10,100); INSERT INTO line_item VALUES(1,5,2,10); INSERT INTO line_item VALUES(1,2,36,396); INSERT INTO line_item VALUES(2,1,95,950); INSERT INTO line_item VALUES(2,3,10,50); INSERT INTO line_item VALUES(3,4,3,15); INSERT INTO line_item VALUES(4,4,3,15); INSERT INTO line_item VALUES(5,6,132,1584); INSERT INTO line_item VALUES(6,1,10,100); INSERT INTO line_item VALUES(7,5,5,25); INSERT INTO line_item VALUES(7,4,3,15); INSERT INTO line_item VALUES(8,5,2,10);

Code: Inserting Customer Order Data

Page 8 of 34

Screenshot: Inserting Customer Order Data

SQL Server

Page 9 of 34

To demonstrate something similar, we’ll create a stored procedure named “add_customer_harry” that adds a customer named Harry Joker to the customer order schema. Below is code for this procedure. Let us go through code line by line and discuss the meaning.

Line 1: CREATE PROCEDURE ADD_CUSTOMER_HARRY

The CREATE PROCEDURE phrase indicates to SQL Server that a stored procedure is to be created. All of the words in this phrase are SQL keywords, meaning that they are words predefined in the SQL language to have a specific meaning. We must use keywords exactly as they are defined by the language in order to tell the language what we want to do. The ADD_CUSTOMER_HARRY word is the name of the stored procedure. This name is an identifier, which means that the language allows us to define our own name. SQL Server does restrict the length of identifiers to be no longer than 128 characters, and has some character restrictions, for example, that identifiers should not contain the “%” character. Within these restrictions we can specify any name we like. Of course, it is best to name a stored procedure reasonably based upon the function it performs. For this procedure, I chose the name ADD_CUSTOMER_HARRY because the logic of the procedure inserts a customer named “Harry” into the Customer table. SQL Server relaxes many of its restrictions on an identifier if the identifier is quoted or enclosed in brackets; however, it is best not to use an identifier that must be quoted each time it is used, so we stay within the regular identifier guidelines.

Line 2: AS AS is a SQL keyword that is required by the language to define a stored

procedure, but otherwise has no significant meaning. SQL is defined to be natural for English speakers, so the full phrase CREATE PROCEDURE ADD_CUSTOMER_HARRY AS leads an English speaker to naturally think that the definition of the stored procedure follows the AS keyword.

Line 3: BEGIN

STEP 2 Create a stored procedure named “add_zana_sage” which adds a person named “Zana Sage” to the Person table, then execute the stored procedure. List out the rows in the Person table to show that Zana Sage has been added.

CREATE PROCEDURE ADD_CUSTOMER_HARRY AS BEGIN INSERT INTO CUSTOMER (CUSTOMER_ID,CUSTOMER_FIRST,CUSTOMER_LAST,CUSTOMER_TOTAL) VALUES(8, 'Harry', 'Joker', 0); END;

Code: Creating add_customer_harry Procedure

Page 10 of 34

This word is optional when creating stored procedures in SQL Server. Its use helps in the readability of the stored procedure, so that one can determine at a glance where the content of the stored procedure begins. If the BEGIN word is used, it must be coupled with the END keyword described below.

Lines 4-5: INSERT INTO CUSTOMER (customer_id,customer_first,customer_last,customer_total) VALUES(8, ’Harry’, ’Joker’, 0);

You might think this command looks familiar, because it does! It is just a standard SQL statement. Wait a minute. The procedural language and the SQL language are two different languages, right? So why is this SQL statement inside of a stored procedure that uses the procedural language? Simple! Because certain SQL commands can be embedded inside of the procedural language in the right context. In this case, I have embedded an insert statement that inserts a new customer “Harry” into the Customer table. This way, when you execute this stored procedure, the stored procedure will insert the new customer on your behalf, without the need for you to type the SQL command yourself.

Line 6: END; The END keyword is optional and is only required if the BEGIN keyword is used.

This word helps in the readability of the stored procedure, so that one can determine at a glance where the content of the stored procedure ends. Likewise, the semicolon after the END keyword is optional.

Now to be clear, the above code, when executed, creates the stored procedure so that it’s available for use. Below is a screenshot of creating this stored procedure.

Screenshot: Creating add_customer_harry Procedure

SQL Server

Notice that the output indicates that the commands completed successfully. This is a technical way for the DBMS to state that the procedure has been processed and is available for use. Now that we have created the stored procedure, we need to execute it for its code to take effect. First, let’s look at the code to do so.

EXECUTE ADD_CUSTOMER_HARRY;

Code: Executing add_customer_harry Procedure

Page 11 of 34

The EXECUTE keyword can be used to execute many different kinds of objects in SQL Server, and in this context we use to execute the stored procedure we have created. We used the name of our stored procedure, ADD_CUSTOMER_HARRY, to specify which stored procedure to execute. Below is a screenshot of executing this code.

Screenshot: Executing add_customer_harry Procedure

SQL Server

The output states “1 row(s) affected)” to indicate that the code within the anonymous block has executed (by inserting 1 row). We can now select all rows from the Customer table to make sure that our stored procedure inserted a row as we would expect.

Screenshot: Customer Table After Execution

SQL Server

Sure enough, we see that the customer “Harry Joker” is listed in the table as the last row listed. We have now successfully created and executed a stored procedure! You can now perform a similar series of steps in order to add Zana to the Person table through use of a stored procedure.

Page 12 of 34

To demonstrate this, we’ll attempt to execute the add_customer_harry procedure a second time. Realistically the ADD_CUSTOMER_HARRY stored procedure can be executed only once successfully. Inside the procedure, we placed the literal value “8” for the customer_id column, the literal value “Harry” for the customer_first column, the literal value “Joker” for the customer_last column, and the literal value “0” for the customer_total column. This placement is termed “hardcoding” by computer programmers, a term which means that a value is embedded directly into the source of a program, instead of obtaining the value dynamically. Attempting to execute it a second time would result at the very least in a primary key violation, since it’s attempting to insert the same primary key value a second time. This is illustrated below.

Screenshot: Second Execution of add_customer_harry

SQL Server

Notice that the error is indicating that the primary key constraint would be violated by this execution, and it even lists out the duplicate value, 8. The insert was blocked. It’s a good thing the constraint blocked it actually, because otherwise we would have inserted the same customer twice, possibly without realizing it. You will get similar results when you execute your stored procedure a second time.

STEP 3 Attempt to execute the “add_zana_sage” procedure a second time. Summarize what the issue is from the error that occurs as a result.

Page 13 of 34

It is best to make our stored procedures reusable, so that they can be executed wherever the logic contained in them is needed. The fact that our ADD_CUSTOMER_HARRY stored procedure cannot be executed multiple times makes it less valuable as a resource. To achieve reusability, instead of hardcoding literal values in a procedure, we use placeholders which instruct the DBMS to use whatever value is given to the stored procedure when it is executed. These placeholders are termed parameters. At a minimum, a parameter has a name, which is an identifier by which it is referred, and a datatype, which determines the set of legal values that can be assigned to the parameter. For example, if instead of hardcoding the value “8” for the customer_id column, we defined a parameter named “cus_id_arg” with a datatype of “DECIMAL”, the particular value can be specified when the stored procedure is executed. Below is an ADD_CUSTOMER stored procedure that makes use of several parameters and is therefore reusable, allowing us to add any customer rather than just one specific customer. Comments next to the parameters help explain their purpose. Notice that instead of hardcoding particular values in the insert statement, the parameter names are referenced instead, particularly in the VALUES(@cus_id_arg,@first_name_arg,@last_name_arg,0) part of the insert statement. Instead of “8”, the parameter cus_id_arg is used. Instead of “Harry” and “Joker”, the first_name_arg and last_name_arg parameters are used. Essentially, this is instructing the SQL engine to insert whatever values have been passed into the stored procedure, rather than specific values. Creating the stored procedure in SQL Server looks as follows.

STEP 4 Create a reusable stored procedure named “add_person” that uses parameters and allows you to insert any new person into the Person table. Execute the stored procedure with a person of your choosing, then list out the Person table to show that the person was added to the table.

CREATE PROCEDURE ADD_CUSTOMER -- Create a new customer @cus_id_arg DECIMAL, -- The new customer's ID. @first_name_arg VARCHAR(30), -- The new customer’s first name. @last_name_arg VARCHAR(40) -- The new customer's last name. AS -- This "AS" is required by the syntax of stored procedures. BEGIN -- Insert the new customer with a 0 balance. INSERT INTO CUSTOMER (CUSTOMER_ID,CUSTOMER_FIRST,CUSTOMER_LAST,CUSTOMER_TOTAL) VALUES(@cus_id_arg,@first_name_arg,@last_name_arg,0); END;

Code: Creating add_customer Procedure

Page 14 of 34

Screenshot: Creating add_customer Procedure

SQL Server

The same as with the add_harry procedure, the DBMS indicates the procedure was compiled with a generic error message. When the stored procedure is executed, the values to be used are specified by the executor. The syntax is similar to calling a stored procedure with no parameters, with the parameters themselves comma separated within parentheses. The code for adding a customer named “Mary Smith” with a unique primary key, using the stored procedure, is given below. Notice that “9, ‘Mary’, ‘Smith’” part of the stored procedure call which specifies what parameters to use. The order in which the parameters appear matters, as this ordering is correlated with the ordering the parameters are declared in the stored procedure. Since “9” comes first, it’s matched to cus_id_arg comes first in the stored procedure declaration. Since “Mary” comes second, it’s matched to first_name_arg, and “Smith” is matched to last_name_arg. Using this approach, you need to know the order in which the parameters are declared in the stored procedure in order to execute the stored procedure. Executing the stored procedure gives us the same confirmation we saw with the prior execution of the “add_customer_harry” procedure, as shown below.

Screenshot: Executing add_customer Procedure

SQL Server

Just to make sure Mary Smith made it in, we’ll list out our Customer table again, illustrated below.

EXECUTE ADD_CUSTOMER 9,'Mary','Smith'

Code: Executing add_customer Procedure

Page 15 of 34

Screenshot: Listing Customer Table After Add

SQL Server

Notice that Mary Smith is listed in the table with an ID of 9, just as we specified when executing the add_customer procedure. We could add many more customers using this stored procedure just by changing the parameter values given to the stored procedure! Hopefully this gives you an idea of the usefulness of parameterized stored procedures. Code the logic once, then execute the stored procedure whenever you need it. Of course, the logic we put into this procedure is a simple insert statement, but you can put much more than that into more complex procedures, which we’ll look at in subsequent steps. You can now use a similar approach to the one demonstrated to address Step 4.

Page 16 of 34

You learned in Step 4 how to create reusable stored procedures by using parameters, so using a variable is the new skill for this step. The basic concepts of variables are not too complex. A variable is a named placeholder that can store a value, and can later be referenced by name to retrieve the stored value. Let’s take an example from the customer order schema we have been using throughout this section. What if, instead of hardcoding the item code for an item, we wanted the database to assign it a unique value? What we could do is, create a variable, calculate the item code and store it in the variable, then reference the variable when inserting into the item table. Let’s first illustrate this in pseudo‐code so that you understand the concepts. In line 1 in the pseudocode, the v_item_code variable is declared. A variable declaration identifies the existence, name, and datatype of the variable. In programmatic SQL (and also in many programming languages), a variable cannot be used unless it is first declared. Its name identifies how the variable will be later referenced, and its datatype indicates what kind of value it can store (such as character string, number, date, etc …) In line 2 in the pseudocode, the variable is assigned a value. A variable assignment places a value into the variable. Referencing the variable later will use the value assigned. In line 3, the variable is used by referencing it by name. A variable reference uses whatever value is in the variable. Of course, the references to the variable are what makes a variable useful, since simply declaring one and assigning a value to it would not be useful alone. Now that you understand the pseudocode, let’s look at the stored procedure code then analyze the lines.

STEP 5

Create a reusable stored procedure named “add_post” that uses parameters and allows you to insert any new post into the Post table. Instead of passing in the summary as a parameter, derive the summary from the content, storing the derivation temporarily in a variable (which is then used as part of the insert statement). Execute the stored procedure to add a post of your choosing, then list out the Post table to show that the addition succeeded.

1: Declare variable v_item_code as a character string

2: Calculate a unique value and store it in the v_item_code variable

3: Insert whatever value is in the v_item_code variable into the item table

Pseudocode for Basic Variable Use

Page 17 of 34

First, you’ll notice that the procedure is named “add_item” since it allows for adding an item to the database. Next, you’ll notice that three of the four values needed in the Item table – item_id, description, and price – all have corresponding parameters. The executor will decide what these values are whenever the stored procedure is invoked (you have already witnessed this strategy in the prior step). Notice the DECLARE @v_item_code VARCHAR(4); code. This line is the variable declaration, where we indicate

the variable exists (by the existence of the declaration), give the variable its name (v_item_code), and its

datatype (VARCHAR(4)). We give it that datatype since that is the same datatype as found in the table. This

variable declaration sets up the variable so it can be assigned values and its values can be retrieved.

Next, you’ll notice the SET @v_item_code = CONCAT(SUBSTRING(@p_description,1,1), FLOOR(RAND()*1000));

line. There are several pieces of code here you may not recognize, but don’t let that keep you from

understanding the basic fact that this is the line that sets the value for the variable. What we are setting it to

is the first character of the description, followed by a random 3‐digit number. For example, if the item

description is “Napkin”, then the item code would start with “N” since that is the first letter, followed by a

random 3‐digit number. If the database randomly selects 867 for example, then the item code would be

“N867”.

The SUBSTRING function in SQL Server returns a portion of a character string. The SUBSTRING(@p_description,

1, 1) code indicates to start at the first character (the first 1 argument), and to grab 1 character from there

(the second 1 argument), thereby retrieving the first character. The RAND() function obtains a random

number between 0 and 1, so we multiply it times 1000 to get a number between 0 and 999. The FLOOR

function chops off the decimal point and leaves the nearest whole number. The CONCAT() function

concatenates two or more values together as a string. When the results of these functions are combined

through concatenation, it results in a 4‐character item code as described above.

Last, you’ll notice the insert line, which inserts the values into the Item table, with a reference to

“v_item_code” for the item_code value. Referencing the variable instructs the SQL engine to pull the value

stored in the variable.

Let’s try out compiling and executing the stored procedure to add a “napkin” item.

CREATE PROCEDURE ADD_ITEM @p_item_id DECIMAL(12), -- The new item ID, must be unused @p_description VARCHAR(64), -- The item's description @p_price DECIMAL(10,2) -- The item's price AS BEGIN DECLARE @v_item_code VARCHAR(4); --Declare a variable to hold an item_code value. --Calculate the item_code value and put it into the variable. SET @v_item_code = CONCAT(SUBSTRING(@p_description,1,1), FLOOR(RAND()*1000)); --Insert a row with the combined values of the parameters and the variable. INSERT INTO ITEM (item_id, description, price, item_code) VALUES(@p_item_id, @p_description, @p_price, @v_item_code); END;

Code: Creating add_item Procedure

Page 18 of 34

Screenshot: Compiling and Executing add_item

SQL Server

Notice that it was necessary to put the GO keyword after the stored procedure definition, so that we could combine DDL (data definition language) with DML (data manipulation language). We otherwise execute the add_item stored procedure just as we executed the add_customer procedure in the prior step. Let’s look at the item table now to see if our item was added.

Screenshot: Listing Item Table

SQL Server

Sure enough, the Napkin item was added, and the item_code value was automatically calculated rather than being passed in as a parameter by the executor. We achieved something new! There is one more important concept you need to understand about variables to make effective use of them. Variable scope is the region in which a variable is accessible. In the examples in this step, we declare the

Page 19 of 34

variable within the stored procedure, which means that the variable is only accessible within that same stored procedure. Another stored procedure, or the SQL engine itself, cannot access the variable. When the procedure is invoked, the variable becomes accessible by code in the procedure. Unless a value is given in its declaration, the variable is initialized to null, and another line of code can explicitly set its value to something else. What about multiple executions of the same procedure? Does the variable’s value remain across executions? The simple answer is no. Every time the procedure is invoked, the variable’s value is initialized and available only to that particular execution. Different executions of the stored procedure have access to different values of the variable. That is, even though it appears multiple executions are accessing the same variable since it carries the same and declaration, each execution has its own copy of the variable so that each execution can use the variable independent of another execution. Hopefully the examples in this step help illustrate one purpose of using variables. A variable provides a place to store values, which can be calculated by using expressions, and then the variable can later be referenced to retrieve its value. You now have enough knowledge to create the “add_post” stored procedure for this step.

Page 20 of 34

What you’re being asked to do is certainly becoming more complex, but don’t worry, you already have most of the skills you need. You already know how to create parameterized stored procedures, and declare and use variables. The one skill you have not learned yet is setting the value of a variable based upon the results of a query. Since your procedure will be given a username and not a person_id, you will need to look this up by executing a query. There is a way to do this and store it into a variable. We’ll demonstrate how to do this by creating an “add_line_item” stored procedure that supports adding line items to the database. Rather than the executor specifying the item_id, the stored procedure will take the item_code as a parameter, then lookup the item_id. The other parameters will be specified as usual. Such a procedure can look like this. There are four columns in the line_item table – item_id, order_id, item_quantity, and line_price. Order_id and item_quantity are specified explicitly as parameters, so the executor decides what values to pass in explicitly. However, the other two are not specified explicitly, but are looked up by using the item_code. Notice that there are two variables declared, v_item_id and v_line_price; these will be used to store these values. It’s this select statement that is interesting for this step. SELECT @v_item_id=item_id, @v_line_price=price*@p_quantity FROM Item WHERE item_code = @p_item_code;

STEP 6

Create a reusable stored procedure named “add_like” that uses parameters and allows you to insert any new “like”. Rather than passing in the person_id value as parameters to identify which person is liking which post, pass in the username of the person. The stored procedure should then lookup the person_id and store it in a variable to be used in the insert statement. Execute the procedure to add a “like” of your choosing, then list out the Like table to show the addition succeeded.

CREATE PROCEDURE ADD_LINE_ITEM @p_item_code VARCHAR(4), -- The code of the item. @p_order_id DECIMAL(12), -- The ID of the order for the line item. @p_quantity DECIMAL(10) -- The quantity of the item. AS BEGIN DECLARE @v_item_id DECIMAL(12); --Declare a variable to hold the ID of the item code. DECLARE @v_line_price DECIMAL(12,2); --Declare a variable to calculate line price. --Get the item_id based upon the item_code, as well as the line total. SELECT @v_item_id=item_id, @v_line_price=price*@p_quantity FROM Item WHERE item_code = @p_item_code; --Insert the new line item. INSERT INTO LINE_ITEM(item_id, order_id, item_quantity, line_price) VALUES(@v_item_id, @p_order_id, @p_quantity, @v_line_price); END;

Code: ADD_LINE_ITEM procedure

Page 21 of 34

The standard SELECT, FROM, WHERE clauses combined are retrieving the item corresponding to the item code passed in as a parameter, and pulling back the item_id column, and calculating what the line price would be by multiplying the price times the quantity specified. You might have guessed that the syntax we have not dealt with before, the @v_item_id= and the @v_line_price=, provide a way to assign the column’s value into a variable. @v_item_id is set to the item_id column value, and @v_line_price is set to the expression of price * @p_quantity. Do you see the power of this syntax? You can use it to lookup values in other tables, store them in variables, then later use those variables as needed. In our case, we are using the syntax to determine what the item_id and line_price values should be, then using those variables in our insert statement. Next, let’s use our stored procedure to add a line item for an order, where three “fork” items are added. As a reminder, the “fork” item has these values: item_id description price item_code

4 Fork 5 F004

It’s item_code is “F004”, its price is $5, and its ID is 4. Here is a screenshot of the code used to add three fork items to an order (order with id 8).

Screenshot: Compiling and Executing add_line_item

SQL Server

The ADD_LINE_ITEM procedure was invoked, referencing the “F004” item code, order id 8, and a quantity of 3. Now let’s see if our results made it into the table by selecting all line items for order 8.

Page 22 of 34

Screenshot: Listing Line_item After Execution

SQL Server

Notice that the first line is the one we just inserted using the procedure! Order with ID 8 now has an additional line item for forks (with ID 4), quantity 3, and a price of $15 (since $5 * 3 = $15). We are able to use this procedure to automatically calculate the line price, and to retrieve the correct item, all with its item code. Do you see now the power of lookups and storing the values in? It gives a new dimension to your stored procedures, as your procedures can now take parameters, lookup values in various tables, and use them as needed. You’re on your way to becoming an expert! Now you can use a similar strategy to create your “add_like” procedure.

Page 23 of 34

In addition to reusability through the use of parameters, stored procedures can also be made more useful by executing multiple SQL commands that make up one logical unit of work. For example, what if we want to delete the Customer John Smith, as well as all of his Order and Line_item information? We would need to execute several delete statements in the correct order in order to do so. We could do so manually, but better yet we could create a parameterized stored procedure that would delete any customer’s information of our choosing. We would just need to specify the customer_id when executing the stored procedure. We create such a stored procedure, then execute it to delete John Smith and all Order and Line_item information associated with John Smith. To do so, we need to make use of a simple subquery to delete the line items. Subqueries are not covered fully in Module 4, so below is a basic form of the subquery.

DELETE FROM Line_item WHERE order_id IN (SELECT order_id FROM Customer_order WHERE customer_id = 5);

This command instructs the database to delete any row in the Line_item table that has an order_id placed by a Customer with customer_id 5. The part in parentheses, (SELECT order_id...), is the subquery which retrieves all order ids associated to Customer 5. The IN clause matches any of the IDs in that list. You will learn more details about subqueries in lab 5, but this basic knowledge of subqueries is sufficient for this step. For illustrative purposes, here we hardcode 5, but of course make the results more dynamic by using parameters in the stored procedure. Below is the code for the stored procedure.

STEP 7

Create a reusable stored procedure named “delete_person” that takes only one parameter, the username of a person, and deletes all record of that person from the database. This means deleting all of a person’s posts, likes, and the Person record itself. Execute the procedure to delete a person of your choosing (make sure the person has at least one post and at least one like. List out all three tables to show that all record of the person is gone.

Page 24 of 34

The procedure takes a single parameter for the customer id. It then performs the following steps. a. Delete records from LINE_ITEM table. b. Delete records from CUSTOMER_ORDER table. c. Delete records from CUSTOMER table. The order of deletion is important. We cannot delete the customer before deleting his or her orders. We cannot delete the orders before deleting the orders’ line items. So, we delete the line items first, followed by the orders, followed by the customer. John Smith has an ID of 1. Below is a screenshot of deleting him from the database using this stored procedure.

CREATE PROCEDURE DELETE_CUSTOMER @p_customer_id DECIMAL(12) AS BEGIN --Delete all line items associated with orders associated --with the customer. DELETE FROM Line_item WHERE order_id IN (SELECT order_id FROM Customer_order WHERE customer_id = @p_customer_id); --Delete all orders associated with the customer. DELETE FROM Customer_order WHERE customer_id = @p_customer_id; --Delete the customer. DELETE FROM Customer WHERE customer_id = @p_customer_id; END;

Code: DELETE_CUSTOMER procedure

Page 25 of 34

Screenshot: Deleting Customer John Smith

SQL Server

We just deleted John Smith by using the procedure! To make sure, let’s list out a couple of the tables, looking for John Smith.

Screenshot: Listing John Smith After Deletion

SQL Server

Voila! John Smith no longer exists. You can use similar logic to create your delete_person procedure.

Page 26 of 34

Section Two – Triggers

To demonstrate how to do this, we will create a trigger that prevents the customer balance from being negative. This validation only needs information from the table being modified and so qualifies as an intra‐ table validation. Below is the code for such a trigger. Since we have not yet reviewed triggers, let’s examine this line by line.

Line 1: CREATE TRIGGER no_neg_bal_trg

The CREATE TRIGGER phrase indicates that a trigger is to be created. The no_neg_bal_trg word is the name of the trigger, an identifier of our own choosing. We put “trg” at the end of the identifier as a convention, so it’s recognizable as the name of a trigger. The rest of the name helps describe what the trigger does, which is validate that there is no negative balance.

Line 2: ON Customer AFTER INSERT,UPDATE

ON Customer ties to trigger to the Customer table, that is, indicates that the aforementioned actions (update and insert) will fire this trigger only if they happen on the Customer table. Triggers are inexorably linked to one table by definition. If the table is dropped, the trigger is also automatically dropped.

AFTER is a SQL keyword that instructs the trigger is to be executed after the insert or update occurs in the database (but still within the same transaction). INSERT,UPDATE indicates that the trigger is to be fired when either an insert or

STEP 8

One practical use of a trigger is validation within a single table (that is, the validation can be performed by using columns in the table being modified). Create a trigger that validates that the summary is being inserted correctly, that is, that the summary is actually the first 12 characters of the content followed by “…”. The trigger should reject an insert that does not have a valid summary value. Verify the trigger works by issuing two insert commands – one with a correct summary, and one with an incorrect summary. List out the Post table after the inserts to show one insert was blocked and the other succeeded.

CREATE TRIGGER no_neg_bal_trg ON Customer AFTER INSERT,UPDATE AS BEGIN DECLARE @CUSTOMER_TOTAL DECIMAL; SET @CUSTOMER_TOTAL=(SELECT INSERTED.customer_total FROM INSERTED); IF @CUSTOMER_TOTAL < 0 BEGIN ROLLBACK; RAISERROR('Customer balance cannot be negative',14,1); END; END;

Code: No Negative Balance Validation Trigger

Page 27 of 34

an update statement happens on the table. If we had omitted “INSERT” for example, then the trigger would only fire when an update occurs. We want to block all negative balances so we have the trigger fire on both updates and inserts.

Line 3: AS This is just part of the syntax of creating the trigger (similar to stored

procedures). Line 4: BEGIN Just as with stored procedures, code for triggers needs to be defined within a

BEGIN/END block. The BEGIN keyword opens the block for the trigger. Line 5: DECLARE @CUSTOMER_TOTAL DECIMAL;

This declares a variable that will be used to store the total just updated or inserted.

Line 6: SET @CUSTOMER_TOTAL=(SELECT INSERTED.customer_total FROM INSERTED); This sets the customer_total variable to the total just inserted or updated.

INSERTED is a pseudo‐table only available in triggers which has all of the same columns as the table the trigger is attached to (in this case, Customer), and has all of the updated rows (since inserts and updates can affect more than one row in SQL Server). By accessing INSERTED.customer_total, we are accessing the total column after it was updated or inserted. Notice that we used the SET keyword to set the value of the customer_total variable, with a nested query. This is just an alternative method for setting the variable. We could have also used the “@customer_total=” syntax within the query itself.

Line 7: IF @CUSTOMER_TOTAL < 0 The IF keyword tells SQL Server that the block following is only to be executed

of the Boolean expression evaluates to true. If statements allow us to conditionally execute code. For this trigger, we only want to reject SQL statements that attempt to create a negative balance, so we use an if statement. The @CUSTOMER_TOTAL < 0 component is the Boolean expression that the if statement will evaluate, which evaluates to true only when the new customer_total value is less than 0 (negative).

Line 8: BEGIN

This begins the block for the if statement. Any code within this block is conditionally executed based upon the Boolean expression.

Line 9: ROLLBACK; This is a transaction control statement that rolls back the in‐progress

transaction. Even though the trigger is firing after the update has been made, the trigger is still executing within the same transaction as the insert or update statement that caused the trigger to fire. Since we don’t want the insert or update to make it into the database, we must rollback the transaction.

Line 10: END; This ends the IF block. Code outside the IF block is not conditional, and code

within the IF block is conditional. Line 11: END; This ends the block for the trigger itself.

First, we compile the trigger as illustrated in the screenshot below.

Page 28 of 34

Screenshot: Compile the No Negative Balance Trigger

SQL Server

As soon as the trigger is compiled successfully, it is active in the database and will execute when the triggering event happens from that point forward (until, of course, the trigger is disabled or dropped). If we attempt to insert a customer with a negative balance, the trigger will fire and reject it, shown below.

Screenshot: Test the No Negative Balance Trigger

SQL Server

Notice that the insert was immediately rejected by the trigger, and the message is “Customer balance cannot be negative.” We cannot execute the trigger directly, but we can see the effects of the trigger when we execute a triggering statement such as an insert. You can use similar logic to create a trigger to validate the summary field in the Post table.

Page 29 of 34

To demonstrate cross‐table validation, imagine we want to validate the fact that the line price for a line item actually equals the quantity times the item price. The quantity is stored in the Line_item table while the item price is stored in the Item table. We can setup a trigger on the Line_item table to perform this validation using constructs we’ve already used in prior steps in this lab. Here is the code for such a trigger. You’ve seen all of the constructs here individually, but the integration of them needs more explanation. Two variables are declared, one to store the actual line price being inserted or updated, and the other to store the correct line price. Both of these are set by using the SELECT statement to pull from both the Item table as well as the INSERTED pseudo‐table. The trigger then uses an if statement to determine if the line price of the new or updated row is correct. If it’s not, it rolls back the transaction and raises an error that indicates the line price is not correct. You’ve seen all of these constructs before, so you’re not witnessing just another use case. Let’s try it out. We’ll try to insert a line item with an invalid line price, as follows.

STEP 9

Another practical use of a trigger is cross‐table validation (that is, the validation needs columns from at least one table external to the table being updated). Create a trigger that blocks a “like” from being inserted if its “liked_on” date is before the post’s “created_on” date. Verify the trigger works by inserting two “likes” – one that passes this validation, and one that does not. List out the Likes table after the inserts to show one insert was blocked and the other succeeded.

CREATE TRIGGER line_price_trg ON Line_item AFTER INSERT,UPDATE AS BEGIN DECLARE @v_actual_line_price DECIMAL(12,2); DECLARE @v_correct_line_price DECIMAL(12,2); SELECT @v_actual_line_price=INSERTED.line_price, @v_correct_line_price=INSERTED.item_quantity * Item.price FROM Item JOIN INSERTED ON INSERTED.item_id = Item.item_id; IF @v_actual_line_price <> @v_correct_line_price BEGIN ROLLBACK; RAISERROR('The line price is not correct.',14,1); END; END;

Code: Correct Line Price Validation Trigger

Page 30 of 34

Screenshot: Test the Price Validation Trigger

SQL Server

We attempted to insert a line item with a line price of 100, and the trigger rejected it because the line price should be 25. Why? Item with ID 3 has a price of 5, and there is a quantity of 5, so the line price would be 25 and not 100. We successfully used a trigger to perform a cross‐table validation, simply by combining constructs we were already familiar with. With all of this learning, hopefully by now you are beginning to feel very powerful (just make sure to use your powers for good and not evil). You can use similar logic to create a cross‐table validation on the Likes table.

Page 31 of 34

To demonstrate capturing history, imagine that we would like to store a history of price changes for each item, so that we know the price of an item at any point in time despite any price updates. Abstractly, these fields should be included in a history table – a reference (foreign key) to the table being changed, the old value of the column, the new value of the column, and the date of change. You can think of this set of fields as a design pattern for history tables. For our example, we would create an Item_price_history table that stores a reference to the item, its old price, its new price, and the date of the change. Before showing you code, let’s make sure to differentiate history and auditing, which have two different purposes. A history table records changes over time but remains active in the schema, with proper foreign keys for references. The fields are setup so that SQL queries and transactions can use the table along with the other tables in the schema, that is, so that the table can be used regularly like any other table in the schema. The purpose of a history table is to make prior values available to the people and applications that use the database in a way that coexists with the current values. An audit table also records changes over time, but it does not remain active in the schema. The purpose of an audit table is to simply record that a change happened in a way that people can manually review the changes later in case of any concern or dispute. An audit table has no foreign keys, and acts more like a log which contains the full value of each field. Since an audit table does not make use of foreign keys, and flattens out the needed fields, it survives schema changes over time well. For example, as already mentioned a history table for price changes would have a foreign key to the item, but an audit table would instead have the description of the item along with any other information needed to identify the item. Someone could manually review the audit table to see which prices changes over time for which items. It’s a best practice to be aware of which kind of table you’re creating – history or audit – and follow the design patterns for that table. Some organizations inadvertently create hybrid tables that perhaps start out strictly for auditing, but then later add in foreign keys, and this can cause problems as changes are made to the database. In this step, we are creating a history table that remains active in the schema. First, let’s look at the code for creating the Item_price_history table, below.

STEP 10

Another practical use of trigger is to maintain a history of values as they change. Create a table named post_content_history that is used to record updates to the content of a post, then create a trigger that keeps this table up‐to‐date when updates happen to post contents. Verify the trigger works by updating a post’s content, then listing out the post_content_history table (which should have a record of the update).

Page 32 of 34

You’re very familiar with table creation syntax at this point, so there’s no need to belabor the basics of this SQL. Instead, we’ll focus on what’s important here. We opted to avoid giving this table a primary key; a primary key is not necessary to record the history. In some real‐world schemas, the standards employed by the organization may mandate a primary key, especially if the schema will be mapped by an object‐relational mapping tool within an application. There is a foreign key to the Item which has been changed. The old_price and new_price columns have the same datatype as the original Item.price column, since it will be a record of the change. Once the table is created, we then define a trigger on the Item table that inserts a row into the Item_price_history table whenever an item price is updated. The code for the trigger is below. This trigger only fires on updates, because we only want to record changes in price. We’ve opted not record the initial price so we don’t have the trigger fire on insert; however, one variation of the history table would record every price including the initial one. In that case, the trigger would be modified to also trigger on insert, and the old_price column would be nullable so that when the first price is created, the old_price is null (since there is no price). DELETED is a pseudo‐table that records the row before it was updated, in case of an update. If we had assigned the trigger fire on DELETE as well, then it would record the row before it was deleted. We use the DELETED pseudo‐table to extract the old price, and the INSERTED pseudo‐table to extract the new price and the item id. If the old price is different than the new price, it is recorded as an insert into the history table. The function “GETDATE()” is used to retrieve the current date. Let’s test that our trigger works by modifying the price of an item in the Item table. The compilation and update are illustrated first, below.

CREATE TABLE Item_price_history ( item_id DECIMAL(12) NOT NULL, old_price DECIMAL(10,2) NOT NULL, new_price DECIMAL(10,2) NOT NULL, change_date DATE NOT NULL, FOREIGN KEY (item_id) REFERENCES Item(item_id));

Code: Create the Item_price_history Table

CREATE TRIGGER item_history_trg ON Item AFTER UPDATE AS BEGIN DECLARE @v_old_price DECIMAL(10,2) = (SELECT price FROM DELETED); DECLARE @v_new_price DECIMAL(10,2) = (SELECT price FROM INSERTED); DECLARE @v_item_id DECIMAL(12) = (SELECT item_id FROM INSERTED); IF @v_old_price <> @v_new_price BEGIN INSERT INTO Item_price_history(item_id, old_price, new_price, change_date) VALUES(@v_item_id, @v_old_price, @v_new_price, GETDATE()); END; END;

Code: The Item_price_history Trigger

Page 33 of 34

Screenshot: Compilation and Update for Item_price_history

SQL Server

Next, the listing of the table itself is included, to show that the trigger recorded the update.

Screenshot: Listing Item_price_history

SQL Server

We now see a row in the history table indicating the item with ID 1 (Plate) had an old price of 10, a new price of 35, and the change happened on the specific date. With this structure, all such price changes will be recorded over time. And following this pattern, we could record a history for whatever column we like for whatever table we need. Amazing! You can follow this pattern to create and populate the post_content_history pattern for your lab.

Page 34 of 34

Congratulations! You are well on your way to becoming a database design and development guru.