I II – O Modelo OR

1. III – O Modelo OR Estudo de Caso, modelo Oracle9i

2. Pedidos de Compra Esquema Conceitual

4. Interface I-Endereco { attribute struct end {string rua, string cidade, string estado, string cep} endereco; } Class Cliente : I-Endereco (extent Clientes key codigo) { attribute integer codigo; attribute string nome; attribute Array<string, 10> telefones }

5. Class Pedido : I-Endereco (extent Pedidos key codigo) { attribute integer codigo; attribute date data_pedido; attribute date data_entrega; attribute List<struct linha{integer num_linha, integer quantidade, float desconto}> linhas; relationship List<Produto> refere_se; float total() } Class Produto (extent Produtos key codigo) { attribute integer codigo; attribute float preco }

6. Pedidos de Compra Esquema Lógico Oracle

7. Definindo os tipos CREATE TYPE StockItem_objtyp CREATE TYPE LineItem_objtyp CREATE TYPE PurchaseOrder_objtyp CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20)

8. CREATE TYPE Address_objtyp AS OBJECT ( Street VARCHAR2(200), City VARCHAR2(200), State CHAR(2), Zip VARCHAR2(20) )

9. CREATE TYPE Customer_objtyp AS OBJECT ( CustNo NUMBER, CustName VARCHAR2(200), Address_obj Address_objtyp, PhoneList_var PhoneList_vartyp, ORDER MEMBER FUNCTION compareCustomers(x IN Customer_objtyp) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES ( compareCustomers, WNDS, WNPS, RNPS, RNDS) )

10. 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

11. CREATE TYPE PurchaseOrder_objtyp AS OBJECT ( PONo NUMBER, Cust_ref REF Customer_objtyp, OrderDate DATE, ShipDate DATE, LineItemList_ntab LineItemList_ntabtyp, ShipToAddr_obj Address_objtyp, MAP MEMBER FUNCTION getPONo RETURN NUMBER, PRAGMA RESTRICT_REFERENCES ( getPONo, WNDS, WNPS, RNPS, RNDS), MEMBER FUNCTION sumLineItems RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (sumLineItems, WNDS, WNPS) )

12. CREATE TYPE StockItem_objtyp AS OBJECT ( StockNo NUMBER, Price NUMBER, TaxRate NUMBER )

13. Definindo os Métodos CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS MAP MEMBER FUNCTION getPONo RETURN NUMBER is BEGIN RETURN SELF.PONo; END; MEMBER FUNCTION sumLineItems RETURN NUMBER is i INTEGER; StockVal StockItem_objtyp;

14. Total NUMBER := 0; BEGIN FOR i in 1 .. SELF.LineItemList_ntab.COUNT LOOP UTL_REF.SELECT_OBJECT(SELF.Line ItemList_ntab(i).Stock_ref, StockVal); Total := Total + SELF.LineItemList_ntab(i).Quantity * StockVal.Price; END LOOP; RETURN Total; END; END;

15. CREATE OR REPLACE TYPE BODY Customer_objtyp AS ORDER MEMBER FUNCTION compareCustomers (x IN Customer_objtyp) RETURN INTEGER IS BEGIN RETURN SELF.CustNo - x.CustNo; END; END;

16. Criando as “Object Tables” CREATE TABLE Customer_objtab OF Customer_objtyp (PRIMARY KEY (CustNo)) CREATE TABLE Stock_objtab OF StockItem_objtyp (PRIMARY KEY (StockNo))

17. CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (PRIMARY KEY (PONo), FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab) NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab ALTER TABLE PoLine_ntab ADD (SCOPE FOR (Stock_ref) IS stock_objtab)

18. Criando objetos INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) INSERT INTO Customer_objtab VALUES (1, 'Jean Nance', Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'), PhoneList_vartyp('415-555-1212') )

19. INSERT INTO PurchaseOrder_objtab SELECT 1001, REF(C), SYSDATE, '10-MAY-1999', LineItemList_ntabtyp(), NULL FROM Customer_objtab C WHERE C.CustNo = 1

20. 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 = 1004

21. Consultando objetos Imprimir os números dos pedidos em ordem SELECT p.PONo FROM PurchaseOrder_objtab p ORDER BY VALUE(p)

22. Para o pedido 1, os detalhes SELECT DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo, p.OrderDate, p.LineItemList_ntab FROM PurchaseOrder_objtab p WHERE p.PONo = 1001

23. O valor total dos pedidos de compra SELECT p.PONo, p.sumLineItems() FROM PurchaseOrder_objtab p

24. Pedidos do item 1004 SELECT po.PONo, po.Cust_ref.CustNo, L.* FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004

25. Destruindo objetos DELETE FROM PurchaseOrder_objtab p WHERE p.PONo = 1001

26. Exercícios • Observe a segunda consulta, C2. Ela imprime os apontadores para os itens pedidos. Ora, não é isto o que queremos. O que queremos são o preço e o desconto dos itens, em lugar dos apontadores para os ítens. Modifique a consulta, com este objetivo • Idem para a quarta consulta