databases-lab manual 0

8/9/2019 Databases-lab Manual 0

1/24

Databases Lab

1

Thiagarajar College Of Engineering

Department Of Computer Science & Engineering

C48C48C48C48 Databases LabDatabases LabDatabases LabDatabases Lab

Lab ManualLab ManualLab ManualLab Manual

Prepared By

Ms. A.M.Rajeswari

Ms. B.Subbulakshmi


2/24

Databases Lab

2

LIST OF EXPERIMENTS

S.No Name of the experimentName of the experimentName of the experimentName of the experiment

PagePagePagePage

NoNoNoNo

Objective:

To provide practice in SQL and application development with ORACLE and DB2.To provide practice in Data mining techniques using the data mining tool - WEKA

1. Creation and Modification of Tables using ORACLE and DB2

2. Integrity Constraint enforcement using ORACLE

3.

Simple SQL Queries in DB2

4. Complex SQL Queries in ORACLE

5. Creation and usage of other database objects in ORACLE

6. Declaration and use of Cursors in ORACLE

7. Creation of Procedures and Functions using ORACLE and DB2.

8. Creation of Package and Trigger in ORACLE

9. Creation of composite data types in PL/SQL

10. Database application using JDBC

11. Database application using ODBC

12. Association Rule Mining, Classification and Clustering using WEKA


3/24

Databases Lab

3

Ex No: 1 Creation and Modification of Tables using ORACLE and DB2

Aim:

To create a student database using the schema

Student (regno, name, branch)

Marks (regno, sub1, sub2, sub3, total, result)in ORACLE and DB2 environment , modifying the existing structure and to drop the table.

E-R Diagram:

Table Design:

Identify the data types of each field and create the table.

Name Null? Type---------------- -------- -----------------------

REGNO CHAR(6)

NAME VARCHAR2(20)BRANCH CHAR(4)

Name Null? Type--------------- -------- -----------------------

REGNO CHAR(6)

SUB1 NUMBER(4,2)SUB2 NUMBER(4,2)

SUB3 NUMBER(4,2)

TOTAL NUMBER(5,2)

RESULT CHAR(4)

Procedure :

Creation and modification of Tables in ORACLE: Create the table using the command

CREATE TABLE table_name ( column1 datatype, columnN datatype );

Insert the values using the command

INSERT INTO table_name VALUES(column1_value, columnN_value );

Add or modify the columns in an existing table using

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name MODIFY column_name new_datatype;

Drop columns in an existing table usingALTER TABLE table_name DROP COLUMN column_name;


4/24

Databases Lab

4

Delete only the data inside the table retaining back the structure usingTRUNCATE TABLE table_name;

Delete the table including the structure usingDROP TABLE table_name;

Creation and modification of Tables in DB2:

Goto Startand do the following

After clicking the Command Center, the following screen of the Command Center willappear. In Interactivetab, type the connect statement in commandwindow.

The Command Center is a graphical interface for working with the SQL Commands. You canaccess other DB2 tools through the Toolbar across the top of the Command Center.You can enter SQLstatements either interactively or by writing a script and scheduling that script for a later time.

Any, DB2 messages appear in the bottom window while Query Results, if any; appear on the

Query Results Tab. The General syntax for establishing Connection is

Connect toDatabaseName userUserName usingPassword


5/24

Databases Lab

5

Press SQLAssist to create the table.

We can perform group by, order by filters in the SQL statement


6/24

Databases Lab

6

To create and modify the tables the syntax given for ORACLE environment can be usedwithout ; as delimiter. To see the results of query, go to Query Results Tab.

You can also create a table from the Command Line Processor. DB2 has a number of

command line processor commands that provide access to snapshot monitor information.

o Goto Start and Choose Programs

o Choose IBMDB2

o Choose COMMAND LINE TOOLS

o

Choose COMMAND LINE PROCESSOR

You can also create a table using Command Window by typingDB2 as prefix for all DDL

commands with ; as the delimiter.

Ex No: 2 Integrity Constraint enforcement using ORACLE

Aim:To enforce the integrity constraints in the student database

Student (regno, name, branch)

Marks (regno, sub1, sub2, sub3, total, result)

in ORACLE SQL plus environment.

E-R Diagram:


7/24

Databases Lab

7

Table Design:

Identify the data types of each field and create the table.

Name Null? Type

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

REGNO NOT NULL CHAR(6)NAME VARCHAR2(20)

BRANCH CHAR(4)

Name Null? Type--------------- -------- -----------------------

REGNO CHAR(6)

SUB1 NUMBER(4,2)SUB2 NUMBER(4,2)

SUB3 NUMBER(4,2)TOTAL NUMBER(5,2)

