DBMS Lab Manual

(1)

Data Base Management Systems

Lab Manual for III B.Tech II Semester CSE

Prepared By

Sri V Prasad

Associate Professor

Dept. Of Computer Science & Engg.

Raghu Institute of Technology

Approved by AICTE(New Delhi) , Affiliated to Andhra University(Andhra Pradesh), Dakamarri(V), Bheemunipatnam (M), Visakhapatnam District, Andhra Pradesh, India. Tel No: +91 8922 248003, 248013. Fax no: +91 8922 248013. Website: www.raghuinstech.com

(2)

S.No Content Page No. 1 Lab Objective

2 Introduction to DBMS

3

(a) Introduction to Oracle 10 G Express. (b) Installation Screens of Oracle 10G

4 Introduction to SQL

5 List of Syllabus Programs

Project -1

6

Abstract – Class Marks Management System Introduction to Class Marks Management Systems

7

(a) DDL & DML Statements(Table Creations for CMM) (b) Importance of Dual Command

(c) Queries and Sub Queries Generation Using (i) Relational functions

(ii) Aggregate Functions (iii) Conversion Functions (iv) String Functions (v) Date Functions

8 VIEWS (a)Creation (b) Updating (c) Deletion .

9 PL /SQL Programming with Exception Handling 10 Functions Implementation

11 Procedure Implementation

(3)

INDEX

13 E-R Diagrams for CMM

Project -2

13 Viva Voice Questions

(4)

Lab Objective

Upon completing the course, students will be fully prepared to design, implement and manage DBMS to serve a wide range of goals in a range of educational settings.

Students will specifically be able to do the following:  Describe the uses of DBMS within educational settings.  Describe the benefits and structure of a relational DBMS.  Understand basic concepts and terminology related to DBMS.  Know and use procedures to design and implement a basic DBMS.  Implement the rules of data normalization to improve DBMS design.

 Build tables and construct relationships among them utilizing normalized data.  Retrieve data with simple queries.

 Design and implement a simple DBMS to integrate into a classroom lesson.  Design, create and use forms for data entry.

 Define and use queries to access specific data.

 Create a lesson plan that includes an existing database in an integral way.

 Design and implement a base DBMS to integrate into a specific classroom instructional situation.

 Know advanced DBMS topics and techniques regarding design, data and tables, queries, forms and reports.  Analyze an educational situation to identify data components and relationship among them.

 Use a student achievement DBMS to guide instructional decisions for classrooms, schools, and districts.  Discuss a variety of current issues related to using DBMS in educational settings.

 Understand a variety of currently emerging developments and trends related to DBMS.

 Select among DBMS products in terms of tradeoffs between flexibility, performance, easy of use and cost.

(5)

LIST OF PROJECTS TO BE CONDUCTED IN THE LAB

CSE 3.2.8 DBMS LAB Credits:2 Lab: 3 Periods/week Sessional Marks: 50

Univ-Exam : 3 Hours Univ-Exam-Marks: 50

Study features of a commercial RDBMS package such as ORACLE/DB2, MS Access, MYSQL & Structured Query Language (SQL) used with the RDBMS.( Select two of RDMSs)

Laboratory exercises should include defining schemas for applications, creation of a database, writing SQL queries, to retrieve information from the database, use of host languages,

interface with the embedded SQL, use of forms & report writing packages available with the chosen RDBMS product.

Some sample applications, which may be programmed, are given below: 1. Class marks management.

2. Accounting package for a shop.

3. Database manager for a Magazine agency or a newspaper agency. 4. Ticket booking for performances.

5. Preparing greeting cards & birthday cards.

6. Personal accounts - Insurance, loans, mortgage payments, etc. 7. Doctor's diary& billing system.

8. Personal bank account. 9. Hostel accounting. 10. Video Tape library. 11. History of cricket scores.

12. Cable TV transmission program manager. 13. Personal library.

(6)

INRODUCTION TO DATABASE MANAGEMENT SYSTEM.

A Database Management System (DBMS) is a set of computer programs that controls the creation, maintenance, and the use of a database. It allows organizations to place control of database development in the hands of database administrators (DBAs) and other specialists. A DBMS is a system software package that helps the use of integrated collection of data records and files known as databases. It allows different user application programs to easily access the same database. DBMSs may use any of a variety of database models, such as the network model or relational model. In large systems, a DBMS allows users and other software to store and retrieve data in a structured way. Instead of having to write computer programs to extract information, user can ask simple questions in a query language. Thus, many DBMS packages provide Fourth-generation programming language (4GLs) and other application development features. It helps to specify the logical organization for a database and access and use the information within a database. It provides facilities for controlling data access, enforcing data integrity, managing concurrency, and restoring the database from backups. A DBMS also provides the ability to logically present database information to users.

