In this task, you will experience the different object types in an Oracle database.
An object type is a kind of datatype. You can use an object types in the same ways as other datatypes such as NUMBER or VARCHAR2.
Object types are user defined; they are named and described by a database designer. Object types are composed of parts, called attributes and methods. Attributes hold the data about an object's features. Methods are procedures or functions provided to enable applications to perform useful operations on the attributes of the object type. An object type is a kind of template that defines structure and behavior.
Object-oriented features of Oracle include object tables, variable-length arrays and nested tables. All provide higher-level ways to organize and access data in the database.
An object table is a special kind of table in which each row represents an object. A variable-length array, VARRAY, is an ordered collection of elements. A nested table is an Oracle data type used to support columns containing multivalued attributes - columns that can hold a sub-table.
Objects are stored in complete rows in object tables are called row objects. Objects that are stored as columns of a table in a larger row, or are attributes of other objects are called column objects.
Objectives:
• Learn about object types and the object-relational features of Oracle
• Explore the key features and learn about the advantages of the object-relational model
Business situation
Study the Sample Application Using Object-Relational Features of Oracle from Oracle's Database Object-Relational Developer's Guide.
The guide gives the sql to create a Purchase Order (PO) application. You will write (or copy and paste) the SQL from the guide to Oracle Developer to create the database. First, you will create PO application that has only relational objects, then you will create an object-relational version of the database PO.
As you complete each task below, answer the questions that follow. Write all answers to the questions on this document.
In items 1 - 3, you will write and execute the sql examples in from Oracle's Database-Object Relational Developer's Guide. Place a copy of each query in the file named LabPart8.sql
1. Write the SQL to create Example A-1 Creating the Customer_reltab Table, Example A-2 Creating the PurchaseOrder_reltab Table, Example A-3 Creating the Stock_reltaband TableExample A-4 Creating the LineItems_reltab Table.
a. For each example, add at least one comment to the CREATE TABLE statement that describes the purpose and the components of the CREATE statement.
2. Write the SQL for Example A-5 Establish Inventory, Example A-6 Register Customers, Example A-7 Place Orders and Example A-8 Detail Line Items.
a. For each example, add at least one comment that describes the purpose and the components of the INSERT statements.
3. Write the SQL for Examples A-9 - A-13.
a. For each example, add at least one comment that describes the purpose and the components of the INSERT statements
4. Execute the SQL of the relational PO Application
a. Show evidence below that you executed the queries.
Object-Relational Model PO Application
5. Write the SQL for Examples A-14 - A-39.
a. For each example, add at least one comment that describes the purpose and the components of the statement.
6. Describe the differences between the relational PO datable and the object-relational PO database.
7. A semantic data model is a method of structuring data in order to represent it in a specific logical way. The enhanced E-R Model (EER) also known as the Extended Entity Relationship Model allows for semantic constructs such as a specialization hierarchy. Study the following specialization hierarchy.
8. Identify the Supertype/Subtype relationships.
a. List the super type/subtype pairs.
b. For each supertype/subtype pair identify the inherited attributes.
c. For each supertype/subtype pair, identify the disjoint or overlapping constraint.
d. For each supertype/subtype pair, identify the Completeness or Partial completeness constraint
9. List at least three resources in APA style that you used to complete the tasks in this assignment.
10. List three things that you learned from this assignment.