RESULT CHAR(4)

Procedure :

Constraint can be created when the table is being created using the commandsColumn Level :

CREATE TABLE table_name

( column1 datatype constraint_name, ..columnN datatype );

Table Level :CREATE TABLE table_name

( column1 datatype columnN datatype )

CONSTRAINT constraint_name (column_name) ;

Constraint can also be created after creating the table by using the commandALTER TABLE table_name

ADD CONSTRAINT constraint_name (column_name);

Constraints created for a table can be viewed by using the commandSELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITIONFROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'table_name';

Constraint can be enabled by using the commandALTER TABLE table_nameENABLE CONSTRAINT constraint_name;

Constraint can be disabled by using the command

ALTER TABLE table_nameDISABLE CONSTRAINT constraint_name;

Constraint can be dropped by using the commandALTER TABLE table_name

DROP CONSTRAINTS; // To drop all the constraints of the table.ALTER TABLE table_name

DROP CONSTRAINT constraint_name; // To drop only the specified constraint.


8/24

Databases Lab

8

Ex.No: 3 Simple SQL Queries in DB2

Aim:

To create the database using the schemaEmployee ( empno , ename , job , hiredate , basic_sal , comm. , dept_no )

Client_Master ( client_no , name , address , city , pincode , state , bal_due )

in DB2 environment , and answer the given set of simple queries.

Table Design:

Identify the data types of each field and find out the primary key and foreign keyin each table.

E-R Diagram:Draw the E-R Diagram for the above database.

Procedure :

Retrieve the data from a single table using the command

SELECT * |[ DISTINCT ] column_name |

column1_name, ., columnN_name |expression [alias_name]

FROM table_name

[ WHERE condition(s) ];-

The condition can be of Aritmetic or Comparative type.

- The condition can be a BETWEEN operator.

- We can have more than a condition in the WHERE clause using the operator AND, OR.

Retrieve the data from multiple tables using the command

SELECT * |

[ DISTINCT ] column_name |column1_name, ., columnN_name |expression [alias_name]

FROM table1_name, table2_name

[ WHERE table1_name.column1_name= table2_name.column2_name ];

Retrieve the data from the tables using sub query as

SELECT selection_list

FROM table_name

WHERE conditional_expression

( SELECT selection_listFROM table_name );

-

The conditional_expression can be of Aritmetic or Comparative type or LIKE, NOT LIKEoperators.

- The conditional_expression can have operators like IN, NOT IN, ALL, and SOME.

- The conditional_expression can be a BETWEEN operator.

- We can have more than a condition in the WHERE clause using the operator AND, OR.

Retrieved can be displayed in order by the commandSELECT column1_name, ., columnN_name

FROM table_name

WHERE expression_operatorORDER BY column1_name[ASC | DESC ];


9/24

Databases Lab

9

Retrieve the data from the table using group by functions and to order it asSELECT [column1_name] , group_function (column2_name), ...

FROM table[WHERE condition(s)]

GROUP BY column1_name

ORDER BY column1_name;

Use Command Line Processor or Command Window or Command Center for retrieving the

data from the table.

Ex. No: 4 Complex SQL Queries in ORACLE

Aim:To create the database using the schema

Client_Master ( client_no , name , address , city , pincode , state , bal_due )

Product_Master ( product_no , description , profit , units , qty_availlable , sell_price , cost_price )

Saleman_Master ( salesman_no , name , address , city , pincode , state , sale_amt , target )

Sales_Order ( order_no , order_date , client_no , salesman_no , delivery_date , order_status )

Sales_Order_Details ( order_no , product_no , qty_ordered , qty_delivered , total_amt )

in ORACLE SQL plus environment , and answer the given set of complex queries.

Table Design:

Identify the data types of each field and find out the primary key and foreign key

in each table.

E-R Diagram:

Draw the E-R Diagram for the above database.

Procedure :

Retrieve the data from the table using group by functions along with the condition for thegroup to be displayed and to order it as

SELECT [column1_name] , group_function (column2_name), ...

FROM table

[WHERE condition(s)]GROUP BY column1_name

HAVING having_expression

ORDER BY column1_name;

Retrieve the data from two tables using the set operator-UNION & UNION ALLSELECT column1_name, ., columnN_name FROM table_name1

[ WHERE condition(s) ]

UNION | UNION ALLSELECT column1_name, ., columnN_name FROM table_name2

[ WHERE condition(s) ] ;

For set operators to be performed the number of columns selected from the tables should besame and must be of same type and in same order.


10/24

Databases Lab

10

Retrieve the data from two or more tables using the set operator- INTERSECTSELECT column1_name, ., columnN_name FROM table_name1

[ WHERE condition(s) ]INTERSECT

SELECT column1_name, ., columnN_name FROM table_name2


Retrieve the data from two or more tables using the set operator- MINUS

SELECT column1_name, ., columnN_name FROM table_name1[ WHERE condition(s) ]

MINUSSELECT column1_name, ., columnN_name FROM table_name2


Retrieve the data from the table using EXISTS, NOT EXISTS operators

SELECT column1_name, ., columnN_name FROM outer_tablenameWHERE EXISTS | NOT EXISTS

( SELECT x FROM inner_tablename

WHERE outer_tablename.column_name=inner_tablename.column_name) ;

Ex. No: 5 Creation and usage of other database objects in ORACLE

Aim:To create the database objects like View, Index, Sequence, Synonym, Abstract Data Type, Nested

Table and Varray.

Procedure :

Create view using the commandCREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name

[(alias[, alias]...)] AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

Create the sequence using the command

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[MAXVALUE n | NOMAXVALUE][MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE n | NOCACHE];

The sequence can be used by the pseudo columns NEXTVAL and CURRVAL. Create an index using the command

CREATE INDEX index_name ON table_name (column[, column]...);

Create the synonym using the command

CREATE [PUBLIC] SYNONYM synonym_name FOR object;


11/24

Databases Lab

11

Create a Abstract Data type using the commandStep1: Declaration

CREATE OR REPLACE TYPE type_name as OBJECT( column1 datatype columnN datatype );

Step2: Usage

CREATE TABLE table_name ( column1 type_name, columnN datatype );

Create the Varray using the command

Step1: DeclarationCREATE TYPE type_name as VARRAY (MAX_SIZE) OF datatype

Step2: UsageCREATE TABLE table_name ( column1 type_name, columnN datatype );

Create the Nested Table using the commandStep1: Declaration of Abstract Type

CREATE TYPE abstract_type_name as OBJECT(column1 datatype columnN datatype );

Step2: Declaration of Nested Table

CREATE TYPE table_type_name AS TABLE OF abstract_type_name;Step 3: Usage of Nested table

CREATE TABLE table_name (column1 table_type_name.. columnN datatype )NESTED TABLE column1 STORE AS nested_table_name;

Ex. No: 6 Declaration and use of Cursors in ORACLE

Aim:To create and use cursor in PL/SQL block

Procedure:

Create the cursor in PL/SQL block as followsDECLARE

CURSOR cursor_name IS select_statement; // Declaration

BEGINOPEN cursor_name; // Open

FETCH cursor_name INTO

[variable1, variable2, ... | record_name]; // Fetch the valuesCLOSE cursor_name; // Close

END;

Cursor FOR loop can be used to replace OPEN, FETCH and CLOSE commandsFOR variable_list | record_name IN cursor_name

LOOPstatement1; statement2;

. . .END LOOP;

To obtain status information about a cursor, the following cursor_attributes can be used%ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT


12/24

Databases Lab

12

Ex. No: 7 Creation of Procedures and Functions using ORACLE and DB2

Aim:To create stored procedure and function using ORACLE and DB2 environment

Procedure:

Creation of stored procedure in PL/SQL:

Create a procedure using the commandCREATE [OR REPLACE] PROCEDURE procedure_name[(Parameter1 [mode] datatype1, parameter2 [mode] datatype2, . . .)]

IS | AS

[Local Variable Declaration]BEGIN

Executable Statements;

[EXCEPTIONException Section;]

END; Procedural Parameter mode can be

IN, OUT, INOUT

To execute a procedureEXEC procedure_name(parameter_list);

Creation of stored procedure in DB2:

Use Control Centerto open the Development CenterThe control Centeris the main DB2 graphical tool for administering your database.

The Control Centergives an overview of all the systems and database objects being managed

Other administration tools are accessed through the Control Center.


13/24

Databases Lab

13

The Tab that has been highlighted is the Development Center

Choose the Development Center. This Guides you step-by-step through a common DBA tasks


14/24

Databases Lab

14

Press the Create Projecttab.

Select the New tab and give a Project Name.Press OK.

Click Add Connection.


15/24

Databases Lab

15

Click the Onlinecheckbox in the Connection Type Wizard.

Choose the Database Alias Name(For example webdb2) For establishing connection with thedatabase in the Add Database Connection Wizard.

Provide the User Informationby setting the UserName and Password to be connected to the

Database.

Press the Test Connection Tab.The resultant is shown below.


16/24

Databases Lab

16

Press the NextTab in Options Dialogue Box And see the summary

Press Finish Tab

Click on the Create Object tab

Choose the Stored Procedure Tab and Choose whether you want SQL or Java Stored

Procedure

Suppose you have chosen SQL And PressOK. Then you will get the following screen.