The various functions of DBMS are 1. Data Definition

2. Data manipulation

3. Data security and integrity 4. Data recovery and concurrency 5. Data dictionary maintaince 6. Performance

DBMS consists of 1. Data base design

2. Application development 3. Data analysis

4. Concurrency and robustness 5. Efficiency

6. Security

We can hide the data in the database. A DBMS is a set of software programs that controls the organization, storage, management, and retrieval of data in a database. We can hide the data in the database.i.e the Data Abstraction. There are certain levels in the data abstraction, they are 1. Physical level

2. Logical level 3. View level

The overall design of a database is called as SCHEMA. There are 3 types of schemas. Physical schema, logical schema, sub schema. The data in the database is stored in the form of entities, the tables. The content is in the form of bytes. We can perform certain operations of DDL,DML commands for the entities in the database like create ,insert, delete, rename, alter, truncate, drop, update. A VIEW is a table whose rows are not explicitly stored in the database but are computed as needed,it can be used just like a base table. We can perform operations like create, update, delete, drop.

An entity-relationship model (ERM) is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion. Diagrams created by this process are called ER diagrams. We represent the relation between the entites in the ER diagrams. We have

(7)

certain relations like Binary relation, Ternary relation, and Aggregate relation. We have certain Integrity constraints over the relationships.

We can remove the unwanted data in the database,is NORMALIZATION, elimination of redundant data. There are befinits of normalization

1. Less storage space 2. Quicker updates 3. Less data inconsistency 4. Easier to add data 5. Flexible structure

6. Clearer data relationships

We have types in normalization like 0NF,1NF,2NF,3NF,Boyce-codd normal form.

A trigger is a application which consists of event ,condition, and action in an organized manner. It will be activated only when there is some insertion, deletion or modification of content present in database. There are 2 types of triggers row-level trigger and statement level trigger.

Applications of DBMS 1. Data indepence 2. Efficient data access 3. Data integrity 4. Data security 5. Data admistration 6. Concurrent access 7. Crash recovery

8. Reduced application development

Examples of DBMS are: 1. Banking 2. Finance 3. Human resources 4. Tele communication 5. Universities 6. Airlines

7. Online ticket booking 8. In e-seva .

(8)

INTRODUCTION TO ORACLE 10G

