SETM LAB 5:)
2009 AUG 21

cd "Z:\SETM lab5"

Bytecodeparser
->constant pool symbol table

Z:\SETM lab5>java setm.metrics.ByteCodeParser

open ->prac4.class

-------------------------output------------------------

Constant Pool Symbol Table
Entry Tag Value
0 undefined (0) undefined
1 CONSTANT_Utf8 bytes=
2 CONSTANT_Utf8 bytes=()V
3 CONSTANT_Utf8 bytes=mystery
4 CONSTANT_Utf8 bytes=(II)I
5 CONSTANT_Utf8 bytes=Code
6 CONSTANT_Utf8 bytes=
7 CONSTANT_NameAndType name[0x6]= descriptor[0x2]=()V
8 CONSTANT_Utf8 bytes=java/lang/Object
9 CONSTANT_Classref index[0x8]=java/lang/Object
a CONSTANT_Methodref class[0x9]=java/lang/Object NameAndType[0x7]
= ()V
b CONSTANT_Utf8 bytes=SourceFile
c CONSTANT_Utf8 bytes=Prac4
d CONSTANT_Classref index[0xc]=Prac4
e CONSTANT_Utf8 bytes=prac04.java

end of ConstantPoolTable


Parsing classs area in class files is not supported in this version


Z:\SETM lab5>java setm.gui.HexViewer


------------------output----------------------


0: ca fe ba be 00 03 00 - 00 0f 01 00 08 < c l
15: i n i t > 01 00 03 ( ) V 01 00 07 m y
31: s t e r y 01 00 05 ( I I ) I 01 00 04
47: C o d e 01 00 06 < i n i t > 0c 00 06
63: 00 02 01 00 10 j a v a / l a n g / O
79: b j e c t 07 00 08 0a 00 09 00 07 01 00 0a
95: S o u r c e F i l e 01 00 05 P r a
111: c 4 07 00 0c 01 00 0b p r a c 0 4 . j
127: a v a 00 ! 00 0d 00 09 00 00 00 00 00 02 00
143: 09 00 03 00 04 00 01 00 05 00 00 00 15 00 02 00
159: 02 00 00 00 09 1a 1b a2 00 05 1a ac 1b ac 00 00
175: 00 00 00 01 00 06 00 02 00 01 00 05 00 00 00 11
191: 00 01 00 01 00 00 00 05 * b7 00 0a b1 00 00 00
207: 00 00 01 00 0b 00 00 00 02 00 0e

magic: ca fe ba be
minor version: 00 03
major version : 00 -
constant pool count : 00 0f -15
-----------------------------
inside constant pool table we need to have 130 bytes:
-----------------------------

access flags: 00 0! public
name of the class :00 0d Prac4
name of the super_class : 00 09 java/lang/object
the number of interfaces implemented : 00 00
the number of fields :00 00
the number of methods:00 02

---------------Methods-------------
-------------------------------------------------------------------------
*****Constructor methods cannot analysed **********
-------------------------------------------------------------------------
2 methods -constructor class & mystery method
-------------------------------------------------------------------------
• access flags :00 09 public static
• name of the class :00 03 mystery
• return type and the types of all parameters :00 04(I I)I

return type: integer Parameter types: integer (2 parameters)
attribute-00 01
• the number of bytes in the code attribute



code length=00 00 00 09

1a 1b a2 00 05 1a ac 1b ac


--------------------------------Part 6----------------------------------

Z:\SETM lab5>javap -l -c Prac4

--------------------------------output----------------------------------


Compiled from "prac04.java"
public class Prac4 extends java.lang.Object{
public static int mystery(int, int);
Code:
0: iload_0
1: iload_1
2: if_icmpge 7
5: iload_0
6: ireturn
7: iload_1
8: ireturn




public Prac4();
Code:
0: aload_0
1: invokespecial #10; //Method java/lang/Object."":()V
4: return




}


------------------------------------



• Determine the number of return statements contained in method. = 2
• Determine if this method follows generally accepted coding standards and justify your answer.

