DBM 449 Lab 4 Oracle Object type

L A B O V E R V I E W

Scenario/Summary
For this lab you will begin by using the same set of tables that you used for Lab 1 so be sure that you are connected to Oracle as the DBM449_USER user. The objective of this lab will be to create a series of object-relational tables using the SQL*Plus editor that will allow data to be stored in a more “real-world” format. Data for your new tables can be found in the file Lab4_data.txt associated with this link. You will need to manipulate the data in various ways, but the file will give you access to the raw data to use.
To record your work for this lab use the LAB4_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Drop Box for Week 4.

L A B S T E P S
STEP 1: Create a table with a column data type
Modify the design of the COURSE table created in iLab 1 to incorporate the use of the column abstract data type.

1. Write and execute the SQL to create a single object type called COURSE_OBJ1 that contains both the attributes course code and course name. Remember that with abstract objects you must use the / after the CREATE statement to execute it.
2. Next, write and execute the SQL to create a table called NEW_COURSE1 that contains COURSE_OBJ1 along with the original attributes from the original COURSE table. Keep in mind what attributes the new object type COURSE_OBJ1 contains. Your table should have a total of 4 individual columns when finished.
3. Using the data from the LAB4_DATA file create and execute the insert statements to load the new table NEW_COURSE1. SUGGESTION: Using the Lab4_data file create a script file of your insert statements and then run the script file. Remember that you will need enclose some of the data in single quotes depending on if it is character, date, or numeric data.
4. Run DESCRIBE command to describe structure of table NEW_COURSE1.
5. SET DESCRIBE DEPTH 2 and run DESCRIBE NEW_COURSE1 again.
6. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query). Use the COLUMN column_name FORMAT A## session command to format columns within the table to keep the result set data from wrapping around. Be sure that you properly display data inside the object column. (HINT: When querying attributes of an abstract data type, you must use a correlation variable for the table.)

STEP 2: Create an object table with a row data type
Create a second COURSE table, this time as an object table using the row abstract data type.

1. Write and execute the SQL to create an object called COURSE_OBJ2 that contains the attributes course code, course name, course date, instructor, and location.
2. Write and execute the SQL to create a table called NEW_COURSE2 with a single column defined using the COURSE_OBJ2 object.
3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE2.
4. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query).

STEP 3: Create a Varying Array
Modify the design of the CLIENT table created in iLab 1 to incorporate the use of the Varying Array.

1. Write and execute the SQL to create a Varying Array to represent the phone contact information for the client (up to 3 phone numbers). Name the varying array as PHONE_LIST.
2. Write and execute the SQL to create a table called NEW_CLIENT that contains the attributes that the original CLIENT table contained plus the phone list array.
3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_CLIENT.
4. Execute a SELECT statement to query the data from the CLIENT_NO and CLIENT_NAME columns along with the data in the column containing the phone number Varray (You cannot use a SELECT * type query for this step).

Deliverables
Submit your completed Lab 4 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.