The Oracle Database (commonly referred to

as Oracle RDBMS or simply as Oracle) is an object-relational database management system (ORDBMS) produced and marketed by Oracle Corporation. An oracle is free to develop, delay and distributed. Oracle database 10g express edition is an entry level, small foot print database based on the oracle database 10g release to code base that’s free to develop, delay and distribute, fast to download and simple to administer. Oracle database XE is a great starter database for:

 Developers working on PHP, JAVA, .NET, XML and open source application.

 DBA’s who need a free starter database for training and development.

 Independent software vendors (ISVS) and hardware vendors who want a starter database to distribute free of charge.

 Educational institutions and students who need free database for their curriculum. With oracle database XE,we can now develop and delay application with a powerful, proven, industry leading infrastructure and then upgrade when necessary without costly and complex migrations. An Oracle database system—identified by an alphanumeric system identifier or SID comprises at least one instance of the application, along with data storage. Users of the Oracle databases refer to the server-side memory-structure as the SGA (System Global Area). The Oracle DBMS can store and execute stored procedures and functions within itself. PL/SQL (Oracle Corporation's proprietary procedural extension to SQL), or the object-oriented language Java can invoke such code objects and/or provide the programming structures for writing them. The Oracle RDBMS stores data logically in the

form of table spaces and physically in the form of data files ("data files").Table spaces can contain various types of memory segments, such as Data Segments, Index Segments, etc. Segments in turn comprise one or more extents. Extents comprise groups of contiguous data blocks. Data blocks from the basic units of data storage.

Oracle database conventions refer to defined groups of object ownership (generally associated with a "username") as schemas. Each Oracle instance uses a System Global Area or SGA a shared-memory area—to store its data and control-information. Each Oracle instance allocates itself an SGA when it starts and de-allocates it at shut-down time.

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

The data dictionary comprises a set of tables and views that map the structure of the database. Oracle databases store information here about the logical and physical structure of the database. The data dictionary contains information such as:

 User information, such as user privileges

 Integrity constraints defined for tables in the database

 Names and datatypes of all columns in database tables

(9)

 Information on space allocated and used for schema objects.

The Program Global Area or PGA memory-area of an Oracle instance contains data and control-information for Oracle's server-processes. The dynamic performance views (also known as "fixed views") within an Oracle database present information from virtual tables (X$ tables) built on the basis of database memory. Database users can access the V$ views to obtain information on database structures and performance.

Oracle database can be installed on any size host machine with any number of cpu’s, but XE will store upto 4GB of user data, use upto 16GB of memory and use one cpu on the host machine.

Version numbering:

 Oracle Application Server 10g (also known as "Oracle AS 10g"): a middleware product;

 Oracle Applications Release 11i (aka Oracle e-Business Suite, Oracle Financials or Oracle 11i): a suite of business applications;

 Oracle Developer Suite 10g (9.0.4);

 Oracle JDeveloper 10g: a Java integrated development environment;

The latest certification information of oracle application server log(10.1.4)

Licensing information regarding compliance for oracle application server is available at

http://www.oracle.com/technology/productias/ index.html.

(10)

ORACLE 10 G PRINT SCREENS

(11)

(12)

(13)

(14)

(15)

PROJECT TITLE : CLASS MARKS MANAGEMENT SYSTEM

ABSTRACT

The basic idea of generating this project is for showing the essentiality of ER Diagrams in the Data base management systems. Organizing of the data according to the relevant information is clearly specified in our project .

Class marks management system is an entity-relationship model based database management project. We use Oracle 10g to implement this project. It has been designed to maintain and manage the database of the marks and other information of the students in a class. In this database we maintain the information of the students, the faculty who teach these students and the subjects they handle along with the department information.

We create the entities- student, faculty, department, subject and marks. We establish relationships between the entities such as assigns, learns, studies, handles and an aggregation relationship-monitors. There are integrity constraints for every entity which makes the database more flexible and data retrieval easy.

We normalize the data stored in the database so as to eliminate data duplication which can further lead to the destruction of data integrity. The efficiency of the output has been improved by imbibing various features into the program like nested-if, case and case expressions, cursors, conversion functions, procedures and functions. We also use triggers to make our system more responsive.

(16)

INTRODUCTION TO CLASS MARKS MANAGEMENT SYSTEM

In the project, class marks management system, firstly we created an entity-relationship diagram. According to the design created we listed the entities with their attributes as follows:

-Student entity: It has the attributes-sid (student id), sname (student’s name),saddr (student’s address).

-Subject entity: It has the attributes-subid(subject’s id) fname(faculty’s name), subauthor(book’s author name).

-Marks entity: It has the attributes-sid, subid and marks (marks of each student in each subject).

-Faculty entity:It has the attributes-fid(faculty’s id), fname(faculty;s name), fsubject(subject taught by that faculty).s

-Department entity: It has the attributes-did (department’s id), dname(department’s name), dhod(department’s head).

The relationships that we established between the above defined entities are:

-learns: This relationship is between the entities student and subject. It contains the descriptive attribute ‘since’ and derived attributes-sid, subfac.

-assigns: This relationship is between the entities faculty and marks. It has the derived attributes-fid, sid, subid, smarks.

-handles: This relationship is between the entities department and faculty. It has the descriptive attribute-since.

-studies: This relationship is between the student and department entities.

-monitors: This relationship is an aggregation relationship between the relationship set that consists of faculty and department and the entity student.

We use various key constraints in these entities, such as:

-primary key: This key uniquely identifies a tuple. The primary keys used in the defined entities are:-

->Sid in student entity ->subid in subject entity

(17)

->fid in faculty entity ->did in department entity.

Here the entity “marks” doesn’t have a primary key, it depends on another entity for a unique id, and hence it’s a weak entity.

-foreign key: To keep the data consistent, we use an integrity constraint that involves relations, linking them both. This is nothing but the foreign key. Some of the foreign keys used here are:

->sid is a foreign key in marks entity.

->subid is also a foreign key in marks entity.

SQL queries: They allow the user to describe desired data, leaving the database management system (DBMS) responsible for planning, optimizing, and performing the physical operations necessary to produce that result as it chooses. To enhance the efficiency of these queries we use:

-nested queries, in which a relation referred to in the query is itself defined within the query. -aggregate operators, which allows us to write SQL queries that are not expressible in relational algebra.

-cursors, using which relations can be accessed one tuple at a time.

-triggers are which describe actions to be taken when certain situations arise. These are invoked when such situations arise.

-procedures and functions, are a set of statements that can be executed. A procedure doesn’t return a value, but a function does.

In this project, we also implemented views (a relation whose instance is not explicitly stored but are computed as need) to enable logical independence.

PL/SQL programs have been written using many enhanced features of SQL, to improve the efficiency of programming, like:

-nested-if.

-case and case expressions. -nullif functions etc.

Conversion functions for string manipulation and date format conversions have also been implemented.

(18)

Oracle exe 10g -- Oracle Database 10G Express Edition Modes for Executing

1. Graphical Mode(Goto Home Page)

2. Command Mode(Run SQL Command Line) Administrator User : system

Password : Userdefined

Path where the programs will be stored defaultly: C:\oraclexe\app\oracle\product\10.2.0\server\BIN DDL Statements:

Creation Of Tables with & Without Constraints : create table student

(sid NUMBER NOT NULL PRIMARY KEY,

roll NUMBER NOT NULL PRIMARY KEY,

name varchar(10) NOT NULL, age NUMBER NOT NULL);

CREATE TABLE Department

(deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20));