Labels:

CREATE TYPE manufacture_ty as OBJECT(
name varchar(20),
country varchar(20)
)
/

CREATE TYPE product_ty AS OBJECT(
modelno number,
type varchar(10),
price number(8,2),
name ref manufacture_ty
)NOT FINAL;
/


CREATE TYPE computer_ty UNDER product_ty (
speed number,
ram number,
hdisksize number,
rdspeed varchar2(3),
rdtype varchar2(3)
)NOT FINAL;
/

CREATE TYPE printer_ty UNDER product_ty(
PRType varchar2(8),
ifcolour varchar2(5)
)
/

CREATE TYPE pc_ty UNDER computer_ty(
monitorsize number(2),
casetype varchar2(5)
)
/

CREATE TYPE laptop_ty UNDER computer_ty(
screen number(8,2),
weight number(8,2)
)
/


CREATE TABLE manufacture_tab of manufacture_ty(
constraint pk_manufacture_tabl primary key(name)
)
/

CREATe TABLE product_tab of product_ty
(
constraint pk_product_tab primary key (modelno),
constraint fk_procut_tab foreign key(name) references manufacture_tab
)
/


insert into manufacture_tab values('ACER','SINGAPORE');
insert into manufacture_tab values('COMPAQ','USA');
insert into manufacture_tab values('IBM','USA');
insert into manufacture_tab values('TOSHIBA','JAPAN');
insert into manufacture_tab values('HP','USA');
insert into manufacture_tab values('CANON','JAPAN');

insert into product_tab values(
pc_ty(1001,'PC',1799,(select ref(b) from manufacture_tab b where b.name='ACER'),2700,256,40,'48x','CD',15,'tower'))
/

insert into product_tab values(
pc_ty(1002,'PC',2499,(select ref(b) from manufacture_tab b where b.name='ACER'),3500,512,60,'64x','DVD',17,'tower'))
/

insert into product_tab values(
pc_ty(1003,'PC',1999,(select ref(b) from manufacture_tab b where b.name='ACER'),1800,512,30,'48x','DVD',15,'flat'))
/

insert into product_tab values(
pc_ty(1004,'PC',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),1800,256,40,'64x','DVD',15,'flat'))
/

insert into product_tab values(
pc_ty(1005,'PC',2499,(select ref(b) from manufacture_tab b where b.name='IBM'),4000,512,60,'64x','DVD',15,'flat'))
/

insert into product_tab values(
pc_ty(1006,'PC',2119,(select ref(b) from manufacture_tab b where b.name='IBM'),2300,1024,40,'96x','DVD',17,'tower'))
/

insert into product_tab values(
pc_ty(1007,'PC',2299,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2400,512,80,'64x','DVD',17,'tower'))
/

insert into product_tab valueS(
pc_ty(1008,'PC',1999,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2700,256,30,'96x','CD',15,'tower'))
/

insert into product_tab values(
pc_ty(1009,'PC',1699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),3200,512,80,'64x','DVD',17,'tower'))
/

---------------

insert into product_tab values(
laptop_ty(2001,'LP',1448,(select ref(b) from manufacture_tab b where b.name='ACER'),1700,256,40,'48x','CD',12.1,3.5))
/

-----------

insert into product_tab values(
laptop_ty(2002,'LP',2559,(select ref(b) from manufacture_tab b where b.name='ACER'),2200,512,30,'64x','DVD',15.1,3.1))
/

insert into product_tab values(
laptop_ty(2003,'LP',2799,(select ref(b) from manufacture_tab b where b.name='ACER'),1866,512,60,'48x','DVD',15.1,2.8))
/

insert into product_tab values(
laptop_ty(2004,'LP',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),1866,256,40,'64x','DVD',12.1,3.5))
/

insert into product_tab values(
laptop_ty(2005,'LP',1499,(select ref(b) from manufacture_tab b where b.name='IBM'),1700,256,60,'64x','DVD',12.1,3.1))
/

