CIT225: Lab 4 Instructions at Database Tutorial

CIT225: Lab 4 Instructions

Lab #3: Oracle Assignment Underdevelopment

Objectives

The lab teaches you how to work with sequential and non-sequential INSERT statements across a dependent series of tables. Also, the lab teaches you how to call an API, use subqueries and literal values in the VALUES clause of the INSERT statement; and how to use a derived table instead of a table or view in a query and how to perform a correlated UPDATE statement.

The detail learning objectives are:

Learn how to write a set of sequential INSERT statements across a set of dependent tables.
Learn how to write a set of non-sequential INSERT statement by leveraging queries to discover appropriate primary key values in an independent table that you can use as foreign key values in a related dependent table.
Learn how to use the .NEXTVAL pseudo column in one table as a primary key value; and the .CURRVAL pseudo column in another table with a dependency on the former table as a foreign key value.
Learn how to call a store procedure that acts like an API (Application Programming Interface) to a set of tables.
Learn how to write a subquery as a derived table (or the result of a table subquery) inside the FROM clause.
Learn how to correlate (or join) the results of a subquery (a correlated subquery) with an external UPDATE statement.

Business Scenario

Building data-centric application software often requires that you pre-seed data in certain database tables. The process of pre-seeding involves inserting rows of data. Sometimes the Entity Relationship Diagram (ERD) can create conflicts, which require you to temporarily disable constraints to insert data.

The lab is designed to teach you how to write INSERT statements. The INSERT statements use:

Literal values
Pseudo columns for sequence values
Subqueries that retrieve surrogate primary key values from tables by using the natural keys of the tables

You will disable foreign key constraints and remove not null constraints to pre-seed some of the tables with data. After inserting the new data, you will add re-enable foreign key constraints and re-add not null constraints. You should use the preseed_oracle_store.sql script as a reference model for SQL syntax.

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.

