Database Tutorial

Implementation Note: The TRANSACTION table has a range relationship to a CALENDAR table that will be defined later.

Business Rule: You may enter an account number into the TRANSACTION_ACCOUNT column of 111-111-111-111 for a debit, and 222-222-222-222 for a credit. A debit recognizes payment for a rental, and is a value that adds to the company’s assets. A credit recognizes a refund for a rental, and is a value that decreases the company’s assets. Debits should have a TRANSACTION_TYPE that maps to the corresponding debit row in the COMMON_LOOKUP table, and credits should have a TRANSACTION_TYPE that maps to the corresponding credit row in the COMMON_LOOKUP table.

System Logic: In a future refinement of our pilot design, the TRANSACTION_ACCOUNT column will be replaced by a foreign key that points to the CHART_OF_ACCOUNT table.

After you create the table, you need to add a UNIQUE INDEX on the following columns to the TRANSACTION table. This is necessary to minimize the run-time performance of the merge operations later in this lab.

• RENTAL_ID
• TRANSACTION_TYPE
• TRANSACTION_DATE
• PAYMENT_METHOD_TYPE
• PAYMENT_ACCOUNT_NUMBER

Don’t forget that on the Oracle Database 10g Express Edition, that you’ll need to provide an explicit TRANSACTION_S1 sequence. The sequence should start with the default value of a 1.

Implementation Note: The AIRPORT table has a filtered substring relationship to ACCOUNT numbers because the business model is evolving toward airport kiosks rather than stores. The ACCOUNT_LIST contains a set of pre-seeded values that may be consumed as new customers are entered into the Video Store application.

Business Rule: You must seed the AIRPORT with any additional information that may be added to the model to support new customers. The city where the customer lives needs to be mapped to the nearest regional airport. All ACCOUNT_NUMBER column values must contain a valid account number found in the ACCOUNT_LIST table. When an account number is assigned to a customer, you must indicate the SYSTEM_USER_ID linked to assigning the account number in the CONSUMED_BY column of the ACCOUNT_LIST table, and provide the date of that activity in the CONSUMED_BY column.

System Logic: Seeding procedures are provided in the lab to update all existing data in the model, provided you add any required new cities in the AIRPORT table. The import of external records is now dependent on accurate ACCOUNT_NUMBER values in the MEMBER table. You must update all pre-existing records.

1. You need to create the AIRPORT table and ACCOUNT_LIST tables.

2. You need to seed the AIRPORT table with at least these cities, and any others that you’ve used for inserted values in the CONTACT table.

3. You need to seed the ACCOUNT_LIST table. This would take more time than leveraging a stored procedure but writing stored procedures from class is a beyond scope. Therefore, you’ve been provided with stored procedures for the databases.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Create or replace seeding procedure.
CREATE OR REPLACE PROCEDURE seed_account_list IS
BEGIN
  /* Set savepoint. */
  SAVEPOINT all_or_none;
 
  FOR i IN (SELECT DISTINCT airport_code FROM airport) LOOP
    FOR j IN 1..50 LOOP
 
      INSERT INTO account_list
      VALUES
      ( account_list_s1.NEXTVAL
      , i.airport_code||'-'||LPAD(j,6,'0')
      , NULL
      , NULL
      , 2
      , SYSDATE
      , 2
      , SYSDATE);
    END LOOP;
  END LOOP;
 
  /* Commit the writes as a group. */
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
END;
/

EXECUTE seed_account_list();

SELECT COUNT(*) AS "# Accounts"
FROM   account_list;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE seed_account_list' AS "Statement";
DROP PROCEDURE IF EXISTS seed_account_list;
 
-- Create procedure to insert automatic numbered rows.
SELECT 'CREATE PROCEDURE seed_account_list' AS "Statement";
 
-- Reset delimiter to write a procedure.
DELIMITER $$
 