CREATE TABLE Employee

(empno NUMBER PRIMARY KEY, empname VARCHAR2(20), deptno NUMBER REFERENCES Department(deptno));

CREATE TABLE student( roll NUMBER PRIMARY KEY, name VARCHAR2(10) NOT NULL, marks NUMBER NOT NULL CHECK ( marks BETWEEN 50 AND 99)); ALTER WITH ADD:

alter table student add address varchar(10);

SID ROLL NAME AGE

DEPTNO DEPTNAME

EMPNO EMPNAME DEPTNO

SID ROLL MARKS

(19)

ALTER with MODIFY:

alter table student MODIFY address VARCHAR2 (20);

/* The Byte Size of Address has been changed */

DROP:

drop table student; /* Drops the Table Student */

RENAME :

rename student to student1; /* Renames Student Table with Student 1 Name */

TRUNCATE :

Truncate student; /* Data in the Students table is Removed*/

To Run Programming files in Command Mode: EDIT xyz.txt

SQL> @ filename or SQL> @ path\filename

2 RUN 2 RUN Enter the Data in a file as:

Select roll,name from student;

Save xyz.txt

To get the Command Back , Just Type Get xyz.txt

DML Statements :

insert into student values(1001,1,'raj',78); insert into student values(1002,2,'Yash',67); insert into student values(1003,3,'Srinu',84); insert into student values(1004,4,'Lakhan',97);

Inserting Values at the run time :

ROLL NAME

2 yash

1 raj

3 Srinu

4 Lakhan

SID ROLL NAME MARKS

1002 2 yash 20

1001 1 raj 19

1003 3 Srinu 22

(20)

INSERT INTO student VALUES (&sid,&roll, &name, &marks);

Sorting in form of marks :

select * from student order by age;

Update with Set using where clause:

UPDATE student SET name = 'raju' WHERE roll = 2; /* Changing name of Yash to Raju */

DELETE using where clause:

DELETE FROM mrk WHERE reg = 2; /* Delete a tupple whose reg equals 2*/

Get List Of Tables :

Select * from tab ; /* To see the list of tables in the database*/

To Get Structure Of Table:

DESCRIBE student; /* Description of the table is given*/

To Quit : EXIT /* To Quit from Data Base */

DUAL

It’s a Single column /single table to evaluate constant expression in a select statement. (Contains Dummy Value)

select * from dual; Output : X

select sysdate from dual; Output: System Date is Displayed select 2+3 from dual; Output : 5

SELECT TO_DATE ('2-1-09', 'mm-dd-yy') FROM DUAL; Output: Day is Displayed

To current Date:

SID ROLL NAME AGE

1002 2 Yash 19

1001 1 Raj 20

1003 3 Srinu 22

(21)

SELECT TO_CHAR (sysdate, 'mm-dd-yy') FROM DUAL;

Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION, INTERSECT, Constraints.

Create the table marks(roll number,name char(20),marks number);

Insert into marks values(1,raj,93); Insert into marks values(2,yash,78); Insert into marks values(3,srinu,89); Insert into marks values(4,lakhan,68);

ANY:

select * from marks where marks = ANY (89, 78);

Output -

ALL:

select * from marks where roll <> ALL (2, 3); Output -

UNION :

select * from marks where marks <80 union select * from marks where marks > 70; Output -

INTERSECT:

select * from marks where marks <80 intersect select * from marks where marks > 50;

Output is: roll 5,6 ORDER BY & DESC:

select * from marks ORDER BY marks DESC; Output-

ROLL NAME MARKS

1 Raj 93

2 Yash 78

3 Srinu 89

4 Lakhan 68

ROLL NAME MARKS

2 Yash 78

3 Srinu 89

ROLL NAME MARKS

1 Raj 93

4 Lakhan 68

ROLL NAME MARKS

2 Yash 78

ROLL NAME MARKS

1 Raj 93

3 Srinu 89

2 Yash 78

(22)

SUB-QUERIES: Output - Select * from (select * from marks ORDER BY marks DESC)

where rownum < 3;

IN: Output -

select * FROM marks where roll IN (3,4);

NOT IN: Output-

select * FROM marks where roll NOT IN (2,3,4);

SELECTION : Output-

select marks FROM marks where marks = 93;

EXISTS: Output-

select * FROM marks where EXISTS

(select marks FROM marks where marks = 68); NOT EXISTS:

select * FROM marks where NOT EXISTS (select marks FROM marks where marks = 93); Output : No Data will be available

