Database Tutorial

Course Tutorial Site

Site Admin

Help Section

without comments

Lab #1: Installations

Learn about hierarchical, networked, and relational databases; introduce you to how to navigate Oracle’s SQL*Plus Command-line Interface (CLI); and how basic SQL queries work.

Help Section

There are many moving parts in the Linux and Oracle environment. You are asked to do the following three things before beginning the weekly lab assignment.

You are asked to review the following preparation material:

Oracle’s APEX tool is a browser-based Integrated Development Environment (IDE). Oracle’s SQL Developer is a Java-based Integrated Development Environment (IDE).

Instructional Material →

The supplemental materials depend on your reading and understanding the preparation material qualified earlier in this lab. The coding segments below show you:

  • How to create the grandma and tweetie_bird tables
  • How to insert rows into each of these table in sequential order
  • How to create an integration script that tests both unit tests

The instructions walk you through creating three script files. One script file creates the grandma and tweetie_bird tables and a second script file enters data into the tables in sequence. Each of the first two script files constitutes a unit test. The third script file calls the two script files in sequence and by so doing integrates the behavior of the first two script files.

  1. Unit Test Module 1

    2. You can do this assignment by launching the gedit editor. Then, you can copy the following code contents into a create_sample_tables.sql file. You should save the file in your /user/student/Data/cit225/oracle/lab1 directory.

    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

    /* 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;
 
/* Verify that you successfully created the two tables by querying the
   data dictionary. The data dictionary stores information about all
   structures, like tables and constraints, that you create in any
   database. */
SELECT   table_name
FROM     user_tables
WHERE    table_name IN ('GRANDMA','TWEETIE_BIRD')
ORDER BY table_name;

    After you save the create_sample_tables.sql file, you should navigate to the /user/student/Data/cit225/oracle/lab1 directory and logon to the SQL*Plus environment like this:

    sqlplus student/student

    From the SQL> command prompt, you can run the create_sample_tables.sql script file with the following relative path command, which relies on your launching SQL*Plus (sqlplus) from the same directory where the file you want to run is stored.

    @create_sample_tables.sql

    The script should show you that it created two tables by returning the following from the data dictionary query:

    TABLE_NAME
------------------------------
GRANDMA
TWEETIE_BIRD

    You have now completed your first unit test module. You have successfully written and deployed a small script file and performed a unit test on its functionality.

  2. Unit Test Module 2

    3. There are a few things you need to understand before you add data into the tables. These are the rules that govern how you interact with data in a multiuser database system.

    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.

    While every INSERT, UPDATE, and DELETE statement is ACID compliant against a single table, SQL provides Transaction Control Language (TCL) to manage interactions across more than two tables. The following example will show you how to add data into a pair of tables.

    The INSERT statement will use the .NEXTVAL pseudo column for primary keys and the .CURRVAL pseudo column for foreign keys. At this point, you simply have to trust this syntax that will be explained in the subsequent week’s content.

    You can add the data by creating another small script file. You launch or relaunch the gedit editor. Like the prior unit test module, you copy the following code contents into a new file. You should call it the add_data_in_tables.sql file, and store the add_data_in_tables.sql file in your /user/student/Data/cit225/oracle/lab1 directory.

    /* 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 );
 
/* Query the joined results of the insert into two tables. */
COL grandma_house      FORMAT A14  HEADING "Grandma|House"
COL grandma_name       FORMAT A14  HEADING "Grandma|Name"
COL tweetie_bird_name  FORMAT A14  HEADING "Tweetie Bird|Name"
SELECT grandma_house
,      grandma_name
,      tweetie_bird_name
FROM   grandma g JOIN tweetie_bird tb ON g.grandma_id = tb.grandma_id;

    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.

    For the purpose of this lab you do not need to use a COMMIT or ROLLBACK statement. You unit test your add_data_in_tables.sql file by connecting to the sqlplus utility. As the student user, you run the file with the following syntax:

    @add_data_in_tables.sql

    The script should show you that it created two tables by returning the following from the data dictionary query:

    Grandma        Grandma	      Tweetie Bird
House	       Name	      Name
-------------- -------------- --------------
Yellow	       Hazel	      Henry

    You have now successfully completed the second unit test.

  3. Integration Test Module

    4. An integration script calls one or more unit test scripts. You write integration test scripts to package or bundle your code components by modules.

    You create your integration script inside the gedit editor. You should create the apply_sample_script.sql integration script in the /home/student/Data/cit225/oracle/lab directory, and it should call both of the unit test programs, like the following example file.

    -- Run the unit test scripts.
@/home/student/Data/cit225/oracle/lab1/create_sample_tables.sql
@/home/student/Data/cit225/oracle/lab1/add_data_in_tables.sql
 
 
-- Put any program logic specific to the integration script.  
SPOOL apply_sample_lab.txt
 
-- Insert your SQL statements here ... 
-- start with the validation scripts ...
 
SPOOL OFF
 
-- ------------------------------------------------------------------
--  This is necessary to avoid a resource busy error. You can
--  inadvertently create a resource busy error when testing in two
--  concurrent SQL*Plus sessions unless you provide an explicit
--  COMMIT; statement. 
-- ------------------------------------------------------------------
COMMIT;

    You can test the apply_sample_lab.sql with the following syntax:

    @apply_sample_lab.sql

    You have successfully run the integration test after you run the apply_sample_lab.sql script file.

Written by michaelmclaughlin

August 13th, 2018 at 12:36 pm

Posted in