DB Development in SQL

by K. Yue

1. Introduction

Read Chapters 4, 5 and 13 of EN.
Cover some topics useful in DB development in SQL.
Use MySQL as examples.

2. System Catalog

Named collections of meta data in SQL DBMS.
Important for DB administrators and developers to know.
Usually stored in relations.
Common terms with similar meaning: data dictionary, catalog, etc.
Thee major databases that are included with MySQL (but not XAMPP): mysql, information_schema and performance_schema.
There is also an example database: Sakila.
Note that HeidiSQL may not be used to see the system catalog in MySQL 8.0.

Example:

mysql database

Store information about the MySQL users.
For example, the user table stores users, passwords (encrypted) and privileges.
So, to create a new user, you can either use the CREATE USER command or by directly inserting a new row into the USER table.

Example:

Try to add a new user by inserting into the user table: http://dev.mysql.com/doc/refman/5.6/en/adding-users.html. There are problems.

use mysql;

Try:

INSERT INTO user (Host,User,Password,select_priv, insert_priv, update_priv,ssl_cipher,x509_issuer,x509_subject)
VALUES ('localhost', 'abcde', PASSWORD('IloveDBMS'), 'Y', 'Y', 'Y', 'NULL', 'NULL', 'NULL');

FLUSH PRIVILEGES;

information_schema database

Store information about all databases in MySQL.
Contents are views: cannot be updated.
Triggers cannot be defined on tables in information_schema.

Example:

It is more flexible than the SHOW command.

Example:

SELECT t.TABLE_SCHEMA AS `schema`, COUNT(t.TABLE_NAME) AS num_tables
FROM information_schema.tables t
GROUP BY `schema`
ORDER BY num_tables DESC;

SELECT t.TABLE_SCHEMA AS `schema`, t.ENGINE, COUNT(t.TABLE_NAME) AS num_tables
FROM information_schema.tables t
GROUP BY `schema`, t.ENGINE
ORDER BY num_tables DESC;

performance_schema database

contain performance related server data.

Sakila database

A good toy example for learning MySQL and practicing SQL.
Search for "MySQL Sakila", Download and install.

3. Views

Views are virtual table derived from other tables.
Some advantages of using views:
1. Better data abstraction
2. Logical data independence
3. Better consistency
4. Possible centralized security control
5. Can be more efficient
Some disadvantages:
1. More work.
2. Can be inefficient.
3. Complicated especially when views are updated.
MySQL Create View Manual: search for "mysql view manual"

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Example:

The Sakila database comes preloaded with MySQL installation. Refer to the documentation at by googling sakila. Download the database at review the SQL statements for declaring and populating the database. (Note that XAMPP does not include Sakila and you will need to install it yourself.)

Define a view to provide information of all paying customers: their id, the total sum of their payment, and the total sum of their payment towards film rentals.

DROP VIEW customer_payment;

CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW customer_payment
AS
SELECT
   p.customer_id AS customer,
   sum(p.amount) AS "Total Payment",
   sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment"
FROM payment as p
GROUP BY p.customer_id;

MariaDB [sakila]> SELECT * FROM customer_payment LIMIT 20;
+----------+---------------+---------------------+
| customer | Total Payment | File Rental Payment |
+----------+---------------+---------------------+
|        1 |        118.68 |              118.68 |
|        2 |        128.73 |              128.73 |
|        3 |        135.74 |              135.74 |
|        4 |         81.78 |               81.78 |
|        5 |        144.62 |              144.62 |
|        6 |         93.72 |               93.72 |
|        7 |        151.67 |              151.67 |
|        8 |         92.76 |               92.76 |
|        9 |         89.77 |               89.77 |
|       10 |         99.75 |               99.75 |
|       11 |        106.76 |              106.76 |
|       12 |        103.72 |              103.72 |
|       13 |        131.73 |              131.73 |
|       14 |        117.72 |              117.72 |
|       15 |        134.68 |              134.68 |
|       16 |        120.71 |              118.72 |
|       17 |         98.79 |               98.79 |
|       18 |         91.78 |               91.78 |
|       19 |        125.76 |              125.76 |
|       20 |        115.70 |              115.70 |
+----------+---------------+---------------------+

