Code

S Practical : Object Oriented Database

Practical : Object Oriented Database

Create different types that include attributes and methods. Define tables for these types by adding at least 10 tuples. Demonstrate insert, update and delete operations on these tables. Fire at least 5 queries on them.

Create different types as follows: SQL> Create type AddrType as object( Street char(20), City varchar2(50), state varchar2(40), no number(4), Pincode number(5)); SQL> create type BranchType as object( address AddrType, phone1 integer, phone2 integer ); SQL> create type AuthorType as object( name varchar2(50), addr AddrType );

Create different tables as follows: SQL> create table BranchTableType of BranchType; SQL> create table authors of AuthorType; SQL> create table books( title varchar2(50), year date, published_by ref PublisherType, authors AuthorListType); SQL> create type AuthorListType as varray(10) of ref AuthorType;

Create a type and define nested table as follows: SQL> create type PublisherType as object( name varchar2(50),

PRACTICALS

2

addr AddrType, branches BranchType); we may create a type that is a nested table of objects of this type by: SQL> create type PublisherTableType as table of PublisherType Now one can define a relation of publishers that will nest their branches inside: SQL> create table Publishers of PublisherType NESTED TABLE branches STORE as branchTable;

Insertion of tuples into tables as follows: (1) Insertion into Authors using following format: AUTHOR(name, addr:) SQL>

insert into Authors values('Kale', AddrType('s.street','pune','mha',04,5002))

SQL>

insert into Authors values('Bale', AddrType('n.s.street','nasik','mha',08,7008))

SQL>

insert into Authors values('Dhumdhare', AddrType('m.g.street','mumbai','mha',08,400003))

Add 10 tuples like this!

(2) Insertion of tuples into publishers having 5 components as follows PUBLISHERS(name, addr:< street,city,state,no, pincode>, branches:set of ,phone1,phone2> ) SQL> insert into Publishers values('Venus', AddrType('r.s.street','mumbai','mha',03,4002), BranchTableType(BranchType( AddrType('f.s.treet','mumbai','mha',03,5003),23406,69896))) SQL> insert into Publishers values('Lotus', AddrType('s.s.street','mumbai','mha',04,1007), BranchTableType(BranchType( AddrType(7007,'sstreet','mumbai','mha',1007),4543545, 8676775))) Similarly one can add 10 tuples.

[email protected]

3

PRACTICALS

(3) Insertion of tuples into books can be done using following format: BOOKS(title, year, published_by: ref, authors: list of ref AUTHOR ) SQL>

insert into books select 'java','28-may-1983',ref(pub) ,AuthorListType(ref(aut)) from Publishers pub, Authors aut where pub.name='joshi' and aut.name='aho'

//1 row created//

QUERIES: (1) Select all tuples of author: SQL> select * from authors; (2) Select all tuples of publishers: SQL> Select * from publishers; (3) List all of the authors that have the same address as their publisher: Publishers addr SQL>

(4)

authors addr, name(?)

select a.name from authors a, publishers p where a.addr = p.addr;

List all of the authors that have the same pincode as their publisher: SQL> select a.name from authors a, publishers p where a.addr.pincode = p.addr.pincode;

(5) List all books that have 2 or more authors: SQL>

select * from books b where 1 < ( select count(*) from table(b.authors));

(6) List the title of the book that has the most authors: SQL> Select title from books b, table(b.authors)

[email protected]

4

PRACTICALS group by title having count(*)= (select max(count(*)) from books b, table(b.authors) group by title);