/* 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;

/* 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.

Lab Description

[25 points] Click the Lab Instructions link to open the instructions inside the current webpage.

The lab requires that you run two preparation scripts, which in their respective sequence of operation call other scripts. You will call the seeding.sql script from the /home/student/Data/cit225/oraclelib2/seed directory.

Its recommended that you create your apply_oracle_lab4.sql script in the lab4 directory first. While you’ll add to this file later, the initial file should have the following functional lines:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab3/apply_oracle_lab3.sql
@/home/student/Data/cit225/oracle/lib2/seed/seeding.sql
 
-- insert calls to other SQL script files here, in the order that they appear in the seeding.sql script ...
 
SPOOL apply_oracle_lab4.txt
 
-- insert your SQL statements here ... 
-- start with the validation scripts you find in the seeding.sql script.
-- copying the seeding.sql file and editing it to conform to this layout is the simplest approach to the lab. 
 
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;

Click the Lab Instructions link to display the lab steps:

Lab Instructions →

You should complete the following ten steps:

You should do the following in this part:

Copy the group_account1.sql from the ../lib2/seed directory into your ../lab4 directory as group_account1_lab.sql script file.
Edit the group_account1_lab.sql file to work exclusively with your _lab tables, sequences, and constraints.
Test the group_account1_lab.sql file to verify that it creates the desired group account and respective contact members.

Copy the /home/student/Data/cit225/oracle/lib2/seed/group_account1.sql file to the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/group_account1.sql group_account1_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/group_account1.sql group_account1_lab.sql
Edit the group_account1_lab.sql file to work with the following _lab tables:
- MEMBER_LAB
- CONTACT_LAB
- ADDRESS_LAB
- STREET_ADDRESS_LAB
- TELEPHONE_LAB

Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the group_account1_lab.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your group_account1_lab.sql script.

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member_lab m INNER JOIN contact_lab c ON m.member_lab_id = c.member_lab_id INNER JOIN
         address_lab a ON c.contact_lab_id = a.contact_lab_id INNER JOIN
         street_address_lab sa ON a.address_lab_id = sa.address_lab_id INNER JOIN
         telephone_lab t ON c.contact_lab_id = t.contact_lab_id AND a.address_lab_id = t.address_lab_id
WHERE    c.last_name = 'Winn';

It should show:

Account    Name
Number	   (Last, First MI) City	 State	    Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71445 Winn, Brian	    San Jose	 CA	    001-(408) 111-1111
B293-71445 Winn, Randi	    San Jose	 CA	    001-(408) 111-1111

You should do the following in this part:

Copy the group_account2.sql from the ../lib2/seed directory into your ../lab4 directory as group_account2_lab.sql script file.
Edit the group_account2_lab.sql file to work exclusively with your _lab tables, sequences, and constraints.
Test the group_account2_lab.sql file to verify that it creates the desired group account and respective contact members.

Copy the /home/student/Data/cit225/oracle/lib2/seed/group_account2.sql file to the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/group_account2.sql group_account2_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/group_account2.sql group_account2_lab.sql
Edit the group_account2_lab.sql file to work with the following _lab tables:
- MEMBER_LAB
- CONTACT_LAB
- ADDRESS_LAB
- STREET_ADDRESS_LAB
- TELEPHONE_LAB

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member_lab m INNER JOIN contact_lab c ON m.member_lab_id = c.member_lab_id INNER JOIN
         address_lab a ON c.contact_lab_id = a.contact_lab_id INNER JOIN
         street_address_lab sa ON a.address_lab_id = sa.address_lab_id INNER JOIN
         telephone_lab t ON c.contact_lab_id = t.contact_lab_id AND a.address_lab_id = t.address_lab_id
WHERE    c.last_name = 'Vizquel';

It should show:

Account    Name
Number	   (Last, First MI) City	 State	    Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71446 Vizquel, Oscar   San Jose	 CA	    001-(408) 222-2222
B293-71446 Vizquel, Doreen  San Jose	 CA	    001-(408) 222-2222

You should do the following in this part:

Copy the group_account3.sql from the ../lib2/seed directory into your ../lab4 directory as group_account3_lab.sql script file.
Edit the group_account3_lab.sql file to work exclusively with your _lab tables, sequences, and constraints.
Test the group_account3_lab.sql file to verify that it creates the desired group account and respective contact members.

Copy the /home/student/Data/cit225/oracle/lib2/seed/group_account3.sql file to the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/group_account3.sql group_account3_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/group_account3.sql group_account3_lab.sql
Edit the group_account3_lab.sql file to work with the following _lab tables:
- MEMBER_LAB
- CONTACT_LAB
- ADDRESS_LAB
- STREET_ADDRESS_LAB
- TELEPHONE_LAB

Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the group_account3_lab.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your group_account3_lab.sql script.

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member_lab m INNER JOIN contact_lab c ON m.member_lab_id = c.member_lab_id INNER JOIN
         address_lab a ON c.contact_lab_id = a.contact_lab_id INNER JOIN
         street_address_lab sa ON a.address_lab_id = sa.address_lab_id INNER JOIN
         telephone_lab t ON c.contact_lab_id = t.contact_lab_id AND a.address_lab_id = t.address_lab_id
WHERE    c.last_name = 'Sweeney';

It should show:

Account    Name
Number	   (Last, First MI) City	 State	    Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71447 Sweeney, Matthew San Jose	 CA	    001-(408) 333-3333
B293-71447 Sweeney, Meaghan San Jose	 CA	    001-(408) 333-3333
B293-71447 Sweeney, Ian M   San Jose	 CA	    001-(408) 333-3333

You should do the following in this part:

Copy the item_inserts.sql from the from the ../lib2/seed directory into your ../lab4 directory as the item_inserts_lab.sql script file.
Edit the item_inserts_lab.sql file to work exclusively with your item_lab tables, sequences, and constraints. (HINT: You also need to change subquery components to use the COMMON_LOOKUP_LAB table, column names, and values.
Test the item_inserts_lab.sql file to verify that it creates the twenty-one rows in the ITEM table.

Copy the item_inserts.sql file to the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/item_inserts.sql item_inserts_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/item_inserts.sql item_inserts_lab.sql
Edit the item_inserts_lab.sql file to work with the item_lab and common_lookup_lab tables.

Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the item_inserts.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your item_inserts_lab.sql script.

SET PAGESIZE 99
COL item_lab_id                FORMAT 9999  HEADING "Item|ID #"
COL common_lookup_meaning  FORMAT A20  HEADING "Item Description"
COL item_title             FORMAT A30  HEADING "Item Title"
COL item_release_date      FORMAT A11  HEADING "Item|Release|Date"
SELECT   i.item_lab_id
,        cl.common_lookup_meaning
,        i.item_title
,        i.item_release_date
FROM     item_lab i INNER JOIN common_lookup_lab cl ON i.item_type = cl.common_lookup_lab_id;

It should show:

							  Item
 Item							  Release
 ID # Item Description	   Item Title			  Date
----- -------------------- ------------------------------ -----------
 1001 DVD: Wide Screen	   The Hunt for Red October	  02-MAR-90
 1002 DVD: Wide Screen	   Star Wars I			  04-MAY-99
 1003 DVD: Full Screen	   Star Wars II 		  16-MAY-02
 1004 DVD: Wide Screen	   Star Wars II 		  16-MAY-02
 1005 DVD: Wide Screen	   Star Wars III		  19-MAY-05
 1006 DVD: Wide Screen	   The Chronicles of Narnia	  16-MAY-02
 1007 XBOX		   RoboCop			  24-JUL-03
 1008 XBOX		   Pirates of the Caribbean	  30-JUN-03
 1009 XBOX		   The Chronicles of Narnia	  30-JUN-03
 1010 Nintendo Gamecube    MarioKart			  17-NOV-03
 1011 Playstation2	   Splinter Cell		  08-APR-03
 1012 Playstation2	   Need for Speed		  15-NOV-04
 1013 XBOX		   The DaVinci Code		  19-MAY-06
 1014 XBOX		   Cars 			  28-APR-06
 1015 Blu-ray		   Beau Geste			  01-MAR-92
 1016 Blu-ray		   I Remember Mama		  05-JAN-98
 1017 Blu-ray		   Tora! Tora! Tora!		  02-NOV-99
 1018 Blu-ray		   A Man for All Seasons	  28-JUN-94
 1019 Blu-ray		   Hook 			  11-DEC-91
 1020 Blu-ray		   Around the World in 80 Days	  04-DEC-92
 1021 Blu-ray		   Camelot			  15-MAY-98

You should do the following in this part:

Copy the create_insert_contacts.sql from the ../lib2/seed directory into your ../lab4 directory as create_insert_contacts_lab.sql script file.
Edit the create_insert_contacts.sql file to work exclusively with your _lab tables, sequences, and constraints without conflicting with the existing contact_insert procedure.
Test the contact_insert_lab.sql file to verify that it creates a valid procedure. The next step (Step #6) will verify that it actually creates valid individual accounts in the _lab tables.

Copy the create_insert_contacts.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/create_insert_contacts.sql create_insert_contacts_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/create_insert_contacts.sql create_insert_contacts_lab.sql
Edit the create_insert_contacts.sql file to work exclusively with your _lab tables, sequences, and constraints without conflicting with the existing contact_insert procedure. You accomplish that by:
- Changing the name of the contact_insert procedure to contact_insert_lab. You must change the name on the first line and the last line or it will not compile correctly.
- Changing the table, column, and sequence names required to work with your _lab tables and sequences.
- Avoid changing any of the parameter names, which ensures you won’t need to change the list of parameters in the individual_accounts.sql script.

Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show the definition of the contact_insert_lab procedure, like

DESC contact_insert_lab

It should show:

PROCEDURE contact_insert_lab
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PV_MEMBER_TYPE 		VARCHAR2		IN
 PV_ACCOUNT_NUMBER		VARCHAR2		IN
 PV_CREDIT_CARD_NUMBER		VARCHAR2		IN
 PV_CREDIT_CARD_TYPE		VARCHAR2		IN
 PV_FIRST_NAME			VARCHAR2		IN
 PV_MIDDLE_NAME 		VARCHAR2		IN     DEFAULT
 PV_LAST_NAME			VARCHAR2		IN
 PV_CONTACT_TYPE		VARCHAR2		IN
 PV_ADDRESS_TYPE		VARCHAR2		IN
 PV_CITY			VARCHAR2		IN
 PV_STATE_PROVINCE		VARCHAR2		IN
 PV_POSTAL_CODE 		VARCHAR2		IN
 PV_STREET_ADDRESS		VARCHAR2		IN
 PV_TELEPHONE_TYPE		VARCHAR2		IN
 PV_COUNTRY_CODE		VARCHAR2		IN
 PV_AREA_CODE			VARCHAR2		IN
 PV_TELEPHONE_NUMBER		VARCHAR2		IN
 PV_CREATED_BY			NUMBER			IN     DEFAULT
 PV_CREATION_DATE		DATE			IN     DEFAULT
 PV_LAST_UPDATED_BY		NUMBER			IN     DEFAULT
 PV_LAST_UPDATE_DATE		DATE			IN     DEFAULT

You should do the following in this part:

Copy the individual_accounts.sql script from the ../lib2/seed directory into your ../lab4 directory as individual_accounts_lab.sql script file.
Edit the individual_accounts.sql file to call the contact_insert_lab procedure.
Test the individual_accounts.sql file to verify that it creates a valid individual accounts.

Copy the individual_accounts.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/individual_accounts.sql individual_accounts_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/individual_accounts.sql individual_accounts_lab.sql
Edit the individual_accounts.sql file to call your new contact_insert_lab procedure completed in the previous step. You should modify all the calls from using the contact_insert procedure name to using the contact_insert_lab procedure.

Run the individual_accounts.sql to create individual membership accounts in the _lab tables; and run the following confirmation query:

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A20  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || SUBSTR(c.middle_name,1,1)
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member_lab m INNER JOIN contact_lab c ON m.member_lab_id = c.member_lab_id INNER JOIN
         address_lab a ON c.contact_lab_id = a.contact_lab_id INNER JOIN
         street_address_lab sa ON a.address_lab_id = sa.address_lab_id INNER JOIN
         telephone_lab t ON c.contact_lab_id = t.contact_lab_id AND a.address_lab_id = t.address_lab_id
WHERE    m.member_type = (SELECT common_lookup_lab_id
                          FROM   common_lookup_lab
                          WHERE  common_lookup_context = 'MEMBER_LAB'
                          AND    common_lookup_type = 'INDIVIDUAL');

It should show:

Account    Name
Number	   (Last, First MI)	City	     State	Telephone
---------- -------------------- ------------ ---------- ------------------
R11-514-34 Clinton, Goeffrey W	Provo	     Utah	001-(801) 423-1234
R11-514-35 Moss, Wendy		Provo	     Utah	001-(801) 423-1235
R11-514-36 Gretelz, Simon J	Provo	     Utah	001-(801) 423-1236
R11-514-37 Royal, Elizabeth J	Provo	     Utah	001-(801) 423-1237
R11-514-38 Smith, Brian N	Spanish Fork Utah	001-(801) 423-1238

You should do the following in this part:

Copy the update_members.sql script from the ../lib2/seed directory into your ../lab4 directory as update_members_lab.sql script file.
Edit the update_members.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
Test the update_members.sql file to verify that it creates a valid individual accounts.

Copy the update_members.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/update_members.sql update_members_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/update_members.sql update_members_lab.sql
Edit the individual_accounts.sql file to call your new contact_insert_lab procedure completed in the previous step. You should modify all the calls from using the contact_insert procedure name to using the contact_insert_lab procedure.

Run the update_members.sql to update the member_type column with correct values before adding a NOT NULL constraint on the member_type column. Then, add the NOT NULL constraint on the member_type column before you run the confirmation query:

-- Use SQL*Plus report formatting commands.
COLUMN member_lab_id         FORMAT 999999 HEADING "Member|ID"
COLUMN members               FORMAT 999999 HEADING "Member|Qty #"
COLUMN member_type           FORMAT 999999 HEADING "Member|Type|ID #"
COLUMN common_lookup_lab_id  FORMAT 999999 HEADING "Member|Lookup|ID #"
COLUMN common_lookup_type    FORMAT A12    HEADING "Common|Lookup|Type"
SELECT   m.member_lab_id
,        COUNT(contact_lab_id) AS MEMBERS
,        m.member_type
,        cl.common_lookup_lab_id
,        cl.common_lookup_type
FROM     member_lab m INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN common_lookup_lab cl
ON       m.member_type = cl.common_lookup_lab_id
GROUP BY m.member_lab_id
,        m.member_type
,        cl.common_lookup_lab_id
,        cl.common_lookup_type
ORDER BY m.member_lab_id;

It should show:

		 Member  Member Common
 Member  Member    Type  Lookup Lookup
     ID   Qty #    ID #    ID # Type
------- ------- ------- ------- ------------
   1001       2    1004    1004 GROUP
   1002       2    1004    1004 GROUP
   1003       3    1004    1004 GROUP
   1004       1    1003    1003 INDIVIDUAL
   1005       1    1003    1003 INDIVIDUAL
   1006       1    1003    1003 INDIVIDUAL
   1007       1    1003    1003 INDIVIDUAL
   1008       1    1003    1003 INDIVIDUAL

You should do the following in this part:

Copy the rental_inserts.sql script from the ../lib2/seed directory into your ../lab4 directory as rental_inserts_lab.sql script file.
Edit the rental_inserts_lab.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
Test the rental_inserts_lab.sql file to verify that it creates a valid rental agreements with one or more items assigned to each rental agreement.

Copy the rental_inserts.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/rental_inserts.sql rental_inserts_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/rental_inserts.sql rental_inserts_lab.sql
Edit the rental_inserts_lab.sql file to accommodate all of the table, column, constraint, and sequence names that need an _lab added

Run the rental_inserts_lab.sql to insert rental_lab and rental_item_lab rows. You should run the following confirmation query:

-- Use SQL*Plus report formatting commands.
COL member_lab_id       FORMAT 9999 HEADING "Member|ID #"
COL account_number      FORMAT A10  HEADING "Account|Number"
COL full_name           FORMAT A20  HEADING "Name|(Last, First MI)"
COL rental_lab_id       FORMAT 9999 HEADING "Rent|ID #"
COL rental_item_lab_id  FORMAT 9999 HEADING "Rent|Item|ID #"
COL item_title          FORMAT A26  HEADING "Item Title"
SELECT   m.member_lab_id
,        m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || SUBSTR(c.middle_name,1,1)
         END AS full_name
,        r.rental_lab_id
,        ri.rental_item_lab_id
,        i.item_title
FROM     member_lab m INNER JOIN contact_lab c ON m.member_lab_id = c.member_lab_id INNER JOIN
         rental_lab r ON c.contact_lab_id = r.customer_id INNER JOIN
         rental_item_lab ri ON r.rental_lab_id = ri.rental_lab_id INNER JOIN
         item_lab i ON ri.item_lab_id = i.item_lab_id
ORDER BY r.rental_lab_id;

It should show:

					      Rent
Member Account	  Name			Rent  Item
  ID # Number	  (Last, First MI)	ID #  ID # Item Title
------ ---------- -------------------- ----- ----- --------------------------
  1002 B293-71446 Vizquel, Oscar	1001  1010 Star Wars I
  1002 B293-71446 Vizquel, Oscar	1001  1001 Star Wars II
  1002 B293-71446 Vizquel, Oscar	1001  1002 Star Wars III
  1002 B293-71446 Vizquel, Doreen	1002  1003 Camelot
  1002 B293-71446 Vizquel, Doreen	1002  1011 I Remember Mama
  1003 B293-71447 Sweeney, Meaghan	1003  1004 Hook
  1003 B293-71447 Sweeney, Ian M	1004  1005 Cars
  1001 B293-71445 Winn, Brian		1005  1007 The Hunt for Red October
  1001 B293-71445 Winn, Brian		1005  1006 RoboCop

You should do the following in this part:

Copy the create_view.sql script from the ../lib2/seed directory into your ../lab4 directory as create_view_lab.sql script file.
Edit the create_view_lab.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
Test the create_view_lab.sql file to verify that it creates a view by querying results from the current_rental view.

Copy the create_view.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command:
cp /home/student/Data/cit225/oracle/lib2/seed/create_view.sql create_view_lab.sql
cp /home/student/Data/cit225/oracle/lib2/seed/create_view.sql create_view_lab.sql
Edit the create_view_lab.sql file to accommodate all of the table, column, constraint, and sequence names that need an _lab added. You should rename the current_rental view as current_rental_lab view.

Run the create_view_lab.sql to check the contents of seeding activities and view generation. You should run the following confirmation query:

-- Use SQL*Plus report formatting commands.
COL member_lab_id       FORMAT 9999  HEADING "Member|ID #"
COL members             FORMAT 9999  HEADING "Member|#"
COL common_lookup_type  FORMAT A12   HEADING "Common|Lookup Type"
SELECT   m.member_lab_id
,        COUNT(contact_lab_id) AS MEMBERS
,        cl.common_lookup_type
FROM     member_lab m INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN common_lookup_lab cl
ON       m.member_type = cl.common_lookup_lab_id
GROUP BY m.member_lab_id
,        m.member_type
,        cl.common_lookup_lab_id
,        cl.common_lookup_type
ORDER BY m.member_lab_id;

It should show:

Member Member Common
  ID #	    # Lookup Type
------ ------ ------------
  1001	    2 GROUP
  1002	    2 GROUP
  1003	    3 GROUP
  1004	    1 INDIVIDUAL
  1005	    1 INDIVIDUAL
  1006	    1 INDIVIDUAL
  1007	    1 INDIVIDUAL
  1008	    1 INDIVIDUAL

Modify the view name from current_rental to current_rental_lab and compile the view. Then, you can query the view with the following SELECT statement:

COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL title           FORMAT A30  HEADING "Item Title"
COL check_out_date  FORMAT A11  HEADING "Check|Out|Date"
COL return_date     FORMAT A11  HEADING "Return|Date"
SELECT   cr.full_name
,        cr.title
,        cr.check_out_date
,        cr.return_date 
FROM     current_rental_lab cr;

It should show:

						Check
Name						Out	    Return
(Last, First MI) Item Title			Date	    Date
---------------- ------------------------------ ----------- -----------
Winn, Brian	 RoboCop			02-FEB-18   07-FEB-18
Winn, Brian	 The Hunt for Red October	02-FEB-18   07-FEB-18
Vizquel, Oscar	 Star Wars I			02-FEB-18   07-FEB-18
Vizquel, Oscar	 Star Wars II			02-FEB-18   07-FEB-18
Vizquel, Oscar	 Star Wars III			02-FEB-18   07-FEB-18
Sweeney, Ian M	 Cars				02-FEB-18   07-FEB-18

You should do the following in this part:
- Copy the seeding.sql script from the ../lib2/seed directory into your ../lab4 directory as apply_oracle_lab4.sql script file.
- Edit the apply_oracle_lab4.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
- Test the apply_oracle_lab4.sql file to verify that the series of files run as a group.

Written by michaelmclaughlin

February 1st, 2018 at 3:51 pm

Posted in

Database Tutorial

CIT225: Lab 4 Instructions

My Technical Blog

Tutorial Pages

Blogroll