Note:

The use of SECURITY INVOKER for security control. The privilege of the invoker is used as the privilege executing the view.
The use of the IF function.
The output for customer with id 16.
Only one table is needed to define the view.

Example

Define the view category_film_count to show the category names, the numbers of inventoried films and the total number of copies of films in the categories.

For example:

mysql> SELECT * from category_film_count;
+-------------+--------------------------+-----------------------+
| category    | Number of distinct films | Total copies of films |
+-------------+--------------------------+-----------------------+
| Action      |                       61 |                   312 |
| Animation   |                       64 |                   335 |
| Children    |                       58 |                   269 |
| Classics    |                       54 |                   270 |
| Comedy      |                       56 |                   269 |
| Documentary |                       63 |                   294 |
| Drama       |                       61 |                   300 |
| Family      |                       67 |                   310 |
| Foreign     |                       67 |                   300 |
| Games       |                       58 |                   276 |
| Horror      |                       53 |                   248 |
| Music       |                       51 |                   232 |
| New         |                       60 |                   275 |
| Sci-Fi      |                       59 |                   312 |
| Sports      |                       73 |                   344 |
| Travel      |                       53 |                   235 |
+-------------+--------------------------+-----------------------+
16 rows in set (0.01 sec)

Suggested Solution:

DROP VIEW category_film_count;

CREATE VIEW category_film_count
AS
SELECT
   cat.name AS category,
   count(DISTINCT fc.film_id) AS "Number of distinct films",
   count(i.inventory_id) AS "Total copies of films"
FROM category AS cat
   LEFT JOIN film_category AS fc ON cat.category_id = fc.category_id
   JOIN inventory AS i ON fc.film_id = i.film_id
GROUP BY category;

Note that there are restrictions in View. For examples, in MySQL 5.7:

No index on views.
No reference to program and user-defined variable in the SELECT clause.
See: http://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html
Also, restrictions are evolving. For example, View cannot contain subqueries in the from clause until version 5.7.7.

4. Stored Procedures

Routines stored by the DBMS that can be called.
Routines can be
1. functions: return a value (but not a table), or
2. procedures: do not return a value.
Known as SQL/PSM (SQL/Persistent Stored Modules) in MySQL.
1. Can execute SQL statements
2. Include general programming constructs
Some advantages of stored procedures
1. Enforcing application constraints and requirements.
2. Providing consistency and security control.
3. Possible performance optimization by both the developers and the DBMS.
4. Sharing among DB applications.
Some disadvantages of stored procedures:
1. Use up DB server's resources.
2. Potentially inefficient because of limitations in language constructs.
3. Potentially harder to develop because of relative lack of libraries and difficulty in debugging.
4. Can be vendor specific.
In general, stored procedures should be used more often.
MySQL stored procedures:
- http://dev.mysql.com/doc/refman/5.6/en/stored-routines.html
- http://dev.mysql.com/doc/refman/5.6/en/faqs-stored-procs.html#qandaitem-B-4-1-2

Example:

DROP PROCEDURE AllSuppliers;

-- A very simple stored procedure
DELIMITER //

CREATE PROCEDURE AllSuppliers()
BEGIN
SELECT * FROM Supplier;
END //

DELIMITER ;

-- Call
CALL AllSuppliers;

Note:

Use of the DELIMITER command to redefine // as the delimiter indicating the end of the stored procedure. Otherwise, the default ; is the delimiter indicating the end of the stored procedure.
No parameters for the procedure.
A procedure does not return any value and accomplish its goal through side effects.
The result is displayed.

Example:

DROP FUNCTION GetAllSuppliers;

-- A very simple stored function
DELIMITER //

CREATE FUNCTION GetAllSuppliers() RETURNS INT
BEGIN
   DECLARE count INT DEFAULT 0;
   SET count = 0;    -- Not really needed.

   SELECT COUNT(*) INTO count FROM Supplier;

   -- The following is not allowed as result set in function.
   -- SELECT * FROM Supplier;

   RETURN count;