CREATE PROCEDURE seed_account_list() MODIFIES SQL DATA
BEGIN
 
  /* Declare local variable for call parameters. */
  DECLARE lv_key CHAR(3);
 
  /* Declare local control loop variables. */
  DECLARE lv_key_min  INT DEFAULT 0;
  DECLARE lv_key_max  INT DEFAULT 50;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE parameter_cursor CURSOR FOR
    SELECT DISTINCT airport_code FROM airport;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a local cursor. */  
  OPEN parameter_cursor;
  cursor_parameter: LOOP
 
    FETCH parameter_cursor
    INTO  lv_key;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_parameter; END IF;
 
    seed: WHILE (lv_key_min < lv_key_max) DO
      SET lv_key_min = lv_key_min + 1;
 
      INSERT INTO account_list
      VALUES
      ( NULL
      , CONCAT(lv_key,'-',LPAD(lv_key_min,6,'0'))
      , NULL
      , NULL
      , 2
      , UTC_DATE()
      , 2
      , UTC_DATE());
    END WHILE;
 
    /* Reset nested low range variable. */
    SET lv_key_min = 0;
 
  END LOOP cursor_parameter;
  CLOSE parameter_cursor;
 
    /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
  /* Commit the writes as a group. */
  COMMIT;
 
END;
$$
 
-- Reset delimiter to the default.
DELIMITER ;

CALL seed_account_list();

SELECT COUNT(*) AS "# Accounts"
FROM   account_list;

4. In a prior lab and in both the create and seed scripts, the STATE_PROVINCE column values used a mix of USPS state abbreviations and full name. The next seeding program and the import program rely on the use of a full state name. You need to update all values that don’t have the full state name. The script updates only the create and seed store script data. You need to modify the script to work with your independent entries from Lab #6.

1
2
3

UPDATE address
SET    state_province = 'California'
WHERE  state_province = 'CA';

5. The rules for replacing ACCOUNT_NUMBER column values in the MEMBER table are complex. A single query can’t support the complete logic. Another procedure has been written for you to use to accomplish this task. As with other provided code samples, you’re responsible for learning how and why this work to accomplish the task at hand.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

CREATE OR REPLACE PROCEDURE update_member_account IS
 
  /* Declare a local variable. */
  lv_account_number VARCHAR2(10);
 
  /* Declare a SQL cursor fabricated from local variables. */  
  CURSOR member_cursor IS
    SELECT   DISTINCT
             m.member_id
    ,        a.city
    ,        a.state_province
    FROM     member m INNER JOIN contact c
    ON       m.member_id = c.member_id INNER JOIN address a
    ON       c.contact_id = a.contact_id
    ORDER BY m.member_id;
 
BEGIN
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a local cursor. */  
  FOR i IN member_cursor LOOP
 
      /* Secure a unique account number as they're consumed from the list. */
      SELECT al.account_number
      INTO   lv_account_number
      FROM   account_list al INNER JOIN airport ap
      ON     SUBSTR(al.account_number,1,3) = ap.airport_code
      WHERE  ap.city = i.city
      AND    ap.state_province = i.state_province
      AND    consumed_by IS NULL
      AND    consumed_date IS NULL
      AND    ROWNUM < 2;
 
      /* Update a member with a unique account number linked to their nearest airport. */
      UPDATE member
      SET    account_number = lv_account_number
      WHERE  member_id = i.member_id;
 
      /* Mark consumed the last used account number. */      
      UPDATE account_list
      SET    consumed_by = 2
      ,      consumed_date = SYSDATE
      WHERE  account_number = lv_account_number;
 
  END LOOP;
 
  /* Commit the writes as a group. */
  COMMIT;
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('You have an error in your AIRPORT table inserts.');
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
  WHEN OTHERS THEN
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED
EXECUTE update_member_account();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Format the SQL statement display.
COLUMN member_id      FORMAT 999999 HEADING "Member|ID #"
COLUMN last_name      FORMAT A10    HEADING "Last|Name"
COLUMN account_number FORMAT A10 HEADING "Account|Number"
COLUMN city           FORMAT A16 HEADING "City"
COLUMN state_province FORMAT A10 HEADING "State or|Province"
 
-- Query distinct members and addresses.
SELECT   DISTINCT
         m.member_id
,        c.last_name
,        m.account_number
,        a.city
,        a.state_province
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id
ORDER BY 1;

 Member Last       Account                     State or
   ID # Name       Number     City             Province
------- ---------- ---------- ---------------- ----------
   1001 Winn       SJC-000001 San Jose         California
   1002 Vizquel    SJC-000002 San Jose         California
   1003 Sweeney    SJC-000003 San Jose         California
   1004 Clinton    SLC-000001 Provo            Utah
   1005 Moss       SLC-000002 Provo            Utah
   1006 Gretelz    SLC-000003 Provo            Utah
   1007 Royal      SLC-000004 Provo            Utah
   1008 Smith      SLC-000005 Spanish Fork     Utah

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE update_member_account' AS "Statement";
DROP PROCEDURE IF EXISTS update_member_account;
 