insert into product_tab values(
laptop_ty(2006,'LP',2119,(select ref(b) from manufacture_tab b where b.name='IBM'),3700,1024,80,'64x','DVD',15.7,3.1))
/

insert into product_tab values(
laptop_ty(2007,'LP',2229,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2900,512,80,'48x','DVD',15.1,3.5))
/

insert into product_tab values(
laptop_ty(2008,'LP',999,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),1700,256,30,'96x','DVD',12.1,3.6))
/

insert into product_tab values(
laptop_ty(2009,'LP',699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),1800,128,40,'64x','DVD',15.1,2.9))
/

insert into product_tab values(
laptop_ty(2010,'LP',1699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),2750,512,30,'96x','CD',12.1,3.0))
/

------------
insert into product_tab values(
printer_ty(3001,'PR',231,(select ref(b) from manufacture_tab b where b.name='HP'),'ink-jet','True'))
/
-------------
insert into product_tab values(
printer_ty(3002,'PR',267,(select ref(b) from manufacture_tab b where b.name='CANON'),'ink-jet','True'))
/

insert into product_tab values(
printer_ty(3003,'PR',390,(select ref(b) from manufacture_tab b where b.name='HP'),'laser','False'))
/

insert into product_tab values(
printer_ty(3004,'PR',439,(select ref(b) from manufacture_tab b where b.name='CANON'),'ink-jet','True'))
/

insert into product_tab values(
printer_ty(3005,'PR',200,(select ref(b) from manufacture_tab b where b.name='CANON'),'bubble','True'))
/

insert into product_tab values(
printer_ty(3006,'PR',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),'laser','True'))
/

insert into product_tab values(
printer_ty(3007,'PR',350,(select ref(b) from manufacture_tab b where b.name='HP'),'laser','False'))
/

--Q4.
--a

select p.name.name,treat (value(p) as pc_ty).speed
from product_tab p
where value(p) IS OF(pc_ty) and
treat(value(p) as pc_ty).hdisksize>=60
/

--b

select p.modelno,p.price
from product_tab p
where p.name.name like 'C%'
/

--c
select distinct p.name.name
from product_tab p
where value(p) is of (only pc_ty)
/

--OR

--C-
select distinct p.name.name
from product_tab p
where value(p) IS OF (pc_ty) and
p.name.name NOT IN (select p1.name.name
from product_tab p1
where value(p) IS OF(printer_ty))

--d
select p.name.name
from product_tab p
where value(p) is of (pc_ty)
group by p.name.name
having count(p.modelno)>=3
/

--e
select p.name.name,avg(treat(value(p) as laptop_ty).screen)
from product_tab p
where value(p) IS OF (laptop_ty)
group by p.name.name
/


--Q5-A)

alter type product_ty add member function print return integer cascade
/

create or replace type body product_ty AS
MEMBER function print return integer is
begin
return self.modelno;
end print;
end;
/


--b
alter type computer_ty add overriding member function
print return integer cascade
/

create or replace type body computer-Ty as
overriding mbmerber function print return integer is
begin
return 'Model NO:'||self.modelno||'SPEED:'||self.speed||'RAM:'||self.ram||'HDSIZE'||self.hdiskskize
end print;
end;
/


select p.print()
from product_tab p


select p.print()
from product_tab p
where value(p) is of (pc_ty)

select p.print()
from product_tab p
where value(p) IS OF (computer_ty)

Labels:



Download Now

Labels:


--DBMS ANSWER's Provided by Iranga & Sachit --


--1

--a)
ALTER TYPE stock3_t ADD MEMBER FUNCTION getYield RETURN FLOAT CASCADE;

--B)
ALTER TYPE stock3_t ADD MEMBER FUNCTION toUSD(rate IN FLOAT) RETURN FLOAT CASCADE;

--c)
ALTER TYPE stock3_t ADD MEMBER FUNCTION countExchanges RETURN INTEGER CASCADE;

--d)
ALTER TYPE client3_t ADD MEMBER FUNCTION totalPurchase RETURN FLOAT CASCADE;

--e)
ALTER TYPE client3_t ADD MEMBER FUNCTION totalProfit RETURN FLOAT CASCADE;



CREATE OR REPLACE TYPE BODY stock3_t AS
MEMBER FUNCTION getYield RETURN FLOAT IS
BEGIN
RETURN ( (SELF.dividend / SELF.currprice)*100 );
END getYield;

MEMBER FUNCTION toUSD(rate IN FLOAT) RETURN FLOAT IS
BEGIN
RETURN (SELF.currprice * rate);
END toUSD;

MEMBER FUNCTION countExchanges RETURN INTEGER IS
countEx INTEGER;
BEGIN
SELECT COUNT(e.COLUMN_VALUE) INTO countEx
FROM TABLE(SELF.exchangeS)e ;
RETURN countEx;
END countExchanges;

END;
/

CREATE OR REPLACE TYPE BODY client3_t AS
MEMBER FUNCTION totalPurchase RETURN FLOAT IS
total FLOAT;
BEGIN
SELECT SUM(s.purchaseprice*s.qty) INTO total
FROM TABLE(SELF.investments)s;
RETURN total;
END totalPurchase;

MEMBER FUNCTION totalProfit RETURN FLOAT IS
profit FLOAT;
BEGIN
SELECT SUM(s.qty *(s.company.currprice - s.purchaseprice) ) INTO profit
FROM TABLE (SELF.investments)s;
RETURN profit;
END totalProfit;

END;
/

--2)
--a)
SELECT s.company AS CNAME,s.exchangeS AS EXCHANGES ,s.toUSD(0.74) AS USDPRICE ,s.getYield() AS YIELD
FROM stock3 s;

--b)
SELECT s.company AS CNAME ,s.currprice AS CPRICE ,s.countExchanges() AS CNT_eXCHANGES
FROM stock3 s
WHERE s.countExchanges() > 1 ;

--c)
SELECT DISTINCT c.name AS NAME ,I.company.company AS STK_NAME,I.company.getYield() AS YIELD,I.company.currprice AS CPRICE,I.company.eps AS EPS
FROM client3 c, TABLE(c.investments)I ;

--d)
SELECT DISTINCT c.name AS NAME ,c.totalPurchase() AS PVALUE
FROM client3 c;

--e)
SELECT DISTINCT c.name AS NAME ,c.totalProfit() AS PROFIT
FROM client3 c;

Labels:

DBMS III - Practicle 3

0 Comments Posted by Hirushan De Silva at 11:24 AM

DBMS prac3 Answer's Provided by Iranga & Sachit talagala


------------------ CREATE TYPES & TABLES QUERIES ------------------------------------------------------------------------------------

CREATE TYPE exchangeS_arr AS VARRAY(5) OF varchar(12)
/


CREATE TYPE stock3_t AS OBJECT
(
company char(7),
currprice number(6,2),
exchangeS exchangeS_arr,
dividend number(4,2),
eps number(4,2)
)
/


CREATE TYPE address_t AS OBJECT
(
streetNo char(4),
streetName varchar(20),
suburb varchar(20),
state varchar(10),
pin char(5)
)
/


CREATE TYPE Investments_t AS OBJECT
(
company REF stock3_t,
purchaseprice number(6,2),
pdate date,
qty number(6)
)
/


CREATE TYPE Investments_tbl AS TABLE OF Investments_t
/


CREATE TYPE client3_t AS OBJECT
(
clno char(3),
name varchar(12),
address address_t,
investments Investments_tbl
)
/


CREATE TABLE stock3 OF stock3_t
(
constraint stock_pk primary key(company)
)
/


CREATE TABLE client3 OF client3_t
(
constraint client_pk primary key(clno)
)
NESTED TABLE investments STORE AS Invest_ntbl
/



------------------- DROP QUERIES--------------------------------------------------------------------------------------


DROP TABLE client3;

DROP TABLE stock3;


DROP TYPE client3_t;

DROP TYPE Investments_tbl;

DROP TYPE Investments_t;

DROP TYPE address_t;

DROP TYPE stock3_t;