Aggregate functions

(COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING .

COUNT:

select COUNT (marks) FROM marks; Output - 4

select COUNT (marks) FROM marks where marks >70;

Output -3

SUM:

select SUM (marks) FROM marks; Output -328

select SUM (marks) FROM marks where marks>70; Output -260

AVG:

select AVG (marks) FROM marks; Output is : 82.0

select AVG (marks) FROM marks where marks>90;

ROLL NAME MARKS

1 Raj 93

3 Srinu 89

ROLL NAME MARKS

3 Srinu 89

4 Lakhan 68

ROLL NAME MARKS

1 Raj 93

ROLL NAME MARKS

1 Raj 93

ROLL NAME MARKS

(23)

Output is: 93 MAX:

select MAX(marks) from marks; Output is : 93

MIN:

select MIN(marks) from marks; Output is: 68

select MAX (marks), MIN (marks) FROM marks; Output is : 93 & 68

GROUP BY:

The GROUP BY clause can be used to summarize rows into a group or groups of rows based on a grouping function placed into the select clause. HAVING:

The HAVING clause can then be used to filter out unwanted groups much like the where clause. The expression for the select statement must

include at least one grouping function such as MAX() or COUNT().

You use the GROUP BY clause to group rows into blocks with a common column value

GROUP BY:

Select sdept, max(sbudget) from student3 group by sdept;

Output is : Dpet name with Highest Budget each HAVING:

select sdept,count(sname) from student3 group by sdept having count(sname)>2;

Output : ECE with 3

Conversion Functions

 String Functions

 Date Functions

 To char . To Date Functions

 Least , Great ,Truncate & Round Functions

string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr),

SELECT RPAD(roll, 12, '.'), LPAD(marks, 2, '*') FROM marks WHERE rownum < 3;

SELECT UPPER(name), INITCAP(name) FROM marks ORDER BY LENGTH(name);

Select LTRIM ('** Prasad ** ', '*') FROM dual; select RTRIM (' ** Prasad is Great **', '*') FROM dual;

select TRIM (both '*' from '** Prasad **') FROM dual;