-- Create procedure to insert automatic numbered rows.
SELECT 'CREATE PROCEDURE update_member_account' AS "Statement";
 
-- Reset delimiter to write a procedure.
DELIMITER $$
 
CREATE PROCEDURE update_member_account() MODIFIES SQL DATA
BEGIN
 
  /* Declare local variable for call parameters. */
  DECLARE lv_member_id      INT UNSIGNED;
  DECLARE lv_city           CHAR(30);
  DECLARE lv_state_province CHAR(30);
  DECLARE lv_account_number CHAR(10);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE member_cursor CURSOR FOR
    SELECT DISTINCT
           m.member_id
    ,      a.city
    ,      a.state_province
    FROM   member m INNER JOIN contact c
    ON     m.member_id = c.member_id INNER JOIN address a
    ON     c.contact_id = a.contact_id;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a local cursor. */  
  OPEN member_cursor;
  cursor_member: LOOP
 
    FETCH member_cursor
    INTO  lv_member_id
    ,     lv_city
    ,     lv_state_province;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_member; END IF;
 
      /* Secure a unique account number as they're consumed from the list. */
      SELECT al.account_number
      INTO   lv_account_number
      FROM   account_list al INNER JOIN airport ap
      ON     SUBSTRING(al.account_number,1,3) = ap.airport_code
      WHERE  ap.city = lv_city
      AND    ap.state_province = lv_state_province
      AND    consumed_by IS NULL
      AND    consumed_date IS NULL LIMIT 1;
 
      /* Update a member with a unique account number linked to their nearest airport. */
      UPDATE member
      SET    account_number = lv_account_number
      WHERE  member_id = lv_member_id;
 
      /* Mark consumed the last used account number. */      
      UPDATE account_list
      SET    consumed_by = 2
      ,      consumed_date = UTC_DATE()
      WHERE  account_number = lv_account_number;
 
  END LOOP cursor_member;
  CLOSE member_cursor;
 
    /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
  /* Commit the writes as a group. */
  COMMIT;
 
END;
$$
 
-- Reset delimiter to the default.
DELIMITER ;

SELECT 'CALL update_member_account' AS "Statement";
CALL update_member_account();

1
2
3
4
5
6
7
8
9

SELECT DISTINCT
       m.member_id
,      c.last_name
,      m.account_number
,      a.city
,      a.state_province
FROM   member m INNER JOIN contact c
ON     m.member_id = c.member_id INNER JOIN address a
ON     c.contact_id = a.contact_id;

+-----------+-----------+----------------+--------------+----------------+
| member_id | last_name | account_number | city         | state_province |
+-----------+-----------+----------------+--------------+----------------+
|         1 | Winn      | SJC-000001     | San Jose     | California     |
|         2 | Vizquel   | SJC-000002     | San Jose     | California     |
|         3 | Sweeney   | SJC-000003     | San Jose     | California     |
|         4 | Clinton   | SLC-000001     | Provo        | Utah           |
|         5 | Moss      | SLC-000002     | Provo        | Utah           |
|         6 | Gretelz   | SLC-000003     | Provo        | Utah           |
|         7 | Royal     | SLC-000004     | Provo        | Utah           |
|         8 | Smith     | SLC-000005     | Spanish Fork | Utah           |
+-----------+-----------+----------------+--------------+----------------+

Implementation Note: The TRANSACTION_UPLOAD table is in unnormalized form, which means it has repeating rows and unique rows.

Business Rule: The data has secured a valid CUSTOMER_ID and ITEM_ID before generating the CSV source file. You need to create an import table to support the upload of remote store data.

System Logic: This will be implemented in an Oracle database as an external table, and in MySQL as a memory resident table. Memory resident tables disappear when the database is rebooted. You use the ENGINE=MEMORY as opposed to the ENGINE=INNODB syntax. These rules support the business rules provided you have no FOREIGN KEY references in the TRANSACTION_UPLOAD table.

1
2

CREATE INDEX tu_rental
 ON transaction_upload (account_number, first_name, last_name, check_out_date, return_date);

1
2
3

