Jawaharlal Nehru Engineering College: Laboratory Manual
Jawaharlal Nehru Engineering College: Laboratory Manual
Jawaharlal Nehru Engineering College: Laboratory Manual
Laboratory Manual
For
Third Year Students CSE
Dept: Computer Science & Engineering (NBA Accredited)
FOREWORD
It is my great pleasure to present this laboratory manual for THIRD YEAR
COMPUTER SCIENCE engineering students for the subject of Database
Management System keeping in view the vast coverage required for
cryptography and encryption.
As a student, many of you may be wondering with some of the questions in
your mind regarding the subject and exactly what has been tried is to answer
through this manual.
As you may be aware that MGM has already been awarded with ISO 9001:2000
certification and it is our endure to technically equip our students taking the
advantage of the procedural aspects of ISO 9001:2000 Certification.
Faculty members are also advised that covering these aspects in initial stage
itself, will greatly relived them in future as much of the load will be taken care
by the enthusiasm energies of the students once they are conceptually clear.
Dr. S.D.Deshmukh
Principal
This manual is intended for THIRD YEAR COMPUTER SECINCE students for
the subject of Database Management System. This manual typically contains
practical/Lab Sessions related Database Management System covering various
aspects related the subject to enhanced understanding.
Students are advised to thoroughly go though this manual rather than only
topics mentioned in the syllabus as practical aspects are the key to
understanding and conceptual visualization of theoretical aspects covered in
the books.
Good Luck for your Enjoyable Laboratory Sessions
Prof. D.S.Deshpande
HOD, CSE
Ms. S.B.Jaiwal
Lecturer, CSE Dept.
Submission related to whatever lab work has been completed should be done
during the next lab session. The immediate arrangements for printouts related to
submission on the day of practical assignments.
2. Students should be taught for taking the printouts under the observation of lab
teacher.
3. The promptness of submission should be encouraged by way of marking and
evaluation patterns that will benefit the sincere students.
SUBJECT INDEX
1. Installing oracle 9i on windows xp.
2. Creating Entity-Relationship Diagram using case tools.
3. Writing SQL statements Using ORACLS /MYSQL:
1. Writing basic SQL SELECT statements.
2. Restricting and sorting data.
3. Displaying data from multiple tables.
4. Aggregating data using group function.
5. Manipulating data.
6. Creating and managing tables.
4. Normalization in ORACLE.
5. Creating cursor in oracle.
6. Creating procedure and functions in oracle.
7. Creating packages and triggers in oracle.
8. TEAM Project
1. Lab Exercise
Aim: Installation of oracle 9i On WIN xp( Introduction to
Database.)
S/w Requirement: CD of the setup Oracle 9i, 10g.
Theory:
Database is a collection of information in a structured
way. We can say that it is a collection of a group of facts. Your
personal address book is a database of names you like to keep track
of, such as personal friends and members of your family.
Fig.1 contains required details about each student. There are six
pieces of information on each student. They are Roll No, Name,
Address and Subjects. Each piece of information in database is
called a Field. We can define field as the smallest unit in a
database. Each field represents one and only one characteristic of
an event or item. Thus there are four fields in this database.
ROLL
NO
NAME
200207
Abc
18
200207
Pqr
18
200207
Xyz
18
200207
Stq
18
200207
Plm
18
ADDRESS
n6,Aurangaba
d
n5,Aurangaba
d
n2,Aurangaba
d
n1,Aurangaba
d
n7,Aurangaba
d
SUBJECTS
DBMS
.NET
VB.NET
CG
Fig. 1
If you take a close look at all these fields, they are not of the same
6
type. Name is character type Roll no is number type. In database there can be
five categories of fields. They are:
Numeric
Character
Logic
Memo
Date
Press Next
10
11
12
13
Enter your Global Database Name (also called TNS Name). You
need to know this name for later steps!
In this example, "compiere" is the database name or SID and
"compiere.org" the company name
Press Next.
14
15
In most cases, you could use the default character set, but we recommend
using Unicode; press Next.
Note: The character set AL16UTF16 is more efficient than the default
AL32UTF8. AL16UTF16 is also referred to as UTF16 or UCS2 and is a fixed 16
bit length code. AL32UTF8 is a variable length unicode implementation and a
bit less efficient as with every access, the length/width needs to be calculated.
So, you have to choose between speed (AL16UTF16) and space (AL32UTF8).
16
17
You will see this for a while ... and then ...
18
19
Enter and remember your SYS and SYSTEM password and press
OK.
20
Compiler does not use the Oracle HTTP Server. You can disable the start
in your operation system. Press Exit.
21
2. Lab Exercise
Aim: Study of Entity- Relationship diagram.
S/w Requirement: Oracle 9i, 10g.
Theory:
An entity-relationship (ER) diagram is a specialized graphic that
illustrates the interrelationships between entities in a database. ER
diagrams often use symbols to represent three different types of
information. Boxes are commonly used to represent entities. Diamonds are
normally used to represent relationships and ovals are used to represent
attributes.
1 Entity
An entity is a person, place, concept, or thing about which the
business needs data.
2 Relationship
A relationship is an association between entity types
Example of ER Diagram
we have identified three entity types (Employee, Department, Division) and two
relationships among these entity types (manages, contains). Now we can begin
to represent the problem in the language of ER modeling.
ER models are usually represented graphically. The language we are going to
use represents entity types as rectangles and relationships as diamonds. Below
is the representation of the situation we are working with.
Notice that the contains relationship is drawn between the two entities that it is
associated with. Similarly for the manages relationship. This (simplified) ER
model tells us that:
Certainly, we know more about the problem than this. Consider the
relationship between divisions and departments. We know that divisions have
multiple departments and departments can only be contained within one
division. Or, for every one division there can be many departments. In the
22
Books
CatalogID PageCount Author PublishDate ISBN
23
It may take a few minutes for this design to sink in. As a comparison, here is
what the proposed database table would look like as a single monolithic table.
The one-to-one relationship has saved us from doubling the number of fields in
the Catalog table and, more importantly, helped us break the database into
more discrete entities. In this scenario, we can get all the general information
about an item from the Catalog table and can use the primary key of that table
to pull up the appropriate information from the sub table.
B. One-to-many
The one-to-many relationship is the workhorse of relational databases as well
as being the easiest relationship to understand. Let us say you need to build a
shopping cart application for an e-commerce site. Your first draft of the
database has columns for Item1, Item2, and Item3 with the corresponding
Quantity1, Quantity2, and Quantity3 fields.
OrderNum ShippingInfo Item1 Quantity1 Item2 Quantity2 Item3 Quantity3
Of course, this immediately starts to break down with more than three orders!
Any time you find yourself designing a database and adding similar fields like
this to the same table, you need to break the table into two (or more!) related
tables using a one-to-many relationship.
A one-to-many relationship allows records in Table 1 to be connected to an
arbitrary number of records in Table 2 without the limitations imposed by
resorting to redundant or limited numbers of fields in a single table. This
reduces the size of the database and greatly increases the flexibility and
performance of queries operating on that data. We can take our shopping cart
example and break it into an Order table and an Item table quite simply.
Order Table
OrderID ShippingInfo
OrderItem Table
OrderItemID OrderID Item Quantity
The two tables are linked together using the OrderID field. The contents of any
order in the Order table can easily be found by finding all the items with that
value in the OrderID field. There is also the added advantage that the two
pieces of data are independent and can easily be modified. If we now want to
add an ItemNumber to the OrderItem table, we add a single column; in our
24
Paul McCartney
Song Table
SongID MusicianID SongName
200
44
201
44
But from the song side, you potentially have a song related to many musicians.
The following visual represents that situation.
Song Table
SongID SongName
200
Musician Table
MusicianID SongID MusicianName
43
200
John Lennon
44
200
Paul
McCartney
These two tables work individually, but when you try to put them together, you
end up with this mish-mash table.
25
Song Table
SongID MusicianID SongName
200
43
200
44
201
44
Musician Table
MusicianID SongID MusicianName
43
200
John Lennon
44
200
Paul
McCartney
44
201
Paul
McCartney
John Lennon
44
Paul McCartney
Song_Musician Table
SongID
MusicianID
200
43
200
44
201
44
Song Table
SongID SongName
200
201
26
This time around, all of the redundant data is in the Song_Musician table,
which are only two columns of integers. Any changes to the structure of the
Song or Musician table remain independent of their relationship, which is
precisely what we're after.
D. Many to One
An employee can work in only one department; this relationship is singlevalued for employees. On the other hand, one department can have many
employees; this relationship is multi-valued for departments. The relationship
between employees (single-valued) and departments (multi-valued) is a one-tomany relationship.
To define tables for each one-to-many and each many-to-one relationship:
1. Group all the relationships for which the "many" side of the
relationship is the same entity.
2. Define a single table for all the relationships in the group.
In the following example, the "many" side of the first and second relationships
is "employees" so an EMPLOYEE table is defined.
Table 3. Many-to-One Relationships
Entity
Relationship
Entity
Employees
are
to
departments
Employees
work at
jobs
Departments
report to
(administrative) departments
assigned
WORKDEPT
JOB
000010
A00
President
000020
B01
Manager
27
EMPNO
WORKDEPT
JOB
000120
A00
Clerk
000130
C01
Analyst
000030
C01
Manager
000140
C01
Analyst
000170
D11
Designer
ADMRDEPT
C01
A00
D01
A00
D11
D01
28
3. Lab Exercise
Aim: Writing SQL statements Using ORACLS /MYSQL
S/w Requirement: Oracle 9i, 10g.
Theory:
1. DDL statements [Data Definition Language].
Statements are used to define the database structure or schema. Some
examples:
A. CREATE - to create objects in the database
B. ALTER - alters the structure of the database
C. DROP - delete objects from the database
D.TRUNCATE - remove all records from a table, including all spaces allocated
for the records are removed
SYNTAX:
A. create table<table_name> (column definition 1, column definition 2,);
B. alter table <table_name> modify (column definition.);
alter table <table_name> add (column definition.);
C. drop table <table_name>;
D. truncate table< table_name>;
2. DML Statements [Data Manipulation Language]
Statements are used for managing data within schema objects. Some
examples:
A.SELECT - retrieve data from the a database
B. INSERT - insert data into a table
C. UPDATE - updates existing data within a table
D. DELETE - deletes all records from a table, the space for the records remain
SYNTAX:
A.select <column_name> from <table_name>;
29
A group function returns a result based on group of rows. Some are purely
mathematical function.
A. Avg function :
It returns average of values of the column specified in the arguments in the
column.
30
SYNTAX:
Select avg(column_name whose avg to find) from <table_name> where
condition;
B.min function:
This function will give the least value of the column present in the argument.
Select min(column_name whose min to find) from <table_name> where
condition;
C.max function:
This function will give the maximum
argument.
E.count:
This functionis used to count the number of rows in function .
Select count (*) from <table_name> ;
31
4. Lab Exercise
Aim: Normalization in ORACLE.
S/w Requirement: Oracle 9i, 10g.
Theory:
A. Trivial functional dependency
A trivial functional dependency is a functional dependency of an
attribute on a superset of itself. {Employee ID, Employee Address}
{Employee Address} is trivial, as is {Employee Address} {Employee
Address}.
B. Full functional dependency
An attribute is fully functionally dependent on a set of attributes X if it is
Functionaly dependent on X, and
not functionally dependent on any proper subset of X. {Employee
Address} has a functional dependency on {Employee ID, Skill}, but
not a full functional dependency, because it is also dependent on
{Employee ID}.
C. Transitive dependency
A transitive dependency is an indirect functional dependency, one in
which XZ only by virtue of XY and YZ.
D. Multivalued dependency
A multivalued dependency is a constraint according to which the
presence of certain rows in a table implies the presence of certain other
rows.
E. Join dependency
A table T is subject to a join dependency if T can always be recreated by
joining multiple tables each having a subset of the attributes of T.
F. Superkey
A superkey is an attribute or set of attributes that uniquely identifies
32
rows within a table; in other words, two distinct rows are always
guaranteed to have distinct superkeys. {Employee ID, Employee Address,
Skill} would be a superkey for the "Employees' Skills" table; {Employee
ID, Skill} would also be a superkey.
G. Candidate key
A candidate key is a minimal superkey, that is, a superkey for which we
can say that no proper subset of it is also a superkey. {Employee Id, Skill}
would be a candidate key for the "Employees' Skills" table.
H. Non-prime attribute
A non-prime attribute is an attribute that does not occur in any
candidate key. Employee Address would be a non-prime attribute in the
"Employees' Skills" table.
F. Primary key
Most DBMSs require a table to be defined as having a single unique key,
rather than a number of possible unique keys. A primary key is a key
which the database designer has designated for this purpose.
1 1st NF
2 2nd NF
3 3rd NF
Normal form
Defined by
Brief definition
Table
faithfully
represents
a
First
normal Two versions: E.F. Codd (1970),
relation and has no "repeating
form (1NF)
C.J. Date (2003)
groups"
Second normal
E.F. Codd (1971)
form (2NF)
Third normal
E.F. Codd (1971); see also Carlo Every non-prime attribute is nonform (3NF)
Zaniolo's
equivalent
but transitively dependent on every key
33
Fourth normal
Ronald Fagin (1977
form (4NF)
Every
non-trivial
multivalued
dependency in the table is a
dependency on a superkey
Fifth
normal
Ronald Fagin (1979)
form (5NF)
Domain/key
normal
form Ronald Fagin (1981)
(DKNF)
Now we ready to come to grips with the ideas of normalization. The following
table, containing information about some students at Enormous State
University, is a table that is in 1st Normal Form, 1NF.
You can easily verify for yourself that this table satisfies the definition of 1NF:
viz., it has no duplicated rows; each cell is single-valued (i.e., there are no
repeating groups or arrays); and all the entries in a given column are of the
same kind.
34
Table
FirstName LastName Major
SocialSecurity
Number
123-45-6789
Jack
Jones
Library
Information
Science
and
222-33-4444
Lynn
Lee
Library
Information
Science
and
987-65-4321
Mary
Ruiz
Pre-Medicine
123-54-3210
Lynn
Smith
Pre-Law
111-33-5555
Jane
Jones
Library
Information
Science
and
Table 1
In Table 1 we can see that the key, SSN, functionally determines the other
attributes; i.e., a given Social Security Number implies (determines) a
particular value for each of the attributes FirstName, LastName, and Major
(assuming, at least for the moment, that a student is allowed to have only one
major). In the arrow notation: SSN ? FirstName, SSN ? LastName, and SSN ?
Major.
A key attribute will, by the definition of key, uniquely determine the values of
the other attributes in a table; i.e., all non-key attributes in a table will be
functionally dependent on the key. However, there may be non-key attributes
in a table that determine other attributes in that table. Consider the following
table:
35
Level
Jack
Jones
LIS
Graduate
Lynn
Lee
LIS
Graduate
Mary
Ruiz
PreUndergraduate
Medicine
Lynn
Smith
Pre-Law
Undergraduate
Jane
Jones
LIS
Graduate
Table 2
In Table 2 the Level attribute can be said to be functionally dependent on the
Major attribute. Thus, we have an example of an attribute that is functionally
dependent on a non-key attribute. This statement is true in the table per se,
and that is all that the definition of functional dependence requires; but the
statement also reflects the real-world fact that Library and Information Science
is a major that is open only to graduate students and that Pre-Medicine and
Pre-Law are majors that are open only to undergraduate students.
36
SSN
Level
123456789
Jack
Jones
LIS
Graduate
222334444
Lynn
Lee
LIS
Graduate
987654321
Mary
Ruiz
PreMedicine
Undergraduate
123543210
Lynn
Smith
Pre-Law
Undergraduate
111335555
Jane
Jones
LIS
Graduate
Table 3
With the SSN defined as the key, Table 3 is in 2NF, as you can easily verify.
This illustrates the fact that any table that is in 1NF and has a single-attribute
(i.e., a non-composite) key is automatically also in 2NF.
Table 3 still exhibits some problems, however. For example, it contains some
repeated information about the LIS-Graduate pairing.
Anomalies and Normalization
At this point it is appropriate to note that the main thrust behind the idea of
normalizing databases is the avoidance of insertion and deletion anomalies in
databases.
To illustrate the idea of anomalies, consider what would happen to our
knowledge (at least, as explicitly contained in a table) of the level of the major,
Pre-Medicine, if Mary Ruiz left Enormous State University. With the deletion of
the row for Ms. Ruiz, we would lose the information that Pre-Medicine is an
Undergraduate major. This is an example of a deletion anomaly. We may
possess the real-world information that Pre-Medicine is an Undergraduate
major, but no such information is explicitly contained in a table in our
database.
As an example of an insertion anomaly, we can suppose that a new student
wants to enroll in ESU: e.g., suppose Jane Doe wants to major in Public
Affairs. From the information in Table 3 we cannot tell whether Public Affairs is
37
FirstName LastName
123456789
Jack
Jones
38
222334444
Lynn
Lee
987654321
Mary
Ruiz
123454321
Lynn
Smith
111335555
Jane
Jones
999887777
Newton
Gingpoor
Table 4
Major
Level
LIS
Graduate
PreMedicine
Undergraduate
Pre-Law
Undergraduate
Public
Affairs
Graduate
Table 5
SSN
Major
123-456789
LIS
222-334444
LIS
39
987-654321
PreMedicine
123-543210
Pre-Law
111-335555
LIS
Table 6
The three preceding tables should strike you as providing a better arrangement
of the information in Table 3. For one thing, this arrangement puts the
information about the students into a smaller table, Table 4, which happily
fails to contain redundant information about the LIS-Graduate pairing. For
another thing, this arrangement permits us to enter information about
students (e.g., Newton Gingpoor) who have not yet identified themselves as
pursuing a particular major. For still another thing, it puts the information
about the Major-Level pairings into a separate table, Table 5, which can easily
be expanded to include information (e.g., that the Public Affairs major is at the
Graduate level) about majors for which, at the moment, there may be no
students registered. Finally, Table 6 provides the needed link between
individual students and their majors (note that Newton Gingpoor's SSN is not
in this Table 6, which tells us that he has not yet selected a major).
Tables 4 - 6 are single-theme tables and are in 2NF, as you can easily verify. (In
fact, they are in DKNF, but we are not yet ready to discuss the latter level in
detail.)
The 3rd Normal Form (3NF)
In order to discuss the 3rd Normal Form, we need to begin by discussing the
idea of transitive dependencies.
In mathematics and logic, a transitive relationship is a relationship of the
following form: "If A implies B, and if also B implies C, then A implies C." An
example is: "If John Doe is a human, and if every human is a primate, then
John Doe must be a primate." Another way of putting it is this: "If A
functionally governs B, and if B functionally governs C, then A functionally
governs C." In the arrow notation, we have:
[(A ? B) and (B ? C)] ? (A ? C)
The following table, Table 7, provides an example of how transitive
dependencies can occur in a table in a relational database.
Author
Last
Author
First
Book Title
Subject
Collection
Library
or Building
40
Name
Name
Berdahl
Robert
PCL
General Perry-Casta
Stacks
Library
Yudof
Mark
Child Abuse
Neglect
Law Library
and Legal
Procedures
Townes Hall
Harmon Glynn
PCL
General Perry-Casta
Stacks
Library
Graves
Robert
Classics
Library
Miksa
Library
and Perry-Casta
Information
Library
Science
Collection
Hunter
David
Music
Publishing Music
and Collecting
Literature
Fine
Library
Graves
Robert
PCL
General Perry-Casta
Stacks
Library
Greek
Literature
Waggener
Hall
Arts Fine
Arts
Building
Table 7
By examining Table 7 we can infer that books dealing with history, cognitive
psychology, and folksong are assigned to the PCL General Stacks collection;
that books dealing with legal procedures are assigned to the Law Library; that
books dealing with Greek literature are assigned to the Classics Library; that
books dealing with library biography are assigned to the Library and
Information Science Collection (LISC);and that books dealing with music
literature are assigned to the Fine Arts Library.
Further, we can infer that the PCL General Stacks collection and the LISC are
both housed in the Perry-Casta$ Library (PCL) building; that the Classics
Library is housed in Waggener Hall; and that the Law Library and Fine Arts
Library are housed, respectively, in Townes Hall and the Fine Arts Building.
Thus we see that there is a transitive dependency in Table 7: any book that
deals with history, cognitive psychology, or library biography will be physically
housed in the PCL building (unless it is temporarily checked out to a borrower);
any book dealing with legal procedures will be housed in Townes Hall; and so
on. In short, if we know what subject a book deals with, we also know not only
what library or collection it will be assigned to but also what building it is
physically housed in.
41
Author
First
Name
Book Title
Berdahl
Robert
Yudof
Mark
Harmon Glynn
Human
Knowledge
Memory
and
Graves
Robert
Miksa
Hunter
David
Music
Publishing
Collecting
Graves
Robert
and
Table 8
Book Title
Subject
42
Legal Procedures
Memory
and Cognitive
Psychology
Greek Literature
Library Biography
Music
Publishing
Collecting
Folksong
Table 9
Subject
Collection or Library
History
Legal Procedures
Law Library
Cognitive
Psychology
Greek Literature
Classics Library
Library Biography
Library and
Collection
Music Literature
Folksong
Information
Science
Table 10
Collection or Library
Building
Perry-Casta
Library
43
Law Library
Townes Hall
Classics Library
Waggener Hall
You can verify for yourself that none of these tables contains a transitive
dependency; hence, all of them are in 3NF (and, in fact, in DKNF).
We can note in passing that the fact that Table 8 contains the first and last
names of Robert Graves in two different rows suggests that it might be
worthwhile to replace it with two further tables, along the lines of:
Author
Last
Name
Author
First
Name
Author
Identification
Number
Berdahl
Robert
001
Yudof
Mark
002
Harmon
Glynn
003
Graves
Robert
004
Miksa
Francis
005
Hunter
David
006
Table 12
44
Author
Book Title
Identification
Number
001
The
Politics
of
Prussian Nobility
002
003
Human
Memory
Knowledge
004
005
006
Music
Publishing
Collecting
004
English
Ballads
and
the
and
and
Scottish
Table 13
Though Tables 12 and 13 together take a little more space than Table 8, it is
easy to see that given a much larger collection, in which there would be many
more authors with multiple works to their credit, Tables 12 and 13 would be
more economical of storage space than Table 8. Furthermore, the structure of
Tables 12 and 13 lessens the chance of making updating errors (e.g., typing
Grave instead of Graves, or Miska instead of Miksa).
The Boyce-Codd Normal Form (BCNF)
The Boyce-Codd Normal Form (BCNF) deals with the anomalies that can occur
when a table fails to have the property that every determinant is a candidate
key. Here is an example, Table 14, that fails to have this property. (In Table 14
the SSNs are to be interpreted as those of students with the stated majors and
advisers. Note that each of students 123-45-6789 and 987-65-4321 has two
majors, with a different adviser for each major.)
45
SSN
Major
Adviser
123-456789
Library
Science
123-456789
Public Affairs
222-334444
Library
Science
and
Information Putnam
555-121212
Library
Science
and
Information Dewey
987-654321
Pre-Medicine
Semmelweis
987-654321
Biochemistry
Pasteur
123-543210
Pre-Law
Hammurabi
and
Information Dewey
Roosevelt
Table 14
We begin by showing that Table 14 lacks the required property, viz., that every
determinant be a candidate key.
What are the determinants in Table 14? One determinant is the pair of
attributes, SSN and Major. Each distinct pair of values of SSN and Major
determines a unique value for the attribute, Adviser. Another determinant is
the pair, SSN and Adviser, which determines unique values of the attribute,
Major. Still another determinant is the attribute, Adviser, for each different
value of Adviser determines a unique value of the attribute, Major. (These
observations about Table 14 correspond to the real-world facts that each
student has a single adviser for each of his or her majors, and each adviser
advises in just one major.)
Now we need to examine these three determinants with respect to the question
of whether they are candidate keys. The answer is that the pair, SSN and
Major, is a candidate key, for each such pair uniquely identifies a row in Table
14. In similar fashion, the pair, SSN and Adviser, is a candidate key. But the
determinant, Adviser, is not a candidate key, because the value Dewey occurs
in two rows of the Adviser column. So Table 14 fails to meet the condition that
every determinant in it be a candidate key.
It is easy to check on the anomalies in Table 14. For example, if student 98765-4321 were to leave Enormous State University, the table would lose the
46
Adviser
123-456789
Dewey
123-456789
Roosevelt
222-334444
Putnam
555-121212
Dewey
987-654321
Semmelweis
987-654321
Pasteur
123-543210
Hammurabi
Table 15
Major
Adviser
Roosevelt
Semmelweis
Biochemistry
Pasteur
47
Pre-Law
Hammurabi
History
Herodotus
Table 16
5 Lab Exercise
48
In the above cursor we are going to update the table. Name of table is stud and
its attibutes are rollno which is number, firstname which is of datatype
varchar2, lastname which is of datatype varchar2.
In the Declare section we declare variable that match to the attribute in the
table %TYPE matches the datatype of that variable.In the above procedure
c_rollno is the variable that we declare in the procedure to access the column
rollno from table stud because of %type it directly provide matching of data.
In the begin section we open the cursor and fetch the values. Error handles by
the exception section.
Close the cursor.
And end;
B. Implicit cursor:
These are inbuilt cursor in oracle.
6 Lab Exercise
50
END Procedure_name;
FUNCTION:
Steps for Creating function:
CREATE OR REPLACE FUNCTION function _name (parameter list)AS/IS
Return datatype is/as
(local declaration)
BEGIN
52
(Executable section )
EXCEPTION
(Error handling or exception section ).
END function_name;
A function has two parts, namely function specification and function body. The
function specification begins with the keyword function and end with return
clause. The function bodies begins with the keyword is/as and end with
keyword end
create or replace function studentn(
p_rollno stud.rollno%type)
return boolean as
v_firstname varchar2(20);
v_return boolean;
begin
select firstname into v_firstname from stud where rollno=p_rollno;
if (firstname='seema')then
v_return =true;
else
v_return =false;
end if;
end studentn;
/
7. Lab Exercise
53
PL/SQL_BLOCK
/
Before/After options:
The before/after options can be used to specify when the trigger body should
be fired with respect to the triggering statement. If the user include a before
option, then Oracle fires the triggers before executing the triggering statement.
On the other hand if AFTER is used then , oracle files the trigger after
executing the triggering statement.
For each row/ statement:
For each row/ statement option included in the create trigger syntax specifies
that the triggers fires once per row . By defaults, database triggers a database
triggers fires for each statement .
create or replace trigger orders
before insert on order_detail for each row
declare
orno order_detail .orderno%type
begin
select orderno into orno from order_detail
where qty_ord <qyt_deld;
if orno =001 then
raise_application_error(-200001,enter some other name);
end if;
end;
/
In this trigger before inserting order table it check the condition orno (order
number) equal to 001
and condition is satisfied then the value updated else error is occurred.
create or replace trigger up_trig
before update order_detail for each row
begin
update order_detail set itemcode =i200 where qty_ord=50;
end;
8. Lab Exercise
Aim: TEAM Project
57
A team should contain 2-3 students from same batch. Project report
should be as per IEEE format. All the phases of SDLC should be
followed in project. No project topic should repeat in the class.
Mini Project should follow the steps below:
1) Define problem with specification.
2) Define the functionality.
3) Design solution for project.
4) Implement the solution.
5) Present and Evaluate the project.
Viva-voce Questions: -
What is database?
58
What is DBMS?
Advantage of database
What is data model?
What is object oriented model?
What is an entity?
What is an entity type?
What is an entity set?
What is weak entity set?
What is relationship?
What is DDL?
What is DML?
What is normalization?
What is functional dependency?
What is 1st NF?
What is 2nd NF?
What is 3rd NF?
What is BCNF?
What is fully functional dependency?
What do you mean of aggregation, atomicity?
What are different phases of transaction?
What do you mean of flat file database?
What is query?
What is the name of buffer where all commands are stored?
Are the resulting of relation PRODUCT and JOIN operation is same?
questions are normally are from the scope outside of the books. However
twisted questions and self formulated questions by the faculty can be asked
but correctness of it is necessarily to be thoroughly checked before the
conduction of the quiz.
10. Conduction of Viva-Voce Examinations:
Teacher should oral exams of the students with full preparation. Normally, the
objective questions with guess are to be avoided. To make it meaningful, the
questions should be such that depth of the students in the subject is tested
Oral examinations are to be conducted in co-cordial environment amongst the
teachers taking the examination. Teachers taking such examinations should
not have ill thoughts about each other and courtesies should be offered to each
other in case of difference of opinion, which should be critically suppressed in
front of the students.
60