select SUBSTR(‘Prasad',2) "sub1" from dual ; select SUBSTR( 'Prasadisgreat', 4, 3) "sub2" from dual;

select INSTR ( 'Prasad', 'd' ) "position1" from dual; select INSTR('Prasad is dangerous person','d',1,2) "position2" from dual;

date functions (sysdate, next_day, add_months, last_day, months_between)

SELECT SYSDATE FROM DUAL;

SELECT NEXT_DAY ('14-JAN-2010', 'WED') FROM DUAL;

SELECT LAST_DAY ('17-JULY-2010') FROM DUAL; SELECT MONTHS_BETWEEN (NOV-2009', '14-DEC-2010') FROM DUAL;

to char, to date

SELECT TO_DATE ('2-1-10', 'mm-dd-yy') FROM DUAL;

(24)

SELECT TO_CHAR (sysdate, 'mm-dd-yy') FROM DUAL;

least, greatest, trunc, round

SELECT ROUND (10.567, 2), TRUNC (10.567, 2) FROM DUAL;

SELECT GREATEST(10, 20, 30), LEAST (10, 20, 30) FROM DUAL;

Creating , Working & Deleting a View :

The result of a query is always a table, or more precisely, a derived table. Compared with “real” tables in the database, the result of a query is volatile, but nevertheless, the result is a table. The only thing that is missing for the query result is a name. Essentially, a view is just a query result with a given name. Retrieving information from a view is done in the same manner as retrieving from a table: you simply include the view in the FROM clause of a query. With some views you can also perform DML operations on the base tables. Views don’t store rows. Rows are always stored in tables.

Creating a View:

create view vmarks as select * from marks; Output: View Created

Working on a View: Check output for Vmarks Select * from vmarks;

Dropping a View: Distinct Command :

DROP VIEW vmarks; All Varieties of Categories can be seen View Dropped select distinct name from marks ; Describe Command:

Desc marks;

ROLL NAME MARKS

1 Raj 93

3 Srinu 89

2 Yash 78

(25)

PL / SQL Programming

PL/SQL stands for Procedural Language Extension to SQL.

PL/SQL contains both SQL statements and standard programming constructs like variable declarations, assignments, FOR, WHILE, IF, CASE and so on.

Program 1: General Pl/SQL Program DECLARE

Cmarks NUMBER; Creg NUMBER; BEGIN

SELECT marks,reg into cmarks,creg from student WHERE reg=® DBMS_OUTPUT.LINE_LINE (‘---‘);

DBMS_OUTPUT.LINE_LINE(‘the given number’||creg||’got’||cmarks||’ ‘); EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.PUT_LINE(‘no such reg number’); END;

Output-

Enter value for reg:5 Old 6: where reg>® New 6 : where reg=5; The given number 5 got 90

Program 2: CURSOR REPRESENTATION DECLARE

Cmark mrk%rowtype; Cursor xyz is

SELECT * from mark where marks>&marks; BEGIN

Open xyz; LOOP

Exit WHEN xyz%notfound; Fetch xyz into cmark;

DBMS_OUTPUT.PUT_LINE(CMARK.REG||’’||’CMARK.NAME||’’||CMARK.MARK); END LOOP;

END;

Output- Enter value for marks:85 Old 4: where mark>&mark; New 4: where mark>85; 5 90 gyan

(26)

7 96 anita 11 96 ann 17 88 suleiman 18 94 salman

Program 3: EXCEPTION HANDLING DECLARE

CURSOR xyz is

SELECT * from mark WHERE mark>&mark; Vrxyz xyz%rowtype;

excp EXCEPTION; BEGIN

OPEN xyz;

FETCH xyz into vrxyz; If xyz%notfound THEN RAISE excp; END if; CLOSE xyz; For K in xyz LOOP DBMS_OUTPUT.LINE_LINE(K.reg||’’||K.mark||’ ‘||K.name); END LOOP; EXCEPTION WHEN excp THEN

DBMS_OUTPUT.LINE_LINE(‘NO DATA ‘); END;

Output- Enter value for marks-85 Old 3: where mark>&mark; New 3: where mark>85; 5 90 gyan

7 96 anita 11 96 ann 17 88 suleiman 18 94 salman

Program 4: ELSE IF STATEMENT UTILIZATION DECLARE

vmarks NUMBER ; grade char(1); BEGIN

(27)

INTO vmarks FROM mrk

WHERE reg = ®

if vmarks >= 90 THEN grade := 'A' ; elsif

vmarks >= 80 THEN grade := 'B' ; elsif

vmarks >= 70 THEN grade := 'C' ; elsif

vmarks >= 60 THEN grade := 'D' ; ELSE grade := 'F';

END if;

DBMS_OUTPUT.PUT_LINE ('--- ');

DBMS_OUTPUT.PUT_LINE (' grade is: '||grade); END;

Output-

Enter value for reg:18

Old 8: select marks into vmarks FROM mrk WHERE reg=® new 8: select marks into vmarks FROM mrk WHERE reg=18; Grade is :A

PL/SQL Procedure successfully completed.

Program 5: CASE STATEMENT DECLARE vmarks NUMBER ; grade char(1); BEGIN SELECT marks INTO vmarks FROM mrk

WHERE reg = ® CASE

WHEN vmarks >= 90 THEN grade := 'A' ; WHEN vmarks >= 80 THEN grade := 'B' ; WHEN vmarks >= 70 THEN grade := 'C' ; WHEN vmarks >= 60 THEN grade := 'D' ; ELSE grade := 'F';

(28)

END CASE;

Output-

Enter value for reg:5

Old 8: select marks into vmarks FROM mrk WHERE reg=® new 8: select marks into vmarks FROM mrk WHERE reg=5; Grade is :A

Program 6: CASE EXPRESSION STATEMENT DECLARE vmarks NUMBER ; grade char(1); BEGIN SELECT marks INTO vmarks FROM mrk

WHERE reg = ® grade :=

CASE

WHEN vmarks >= 90 THEN 'A' WHEN vmarks >= 80 THEN 'B' WHEN vmarks >= 70 THEN 'C' WHEN vmarks >= 60 THEN 'D' ELSE 'F'

END ;

Output-

Enter value for reg: 17

Old 8: select marks into vmarks FROM mrk WHERE reg=® new 8: select marks into vmarks FROM mrk WHERE reg=17; Grade is: B

(29)

THE NULLIF FUNCTION

The NULLIF function compares two expressions. If they are equal, the function returns NULL; otherwise, it returns the value of the first expression.

NULLIF has the following structure: NULLIF (expression1, expression2)

The NULLIF function is equivalent to the following CASE expression:

CASE

WHEN expression1 = expression2 THEN NULL ELSE expression1 END Program 8: NULLIF DECLARE n NUMBER := &sn; vremainder NUMBER; BEGIN

-- calculate the remainder and if it is zero return a NULL vremainder := NULLIF(MOD(n,2),0);

DBMS_OUTPUT.PUT_LINE ('v remainder: '||vremainder); END;

Output-

Enter value for sn:43 Old2: n number=&sn; New2: n number=43; Vremainder=1

(30)

FUNCTIONS

A function is similar to a procedure except that a function must return a value.

CREATE OR REPLACE FUNCTION ptr RETURN NUMBER AS x NUMBER; BEGIN x := 1000; RETURN x; END ptr; Output- SQL>@ptr 5/ Function Created SQL>BEGIN 2 DBMS_OUTPUT.PUT_LINE 3 END; 4 / Ptr 1000

create or replace function area(radius in number) return number as v_pi number:=3.14; v_area number; begin v_area:=v_pi*power(radius,2); return v_area; end area; Output- SQL>@area 6/ Function Created

SQL>select area(10) from dual; Area(10)

(31)

PROCEDURES

Program for Representing a procedure without parameters:

Create or replace procedure PR1 as Begin DBMS_OUTPUT.PUT_LINE(‘Hai’); END; Output- SQL>@PR1 3/ Procedure created SQL>Execute PR1 Hai

Program for Representing a procedure with parameters:

Create or replace procedure PR2(N1 IN Number, N2 IN Number, TOT OUT NUMBER) is Begin Tot:=n1+n2; End; Output- SQL>@PR2 4/ Procedure Created SQL>Variable t number; SQL>Exec PR2(100,200,:T)

PL/SQL Procedure successfully completed. PRINT T

(32)

TRIGGERS

Triggers are similar to procedures or functions in that they are named PL/SQL blocks with declarative, executable, and exception handling sections.

Triggers must be stored as stand-alone objects in the database.

A trigger is executed implicitly whenever the triggering event happens, and a trigger doesn’t accept arguments. The act of executing a trigger is known as firing the trigger. The triggering event can be a

DML (INSERT, UPDATE, or DELETE) operation

On a database table or Certain kinds of views or a system event, such as database startup or shutdown CREATE OR REPLACE TRIGGER trigmrk

AFTER UPDATE OF marks ON mrk FOR EACH ROW

WHEN (OLD.marks != NEW.marks) BEGIN

DBMS_OUTPUT.PUT_LINE('marks ' ||:OLD.marks

||' have been change to ' ||:NEW.marks);

END;

SET SERVEROUTPUT ON UPDATE mrk

SET marks = 0 WHERE reg = ®

OUTPUT:

Old Marks 20 are changed to New Marks 50.

(33)

E-R Diagram Class Marks Management System

Buy SmartDraw!- purchased copies print this document without a watermark . Visit www.smartdraw.com or call 1-800-768-3729.

(34)

DATA UPDATION & RETRIEVING DOCUMENT

MAKING ORACLE 10 G as a Back End & JSP as Front End.

Step 1: Installation of JDK: Don't forget to install JDK on your system (if not installed) because any tomcat requires the Java 1.5 (Java 5) and Java 1.6 (Java 6) and then set the class path (environment variable) of JDK.

Step 2: Setting the class path variable is:

First right click on the My Computer->properties->advance->Environment Variables->path. Set bin directory path of JDK in the path variable.

Step 3: Installation Of Tomcat Web Sever :

Install Tomcat 6.0 in JAVA Folder which is present in C:\Program Files\Java

(35)

(36)

(37)

(38)

(39)

(40)

Step 4: Add a Jar File Called ojdbc14.jar into the C:\Program Files\Java\Tomcat 6.0\lib

Then Once again restart your tomcat webserver and check whether the Server Responding properly or not. Step 5: Place All yout JSP files in the folder C:\Program Files\Java\Tomcat 6.0\webapps by creating a New Folder

Into the New Folder copy the Content i.e., META-INF Files and images files and index.html and index.jsp files into your New Folder Which is Created

Step 6: Then Start Executing ur program in the browser by http://localhost:8080/it/reg.jsp Program 1: Program For Entering Data into the Data Base :

/*…….Update.jsp…….*/

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd" <HTML>

<h1><center> DBMS Lab -- Importance of Data Base as Back Ground </center> </h1><br><br><br> <table align="center" border="2">

<tr>

<td>Name Of the Candidate :</td>

<TD><INPUT TYPE="text" name="name" class="reqd name"></td> </tr> <tr> <td>Password Chosen :</td>

<td> <INPUT TYPE="PASSWORD" name="passwd" class="reqd passwd"> </td></tr> <tr><td>Email ID:</td>

<td> <INPUT TYPE="TEXT" name="EMAIL" class="reqd email"> </td> </tr></tr> <td>Phone Number :</td>

</tr> </table> </FORM> <%

String name = request.getParameter("name"); String pass = request.getParameter("passwd"); String email = request.getParameter("EMAIL"); String pno = request.getParameter("PNO");

/* Create string of connection url within specified format with machine name, port number and database name. Here machine name id localhost and database name is logindata. */

String connectionURL = "jdbc:oracle:thin:@127.0.0.1:1521:XE"; // declare a connection by using Connection interface

Connection connection = null;

// declare object of Statement interface that uses for PreparedStatement pstatement = null;

(41)

// Load JDBC driver "oracle.jdbc.OracleDriver"

Class.forName("oracle.jdbc.OracleDriver").newInstance(); int updateQuery = 0;

// check if the text box is empty

if(email!=null && pass!=null && pno!=null && name!=null){ // check if the text box having only blank spaces

if(email!="" && pass!="" && pno!="" && name!="") { try {

/* Create a connection by using getConnection() method that takes parameters of string type connection url, user name and password to connect

to database. */

connection = DriverManager.getConnection (connectionURL, "system", "oracle");

// sql query to insert values in the secified table.

String queryString = "INSERT INTO reg values(?,?,?,?)"; /* createStatement() is used for create statement object that is used for

sending sql statements to the specified database. */ pstatement = connection.prepareStatement(queryString); pstatement.setString(1, name); pstatement.setString(2, pass); pstatement.setString(3, email); pstatement.setString(4, pno); updateQuery = pstatement.executeUpdate(); if (updateQuery != 0) { %> <script type="text/javascript"> alert("Data Entered Successfully"); </script>

<% } }

catch (Exception ex) { %> <script type="text/javascript"> alert("invalid"); </script> <% } finally {

// close all the connections. pstatement.close(); connection.close(); } } } %> </body> </html>

(42)

(43)

(44)

Program 2: Program for Retrieving the Data From the Data Base : /*……….display.jsp……….*/ <%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> <HEAD>

<TITLE>Retrieving the Data From the Data Base </TITLE> </HEAD>

<BODY>

<H1><center> Retrieving the Data Fro the Data Base <center > </H1> <%

Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:XE", "system", "oracle"); Statement statement = connection.createStatement() ; ResultSet resultset =

statement.executeQuery("select * from reg11") ; %>

<TABLE BORDER="1" align="center"> <TR> <TH>Name </TH> <TH>Address</TH> <TH>Country</TH> <TH>Hobbies</TH> </TR> <% while(resultset.next()){ %>

(45)

<TR> <TD> <%= resultset.getString(1) %></td> <TD> <%= resultset.getString(2) %></TD> <TD> <%= resultset.getString(3) %></TD> <TD> <%= resultset.getString(4) %></TD> </TR> <% } %> </TABLE> </BODY> </HTML> Output Screens :

(46)

Viva Voice Questions ?

1.What is a Database? 2.What is DBMS?

3.Explain Data Base System? 4.Trace the Advantages in DBMS ? 5.List the disadvantages in DBMS?

6.Describe the three levels of data abstraction? 7.Define the "integrity rules".

8.What is Data Independence?

9.What is a view? How it is related to data independence?

10.What is Data Model? 11.What is E-R model?

12.What is Object Oriented model? 13. What is an Entity?

14.What is an Entity type? 15. What is an Entity set?

16.What is an Extension of entity type? 17.What is Weak Entity set?

18.What is an attribute?

19.What is a Relation Schema and a Relation? 20. What is degree of a Relation?

21. What is Relationship? 22. What is Relationship set? 23. What is Relationship type?

24. What is degree of Relationship type? 25. What is DDL (Data Definition Language)? 26. What is VDL (View Definition Language)? 27. What is SDL (Storage Definition Language)? 28. What is Data Storage - Definition Language? 29. What is DML (Data Manipulation Language)? 30. What is DML Compiler?

31. What is Query evaluation engine? 32. What is DDL Interpreter?

33. What is Relational Algebra? 34. What is Relational Calculus?

35. How does Tuple-oriented relational calculus differ from domain-oriented relational

calculus

36. What is normalization?

37. What is Functional Dependency?

38. When is a functional dependency F said to be minimal?

39. What is Multivalued dependency? 40. What is Lossless join property? 41. What is 1 NF (Normal Form)?

42. What is Fully Functional dependency? 43. What is 2NF?

44. What is 3NF?

45. What is BCNF (Boyce-Codd Normal Form)?. 46. What are partial, alternate,, artificial, compound and natural key?

47. What is indexing and what are the different kinds of indexing?

48. What is system catalog or catalog relation? How is better known as?

49. What is meant by query optimization? 50. What is durability in DBMS?

51. What do you mean by atomicity and aggregation?

52. What is a Phantom Deadlock?

53. What is a checkpoint and When does it occur? 54. What are the different phases of transaction? 55. What do you mean by flat file database? 56. What is "transparent DBMS"?

57. What is a query?

58. What do you mean by Correlated subquery? 59. What is a Tigger

60.What is an active database 61. Define a function

(47)

References:

Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke, McGraw-Hill

ORACLE PL/SQL by example. Benjamin Rosenzweig, Elena Silvestrova, Pearson Education 3rd Edition

ORACLE DATA BASE 1OG PL/SQL Programming SCOTT URMAN, Tata Mc-Graw Hill. SQL & PL/SQL for Oracle 10g, Black Book, Dr.P.S. Deshpande.