ALTER TABLE rental_item
ADD CONSTRAINT natural_key 
UNIQUE INDEX (rental_item_id, rental_id, item_id, rental_item_type, rental_item_price);

1
2
3

ALTER TABLE member
ADD CONSTRAINT member_u1
UNIQUE INDEX member_key (account_number, credit_card_number, credit_card_type, member_type);

1

SHOW INDEXES FROM common_lookup;

+---------------+------------+-------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name          | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| common_lookup |          0 | PRIMARY           |            1 | common_lookup_id     | A         |          34 |     NULL | NULL   |      | BTREE      |         |
| common_lookup |          0 | common_lookup_key |            1 | common_lookup_table  | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| common_lookup |          0 | common_lookup_key |            2 | common_lookup_column | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| common_lookup |          0 | common_lookup_key |            3 | common_lookup_type   | A         |          34 |     NULL | NULL   |      | BTREE      |         |
| common_lookup |          1 | common_lookup_fk1 |            1 | created_by           | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| common_lookup |          1 | common_lookup_fk2 |            1 | last_updated_by      | A         |           2 |     NULL | NULL   |      | BTREE      |         |
+---------------+------------+-------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+

1
2

ALTER TABLE common_lookup ADD CONSTRAINT common_lookup_u1 UNIQUE INDEX 
  common_lookup_key (common_lookup_table,common_lookup_column,common_lookup_type);

The mapping of the TRANSACTION_UPLOAD external table to the TRANSACTION table requires some translations and mappings. A translation may mean that we take a column value from the source table and use it as a filter in a join to find a key value in another table. Translation may also mean that we apply an aggregation function to a source column. Mapping may mean associating a column name in the import *.csv file, or external table definition, with a column in a target table. A second meaning of mapping may mean that you need to join tables on a natural key to find a surrogate key. Lastly, mapping may mean linking to the system access logs or local current clock.

An example of translation would be using a column value from the *.csv files to find a valid COMMON_LOOKUP_ID value. Any *.csv file column that maps to a value found in the COMMON_LOOKUP_TYPE column of the COMMON_LOOKUP table is a key to discover a proper foreign key value for an import target table.

The system analyst was pushed for time, and your manager assured him that you could build your own mapping guides on the first two MERGE statements. The system analysis did provide a mapping guide between the import *.csv file or external table for the TRANSACTION table because of its complexity. The table provides both translations and mappings. An example of how you may incorporate translation in your statements follows the table.

You should probably develop mapping tables for the first two MERGE statements before you write the code. Then, you should leverage this one while writing the third MERGE statements. This is the “Mapping Table” referred to by Hint #5 on the third query.

Implementation Note: The TRANSACTION_UPLOAD table is in unnormalized form, which means it has repeating rows and unique rows. This generally requires a logic similar to the Oracle MERGE statement, or the MySQL INSERT ... ON DUPLICATE KEY statement.

Business Rule: The source file doesn’t contain any of the surrogate keys and you’ll need to populate them. As part of your uploading and transaforming process.

System Logic: Implement consistent with the business rule and available resources on the respective platforms. You can download the *.csv files here.

This step requires you to build a merge procedure that merges a source data set into three tables. The following demonstrates the Oracle pseudo code for the procedure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- Create a procedure to wrap the transaction.
CREATE OR REPLACE PROCEDURE upload_transaction_log IS 
BEGIN
  -- Set save point for an all or nothing transaction.
  SAVEPOINT starting_point;
 
  -- Merge into RENTAL table.
  MERGE INTO rental ...
 
  -- Merge into RENTAL_ITEM table.
  MERGE INTO rental_item ...
 
  -- Merge into TRANSACTION table.
  MERGE INTO TRANSACTION ...
 
  -- Save the changes.
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/

Naturally, the shell changes when you port it to MySQL or some other database. You can find the MySQL sample here.

1
2
3

UPDATE transaction_upload
SET    middle_name = NULL
WHERE  middle_name = '';

1
2
3
4
5
6
7
8

-- This uses an NVL function in Oracle, you need to change that to an IFNULL function in MySQL.
SELECT   DISTINCT c.contact_id
FROM     member m INNER JOIN transaction_upload tu
ON       m.account_number = tu.account_number INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = tu.first_name
AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
AND      c.last_name = tu.last_name;

1
2

UPDATE SET last_updated_by = SOURCE.last_updated_by
,          last_update_date = SOURCE.last_update_date

1
2
3