END //

DELIMITER ;

-- Call
SELECT GetAllSuppliers();

Note:

A function returns a value and does not accomplish its goal through side effects.
Note variable declaration and assignment.
The scope of the variable is within the function.
On the other hand, a session variable (starts with a @) has the session as its scope. Use them very carefully.

Example:

-- Parts supplied by a supplier

DROP PROCEDURE PartSupplied;
DELIMITER //

CREATE PROCEDURE PartSupplied(
   IN snum VARCHAR(9),
   IN max INT,                  -- Maximum number of records shown
   OUT numberPartsSupplied INT -- Number of different parts supplied
)
BEGIN
   SELECT COUNT(*) INTO numberPartsSupplied
   FROM Supply S
   WHERE S.SNUM = snum;

   SELECT P.PNUM, P.PNAME, S.Quantity
   FROM Supply S, Part P
   WHERE S.SNUM = snum
   AND S.PNUM = P.PNUM
   LIMIT max;

END //

DELIMITER ;

-- Call

SET @numberPartsSupplied = 0;
CALL PartSupplied('S2', 2, @numberPartsSupplied);
SELECT @numberPartsSupplied;

Note:

Parameter passing mode: IN, OUT and IN OUT. This is similar to the language Ada.
Use of the parameter max in the LIMIT clause. There are limitations on where parameters can be used in SQL statements.
Use of the session variable @numberPartsSupplied.

Stored Program Syntax

Syntax for MySQL's stored programs can be found in compound statement section. Google it.
Various high level language constructs are supported, such as variable declaration, conditional statements, control statements, etc.
In particular, cursor is supported for allowing iteration through relations: https://dev.mysql.com/doc/refman/5.7/en/cursors.html. Look for the latest version.

Example on Sakila:

(1) Define a view, actor_appearence, in which each row of the view contains an actor with the following columns:

actor_id
first_name
last_name
num_films: number of films the actors appeared in.
num_categories: number of categories the actors appeared in films of the categories.

For example:

mysql> SELECT *
    -> FROM actor_appearence
    -> LIMIT 10;
+----------+------------+--------------+-----------+----------------+
| actor_id | first_name | last_name    | num_films | num_categories |
+----------+------------+--------------+-----------+----------------+
|        1 | PENELOPE   | GUINESS      |        19 |             13 |
|        2 | NICK       | WAHLBERG     |        25 |             14 |
|        3 | ED         | CHASE        |        22 |             10 |
|        4 | JENNIFER   | DAVIS        |        22 |             12 |
|        5 | JOHNNY     | LOLLOBRIGIDA |        29 |             13 |
|        6 | BETTE      | NICHOLSON    |        20 |             11 |
|        7 | GRACE      | MOSTEL       |        30 |             11 |
|        8 | MATTHEW    | JOHANSSON    |        20 |             10 |
|        9 | JOE        | SWANK        |        25 |             14 |
|       10 | CHRISTIAN | GABLE        |        22 |             15 |
+----------+------------+--------------+-----------+----------------+
10 rows in set (0.05 sec)

Solution:

DROP VIEW actor_appearence;

CREATE VIEW actor_appearence
AS
SELECT a.actor_id, a.first_name, a.last_name,
COUNT(DISTINCT fa.film_id) AS num_films,
COUNT(DISTINCT fc.category_id) AS num_categories
FROM actor a, film_actor fa, film_category fc
WHERE a.actor_id = fa.actor_id
AND fa.film_id = fc.film_id
GROUP BY a.actor_id, a.first_name, a.last_name;

-- Testing:
SELECT *
FROM actor_appearence
LIMIT 10;

(2) Using the view actor_appearence, provide the SQL statement to list all actors who have appeared in more films than HENRY BERRY in the exact format below.

