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
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

S.No Content Page No.

1 Lab Objective

2 Introduction to DBMS

(a) Introduction to Oracle 10 G Express.

3 (b) Installation Screens of Oracle 10G

4 Introduction to SQL

5 List of Syllabus Programs

Project -1

Abstract – Class Marks Management System

6
Introduction to Class Marks Management Systems
(a) DDL & DML Statements(Table Creations for CMM)
(b) Importance of Dual Command
(c) Queries and Sub Queries Generation Using

(i) Relational functions

7
(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

12 Trigger Generations on Active Tables

DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

INDEX
13 E-R Diagrams for CMM

Project -2

13 Viva Voice Questions

14 Reference Books
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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.
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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.

DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

INRODUCTION TO DATABASE MANAGEMENT SYSTEM.

A Database Management System (DBMS) is a set 2. Application development

of computer programs that controls the creation, 3. Data analysis
maintenance, and the use of a database. It allows 4. Concurrency and robustness
organizations to place control of database 5. Efficiency
development in the hands of database 6. Security
administrators (DBAs) and other specialists. A DBMS
is a system software package that helps the use of We can hide the data in the
integrated collection of data records and files database. A DBMS is a set of software programs that
known as databases. It allows different user controls the organization, storage, management,
application programs to easily access the same and retrieval of data in a database. We can hide the
database. DBMSs may use any of a variety of data in the database.i.e the Data Abstraction. There
database models, such as the network are certain levels in the data abstraction, they are
model or relational model. In large systems, a DBMS 1. Physical level
allows users and other software to store and 2. Logical level
retrieve data in a structured way. Instead of having 3. View level
to write computer programs to extract information, The overall design of a database is called as
user can ask simple questions in a query language. SCHEMA. There are 3 types of schemas. Physical
Thus, many DBMS packages provide Fourth- schema, logical schema, sub schema. The data in
generation programming language (4GLs) and other the database is stored in the form of entities, the
application development features. It helps to specify tables. The content is in the form of bytes. We can
the logical organization for a database and access perform certain operations of DDL,DML commands
and use the information within a database. It for the entities in the database like create ,insert,
provides facilities for controlling data access, delete, rename, alter, truncate, drop, update. A
enforcing data integrity, managing concurrency, and VIEW is a table whose rows are not explicitly stored
restoring the database from backups. A DBMS also in the database but are computed as needed,it can
provides the ability to logically present database be used just like a base table. We can perform
information to users. operations like create, update, delete, drop.
An entity-relationship model (ERM) is an
The various functions of DBMS are
abstract and conceptual representation of data.
1. Data Definition
Entity-relationship modeling is a database modeling
2. Data manipulation
method, used to produce a type of conceptual
3. Data security and integrity
schema or semantic data model of a system, often a
4. Data recovery and concurrency
relational database, and its requirements in a top-
5. Data dictionary maintaince
down fashion. Diagrams created by this process are
6. Performance
called ER diagrams. We represent the relation
DBMS consists of
between the entites in the ER diagrams. We have
1. Data base design
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

certain relations like Binary relation, Ternary

relation, and Aggregate relation. We have certain Applications of DBMS
Integrity constraints over the relationships. 1. Data indepence
2. Efficient data access
We can remove the unwanted data in the 3. Data integrity
database,is NORMALIZATION, elimination of 4. Data security
redundant data. There are befinits of normalization 5. Data admistration
1. Less storage space 6. Concurrent access
2. Quicker updates 7. Crash recovery
3. Less data inconsistency 8. Reduced application development
4. Easier to add data
5. Flexible structure Examples of DBMS are:
6. Clearer data relationships 1. Banking
We have types in normalization like 2. Finance
0NF,1NF,2NF,3NF,Boyce-codd normal form. 3. Human resources
A trigger is a application which consists of 4. Tele communication
event ,condition, and action in an organized 5. Universities
manner. It will be activated only when there is some 6. Airlines
insertion, deletion or modification of content 7. Online ticket booking
present in database. There are 2 types of triggers 8. In e-seva .
row-level trigger and statement level trigger.
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

INTRODUCTION TO ORACLE 10G

The Oracle Database (commonly referred to form of table spaces and physically in the form of
as Oracle RDBMS or simply as Oracle) is an object- data files ("data files").Table spaces can contain
relational database management system (ORDBMS) various types of memory segments, such as Data
produced and marketed by Oracle Corporation. An Segments, Index Segments, etc. Segments in turn
oracle is free to develop, delay and distributed. comprise one or more extents. Extents comprise
Oracle database 10g express edition is an entry groups of contiguous data blocks. Data blocks from
level, small foot print database based on the oracle the basic units of data storage.
database 10g release to code base that’s free to
develop, delay and distribute, fast to download and Oracle database conventions refer to defined
simple to administer. Oracle database XE is a great groups of object ownership (generally associated
starter database for: with a "username") as schemas. Each Oracle
instance uses a System Global Area or SGA a shared-
 Developers working on PHP, JAVA, .NET,
memory area—to store its data and control-
XML and open source application.
information. Each Oracle instance allocates itself an
 DBA’s who need a free starter database for
SGA when it starts and de-allocates it at shut-down
training and development.
time.
 Independent software vendors (ISVS) and
hardware vendors who want a starter The library cache stores shared SQL, caching the
database to distribute free of charge. parse tree and the execution plan for every unique
 Educational institutions and students who SQL statement. If multiple applications issue the
need free database for their curriculum. same SQL statement, each application can access
With oracle database XE,we can now develop and the shared SQL area. This reduces the amount of
delay application with a powerful, proven, industry memory needed and reduces the processing-time
leading infrastructure and then upgrade when
used for parsing and execution planning.
necessary without costly and complex migrations.
The data dictionary comprises a set of tables
An Oracle database system—identified by an and views that map the structure of the database.
alphanumeric system identifier or SID comprises at
least one instance of the application, along with Oracle databases store information here about the
data storage. Users of the Oracle databases refer to logical and physical structure of the database. The
the server-side memory-structure as the SGA data dictionary contains information such as:
(System Global Area). The Oracle DBMS can store
and execute stored procedures and functions within  User information, such as user privileges
itself. PL/SQL (Oracle Corporation's proprietary  Integrity constraints defined for tables in the
procedural extension to SQL), or the object-oriented database
language Java can invoke such code objects and/or  Names and datatypes of all columns in database
provide the programming structures for writing tables
them. The Oracle RDBMS stores data logically in the
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

 Information on space allocated and used for  Oracle Applications Release 11i (aka Oracle e-
schema objects. Business Suite, Oracle Financials or Oracle 11i):
a suite of business applications;
The Program Global Area or PGA memory-area of an  Oracle Developer Suite 10g (9.0.4);
Oracle instance contains data and control-
 Oracle JDeveloper 10g: a Java integrated
information for Oracle's server-processes. The
development environment;
dynamic performance views (also known as "fixed
views") within an Oracle database present The latest certification information of oracle
information from virtual tables (X$ tables) built on application server log(10.1.4)
the basis of database memory. Database users can
access the V$ views to obtain information on Licensing information regarding compliance for
database structures and performance. oracle application server is available at

Oracle database can be installed on any size host http://www.oracle.com/technology/productias/

machine with any number of cpu’s, but XE will store index.html.
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;
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

ORACLE 10 G PRINT SCREENS

DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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.
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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

DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

->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.
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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 SID ROLL NAME AGE
KEY,
roll NUMBER NOT NULL PRIMARY
KEY,
name varchar(10) NOT NULL,
age NUMBER NOT NULL);

CREATE TABLE Department

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

CREATE TABLE Employee

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

CREATE TABLE student( roll NUMBER PRIMARY KEY, SID ROLL MARKS
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 ADDRESS
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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
ROLL NAME
2 RUN 2 RUN
2 yash
Enter the Data in a file as:
1 raj
Select roll,name from student;

Save xyz.txt 3 Srinu

To get the Command Back , Just Type 4 Lakhan

Get xyz.txt

DML Statements :
SID ROLL NAME MARKS
insert into student values(1001,1,'raj',78);
1002 2 yash 20
insert into student values(1002,2,'Yash',67);
1001 1 raj 19
insert into student values(1003,3,'Srinu',84);
1003 3 Srinu 22
insert into student values(1004,4,'Lakhan',97);
1004 4 Lakhan 23
Inserting Values at the run time :
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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

Sorting in form of marks : SID ROLL NAME AGE

select * from student order by age; 1002 2 Yash 19

1001 1 Raj 20

1003 3 Srinu 22

1004 4 Lakhan 23

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:
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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); ROLL NAME MARKS

ROLL NAME MARKS

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

ANY:
ROLL NAME MARKS
select * from marks where marks = ANY (89,
2 Yash 78
78);
Output - 3 Srinu 89

ALL:
select * from marks where roll <> ALL (2, 3); ROLL NAME MARKS
Output - 1 Raj 93
4 Lakhan 68

UNION :
ROLL NAME MARKS
select * from marks where marks <80 union
select * from marks where marks > 70; 2 Yash 78
Output -

INTERSECT:
select * from marks where marks <80 intersect ROLL NAME MARKS
select * from marks where marks > 50; 1 Raj 93
Output is: roll 5,6
3 Srinu 89
ORDER BY & DESC: 2 Yash 78
select * from marks ORDER BY marks DESC;
Output- 4 Lakhan 68
 DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

SUB-QUERIES: Output -
Select * from (select * from marks ORDER BY marks DESC)
where rownum < 3; ROLL NAME MARKS
1 Raj 93
3 Srinu 89

IN: Output -
ROLL NAME MARKS
select * FROM marks where roll IN (3,4); 3 Srinu 89
4 Lakhan 68

NOT IN: Output-

ROLL NAME MARKS
select * FROM marks where roll NOT IN 1 Raj 93
(2,3,4);

SELECTION : Output-
ROLL NAME MARKS
select marks FROM marks where marks = 93; 1 Raj 93

EXISTS: Output-
ROLL NAME MARKS
select * FROM marks where EXISTS
4 Lakhan 68
(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 SUM:

(COUNT, SUM, AVG, MAX and MIN), GROUP BY, select SUM (marks) FROM marks;
HAVING . Output -328

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

select COUNT (marks) FROM marks; Output -260
Output - 4
AVG:
select COUNT (marks) FROM marks where marks select AVG (marks) FROM marks;
>70; Output is : 82.0
Output -3
select AVG (marks) FROM marks where marks>90;
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

Output is: 93 string functions (Concatenation, lpad, rpad, ltrim,

rtrim, lower, upper, initcap, length, substr and
MAX: instr),
select MAX(marks) from marks;
Output is : 93 SELECT RPAD(roll, 12, '.'), LPAD(marks, 2, '*') FROM
marks WHERE rownum < 3;
MIN:
select MIN(marks) from marks; SELECT UPPER(name), INITCAP(name) FROM marks
Output is: 68 ORDER BY LENGTH(name);

select MAX (marks), MIN (marks) FROM marks; Select LTRIM ('** Prasad ** ', '*') FROM dual;
Output is : 93 & 68
select RTRIM (' ** Prasad is Great **', '*') FROM
GROUP BY: dual;
The GROUP BY clause can be used to summarize
rows into a group or groups of rows based on a select TRIM (both '*' from '** Prasad **') FROM
grouping function placed into the select clause. dual;

HAVING: select SUBSTR(‘Prasad',2) "sub1" from dual ;

The HAVING clause can then be used to filter out
unwanted groups much like the where clause. select SUBSTR( 'Prasadisgreat', 4, 3) "sub2" from
dual;
The expression for the select statement must
select INSTR ( 'Prasad', 'd' ) "position1" from dual;
include at least one grouping function such as MAX()
or COUNT().
select INSTR('Prasad is dangerous person','d',1,2)
You use the GROUP BY clause to group rows into
"position2" from dual;
blocks with a common column value
GROUP BY:
Select sdept, max(sbudget) from student3 group by date functions (sysdate, next_day, add_months,
sdept; last_day, months_between)
Output is : Dpet name with Highest Budget each SELECT SYSDATE FROM DUAL;
HAVING: SELECT NEXT_DAY ('14-JAN-2010', 'WED') FROM
select sdept,count(sname) from student3 group by DUAL;
sdept having count(sname)>2;
Output : ECE with 3 SELECT LAST_DAY ('17-JULY-2010') FROM DUAL;

Conversion Functions SELECT MONTHS_BETWEEN ('14-NOV-2009', '14-

DEC-2010') FROM DUAL;
 String Functions
 Date Functions to char, to date
 To char . To Date Functions
SELECT TO_DATE ('2-1-10', 'mm-dd-yy') FROM
 Least , Great ,Truncate & Round Functions
DUAL;
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

SELECT TO_CHAR (sysdate, 'mm-dd-yy') FROM SELECT ROUND (10.567, 2), TRUNC (10.567, 2)
DUAL; FROM DUAL;
SELECT GREATEST(10, 20, 30), LEAST (10, 20, 30)
least, greatest, trunc, round 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; ROLL NAME MARKS
Output: View Created 1 Raj 93
3 Srinu 89
Working on a View: 2 Yash 78
Check output for Vmarks 4 Lakhan 68
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;
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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=&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>&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
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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
SELECT marks
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

INTO vmarks
FROM mrk
WHERE reg = &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=&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 = &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';
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

END CASE;
DBMS_OUTPUT.PUT_LINE ('------------ ');
DBMS_OUTPUT.PUT_LINE (' grade is: '||grade);
END;
Output-
Enter value for reg:5
Old 8: select marks into vmarks FROM mrk WHERE reg=&reg;
new 8: select marks into vmarks FROM mrk WHERE reg=5;
Grade is :A
PL/SQL Procedure successfully completed.

Program 6: CASE EXPRESSION STATEMENT

DECLARE
vmarks NUMBER ;
grade char(1);
BEGIN
SELECT marks
INTO vmarks
FROM mrk
WHERE reg = &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 ;
DBMS_OUTPUT.PUT_LINE ('------------ ');
DBMS_OUTPUT.PUT_LINE (' grade is: '||grade);
END;

Output-
Enter value for reg: 17
Old 8: select marks into vmarks FROM mrk WHERE reg=&reg;
new 8: select marks into vmarks FROM mrk WHERE reg=17;
Grade is: B
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

PL/SQL Procedure successfully completed.

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
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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
PL/SQL Procedure successfully completed.

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)
314
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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
PL/SQL Procedure successfully completed.

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
300
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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 = &reg;

OUTPUT:
Old Marks 20 are changed to New Marks 50.

A Small Alert is Given when the Updation in the Table is given

DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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.
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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

DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

To
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

If installation process completes successfully then a window as shown below will appear.
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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>
<script type="text/javascript" src="JScript/script.js"></script>
<body id="reg">
<FORM name="form">
<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>
<td><INPUT TYPE="TEXT" name="PNO" class="reqd pno"> </td></tr>
<tr>
<td align="right"><input type="submit" id="submit"></td>
<td align="center"><input type="reset"></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;
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

// 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>
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

Output Screens :
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

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") ;
%>
<br><br>

<TABLE BORDER="1" align="center">

<TR>

<TH>Name </TH>
<TH>Address</TH>
<TH>Country</TH>
<TH>Hobbies</TH>
</TR>
<% while(resultset.next()){ %>
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

<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 :
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

Viva Voice Questions ?

1.What is a Database? 37. What is Functional Dependency?

2.What is DBMS? 38. When is a functional dependency F said to be
3.Explain Data Base System? minimal?
4.Trace the Advantages in DBMS ? 39. What is Multivalued dependency?
5.List the disadvantages in DBMS? 40. What is Lossless join property?
6.Describe the three levels of data abstraction? 41. What is 1 NF (Normal Form)?
7.Define the "integrity rules". 42. What is Fully Functional dependency?
8.What is Data Independence? 43. What is 2NF?
9.What is a view? How it is related to data 44. What is 3NF?
independence? 45. What is BCNF (Boyce-Codd Normal Form)?.
10.What is Data Model? 46. What are partial, alternate,, artificial, compound
11.What is E-R model? and natural key?
12.What is Object Oriented model? 47. What is indexing and what are the different
13. What is an Entity? kinds of indexing?
14.What is an Entity type? 48. What is system catalog or catalog relation? How
15. What is an Entity set? is better known as?
16.What is an Extension of entity type? 49. What is meant by query optimization?
17.What is Weak Entity set? 50. What is durability in DBMS?
18.What is an attribute? 51. What do you mean by atomicity and
19.What is a Relation Schema and a Relation? aggregation?
20. What is degree of a Relation? 52. What is a Phantom Deadlock?
21. What is Relationship? 53. What is a checkpoint and When does it occur?
22. What is Relationship set? 54. What are the different phases of transaction?
23. What is Relationship type? 55. What do you mean by flat file database?
24. What is degree of Relationship type? 56. What is "transparent DBMS"?
25. What is DDL (Data Definition Language)? 57. What is a query?
26. What is VDL (View Definition Language)? 58. What do you mean by Correlated subquery?
27. What is SDL (Storage Definition Language)? 59. What is a Tigger
28. What is Data Storage - Definition Language? 60.What is an active database
29. What is DML (Data Manipulation Language)? 61. Define a function
30. What is DML Compiler? 62. How procedure helps us in PL SQL Programming
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?
DBMS Lab Manual for III B.Tech II Sem CSE & IT Raghu Institute Of Technology

References:

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

ORACLE PL/SQL by example. Benjamin Rosenzweig, Elena Silvestrova, Pearson Education 3 rd 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.