SELECT   COUNT(*)
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id;

1
2
3
4
5

SELECT   COUNT(*)
FROM     contact c INNER JOIN transaction_upload tu
ON       c.first_name = tu.first_name
AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
AND      c.last_name = tu.last_name;

1
2
3
4
5
6
7

SELECT   COUNT(*)
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN transaction_upload tu
ON       c.first_name = tu.first_name
AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
AND      c.last_name = tu.last_name
AND      m.account_number = tu.account_number;

1
2
3
4
5
6
7
8
9
10
11
12

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  rental_item_id
         ,        r.rental_id
         ,        tu.item_id
         ,        TRUNC(r.return_date) - TRUNC(r.check_out_date) AS rental_item_price
         ,        cl.common_lookup_id AS rental_item_type
         ,        3 AS created_by
         ,        TRUNC(creation_date) AS creation_date
         ,        3 AS last_updated_by
         ,        TRUNC(last_update_date) AS last_update_date
         FROM ... ) il

1
2
3

ON      cl1.common_lookup_table = 'TRANSACTION'
AND     cl1.common_lookup_column = 'TRANSACTION_TYPE'
AND     cl1.common_lookup_type = 'Check mapping table'

1
2
3

ON      cl1.common_lookup_table = 'TRANSACTION'
AND     cl1.common_lookup_column = 'PAYMENT_METHOD_TYPE'
AND     cl1.common_lookup_type = 'Check mapping table'

As another reminder on aggregation and comparison to the DISTINCT operator, they both help you collapse the 11,520 record sets to 4,681 rows. The DISTINCT is slightly better as an approach than the alternative GROUP BY clause.

In Oracle, you can run this query to ensure that you’ve inserted the correct number of rows.

1
2
3
4
5
6
7
8
9
10

COLUMN rental_count      FORMAT 99,999 HEADING "Rental|Count"
COLUMN rental_item_count FORMAT 99,999 HEADING "Rental|Item|Count"
COLUMN transaction_count FORMAT 99,999 HEADING "Transaction|Count"
 
SELECT   il1.rental_count
,        il2.rental_item_count
,        il3.transaction_count
FROM    (SELECT COUNT(*) AS rental_count FROM rental) il1 CROSS JOIN
        (SELECT COUNT(*) AS rental_item_count FROM rental_item) il2 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM TRANSACTION) il3;

Oracle Validation

You should see the following if everything is on track:

         Rental
 Rental    Item TRANSACTION
  COUNT   COUNT       COUNT
------- ------- -----------
  4,689  11,532       4,681

MySQL Validation

You should see the following if everything is on track:

+--------------+-------------------+-------------------+
| rental_count | rental_item_count | transaction_count |
+--------------+-------------------+-------------------+
|         4380 |             11532 |              4372 | 
+--------------+-------------------+-------------------+

In case you’ve forgotten, there where 8 rows in the RENTAL table before you started. The total reflects the new rows plus the existing rows. Likewise, you had 12 rows in the RENTAL_ITEM table and that total will be 12 higher than what you insert.

Make sure you call the UPLOAD_TRANSACTIONS procedure twice and that it returns the same result set. If you don’t test it, you may not earn full credit. When you pass-off the assignment you may only earn 90% of the points possible in the lab.

The loss of points result because your merging operation fails to normalize the denormalized data in the *.csv file. Also, the script isn’t re-runnable, which will be a requirement when you re-use this component in Lab #8.

Implementation Note: This is a preliminary report that lets the business user see what their aggregate earnings would be with a 10% and 20% increase, as well as see the incremental increases by themselves.

Business Rule: This join can be made against only the TRANSACTION table.

System Logic: Implement consistent with the business rule and available resources on the respective platforms. Performing the GROUP BY in an inline view with a non-displayed sorting key that maps to the numeric value of the month is probably the easiest approach.

You’ll also may benefit from knowing about these functions, as you solve this part of the lab. They work in both Oracle and MySQL. The EXTRACT function returns a number between 1 and 12 when you extract a MONTH, and a four-digit year when you extract a YEAR. Copy the code into a terminal session or development tool and test it.

SELECT   EXTRACT(MONTH FROM TO_DATE('02-FEB-2009'))
,        EXTRACT(YEAR FROM TO_DATE('02-FEB-2009'))
FROM     dual;