DROP TYPE exchangeS_arr;



---------------- INSERT QUERIES -------------------------------------------------------------------------------------


INSERT INTO stock3 VALUES ( stock3_t ( 'BHP', 10.50, exchangeS_arr( 'Sydney', 'New York'), 1.50, 3.20 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'IBM', 70.00, exchangeS_arr( 'New York','London', 'Tokyo'), 4.25, 10.00 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'INTEL', 76.50, exchangeS_arr('New York','London'), 5.00, 12.40 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'FORD', 40.00, exchangeS_arr( 'New York'), 2.00, 8.50 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'GM', 60.00, exchangeS_arr( 'New York'), 2.50, 9.20 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'INFOSYS', 45.00, exchangeS_arr( 'New York'), 3.00, 7.80 ));




INSERT INTO client3 VALUES ( client3_t ('C01','John Smith',address_t( '3','East', 'Bentley', 'WA', '6102') ,
Investments_tbl ( Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='BHP'), 12.00,'02-Oct-2001',1000),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='BHP'), 10.50,'08-Jun-2002',2000),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='IBM'), 58.00,'12-Feb-2000',500),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='IBM'), 65.00,'10-Apr-2001',1200),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INFOSYS'), 64.00,'11-Aug-2010',1000)
)));



INSERT INTO client3 VALUES ( client3_t ('C02','Jill Brody',address_t( '42','Bent St', 'Perth', 'WA', '6001') ,
Investments_tbl ( Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INTEL'), 35.00,'30-Jan-2000',300),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INTEL'), 54.00,'30-Jan-01',400),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INTEL'), 60.00,'02-Oct-01',200),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='FORD'), 40.00,'05-Oct-99',300),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='GM'), 55.50,'12-Dec-00',500)
)));



----------------- SELECT QUERIES ------------------------------------------------------------------------------------


SELECT * FROM stock3;

SELECT * FROM client3;



---------------- QUERIES --------------------------------------------------------------------------------------------

--a)

SELECT DISTINCT C.name, I.company.company,I.company.currprice,I.company.dividend,I.company.eps
FROM client3 C, TABLE(C.investments) I;


--b)

SELECT DISTINCT C.name,I.company.company,SUM(I.qty) as Total_Qty,SUM(I.purchaseprice*I.qty)/SUM(I.qty) AS AVGPRICE
FROM client3 C, TABLE(C.investments) I
GROUP BY C.name,I.company.company;


--c)

SELECT C.name,I.company.company AS STOCK,SUM(I.qty) as Total_Qty ,SUM(I.company.currprice*I.qty) AS CURRVAL
FROM client3 C, TABLE(C.investments) I,TABLE(I.company.exchangeS) E
WHERE E.COLUMN_VALUE = 'New York'
GROUP BY C.name,I.company.company;


--d)

SELECT C.name,SUM(I.qty*i.purchaseprice) AS PVALUE
FROM client3 C, TABLE(C.investments) I
GROUP BY C.name;


--e)

SELECT C.name,SUM(I.qty*I.company.currprice)-SUM(I.qty*i.purchaseprice) AS PROFIT
FROM client3 C, TABLE(C.investments) I
GROUP BY C.name;



--4)---------------------------------------------------------
DELETE TABLE(SELECT c.investments FROM client3 c WHERE C.name = 'John Smith' ) i
WHERE i.company = (SELECT ref(S) FROM stock3 S WHERE S.company='INFOSYS');

INSERT INTO TABLE( SELECT c.investments FROM client3 c WHERE C.name = 'Jill Brody')
VALUES(Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INFOSYS'), 64.00,'11-Aug-2010',1000));


DELETE TABLE(SELECT c.investments FROM client3 c WHERE C.name = 'Jill Brody' ) i
WHERE i.company = (SELECT ref(S) FROM stock3 S WHERE S.company='GM');

INSERT INTO TABLE( SELECT c.investments FROM client3 c WHERE C.name = 'John Smith')
VALUES(Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='GM'), 55.50,'12-Dec-00',500));

Labels: