Exercise:
Each box in the diagram corresponds to an object type. But how many object tables are needed? The relationship between Customer and Phone/Address is "has" whereas the relationship between LineItem and StockItem is "refers to". For which one ("has" or "refers to") is it necessary to create an object table for the dependent data?
CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20);
CREATE TYPE Address_objtyp AS OBJECT (
Street VARCHAR2(200),
City VARCHAR2(200),
State CHAR(2),
Zip VARCHAR2(20)
);
CREATE TYPE Customer_objtyp AS OBJECT (
CustNo NUMBER,
CustName VARCHAR2(200),
Address_obj Address_objtyp,
PhoneList_var PhoneList_vartyp
) NOT FINAL;
CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY ;
INSERT INTO Customer_objtab
VALUES (
1, 'Jean Nance',
Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'),
PhoneList_vartyp('415-555-1212')
) ;
INSERT INTO Customer_objtab
VALUES (
2, 'John Nike',
Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'),
PhoneList_vartyp('609-555-1212','201-555-1212')
) ;
Exercises:
CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;
INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) ;
INSERT INTO Stock_objtab VALUES(1011, 4500.23, 2) ;
INSERT INTO Stock_objtab VALUES(1534, 2234.00, 2) ;
INSERT INTO Stock_objtab VALUES(1535, 3456.23, 2) ;
CREATE TYPE LineItem_objtyp AS OBJECT (
LineItemNo NUMBER,
Stock_ref REF StockItem_objtyp,
Quantity NUMBER,
Discount NUMBER
);
CREATE TYPE LineItemList_ntabtyp AS TABLE OF LineItem_objtyp;
Exercises:
CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (
PRIMARY KEY (PONo),
FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
OBJECT IDENTIFIER IS PRIMARY KEY
NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (
(PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))
ORGANIZATION INDEX COMPRESS)
RETURN AS LOCATOR;
INSERT INTO PurchaseOrder_objtab
SELECT 1001, REF(C),
SYSDATE, '10-MAY-1999',
LineItemList_ntabtyp(),
NULL
FROM Customer_objtab C
WHERE C.CustNo = 1 ;
Exercises:
INSERT INTO TABLE (
SELECT P.LineItemList_ntab
FROM PurchaseOrder_objtab P
WHERE P.PONo = 1001
)
SELECT 01, REF(S), 12, 0
FROM Stock_objtab S
WHERE S.StockNo = 1534 ;
Exercises: