Database Tutorial

Course Tutorial Site

Site Admin

Help Section

without comments

Lab #4: Insert Patterns

  • The literal values can be numbers, dates, or strings. Numbers are inserted without delimiters. Strings and dates are inserted with single quote delimiters. Dates are single quote delimited and must conform to one of the default format masks or use the TO_DATE function to use a non-default format mask.
  • The sequence values use the .nextval for new primary key sequence values and .currval for foreign key sequence values where the inserted values are sequential.
  • The scalar subqueries use the natural key in the WHERE clause to discover a row, and the subquery’s SELECT-list returns the primary key value of that row. Scalar subqueries are the way developers discover and copy a primary key into a foreign key column.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

The create_oracle_store2.sql script creates ten tables as the video store. It is an integration script that calls individual scripts that create individual tables. Each of those CREATE and ALTER SQL scripts contain diagnostic queries that verify the integrity of their Data Manipulation Language (DML) statements.

Students modified the DML statements and created ten tables that mirror the video store table in Lab #2. The mirrored tables all have the same table names plus an _LAB suffix. The surrogate key columns of the ten tables have column table names that adopt the convention of table name plus an _ID suffix.

The video store model uses the following ERD (double-click on the image to view it in full screen mode):

The Video Store ERD shows you the physical relationship between tables, which are all one-to-many relationships. All relationships between the tables are composed of two relations. The one side points to the parent primary key in a relationship between two tables; and the many side points to the foreign key, which is a copy of the primary key column’s value.

The grandma and tweetie_bird tables show you how these one-to-many relationships work between tables with foreign key constraints. They also show you how INSERT and UPDATE statements work.

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

/* Conditionally drop the GRANDMA and TWEETIE_BIRD tables, and
   the GRANDMA_SEQ and TWEETIE_BIRD sequences. */
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('GRANDMA','GRANDMA_SEQ'
                                    ,'TWEETIE_BIRD','TWEETIE_BIRD_SEQ')
            ORDER BY object_type ) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the grandma table:
    - A natural key of grandma_house and grandma_name columns.
    - A surrogate and primary key column that uses the table name 
      plus _ID suffix.
    - A primary key constraint on the surrogate key column.
    - A uq_grandma unique index across the two natural key columns.
*/
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT pk_grandma    PRIMARY KEY
, grandma_house  VARCHAR2(30) CONSTRAINT nn_grandma_1  NOT NULL
, grandma_name   VARCHAR2(30) CONSTRAINT nn_grandma_2  NOT NULL
, CONSTRAINT uq_grandma       UNIQUE (grandma_house, grandma_name));
 
/* Create the grandma_seq sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Create the tweetie_bird table with:
    - A natural key of tweetie_bird_house and tweetie_bird columns.
    - A surrogate and primary key column that uses the table name
    - plus an _ID suffix.
    - A primary key constraint on the surrogate key column.
    - A foreign key constraint on the grandma_id column of the grandma table.
    - A uq_tweetie_bird unique index across the two natural key columns.
*/
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id    NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_name  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id         NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, CONSTRAINT pk_tweetie_bird       PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT fk_tweetie_bird       FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
, CONSTRAINT uq_tweetie_bird       UNIQUE (tweetie_bird_name));
 
/* Create the tweetie_bird_seq sequence. */
CREATE SEQUENCE tweetie_bird_seq;

You can now describe the grandma and tweetie_bird tables. You must insert values into the independent or unconstrained grandma table before you insert a row in the tweetie_bird table.

Insert statements are ACID-compliant transactions. ACID is an acronym for Atomic, Consistency, Isolated, and Durable. Atomic transactions are all or nothing transactions. Consistency means that whether you process the inserts serially or in parallel will succeed. Isolated means that you can’t see partial results until they’re complete. Durable means that the transactions becomes permanent.

SQL provides Transaction Control Language (TCL) to manage sets of INSERT statements across more than two tables. The following example will show you how to insert data across a pair of tables. The INSERT statement will use the .NEXTVAL pseudo column for primary keys and the .CURRVAL pseudo column for foreign keys.

/* Set the save point like a book mark sets a page number. If any part
   of the transaction fails, you return to this point by rolling back
   the parts that did complete. */
SAVEPOINT starting_place;
 
/* Insert into grandma table. */
INSERT INTO grandma
( grandma_id
, grandma_house
, grandma_name )
VALUES
( grandma_seq.NEXTVAL
,'Yellow'
,'Hazel');
 
/* Insert into tweetie_bird table. */
INSERT INTO tweetie_bird
( tweetie_bird_id 
, tweetie_bird_name
, grandma_id )
VALUES
( tweetie_bird_seq.NEXTVAL
,'Henry'
, grandma_seq.CURRVAL );

If one of the inserts failed, you would issue a:

ROLLBACK TO starting_place;

A ROLLBACK statement like that undoes everything after the SAVEPOINT statement. It basically returns the data in the grandma and tweetie_bird tables to their states before you attempted the INSERT statements.

If both of the INSERT statements succeeded, you would make their changes permanent with a:

COMMIT;

The COMMIT statement makes your changes permanent and visible to other sessions of the same student user and to other database users to whom you’ve granted access to the grandma and tweetie_bird tables.

The lab has ten parts. Each of the parts links to a SQL script file, and you should treat each file as a distinct use case that requires unit testing. The last part actually calls the nine SQL scripts in sequence and becomes an integration test of your code.

Written by michaelmclaughlin

August 13th, 2018 at 10:49 am

Posted in