Part I. Object-Relational Databases The purpose of this homework is familiar with extended SQL for object-relational databases. The figure below shows the UML class diagram of a purchase order application.

Instead of breaking up addresses or multiple phone numbers into unrelated columns in relational tables, we define types to represent an entire address and an entire list of phone numbers. 1. Similarly, we use nested tables to keep line items with their purchase orders instead of storing them separately. 2. The main entitiescustomers, stock, and purchase ordersbecome object types. 3. Object references are used to express some of the relationships among them. 4, Collection typesvarrays and nested tablesare used to model multi-valued attributes. [REFERENCE] This case study for practicing extended SQL is from https://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjxmp.htm#CHDGGDIF For

For each question below, (1) Write the SQL statement(s) and (2) Show that the completed SQL statement is working with submitting the execution result ** (e.g., a screen shot of the result). 1. Defining Types: We will create user-defined data types below using CREATE TYPE statements.

1-1. StockItem_objtyp type /* This LineItem_objtyp type has three numeric attributes. */ CREATE TYPE ______________________ ( StockNo NUMBER, Price ______, TaxRate ______ );

1-2. LineItem_objtyp type /* This LineItem_objtyp type models the line item entity, and includes an object reference to the corresponding stock objects. All other attributes are numeri attributes.*/ CREATE TYPE _____________________ ( LineItemNo ______, Stock_ref ________________, Quantity ______, Discount ______ );

1-3. PhoneList_vartyp type /* Any instance of type PhoneList_vartyp is a varray of up to 10 telephone numbers, each represented by a data item of type VARCHAR2 with length 20 . */ CREATE TYPE ___________ AS VARRAY(10) ____________;

1-4. Address_objtyp type CREATE TYPE _____________________( Street VARCHAR2(200), City VARCHAR2(200), State CHAR(2), Zip VARCHAR2(20) );

1-5. Customer_objtyp type /* Address_obj attribute has an Address_objtyp object, and PhoneList_var attribute has a PhoneList_vartyp object. */ CREATE TYPE _____________________( CustNo NUMBER, CustName VARCHAR2(200), Address_obj ____________, PhoneList_var ___________ ) NOT FINAL;

1-6. LineItemList_ntabtyp type /* Instance of LineItemList_ntabtyp type is a nested table object, each row of which contains an object of type LineItem_objtyp. */ CREATE TYPE _____________ AS TABLE OF _____________ ;

1-7. PurchaseOrder_objtyp type /* Attribute Cust_ref is a REF to Customer_objtyp, attribute LineItemList_ntab is a nested table of type LineItemList_ntabtyp, and attribute ShipToAddr_obj has an Address_objtyp object. */ CREATE TYPE _________________ AUTHID CURRENT_USER AS OBJECT ( PONo NUMBER, Cust_ref _________________, OrderDate DATE, ShipDate DATE, LineItemList_ntab ________________, ShipToAddr_obj _______________, MAP MEMBER FUNCTION getPONo RETURN NUMBER, MEMBER FUNCTION sumLineItems RETURN NUMBER );

Customer CustNo CustName has Phone 0.. 10 Number has 1 1 Address Street City State Zip places 1 Purchase Order PONO Order Date ShipDate Ship To getPONOO sumLineItems contains Line Item LineItem No Quantity Discount refers to 1 Stock Item StockNo Price TaxRate 1