P Prraaccttiiccaall :: M Mu ullttiim meed diiaa d daattaab baasseess import java.io.*; import java.sql.*; import javax.swing.*; import java.awt.*; import javax.swing.event.*; import java.awt.event.*; class TestImage implements ActionListener { JFrame f; JLabel l; JLabel l2; JLabel l3; JTextField tf1; JTextField tf2; JButton b1; TestImage() { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection c = DriverManager.getConnection("jdbc:odbc:Ora","scott","tiger"); Statement s = c.createStatement(); File file = new File("lgsunlogo.gif"); FileInputStream fis = new FileInputStream(file); int itemnumber = 5; /* PreparedStatement pstmt = c.prepareStatement("insert into UDCS values(?,?,?)"); pstmt.setInt(1, itemnumber); pstmt.setString(2,"lgsunlogo"); pstmt.setBinaryStream(3, fis, (int)file.length()); pstmt.executeUpdate(); System.out.println("Inserted");*/ byte[] imageBytes = new byte[1]; String str="",str1="";

[email protected]

5

PRACTICALS

UDCS where ID='2'");

ResultSet rs=s.executeQuery("select * from if(rs.next()) { str = rs.getString("ID"); str1 = rs.getString("NAME"); imageBytes = rs.getBytes("PHOTO");

} ImageIcon auctionimage = new ImageIcon(imageBytes); int width =auctionimage.getIconWidth(); int height =auctionimage.getIconHeight(); f = new JFrame("My Frame"); l = new JLabel(auctionimage); l2 = new JLabel("ID"); l3 = new JLabel("NAME"); tf1 = new JTextField(25); tf2 = new JTextField(25); b1 = new JButton("SHOW IMAGE"); tf1.setText(str); tf2.setText(str1); FlowLayout());

f.getContentPane().setLayout(new f.getContentPane().add(b1); f.getContentPane().add(l2); f.getContentPane().add(tf1); f.getContentPane().add(l3); f.getContentPane().add(tf2); f.getContentPane().add(l); b1.addActionListener(this); l.setVisible(false); l2.setVisible(false); l3.setVisible(false); tf1.setVisible(false); tf2.setVisible(false); f.setVisible(true); f.setSize(600,600);

} catch (Exception e) { e.printStackTrace(); }

} public void actionPerformed(ActionEvent e) { if (e.getSource()==b1) { l.setVisible(true); l2.setVisible(true);

[email protected]

6

PRACTICALS l3.setVisible(true); tf1.setVisible(true); tf2.setVisible(true);

}

} } public static void main(String[] args) { new TestImage(); }

P Prraaccttiiccaall :: A Accttiivvee d daattaab baasseess Consider the following relations: Emp1 (eno, ename, hrs, pno, super_no) Project1(pname, pno, thrs, head_no) CREATE TABLE EMP1 ( eno Number(10) PRIMARY KEY, ename Varchar(10), hrs Number(10), pno Number(10), super_No Number(10), ) CREATE table Project1 ( pname Varchar(10), pno Number(10), thrs Number(10), head_No Number(10)REFERENCES Emp_CR(Super_No) ) INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO

EMP1 EMP1 EMP1 EMP1 EMP1 EMP1 EMP1

VALUES(113, VALUES(114, VALUES(115, VALUES(116, VALUES(117, VALUES(118, VALUES(119,

After Inserting the records: SELECT * FROM EMP1

[email protected]

'Sachin', 7, 1, 009) 'ROHIT', 4, 1, 009) 'Deepali', 3, 1, 009) 'Revati', 4,2, 010) 'Ashish', 4,2, 010) 'Dheeraj', 4,3, 011) 'Ulka', 4,3, 011)

PRACTICALS ENO ENAME HRS PNO SUPER_NO --- ---------- ---------- ---------- ---------113 Sachin 7 1 9 114 ROHIT 4 1 9 115 Deepali 3 1 9 116 Revati 4 2 10 117 Ashish 4 2 10 118 Dheeraj 4 3 11 119 Ulka 4 3 11 INSERT INTO Project1 VALUES('DBMS', 1, 14, 009) INSERT INTO Project1 VALUES('JAVA', 2, 8, 010) INSERT INTO Project1 VALUES('ORACLE', 3, 8, 011) SELECT * FROM PROJECT1 PNAME PNO THRS HEAD_NO ---------- ---------- --------- --------DBMS 1 14 9 JAVA 2 8 10 ORACLE 3 8 11 Event1: Insert a new Employee Tuple

Creating a Trigger : CREATE OR REPLACE TRIGGER thrs_1 AFTER INSERT ON EMP1 FOR EACH ROW WHEN (NEW.Pno IS NOT NULL) Begin UPDATE Project1 SET Thrs = Thrs + :NEW.Hrs Where Pno = :NEW.Pno; end; INSERT INTO EMP1 VALUES(120, 'Dheeraj', 4,3, 011) SELECT * FROM PROJECT1

PNAME PNO THRS HEAD_NO ---------- ---------- ---------- ---------DBMS 1 14 9 JAVA 2 8 10 ORACLE 3 12 11 [email protected]

7

PRACTICALS

Event 2 : Changing the Hours of existing employees Creating Trigger CREATE OR REPLACE TRIGGER thrs_2 AFTER UPDATE OF hrs ON EMP1 FOR EACH ROW WHEN (NEW.Pno IS NOT NULL) BEGIN

END;

UPDATE Project1 SET thrs=thrs+NEW.hrs-OLD.hrs WHERE Pno=NEW.Pno;

Event 3: Changing the project of an employee CREATE TRIGGER thrs_3 AFTER UPDATE OF pno ON Emp FOR EACH ROW BEGIN UPDATE Project SET thrs = thrs + NEW.hrs WHERE pno = NEW.pno;

END;

UPDATE Project SET thrs = thrs - OLD.hrs WHERE pno = NEW.pno;

[email protected]

8