+-----+----------------+-----------------+
| ID | Name           | Number of Films |
+-----+----------------+-----------------+
| 107 | GINA DEGENERES |              42 |
| 102 | WALTER TORN    |              41 |
| 198 | MARY KEITEL    |              40 |
| 181 | MATTHEW CARREY |              39 |
| 23 | SANDRA KILMER |              37 |
| 81 | SCARLETT DAMON |              36 |
+-----+----------------+-----------------+
6 rows in set (0.10 sec)

Solution:

SELECT actor_id AS "ID",
       CONCAT(first_name, ' ', last_name) AS "Name",
       num_films as "Number of Films"
FROM actor_appearence
WHERE num_films >
    (SELECT num_films
     FROM actor_appearence
     WHERE first_name = 'HENRY'
     AND last_name = 'BERRY')
ORDER BY num_films DESC;

(3) Write a function num_same_film_rented(cid_1 INT, cid_2 INT) to return the number of films both customers cid_1 and cid_2 have rented. For example:

mysql> SELECT num_same_film_rented(1, 558);
+------------------------------+
| num_same_film_rented(1, 558) |
+------------------------------+
| 3 |
+------------------------------+
1 row in set (0.01 sec)

Solution:

DROP FUNCTION num_same_film_rented;

DELIMITER $$

CREATE FUNCTION num_same_film_rented(cid_1 INT, cid_2 INT) RETURNS FLOAT
READS SQL DATA
BEGIN
   DECLARE result INTEGER DEFAULT 0;

   SELECT COUNT(DISTINCT i1.film_id) INTO result
   FROM rental r1, rental r2, inventory i1, inventory i2
   WHERE r1.inventory_id = i1.inventory_id
   AND r2.inventory_id = i2.inventory_id
   AND i1.film_id = i2.film_id
   AND r1.customer_id = cid_1
   AND r2.customer_id = cid_2;

   return result;
END $$

DELIMITER ;

SELECT num_same_film_rented(1, 558);

(4) Write a procedure, PROCEDURE most_similar_customers(cid INT, min_count INT) to return the customers most similar to cid. The most similar customers are defined to be the ones with at least min_count number of same rented films as customer cid.

For example:

mysql> CALL most_similar_customers(1,3);
+-------------+-----------------------------+
| customer_id | Number of same rented films |
+-------------+-----------------------------+
|         406 |                           4 |
|         447 |                           4 |
|         383 |                           4 |
|          52 |                           4 |
|         155 |                           4 |
|          56 |                           3 |
|         161 |                           3 |
|         187 |                           3 |
|         513 |                           3 |
|         558 |                           3 |
|           5 |                           3 |
|          59 |                           3 |
|         102 |                           3 |
|         189 |                           3 |
|         297 |                           3 |
|         503 |                           3 |
|          45 |                           3 |
|         380 |                           3 |
|         277 |                           3 |
|         448 |                           3 |
|         479 |                           3 |
|          16 |                           3 |
|         173 |                           3 |
|         253 |                           3 |
|         426 |                           3 |
|         460 |                           3 |
|          28 |                           3 |
|         112 |                           3 |
|         230 |                           3 |
|         269 |                           3 |
|         372 |                           3 |
|         303 |                           3 |
|         375 |                           3 |
|         485 |                           3 |
+-------------+-----------------------------+
34 rows in set (0.01 sec)

Query OK, 0 rows affected (0.15 sec)

Solution:

DROP PROCEDURE most_similar_customers;

DELIMITER $$

CREATE PROCEDURE most_similar_customers(cid INT, min_count INT)
READS SQL DATA
BEGIN
   SELECT r2.customer_id, COUNT(DISTINCT i1.film_id) AS "Number of same rented films"
   FROM rental r1, rental r2, inventory i1, inventory i2
   WHERE r1.inventory_id = i1.inventory_id
   AND r2.inventory_id = i2.inventory_id
   AND i1.film_id = i2.film_id
   AND r1.customer_id = cid
   AND r2.customer_id <> cid
   GROUP BY r2.customer_id
   HAVING COUNT(DISTINCT i1.film_id) >= min_count
   ORDER BY COUNT(DISTINCT i1.film_id) DESC;
END $$