17/24


18/24

Databases Lab

18

Specify the Parameters for the Procedure. These are the input arguments to be selected orupdated.

Finally the Procedure is created.

o You can check it with the BUILDTool.

o It must display a BUILD SUCCESSFUL

And then Use the RUNtool present next to BUILDTool

Creation of function in PL/SQL:

Create a function using the commandCREATE [OR REPLACE] FUNCTION function_name

[(Parameter1 [mode] datatype1, parameter2 [mode] datatype2, . . .)]RETURN datatype

IS | AS

[Local Variable Declaration]BEGIN

Executable Statements;

END;

Functional Parameter mode must be IN

To execute a function

o Within a PL/SQL Block

Variable_name := function_name(parameter_list);

o In the command prompt VARIABLE variable_name datatype

EXECUTE :variale_name := function_name(parameter_list) PRINT variable_name


19/24

Databases Lab

19

Creation of function in DB2:

Use Control Centerto open the Development Center

Use Development Center to choose theUser-defined Function


20/24

Databases Lab

20

Specify a function Name

Specify the Return Data type and the columnsNOTE: Generally an INSERT statement will not have any Result Set

Specify the Parameters for the Function. These are the input arguments to be selected or

updated.

Finally the User-defined function is createdo You can check it with the BUILDTool.

o It must display a BUILD SUCCESSFUL

And then Use the RUNtool present next to BUILDTool


21/24

Databases Lab

21

Ex. No: 8 Creation of Package and Trigger in ORACLE

Aim:

To create package and trigger using ORACLE

Procedure:

Creation of package in PL/SQL:

Create a package specification using the command

CREATE [OR REPLACE] PACKAGE package_name

IS | ASpublic type and item declarations

subprogram specifications

END package_name;

Create a package body using the commandCREATE [OR REPLACE] PACKAGE BODY package_name

IS | AS

private type and item declarationssubprogram bodies

END package_name;

To execute a procedure within a packageEXEC package_name.procedure_name(parameter_list);


22/24


23/24

Databases Lab

23

Ex. No: 10 Database application using JDBC

Aim :

To build a JAVA application as front end and connect it with oracle for fetching the required datafrom the database using JDBC connectivity.

Description :JDBC provides a standard interface for accessing a relational database from a Java application

regardless of where the application is running and where the database is. It is a way to execute SQL

statements and also call stored database procedures by using JAVA application. One important featureof JDBC is location independence.

Procedure :

Create a Data source as followsSelect Control panel-> ODBC data sources -> add -> Microsoft ODBC for oracle ( for

ORACLE database) / IBM DB2 ODBC DRIVER ( for DB2 database ) and specify the

DSN name, user name & server name of oracle. Load the oracle driver by using the syntax

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Establish the connection by creating a connection object as

Connection con = DriverManager.getConnection(url,"myLogin", myPassword");

A JDBC Statement object is created along with the connection to send the SQL statements tothe database as

Statement stmt = con.createStatement() ;

Execute the statement by using the statement object as

stmt.executeQuery("select * from student ");

stmt.executeupdate("delete from student where rno=101");

After the completion of the process close the connection by using the syntaxcon.close();

Sample I/O :


24/24

Databases Lab

Ex. No: 11 Database application using ODBC

Aim:To build a VB application as front end and connect it with oracle for fetching the required data

from the database using ODBC connectivity.

Description:The Open Database Connectivity interface is a widely accepted application-programming interface

(API) for database access. It is based on the Call-Level Interface (CLI) specifications and usesStructured Query Language (SQL) as its database access language. ODBC is designed for maximum

interoperability, the ability of a single application to access different database management systems

with the same source code. Database applications call functions in the ODBC interface, which are

implemented in database-specific modules called drivers. The use of drivers isolates applications fromdatabase-specific behavior. Because drivers are loaded at run time, a user only has to add a new driver

to access a new database management system; it is not necessary to recompile the application.

Algorithm :

Create a Data source as followsSelect Control panel-> ODBC data sources -> add -> Microsoft ODBC for oracle ( for

ORACLE database) / IBM DB2 ODBC DRIVER (for DB2 database) and specify theDSN name, user name & server name of oracle.

Create the front end (VB form) application using the standard exe option in VB.

Establish database connection using DAO as followsSelect Project -> Reference and check the Microsoft DAO 3.6 object library option.

Create database object and record set object in the general declaration section of the form asfollows

Dim db as database, Dim rs as record set

In the form load event of the application give the code as follows for establishing database

connectivity and creating a record setSet db=open database (,,

ODBC; UID=; PWD=)Set rs=db.openrecordset( sql query )

Sample I/O :

databases-lab manual 0

Documents