The TO_CHAR function lets you apply formatting masks in Oracle, like the following that converts numbers to dollars with two values to the right of the decimal point. Again, you can copy and test how this works.

SELECT   TO_CHAR(9999,'$9,999,999.00') AS "Formatted"
FROM     dual;

The TO_CHAR function doesn’t exist in MySQL. You must combine a number of functions to get the same behavior. Here’s the equivalent of the preceding statement in MySQL:

SELECT   LPAD(CONCAT('$',FORMAT(SUM(9999999.00),2)),14,' ') AS "Formatted"
FROM     dual;

If you changed the second call parameter to the LPAD function from 14 to 10, you’d not see any decimal points displayed because it would truncate the return value on the length of the string. You must like wise use the CONCAT function because piped concatenation never works inside function calls (you can read more on piped concatenation here).

The desired layout is noted below in the color formatted table.

The next two sections show you the expected outcome. You should note that these results work from only the seeded items. However, they do work from customers seeded and you’re three Potter users from your Lab #6.

Oracle Validation

The actual data from the import source should show the following when you run it in an Oracle database:

MONTH      BASE_REVENUE   10_PLUS        20_PLUS        10_PLUS_LESS_B 20_PLUS_LESS_B
---------- -------------- -------------- -------------- -------------- --------------
JAN-2009        $2,671.20      $2,938.32      $3,205.44        $267.12        $534.24
FEB-2009        $4,270.74      $4,697.81      $5,124.89        $427.07        $854.15
MAR-2009        $5,371.02      $5,908.12      $6,445.22        $537.10      $1,074.20
APR-2009        $4,932.18      $5,425.40      $5,918.62        $493.22        $986.44
MAY-2009        $2,216.46      $2,438.11      $2,659.75        $221.65        $443.29
JUN-2009        $1,208.40      $1,329.24      $1,450.08        $120.84        $241.68
JUL-2009        $2,404.08      $2,644.49      $2,884.90        $240.41        $480.82
AUG-2009        $2,241.90      $2,466.09      $2,690.28        $224.19        $448.38
SEP-2009        $2,197.38      $2,417.12      $2,636.86        $219.74        $439.48
OCT-2009        $3,275.40      $3,602.94      $3,930.48        $327.54        $655.08
NOV-2009        $3,125.94      $3,438.53      $3,751.13        $312.59        $625.19
DEC-2009        $2,340.48      $2,574.53      $2,808.58        $234.05        $468.10

MySQL Validation

The actual data from the import source should show the following when you run it in an MySQL database. The principal difference occurs because the data sets are different in the respective *.csv files.

+----------+------------+------------+------------+------------+------------+
| MON-YEAR | BASE       | 10_PLUS    | 20_PLUS    | 10_DIFF    | 20_DIFF    |
+----------+------------+------------+------------+------------+------------+
| JAN-2009 |  $2,957.40 |  $3,253.14 |  $3,548.88 |    $295.74 |    $591.48 |
| FEB-2009 |  $4,022.70 |  $4,424.97 |  $4,827.24 |    $402.27 |    $804.54 |
| MAR-2009 |  $5,654.04 |  $6,219.44 |  $6,784.85 |    $565.40 |  $1,130.81 |
| APR-2009 |  $4,595.10 |  $5,054.61 |  $5,514.12 |    $459.51 |    $919.02 |
| MAY-2009 |  $2,219.64 |  $2,441.60 |  $2,663.57 |    $221.96 |    $443.93 |
| JUN-2009 |  $1,300.62 |  $1,430.68 |  $1,560.74 |    $130.06 |    $260.12 |
| JUL-2009 |  $2,413.62 |  $2,654.98 |  $2,896.34 |    $241.36 |    $482.72 |
| AUG-2009 |  $2,149.68 |  $2,364.65 |  $2,579.62 |    $214.97 |    $429.94 |
| SEP-2009 |  $2,162.40 |  $2,378.64 |  $2,594.88 |    $216.24 |    $432.48 |
| OCT-2009 |  $3,291.30 |  $3,620.43 |  $3,949.56 |    $329.13 |    $658.26 |
| NOV-2009 |  $3,246.78 |  $3,571.46 |  $3,896.14 |    $324.68 |    $649.36 |
| DEC-2009 |  $2,299.14 |  $2,529.05 |  $2,758.97 |    $229.91 |    $459.83 |
+----------+------------+------------+------------+------------+------------+