DELIMITER ;

CALL most_similar_customers(1,3);

(5) Write a function, category_feature_length(customer_id INT), to compute the length of the category feature vector. Each element in the category festure vector is the number of the flims the customer has rented in each category in order.

For example, for customer 1, the category feature vector is:

(2,2,6,5,2,4,1,1,1,4,2,2,2,2,1).

For customer 20, it is:

(1,1,3,2,3,2,2,1,1,1,1,7,0,4,1). Note the 0 as the 14th element.

The length of the vector is the square root of the sum of each element.

Solution:

--
-- List the number of films rented in film categories for each customer.
--
DROP VIEW customer_category_rental;

CREATE VIEW customer_category_rental
AS
SELECT
   r.customer_id AS customer_id,
   fc.category_id AS category_id,
   count(*) AS count
FROM rental AS r
   INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
   INNER JOIN film AS f ON i.film_id = f.film_id
   INNER JOIN film_category AS fc ON f.film_id = fc.film_id
GROUP BY r.customer_id, fc.category_id
ORDER BY r.customer_id, fc.category_id;

DROP FUNCTION category_feature_length;

DELIMITER $$

CREATE FUNCTION category_feature_length(customer_id INT) RETURNS FLOAT
READS SQL DATA
BEGIN
   DECLARE result FLOAT DEFAULT NULL;

   SELECT SUM(Count * Count) INTO result
   FROM customer_category_rental AS C
   WHERE c.customer_id = customer_id;

   IF Result IS NULL THEN
      Return 0;
   ELSE
      Return SQRT(Result);
   END IF;
END $$

DELIMITER ;

One can define and use cursors to iterative through SQL results.

Example:

(6) Write a function, category_feature_distance(cid_1 INT, cid_2 INT), to compute the distance between two customers (with cutomer id cid_1 and cid_2). The function does not need to be efficient.

For example,

mysql> select category_feature_distance(1,20);
+---------------------------------+
| category_feature_distance(1,20) |
+---------------------------------+
| 0.08313219994306564 |
+---------------------------------+
1 row in set, 1 warning (4.80 sec)

Solution:

SELECT category_feature_length(1);

-- inefficient version using cursor.
DROP FUNCTION category_feature_distance;

DELIMITER $$

CREATE FUNCTION category_feature_distance(cid_1 INT, cid_2 INT) RETURNS FLOAT
READS SQL DATA
BEGIN
   DECLARE result FLOAT;
   DECLARE length_1 FLOAT;
   DECLARE length_2 FLOAT;

   DECLARE done INT DEFAULT FALSE;
   DECLARE cat_id INT;
   DECLARE count_1 INT;
   DECLARE count_2 INT;

   DECLARE cursor_1 CURSOR FOR SELECT distinct category_id from category;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   SET length_1 = category_feature_length(cid_1);
   IF length_1 <= 0.0 THEN
      RETURN NULL;
   END IF;

   SET length_2 = category_feature_length(cid_2);
   IF length_2 <= 0.0 THEN
      RETURN NULL;
   END IF;

   OPEN cursor_1;

   SET result = 0;
   compute_loop: LOOP
      -- This is needed as there are other SQL statements that can set Done to true.
      SET done = FALSE;
      FETCH cursor_1 INTO cat_id;
      IF done THEN
         LEAVE compute_loop;
      END IF;

      SET count_1 = 0;
      SELECT count INTO count_1
      FROM customer_category_rental as C
      WHERE C.category_id = cat_id
      AND C.customer_id = cid_1;

      SET count_2 = 0;
      SELECT count INTO count_2
      FROM customer_category_rental as C
      WHERE C.category_id = cat_id
      AND C.customer_id = cid_2;

      SET result = result + (count_1 - count_2) * (count_1 - count_2);

   END LOOP;

   CLOSE cursor_1;
   return SQRT(result) / (length_1 * length_2);
END $$

DELIMITER ;

5. Triggers

Triggers allow event-driven programming.
A trigger is activated when certain events occur. Unlike stored routines, trigger is not called directly.
Events:
- Insert a row
- Update a row
- Delete a row
Actions can be executed:

Before the event
After the event

Information can be obtained through:
- old:
  - the old value of the row before the event.
  - for update and delete events
- new:
  - the new value of the row after the event.
  - for update and insert events
Advantages of triggers:
1. event driven model suit certain kind of tasks better.
2. implementation of consistency check.
3. implementation of business logic and integrity check.
Disadvantages:
1. Relatively invisible and possibly overlook by developers.
2. Potential performance issues.
3. Potential complicated interactions.

Example:

Using the Sakila database, consider the tables inventory and rental.

inventory (inventory_id, film_id, store_id, last_update)
rental (rental_id, rental-date, inventory_id, customer_id, return_date, staff_id, last_update)

The stored function inventory_in_stock(p_inventory_id INT) checks whether an inventory item is in stock. Its logic:

"AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED."

CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
READS SQL DATA
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
    #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

   -- Redundant as the test is covered by the next SQL statement
    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END $$

DELIMITER ;

This can be inefficient.

We consider modifying the table inventory to speed up performance.

inventory (inventory_id, film_id, store_id, lost, in_stock, last_update)

Two columns are added:

lost: whether the inventory item has been lost; default FALSE.
in_stock: whether the inventory item is in stock; default TRUE.

We may consider that the column in_stock as a derived column. Its value can be derived from other columns and tables in the database. Maintaining data consistency of derived columns is a popular use of triggers.

ALTER TABLE inventory
ADD COLUMN lost BOOLEAN DEFAULT FALSE AFTER store_id;

ALTER TABLE inventory
ADD COLUMN in_stock BOOLEAN DEFAULT TRUE AFTER lost;

To reverse the operations:

ALTER TABLE inventory DROP COLUMN lost;
ALTER TABLE inventory DROP COLUMN in_stock;

To populate the columns initially:

UPDATE inventory
SET lost = FALSE;

UPDATE inventory
SET in_stock = inventory_in_stock(inventory_id);

-- checking
select * from inventory limit 5;

We now need to define triggers when we insert or update a rental row. We assume that a rental row will never be deleted.

The trigger after inserting a rental row is shown below. There may be an error. Can you spot it?

DELIMITER $$
CREATE TRIGGER insert_rental AFTER INSERT ON rental FOR EACH ROW
BEGIN
   IF (new.return_date IS NULL)
   THEN
      UPDATE inventory
      SET in_stock = FALSE
      WHERE inventory.inventory_id = rental.inventory_id;
   END IF;
END$$
DELIMITER ;

Note:

The trigger name, which can be used later.
The event that activates the trigger: "AFTER INSERT ON rental".
The "FOR EACH ROW" clause to indicate the action will be conducted for each new rental row insertion.
The keyword "new" is used to indicate the row after the activating event. The keyword old is used to indicate the row before the activating event
The error in the code above: rental.inventory_id should be new.inventory_id

Checking:

-- Checking insert trigger.
SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

-- Rent inventory_id 1 out.
INSERT INTO rental
VALUES (16050,'2011-11-11 22:53:30',1,130,NULL,1,'2011-11-11 23:30:53');

SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

For the update trigger:

DELIMITER $$
CREATE TRIGGER update_rental AFTER UPDATE ON rental FOR EACH ROW
BEGIN
   IF ((old.return_date IS NULL) and (new.return_date IS NOT NULL))
   THEN
      UPDATE inventory
      SET in_stock = TRUE
      WHERE inventory.inventory_id = new.inventory_id;
   END IF;
END$$
DELIMITER ;

To check:

-- Checking insert trigger.
SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

UPDATE rental
SET return_date = '2011-11-11 23:30:53'
WHERE rental_id = 16050;

SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

-- House keeping to revert to original state
DELETE FROM rental WHERE rental_id = 16050;
DROP TRIGGER insert_rental;
DROP TRIGGER update_rental;
ALTER TABLE inventory DROP COLUMN lost;
ALTER TABLE inventory DROP COLUMN in_stock;