INTRODUCTION TO DBMS

Database:

Related information is called as data.

Data stored as a collection in an organized manner is called as database.

DBMS:

A Database Management System is software packages designed to store and manage

databases. It is a software system that uses a standard method of cataloging, retrieving, and
running queries on data. The DBMS manages incoming data, organizes it, and provides ways for
the data to be modified or extracted by users or other programs.

RDBMS:

A Relational database management system is a program that lets you create, update and
administrates a relational database. The primary rule for RDBMS is that the data should be
stored in the form of tables. Most of the RDBMS uses Structured Query Language (SQL) to
access the database. When a database undergoes normalization it is called as RDBMS.

Different Products of RDBMS are:

PRODUCT CORPORATION
ORACLE Oracle Corporation
SQL Server 2000 Microsoft Corporation
DB2 UDB IBM
MySQL MySQL
Sybase Sybase
Teradata NCR

RDBMS Terminology:
1
 Formal relational term Informal equivalent(s)
Relation Table
Tuple Row, Record
Cardinality Number of rows
Attribute Column, field
Degree Number of columns
Primary key Unique identifier
Domain Set of legal values

SQL:

SQL is a special-purpose language used to define, access, and manipulate data. SQL is
nonprocedural, meaning that it describes the necessary components (i.e., tables) and desired
results without dictating exactly how those results should be computed. Every SQL
implementation sits atop a database engine; whose job it is to interpret SQL statements and
determine how the various data structures in the database should be accessed to accurately and
efficiently produces the desired outcome.SQL is not case-sensitive SELECT is same as select.

The SQL language includes two distinct sets of commands: Data Definition Language
(DDL) is the subset of SQL used to define and modify various data structures, while Data
Manipulation Language (DML) is the subset of SQL used to access and manipulate data
contained within the data structures previously defined via DDL. DDL includes numerous
commands for handling such tasks as creating tables, indexes, views, and constraints, while
DML is comprised of just five statements namely insert, delete, select, update and merge.

EX NO 1(a) DDL COMMANDS

2
DATE:

1.0 Aim:

To execute and verify the DDL commands.

2.0 DESCRIPTION
STEP 1: Create the table with its essential attributes.

STEP 2: Insert attribute values into the table

STEP 3: Execute different Commands and extract information from the table.

COMMANDS:

1. Create: This command is used to create a new table.

CREATE TABLE table_ name
(
column_name1 data_type,
column_name2 data_type,
....
);

2. Alter: This command is used to add, delete, and modify columns in an existing table.

To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name data type;

To delete a column in a table, use the following syntax

ALTER TABLE table_name DROP COLUMN column_name;

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name MODIFY column_name datatype;

3. Drop: This command is used to delete a table.

3
 DROP TABLE table_name;

4. Truncate: This command is used to remove all the records in the table and not the table
itself.
TRUNCATE TABLE table_name;

FOR EXAMPLE

1. Create a table in the name example with the following field’s and data type
NAME varchar(10)
ROLL NUMBER number(06)

SQL> create table example(NAME varchar(10), ROLL NUMBER number(06));

SQL> Table Created successfully

SQL> Desc example // describing the table

Fields Data Type with Keys

NAME varchar(10)
ROLL NUMBER number(06)
SQL>

3.0 PROGRAM
1. Create a table in the name employee with the following field’s and data type
EMPLOYEE NAME Varchar(15)
EMPLOYEE NO Number(10)
DEPT NAME Varchar(04)
DEPT NO Number(04)
DATE OF JOINING(DOJ) DATE
and display the table.

4
2. To the employee table ADD a new field with the data type mention below
EMPLOYEE SALARY Number(08)
and display it

3. Alter the table employee by dropping the field date of joining and display it.

5
4. Alter the table employee by changing the data type of dept.no. from integer to
varchar.

5. Truncate the table employee and drop it.

6. Drop the employee table.

6
4.0 RESULT:

REVIEW QUESTIONS:

1. What is SQL?

2. What are tables in SQL?

3. What are different types of statements supported by SQL?

7
 4. What is the difference between CHAR and VARCHAR2 datatype in SQL?

EX NO : 1 (b) SQL CONSTRAINTS

DATE:

1.0 AIM:

8
 To set constraints for the database table.
2.0 DESCRIPTION:
A constraint is a property assigned to a column or the set of columns in a table that prevents
certain types of inconsistent data values from being placed in the column(s). Constraints are used
to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
The following categories of the data integrity exist:

1. Entity Integrity ensures that there are no duplicate rows in a table.

2. Domain Integrity enforces valid entries for a given column by restricting the type, the format,
or the range of possible values.
3. Referential integrity ensures that rows cannot be deleted, which are used by other records
(for example, corresponding data values between tables will be vital).
4. User-Defined Integrity enforces some specific business rules that do not fall into entity,
domain, or referential integrity categories.
Each of these categories of the data integrity can be enforced by the appropriate
constraints. Microsoft SQL Server supports the following constraints:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT
NULL constraint enforces a field to always contain a value. This means that you cannot insert a
new record, or update a record without adding a value to this field. The following SQL enforces
the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE Persons

(

9
 P_Id int NOT NULL,
LastName varchar(15) NOT NULL,
FirstName varchar(15),
Address varchar(25),
City varchar(15)
);

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.The UNIQUE
and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of
columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the
"Persons" table is created:

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(15) NOT NULL,
FirstName varchar(15),
Address varchar(25),
City varchar(15),
UNIQUE (P_Id)
);

SQL UNIQUE Constraint on ALTER TABLE

10
 To create a UNIQUE constraint on the "P_Id" column when the table is already created,
use the following SQL:

ALTER TABLE Persons

ADD UNIQUE (P_Id);

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons

DROP INDEX uc_PersonID;

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values. A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons"
table is created:

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(15) NOT NULL,
FirstName varchar(15),
Address varchar(25),
City varchar(15),
PRIMARY KEY (P_Id)
);

SQL PRIMARY KEY Constraint on ALTER TABLE

11
 To create a PRIMARY KEY constraint on the "P_Id" column when the table is already
created, use the following SQL:

ALTER TABLE Persons

ADD PRIMARY KEY (P_Id);

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons

DROP PRIMARY KEY;

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table: The FOREIGN KEY constraint is used to prevent actions that would
destroy links between tables. The FOREIGN KEY constraint also prevents that invalid data from
being inserted into the foreign key column, because it has to be one of the values contained in the
table it points to.
Table 1.1Person’s Table
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Table 1.2 Orders table

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1

12
 Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the
"Persons" table. The "P_Id" column in the "Persons" table is the PRIMARY KEY in the
"Persons" table. The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders"
table is created:

CREATE TABLE Orders

(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is
already created, use the following SQL:

ALTER TABLE Orders

ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id);

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders

DROP FOREIGN KEY fk_PerOrders;

SQL CHECK Constraint

13
 The CHECK constraint is used to limit the value range that can be placed in a column. If
you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on
values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the
"Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only
include integers greater than 0.

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(15) NOT NULL,
FirstName varchar(15),
Address varchar(25),
City varchar(15),
CHECK (P_Id>0)
);

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created,
use the following SQL:

ALTER TABLE Persons

ADD CHECK (P_Id>0);

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons

DROP CONSTRAINT chk_Person;

SQL DEFAULT Constraint

14
 The DEFAULT constraint is used to insert a default value into a column. The default
value will be added to all new records, if no other value is specified.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the
"Persons" table is created:

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(15) NOT NULL,
FirstName varchar(15),
Address varchar(25),
City varchar(15) DEFAULT 'Sandnes'
);

The DEFAULT constraint can also be used to insert system values, by using functions
like GETDATE():

CREATE TABLE Orders

(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
);

SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created,
use the following SQL:

15
 ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES';

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE Persons

ALTER City DROP DEFAULT;

3.0 PROGRAM:
CREATE AND DESCRIBE THE FOLLOWING TABLES:
1. NAME: branch
FIELDS DATATYPE
branch_name varchar2(10) primary key
branch_city varchar2(10)
assets number(8,2)

2. NAME: account

16
FIELDS DATATYPE
account_no varchar2(11) primary key
branch_name varchar2(10) references branch(branch_name)
balance number(8)

3. NAME: customer
FIELD DATATYPE
customer_id varchar2(11) primary key
customer_name varchar2(10)
customer_street varchar2(15)
customer_city varchar2(15)

17
4. NAME: depositor
FIELD DATATYPE
customer_id varchar2(11)references customer (customer_id)
account_no varchar2(11)references account (account_no)

5. NAME: loan
FIELDS DATATYPE
loan_no varchar2(4) primary key
branch_name varchar2(10) references branch(branch_name)
amount number(8,2)

18
6. NAME: borrower
FIELDS DATATYPE
customer_id varchar2(11) references customer(customer_id)
loan_no varcahr2(4) references loan(loan_no)

7.NAME:employee
FIELDS DATATYPE
ename varchar(10)
eid number(5) primary key
salary number(5) check (salary>5000)

19
4.0 RESULT:

REVIEW QUESTIONS

1. Define database management system.

2. What is the difference between drop, delete and truncate commands?

3. What is a constraint and what are the types of constraints?

4. Define null values.

20
 5. What is primary key and foreign key?

EX.NO. 2 DML COMMANDS

DATE:

1.0 AIM:

To execute and verify the DML commands.

2.0 PROCEDURE:

STEP 1: Create the table with its essential attributes.

STEP 2: Insert attribute values into the table

STEP 3: Execute different Commands and extract information from the table.
21
COMMANDS:

1. Insert: This statement is used to insert a row of data in a table. If the data type of the
values is varchar2, char and date enclose it by using single quote string. The two
ways of expressing insert statement are:

INSERT INTO table_name

VALUES (&value1, &value2, &value3...);

INSERT INTO table_name (column1, column2, column3...)

VALUES (&value1, &value2, &value3...);

2. Select: This statement is used to select data from a SQL database table. This is
usually the very first SQL command every SQL newbie learns and this is because the
SELECT SQL statement is one of the most used SQL commands. The two ways of
expressing select statement are:

SELECT Column1, Column2, Column3…

FROM Table_name;
If you want to select all the columns in the table use the following syntax.
SELECT * FROM Table_name;
3. Update: The SQL UPDATE clause changes the data in already existing database
row(s) and usually we need to add a conditional clause to our SQL UPDATE
statement in order to specify which row(s) we intend to update.

UPDATE Table_name
SET Column1 = Value1, Column2 = Value2
WHERE Some_Column = Some_Value;

4. Delete: This statement allows you to delete a single record or multiple records from a
table.

22
 DELETE FROM table_name
WHERE some_column=some_value;

If you want to delete all rows or records in a table use the following syntax

DELETE * FROM table_name;

3.0 PROGRAM:
Use the following training data(corpus)

Table Name : BOOK

ISBN Title Pub_Year Unit_Pric Author_ Name Pub_Name
e
1001 DBMS 2004 200 Abraham Tata
1002 DAA 2002 300 James Technical
1003 TOC 2003 250 Albert Tata
1004 OS 2001 350 Slibert Springer
1005 DSP 1996 199 Albert Technical
1006 SE 2004 250 Casu Springer

Table Name : Author

Author_Name Country
Abraham India
James Mexico
Albert Frances
Slibert Germany

Table Name : Author

Pub_Name Pub_Add
Tata New Street, Mumbai
Technical Mount Road,Chennai
Springer New Town, U.S

23
1. Create a table BOOK with the fields ISBN,TITLE, PUB_YEAR, UNIT_PRICE,
author_name, and PUB_NAME.(underlined fields should be primary key)

2. Create a table AUTHOR with author_name and country.

3. Create a publisher with pub_name and pub_add.

4. Insert the values in to all the tables and display the tables.

5. Find the titles of the book published in the year 2004.

24
6. Find the titles of the book having price between 300 and 400.

7. Find the titles of the books with author name and country published in year 2004.

8. Find the titles of books with author name and author country.

9. Find the names of authors from authors table where the first two characters of the name are
‘Ba’;

10. Find the author name from author where the second character of name is ‘l’ or ‘a’;

25
11. Display the names of all publishers whose address includes the substring ‘ring’;

12. Display all titles of books with price in ascending order of titles.

13. Display all titles of books with price and year in descending order of year.

14. Find the average price of all books.

26
15. Find the book with least price.

16. Find the book with maximum price.

17. Find the total price of all books.

18. Find the total number of books.

27
19. Delete all books from books relation where publishing year is less than 1997.

20. Delete all books having price less than avg price of books.

21. Increase the price of all books by 15%.

22. Increase the price of the book by 15% whose price is less than 200.

28
4.0 RESULT

REVIEW QUESTIONS

1. What are the difference between DDL, DML and DCL commands?

2. What are the five built in aggregate functions offered by SQL?

3. What are the three clauses of SQL expression?

4. List the string operations supported by SQL.

5. What is the use of rename operation?

29
EX.NO.3 SQL RELATIONSHIP - NESTED QUERIES & JOIN QUERIES

DATE:

1.0 AIM:

To execute and verify the SQL commands using nested queries and Join queries.

2.0 PROCEDURE:

STEP 1: Create the table with its essential attributes.

STEP 2: Insert attribute values into the table

STEP 3: Execute different Commands and extract information from the table.

COMMANDS:

Nested Query: The nested SQL query is a form of the SELECT query that is inside
another SQL query. The nested SQL query is also called a sub query. The outside SQL statement

30
is called the parent statement and the inside SQL statement is the nested or sub query. The nested
query obtains a result set and the SELECT statement (parent statement) uses this results set for
additional processing

You can use the sub query for the following purposes:
- defining a set of row that need to be inserted into a targeted table.
- defining a result set that will be used to create a view or snapshot.
- defining one or more values for an update statement.
- providing values for where, having and start with clauses for select,
update and delete statements.

There are three broad divisions of sub query:

1. Single-row sub queries

2. Multiple-row sub queries
3. Correlated sub queries

Single row and multiple row sub queries:

1) Usually, a sub query should return only one record, but sometimes it can also return
multiple records when used with operators like IN, NOT IN in the where clause. The
query would be like,
SELECT first_name, last_name, subject
FROM student_details
WHERE games NOT IN ('Cricket', 'Football');

2) Lets consider the student_details table which we have used earlier. If you know the
name of the students who are studying science subject, you can get their id's by using this
query below,
SELECT id, first_name
FROM student_details
WHERE first_name IN ('Rahul', 'Stephen');

31
 But, if you do not know their names, then to get their id's you need to write the query in
this manner,
SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');
In the above sql statement, first the inner query is processed first and then the outer query
is processed.

3) Subquery can be used with INSERT statement to add rows of data from one or more
tables to another table. Let’s try to group all the students who study Math’s in a table
'maths_group'.
INSERT INTO maths_group(id, name)
SELECT id, first_name || ' ' || last_name
FROM student_details WHERE subject= 'Maths' ;

4) A subquery can be used in the SELECT statement as follows. Let’s use the product
and order_items table defined in the sql_joins section.
Select p.product_name, p.supplier_name, (select order_id from order_items
where product_id = 101) as order_id from product p where p.product_id=101;

Correlated Sub query:

A query is called correlated sub query when both the inner query and the outer query are
interdependent. For every row processed by the inner query, the outer query is processed as well.
The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p

WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

32
JOINS:

SQL joins are used to query data from two or more tables, based on a relationship
between certain columns in these tables. The JOIN keyword is used in an SQL statement to
query data from two or more tables, based on a relationship between certain columns in these
tables. Tables in a database are often related to each other with keys. Tables in a database are
often related to each other with keys. A primary key is a column (or a combination of columns)
with a unique value for each row. Each primary key value must be unique within the table. The
purpose is to bind data together, across tables, without repeating all of the data in every table.

Different SQL JOINs

Inner Join: The inner join keyword return rows when there is at least one match in both
tables. Inner join is the same as join.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Left Join: The left join keyword returns all rows from the left table (table_name1), even
if there are no matches in the right table (table_name2). In some databases left join is called left
outer join

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Right Join: The right join keyword Return all rows from the right table (table_name2),
even if there are no matches in the left table (table_name1). In some databases right join is called
right outer join

33
 SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Full Join: The full join keyword return rows when there is a match in one of the tables.
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

3.0 EXERCISE:
Nested Quries
1. Display the title, author and publisher name of all books published in the year 2000, 2002
and 2004.

34
2. Get the details of author who have published book in year 2004.

3. Display the title, author and publisher name of all books except those which are published in
the year 2000, 2002 and 2004.

35
4. Get the titles of all books written by authors not living in India.

5. Display the titles of books that have price greater than at least one book published in year
2004.

6. Display the titles of books that have price greater than all the books published in year 2004

36
7. Display the name of author who have received highest royalty amount.

8. Display total price of all books (publisher wise).

37
9. “On every book author receives 15% royalty. Display total royalty amount received by each
author till date.”

10. Display publisher wise total price of books published , except for publisher ‘PHI’

11. Display royalty amount received by those authors whose second character of name contains
‘a’.

38
Join Queries
12. Perform inner join operation on book and author table containing all attributes in both the
tables.

13. Perform inner join operation on selected attributes of book and author tables.

39
14. Perform left outer join operation on selected attributes of book and publisher.

15. Perform right outer join operation on selected attributes of book and publisher.

16. Perform full join operation on book and publisher tables.

40
4.0 RESULT:

REVIEW QUESTIONS

1. What is a join and why do we need joins in database?

2. What is the use of sub queries?

3. What is the use of group by clause?

41
 4. What is the difference between inner join and outer join?

EX NO 4 CREATION OF VIEWS, SYNONYMS and SEQUENCE

DATE:

1.0 AIM:

To execute and verify the SQL commands for Views.

2.0 PROCEDURE:

STEP 1: Create the table with its essential attributes.

STEP 2: Insert attribute values into the table.

STEP 3: Create the view from the above created table.

42
 STEP 4: Execute different Commands and extract information from the View.

COMMANDS

A SQL View is a virtual table, which is based on SQL SELECT query. Essentially
a view is very close to a real database table (it has columns and rows just like a regular table),
except for the fact that the real tables store data, while the views don’t. The view’s data is
generated dynamically when the view is referenced. A view references one or more existing
database tables or other views. In effect every view is a filter of the table data referenced in it and
this filter can restrict both the columns and the rows of the referenced tables.

Create View: This command is used to create a view.

CREATE VIEW view_name AS

SELECT column_name(s)
FROM table_name WHERE condition;

Update View: This command is used to update a view.

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name WHERE condition;

Drop View: This command is used to drop the view table

DROP VIEW view_name;

SEQUENCE :

43
 Specify the name of the sequence to be created.

If you specify none of the following clauses, then you create an ascending sequence that starts
with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a
descending sequence that starts with -1 and decreases with no lower limit.

 To create a sequence that increments without bound, for ascending sequences, omit
the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences,
omit the MINVALUE parameter or specify theNOMINVALUE.
 To create a sequence that stops at a predefined limit, for an ascending sequence, specify a
value for the MAXVALUE parameter. For a descending sequence, specify a value for
the MINVALUE parameter. Also specifyNOCYCLE. Any attempt to generate a
sequence number once the sequence has reached its limit results in an error.
 To create a sequence that restarts after reaching a predefined limit, specify values for
both the MAXVALUE and MINVALUE parameters. Also specify CYCLE. If you do not
specify MINVALUE, then it defaults toNOMINVALUE, which is the value 1.

For Example

CREATE SEQUENCE customers_seq

START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

44
Example

Creating Savepoints: Example To update the salary for Banda and Greene in the sample

table employees, check that the total department salary does not exceed 314,000, then reenter the
salary for Greene:

UPDATE employees
SET salary = 7000
WHERE last_name = 'Banda';
SAVEPOINT banda_sal;

UPDATE employees
SET salary = 12000
WHERE last_name = 'Greene';
SAVEPOINT greene_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT banda_sal;

UPDATE employees
SET salary = 11000
WHERE last_name = 'Greene';

COMMIT;

SYNONYM

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table,

view, sequence, procedure, stored function, package, materialized view, Java class schema
object, user-defined object type, or another synonym.

Synonyms provide both data independence and location transparency. Synonyms permit
applications to function without modification regardless of which user owns the table or view
and regardless of which database holds the table or view. However, synonyms are not a
substitute for privileges on database objects. Appropriate privileges must be granted to a user
before the user can use the synonym.

To create a private synonym in your own schema, you must have the CREATE SYNONYM system
privilege.

To create a private synonym in another user's schema, you must have

the CREATE ANY SYNONYM system privilege.

45
 To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

 Examples To define the synonym offices for the table locations in the schema hr, issue the following
statement:
CREATE SYNONYM offices From hr. locations;
Table Name hr
Field Name Location
Synonym name offices

3.0 EXERCISE:

1.Create a view from book table with selected columns.

2.Create a view from two tables from BOOK and AUTHOR with selected columns.

46
3.Check all DML commands with View

(a)Perform insert operation on book view

(b)Select all attributes from book view where pub_name is Tata

(c)Update the book view by increasing the price of book by 12% for publisher Tata

(d)Delete all books from book view where pub_year is less than 1997.

4.Drop views which you generated.

5. Create a INDEX for book table

47
 6.Create a SEQUENCE for the employee table

7.Create a Synonym for the table book by selecting any one field.

8.Check the Save Point, Commit, Rollback during all DML operation.

4.0 RESULT:

REVIEW QUESTIONS:

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

48
 2. What is the difference between a normal table and view table?

3. What is Index?

4. What is the use savepoint, Rollback and commit?

PL/SQL :
PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding
constructs found in procedural languages,
Genera Structure :

49
DECLARE
//Declarative section: variables, types, and local subprograms
BEGIN
//Executable section: procedural and SQL statements go here.
//This is the only section of the block that is required.
EXCEPTION
//Exception handling section: error handling statements go here.
END;
In PL/SQL code groups of commands are arranged within a block. A block groups
related declarations or statements. A block can be contained within another block or in a larger
programming unit called a subprogram. A subprogram can be a stored procedure, function,
trigger or package.
A stored procedure or simply procedure is essentially an encapsulated named block of code
stored within the database.
A function is distinguished from a procedure in that it is able to return a value. Both
functions and procedures can take parameters and are called interactively.
A procedure cannot be called directly from SQL. A function (in most cases) can be called
from a SQL statement.
A trigger cannot take parameters and is called automatically when a database event occurs.
A  trigger - like a stored procedure - is a subprogram that does not return a value.
Examples of triggering events include the execution of DML statements (an insert, update or
delete) or DDL statements (create, alter, drop).
A Package serves as a container for functions, procedures and PL/SQL types.
Advantage of Using PL/SQL
1. Better performance, as sql is executed in bulk rather than a single statement
2. High Productivity
3. Tight integration with SQL
4. Full Portability
5. Tight Security
6. Support Object Oriented Programming concepts.
PL/SQL Iterative Control :
The LOOP statements allow you to execute one or more sequence of statements
multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, FOR-
LOOP.
LOOP Statement :
LOOP
-- sequence of statements

50
 END LOOP;
EXIT Statement :
LOOP
v_count := 10;
IF v_count = 10 THEN
EXIT;
END IF;
END LOOP;
EXIT-WHEN Statement :
LOOP
v_count := v_count + 10;
EXIT WHEN v_count = 11;
END LOOP;
FOR-LOOP Statement :
FOR counter IN [REVERSE] lower_number.. higher_number LOOP
-- sequence of statements
END LOOP;
WHILE-LOOP Statement :
WHILE condition LOOP
-- sequence of statements
END LOOP;
Creating Subprograms :
create procedure test1 is
begin
dbms_output.put_line('Hello from a stored procedure');
end;
/
To Run :

$ exec test1

PL/SQL PROCEDURE :
The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs
one or more specific tasks.
The procedure contains a header and a body.
o Header: The header contains the name of the procedure and the parameters or variables
passed to the procedure.

51
 o Body: The body contains a declaration section, execution section and exception section
similar to a general PL/SQL block.
How to pass parameters in procedure:
There is three ways to pass parameters in procedure:
1. IN parameters: The IN parameter can be referenced by the procedure or function. The
value of the parameter cannot be overwritten by the procedure or the function.
2. OUT parameters: The OUT parameter cannot be referenced by the procedure or
function, but the value of the parameter can be overwritten by the procedure or function.
3. INOUT parameters: The INOUT parameter can be referenced by the procedure or
function and the value of the parameter can be overwritten by the procedure or function.
Syntax for creating procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name  
    [ (parameter [,parameter]) ]  
IS  
     [declaration_section]  
BEGIN  
     executable_section  
[EXCEPTION  
     exception_section]  
END [procedure_name];  
Table creation:
SQL > create table user(id number(10) primary key,name varchar2(100));  
Now write the procedure code to insert record in user table.
Procedure Code:
create or replace procedure "INSERTUSER"  (id IN NUMBER, name IN VARCHAR2)  i
s    begin    
insert into user1 values(id,name);    
end;    
/       
Sample Output:
Procedure created.
SQL > EXEC procedure_name;
SQL > EXEC insertuser(106,'Ram');  
PL/SQL program to call procedure
Let's see the code to call above created procedure.
BEGIN    

52
     insertuser(106,'Ram');  
    dbms_output.put_line('record inserted successfully');    
END;    
/    
PL/SQL FUNCTION :
The PL/SQL Function is very similar to PL/SQL Procedure. The main difference
between procedure and a function is, a function must always return a value, and on the other
hand a procedure may or may not return a value.
Syntax to create a function:
CREATE [OR REPLACE] FUNCTION function_name [parameters]  
[(parameter_name [IN | OUT | IN OUT] type [, ...])]  
RETURN return_datatype  
{IS | AS}  
BEGIN  
   < function_body >  
END [function_name];  
Ex. :
 Create a function :
create or replace function adder(n1 in number, n2 in number)   
return number   
is     
n3 number(8);   
begin    
n3 :=n1+n2;    
return n3;    
end;    
To call the function :
DECLARE    
   n3 number(2);    
BEGIN    
    n3 := adder(11,22);    
    dbms_output.put_line('Addition is: ' || n3);    
END;    
/    
Output:
Addition is: 33

53
 Statement processed.
0.05 seconds

PL/SQL Implicit Cursor :

A PL/SQL cursor is a pointer that points to the result set of an SQL query against
database tables.

Attribute Description
%FOUND Its return value is TRUE if DML statements like INSERT, DELETE
and UPDATE affect at least one row or more rows or a SELECT
INTO statement returned one or more rows. Otherwise it returns
FALSE.
%NOTFOUND Its return value is TRUE if DML statements like INSERT, DELETE
and UPDATE affect no row, or a SELECT INTO statement return
no rows. Otherwise it returns FALSE. It is a just opposite of
%FOUND.
%ISOPEN It always returns FALSE for implicit cursors, because the SQL
cursor is automatically closed after executing its associated SQL
statements.
%ROWCOUN It returns the number of rows affected by DML statements like
T INSERT, DELETE, and UPDATE or returned by a SELECT INTO
statement
Create customers table and have records:
ID NAME AGE ADDRESS SALARY
1 Ramesh 23 Allahabad 20000
2 Suresh 22 Kanpur 22000
3 Mahesh 24 Ghaziabad 24000
4 Chandan 25 Noida 26000
5 Alex 21 Paris 28000
6 Sunita 20 Delhi 30000

54
Let's execute the following program to update the table and increase salary of each customer by
5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows affected:
Create Cursor procedure:
DECLARE   
    total_rows number(2);  
BEGIN  
    UPDATE  emp   SET sal = sal + 5000;  
    IF sql%notfound THEN  
       dbms_output.put_line('no customers updated');  
    ELSIF sql%found THEN  
       total_rows := sql%rowcount;  
       dbms_output.put_line( total_rows || ' customers updated ');  
    END IF;   
END;  
/  

Sample Output:
6 customers updated
PL/SQL procedure successfully completed.
PL/SQL Explicit Cursors:
Steps:
You must follow these steps while working with an explicit cursor.
1. Declare the cursor to initialize in the memory.
2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.
Create Cursor procedure:
Execute the following program to retrieve the customer name and address.
DECLARE  
   c_id customers.id%type;  
   c_name customers.name%type;  
   c_addr customers.address%type;  
   CURSOR c_customers is   SELECT id, name, address FROM customers;  
BEGIN  
   OPEN c_customers;  
   LOOP  
      FETCH c_customers into c_id, c_name, c_addr;  
      EXIT WHEN c_customers%notfound;  
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);  
   END LOOP;  

55
    CLOSE c_customers;  
END;  
/  
Sample Output:
1 Ramesh Allahabad
2 Suresh Kanpur
3 Mahesh Ghaziabad
4 Chandan Noida
5 Alex Paris
6 Sunita Delhi
PL/SQL procedure successfully completed.
PL/SQL TRIGGER :
Trigger is invoked by Oracle engine automatically whenever a specified event
occurs.Trigger is stored into database and invoked repeatedly, when specific condition match.
Advantages of Triggers
These are the following advantages of Triggers:
o Trigger generates some derived column values automatically
o Enforces referential integrity
o Event logging and storing information on table access
o Auditing
o Synchronous replication of tables
o Imposing security authorizations
o Preventing invalid transactions
General Syntax :
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
Ex. : Create and display trigger for changing the salary on customer table
CREATE OR REPLACE TRIGGER display_salary_changes  

56
 BEFORE DELETE OR INSERT OR UPDATE ON customers  
FOR EACH ROW  
WHEN (NEW.ID > 0)  
DECLARE  
   sal_diff number;  
BEGIN  
   sal_diff := :NEW.salary  - :OLD.salary;  
   dbms_output.put_line('Old salary: ' || :OLD.salary);  
   dbms_output.put_line('New salary: ' || :NEW.salary);  
   dbms_output.put_line('Salary difference: ' || sal_diff);  
END;  
/  
Exception handling :

Predefined Exception :
Oracle
Oracle Exception Name Explanation
Error
ORA- You tried to execute an INSERT or UPDATE
DUP_VAL_ON_INDEX 00001 statement that has created a duplicate value in a
field restricted by a unique index.
ORA- You were waiting for a resource and you timed
TIMEOUT_ON_RESOURCE
00051 out.
ORA- You tried to execute a call to Oracle before
NOT_LOGGED_ON
01012 logging in.
ORA- You tried to log into Oracle with an invalid
LOGIN_DENIED
01017 username/password combination.
NO_DATA_FOUND ORA- You tried one of the following:
01403 1. You executed a SELECT INTO statement
and no rows were returned.
2. You referenced an uninitialized row in a

57
 table.
3. You read past the end of file with the
UTL_FILE package.
ORA- You tried to execute a SELECT INTO statement
TOO_MANY_ROWS
01422 and more than one row was returned.
ORA- You tried to divide a number by zero.
ZERO_DIVIDE
01476
ORA- You tried to execute a SQL statement that tried to
INVALID_NUMBER 01722 convert a string to a number, but it was
unsuccessful.
CURSOR_ALREADY_OPE ORA- You tried to open a cursor that is already open.
N 06511
Ex. : PL/SQL Program using Exception handling
CREATE OR REPLACE PROCEDURE add_new_order (order_id_in IN NUMBER, sales_in
IN NUMBER)
IS
no_sales EXCEPTION; // user defined exception
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales ) VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the
order.');
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');
END;
/

Ex. No. 5(a) DATABASE PROGRAMMING: IMPLICIT AND EXPLICIT CURSOR

DATE:
1.0 AIM:

58
 To implement the database programming for implicit and explicit cursor using PL/SQL
statements for an application.

2.0 PROCEDURE

Cursor Attributes :
Cursor
Description
Attribute
It returns the Boolean result 'TRUE' if the most recent fetch operation
%FOUND
fetched a record successfully, else it will return FALSE.
This works oppositely to %FOUND it will return 'TRUE' if the most recent
%NOTFOUND
fetch operation could not able to fetch any record.
It returns Boolean result 'TRUE' if the given cursor is already opened, else
%ISOPEN
it returns 'FALSE'
It returns the numerical value. It gives the actual count of records that got
%ROWCOUNT
affected by the DML activity.

3.0 PROGRAM
1. Write a Pl/SQL program to display the number of rows processed by this implicit
cursor based on the given empno from the emp table.

Output :

2. Write a PL/SQL program to display the total employee salary is updated in the
‘employee’ table for the given amount using implicit cursor.

59
Output :

60
3. Write a PL/SQL program to implement the explicit cursor to display all the employee
name from the mp table and count the number or records are in emp.

Output :

5.0 RESULT:

REVIEW QUESTIONS
1.What do you understand by PL/SQL cursor?

61
2. Explain Cursor types

3. Explain the difference between cursor declared in procedures and cursors declared in the package
specification?

4. What are PL/SQL Cursor exception?

EX.NO.5(b) PL/SQL - PROCEDURES

DATE:

62
1.0 AIM:

To implement and execute procedures in Oracle database using Procedural Language

concepts.

2.0 PROCEDURE:

STEP 1: Initialize the necessary parameters.

STEP 2: Develop the set of statements with the essential operational parameters.

STEP 3: Specify the Individual operation to be carried out.

STEP 4: Execute the procedure.

PROCEDURES:

Procedure is a subprogram that contains set of SQL and PL/SQL statements. A stored
procedure or in simple a proc is a named PL/SQL block which performs one or more specific
task. This is similar to a procedure in other programming languages. A procedure has a header
and a body. The header consists of the name of the procedure and the parameters or variables
passed to the procedure. The body consists or declaration section, execution section and
exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous
PL/SQL Block but it is named for repeated usage

Merits:
Reusability - Subprograms once executed can be used in any number of applications
Maintainability - Subprogram can simplify maintenance, subprogram will be affected only its
definition changes

Parameters:
1. IN - The IN parameter is used to pass values to a subprogram when it is invoked
2. OUT - The OUT parameter is used to return values to the caller of a subprogram
3. IN OUT-The IN OUT parameter is used to pass initial values to the subprogram when invoked
and also it returns updated values to the caller.
Syntax:

63
 CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
Declaration section
BEGIN
   procedure_body
EXCEPTION
Exception section

END procedure_name;
Where
1. OR REPLACE specifies the procedure is to replace an existing procedure present
2. You can use this option when you want to modify a procedure.
3. A procedure may be passed multiple parameters.
4. IN | OUT | IN OUT specifies the mode of the parameter.
5. Type specifies the type of the parameter.
6. Procedure body contains the SQL and PL/SQL statements to perform the procedure's task.

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

EXAMPLE:

SQL> set serveroutput on
SQL> CREATE or replace PROCEDURE my_first_proc IS
greetings VARCHAR2(20);
  3  BEGIN
  4          greetings := 'Hello World';
  5          dbms_output.put_line(greetings);
  6  END my_first_proc;
  7  /
SQL>
SQL> EXECUTE my_first_proc;

64
Hello World
PL/SQL procedure successfully completed.

When the procedure exists in the database, you can easily call the routine and get the same result
as before, as shown here:
SQL>
SQL> BEGIN
  2          my_first_proc;
  3  END;
  4  /
Hello World
PL/SQL procedure successfully completed.

In Parameter:
CREATE OR REPLACE
PROCEDURE DISP_AB (A INT, B INT) IS
BEGIN
DBMS_OUTPUT.PUT_LINE (’A + B = ’ || (A + B));
DBMS_OUTPUT.PUT_LINE (’A * B = ’ || (A * B));
END;
Which when ran, displays something like (depending on the values you provide)
SQL> CALL DISP_AB (17,23);
A + B = 40
A * B = 391

Out Parameter:
CREATE OR REPLACE
PROCEDURE SUM_AB (A INT, B INT, C OUT INT) IS
BEGIN
C := A + B;
END;
To run it, we also create a small code fragment:

65
 DECLARE
R INT;
BEGIN
SUM_AB(23,29,R);
DBMS_OUTPUT.PUT_LINE(’SUM IS: ’ || R);
END;
Which when ran, displays:
SUM IS: 52
In Out Parameter:
CREATE OR REPLACE
PROCEDURE DOUBLEN (N IN OUT INT) IS
BEGIN
N := N * 2;
END;
To run it, we also create a small code fragment:
DECLARE
R INT;
BEGIN
DBMS_OUTPUT.PUT_LINE(’BEFORE CALL R IS: ’ || R);
DOUBLEN(R);
DBMS_OUTPUT.PUT_LINE(’AFTER CALL R IS: ’ || R);
END;
Which when ran, displays:
BEFORE CALL R IS: 7
AFTER CALL R IS: 14
3.0 EXERCISE:
1. Write a procedure that takes emp no and amount as arguments and update the
salary of an employee by the given amount.

66
2. Write a procedure that accepts the empno and return his total earning per year
to the calling environment.

67
4.0 RESULT:

REVIEW QUESTIONS:

1. How to debug the procedure?

2. How we can create a table through procedure?

68
 3. What is a stored procedure?

4. What are the modes of parameters that can be passed to a procedure?

5. What are the two parts of a procedure?

EX.NO. 5(c) PL/SQL FUNCTIONS

DATE:

69
1.0 AIM:

To implement and execute functions in Oracle database.

2.0 PROCEDURE:

STEP 1: Initialize the necessary parameters.

STEP 2: Develop the set of statements with the essential operational parameters.

STEP 3: Specify the Individual operation to be carried out.

STEP 4: Execute the function.

FUNCTIUONS:

Function is a subprogram that computes and returns a single value Functions and
procedures are structured alike, except that functions have a RETURN clause. A function has
two parts: the specification and the body. The function specification begins with the keyword
FUNCTION and ends with the RETURN clause, which specifies the data type of the result
value. Parameter declarations are optional. Functions that take no parameters are written without
parentheses. The function body begins with the keyword IS and ends with the keyword END
followed by an optional function name.

Comparing Procedures and Functions

PROCEDURE FUNCTION
Execute as a PL/SQL statement Invoke as part of an expression

No RETURN data type Must contain a RETURN data type

Can return none, one or many values Must return a single value

Can not use in SQL Statement Can Use in SQL Statements

Benefits of Stored Procedures and Functions

In addition to modularizing application development, stored procedures and functions have the
following benefits:

70
  Improved performance
• Avoid reparsing for multiple users by exploiting the shared SQL area
• Avoid PL/SQL parsing at run-time by parsing at compile time
• Reduce the number of calls to the database and decrease network traffic by
bundling commands
 Improved maintenance.
• Modify routines online without interfering with other users
• Modify one routine to affect multiple applications
• Modify one routine to eliminate duplicate testing
 Improved data security and integrity

• Control indirect access to database objects from non privileged users with security
Privileges.
•Ensure that related actions are performed together, or not at all, by funneling activity
for related tables through a single path.
Syntax:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type
{IS | AS}
BEGIN
  function_body
END function_name;
Where

1. OR REPLACE specifies the function that is to replace an existing function if present.

2. Type specifies the PL/SQL type of the parameter.
3. The body of a function must return a value of the PL/SQL type specified in the RETURN
clause

EXAMPLE:

1> CREATE OR REPLACE FUNCTION employer_details_func

2> RETURN VARCHAR(20);
3> IS
4> emp_name VARCHAR(20);
5> BEGIN
7> SELECT first_name INTO emp_name

71
 8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10> END;
11> /

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable
‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no
9.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name := employer_details_func;

If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning

the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.

3.0 EXERCISE:

72
1. Write a function which accepts phone no: as parameter and returns the address.

4.0 RESULT:

73
REVIEW QUESTIONS:

1. What is the difference between a procedure and a function?

2. Explain how procedures and functions are called in a PL/SQL block?

3. Give the structure of the function.

4. What are the advantages of stored procedures and functions?

5. What are the ways to execute a PL/SQL function?

74
EX NO 6: WRITE A PL/SQL BLOCK THAT HANDLES ALL TYPES OF EXCEPTIONS
DATE:

1.0 AIM:

To write a PL/SQL block that handles all types of exceptions using Exception Class in
PL/SQL Statements .

2.0 PROCEDURE:

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL

Block known as exception Handling.
Structure of Exception Handling :

DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;

3.0 PROGRAM
1. Raise the exception when no data found in given record.

75
Sample Output :

4. Raise the user defined exception when loop was successfully executed

76
Sample Output :

5. Raise the exception when input number is invalid in the given range .

77
Sample Output :

4.0 RESULT:
The PL/SQL block that handles all types of exceptions using Exception Class in PL/SQL
Statements was successfully written , executed , and verified .

Review questions:
1. What is exception handling in DBMS?

2. Mention some predefined exceptions

78
3. When is the CURSOR_ALREADY_OPEN exception raised?

4. What is the difference between RAISE and RAISE_APPLICATION_ERROR?

5. What is the advantage of having a separate exception section within a PL/SQL

code?

79
EX NO. 7 CREATION OF DATABASE TRIGGERS

DATE:

1.0 AIM:
To implement the creation of database triggers using Pl/SQL statements
2.0 PROCEDURE:
PL/SQL TRIGGER :
Trigger is invoked by Oracle engine automatically whenever a specified event occurs.
Trigger is stored into database and invoked repeatedly, when specific condition match.
Advantages of Triggers
These are the following advantages of Triggers:
o Trigger generates some derived column values automatically
o Enforces referential integrity
o Event logging and storing information on table access
o Auditing
o Synchronous replication of tables
o Imposing security authorizations
o Preventing invalid transactions
Trigger Classification :
Triggers can be classified based on the following parameters.
 Classification based on the timing
o BEFORE Trigger: It fires before the specified event has occurred.
o AFTER Trigger: It fires after the specified event has occurred.
o INSTEAD OF Trigger: A special type. You will learn more in the further topics.
(only for DML )
 Classification based on the level
o STATEMENT level Trigger: It fires one time for the specified event statement.
o ROW level Trigger: It fires for each record that got affected in the specified
event. (only for DML)
 Classification based on the Event
o DML Trigger: It fires when the DML event is specified
(INSERT/UPDATE/DELETE)
o DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
o DATABASE Trigger: It fires when the database event is specified
(LOGON/LOGOFF/STARTUP/SHUTDOWN)
:NEW and :OLD Clause :
 :NEW – It holds new value of the columns of the base table/view during the trigger

80
 execution
 :OLD – It holds old value of the columns of the base table/view during the trigger
execution

General Syntax :
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

3.0 PROGRAM:
1. Create a Trigger message before update in emp .

Sample Output :

81
2. Create a Trigger to shows a before row-level trigger that calculates the commission of
every new employee belonging to department 30 before a record for that employee is
inserted into the EMP table.

Sample Output :

82
3. Create a Trigger message after delete in emp table .

Sample Output :

4. Create and display trigger for changing the salary on customer table.

83
Sample Output :

5. Restriction to Deleting Trigger .

84
Sample Input and Output :

4.0 RESULT:
The implementations of creating database triggers using Pl/SQL statements was
successfully written, executed and verified.

85
Review Questions
1. Define trigger?

2. Which are the different types of triggers?

3. What is the use of InsteadOf and after trigger?

4. Is it possible to create triggers on views?

5. How many triggers are possible per table?

86
EX NO. 8 DATABASE DESIGN USING ER MODELING, NORMALIZATION AND
IMPLEMENTATION FOR ANY APPLICATION

DATE:

1.0 AIM:
To create the relationship between the databases as tables using SQL command.

2.0 PROCEDURE:
Database Normalizations :
Database Normalizations is a technique of organizing the data in the database.
Normalization is a systematic approach of decomposing tables to eliminate data redundancy and
undesirable characteristics like Insertion, Update and Deletion Anomalies.
Normalization is used for mainly two purpose,
 Eliminating redundant(useless) data.
 Ensuring data dependencies make sense i.e data is logically stored.
Table Name :Student

S_i S_Name S_Addres Subject_opted

d s
401 Adam Noida Bio
402 Alex Panipat Maths
403 Stuart Jammu Maths
404 Adam Noida Physics

Problem Without Normalization :

Insertion, Updation and Deletion Anamolies are very frequent if Database is not
Normalized.
 Updation Anamoly : To update address of a student who occurs twice or more than
twice in a table, we will have to update S_Address column in all the rows, else data will
become inconsistent.
 Insertion Anamoly : Suppose for a new admission, we have a Student id(S_id), name
and address of a student but if student has not opted for any subjects yet then we have to
insert NULL there, leading to Insertion Anamoly.
 Deletion Anamoly : If (S_id) 401 has only one subject and temporarily he drops it, when
we delete that row, entire student record will be deleted along with it.

Normalization Rule
Normalization rule are divided into following normal form.

87
 1. First Normal Form - Tables Must Not Contain Repeating Groups Of Data
2. Second Normal Form - Eliminations of Redundant data
3. Third Normal Form - Eliminate columns Not Dependant on the key .
4. Fourth Normal Form - Isolate Independent Multiple Relations .
5. BCNF
First Normal Form (1NF)
o As per First Normal Form, no two Rows of data must contain repeating group of
information i.e each set of column must have a unique value, such that multiple
columns cannot be used to fetch the same row. 
o It should hold only atomic(Single) values.
Example: Suppose a company wants to store the names and contact details of its employees. It
creates a table that looks like this:
Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
8123450987
Two employees (Jon & Lester) are having two mobile numbers so the company stored
them in the same field as you can see in the table above.
This table is not in 1NF as the rule says “each attribute of a table must have atomic
(single) values”, the emp_mobile values for employees Jon & Lester violates that rule.

To make the table complies with 1NF we should have the data like this:
Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
102 Jon Kanpur 9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
104 Lester Bangalore 8123450987
Second normal form (2NF)
A table is said to be in 2NF if both the following conditions hold:
 Table is in 1NF (First normal form)
 No non-prime attribute is dependent on the proper subset of any candidate key of
table.

88
An attribute that is not part of any candidate key is known as non-prime attribute.
Example: Suppose a school wants to store the data of teachers and the subjects they teach. They
create a table that looks like this: Since a teacher can teach more than one subjects, the table can
have multiple rows for a same teacher.
Teacher_i Subject Teacher_a
d ge
111 Maths 38
111 Physics 38
222 Biology 38
333 Physics 40
333 Chemistr 40
y
Candidate Keys: {teacher_id, subject}
Non prime attribute: teacher_age
The table is in 1 NF because each attribute has atomic values. However, it is not in 2NF
because non prime attribute teacher_age is dependent on teacher_id alone which is a proper
subset of candidate key.
This violates the rule for 2NF as the rule says “no non-prime attribute is dependent on the
proper subset of any candidate key of the table”.
To make the table complies with 2NF we can break it in two tables like this:
teacher_details table:
Teacher_i Teacher_age
d
111 38
222 38
333 40
teacher_subject table:
Teacher_i Subject
d
111 Maths
111 Physics
222 Biology
333 Physics
333 Chemistry
Now the tables comply with Second normal form (2NF).
Third Normal form (3NF)
A table design is said to be in 3NF if both the following conditions hold:

89
  Table must be in 2NF
 Transitive functional dependency of non-prime attribute on any super key should be
removed.
An attribute that is not part of any candidate key is known as non-prime attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each
functional dependency X-> Y at least one of the following conditions hold:
 X is a super key of table
 Y is a prime attribute of table
An attribute that is a part of one of the candidate keys is known as prime attribute.
Example: Suppose a company wants to store the complete address of each employee, they create
a table named employee_details that looks like this:
Emp_i Emp_name Emp_zip Emp_state Emp_city Emp_district
d
1001 John 282005 UP Agra Dayal Bagh
1002 Ajeet 222008 TN Chennai M-City
1006 Lora 282007 TN Chennai Urrapakkam
1101 Lilly 292008 UK Pauri Bhagwan
1201 Steve 222999 MP Gwalior Ratan
  Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so on
Candidate Keys: {emp_id}
Non-prime attributes: all attributes except emp_id are non-prime as they are not part of
any candidate keys.
Here, emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is
dependent on emp_id that makes non-prime attributes (emp_state, emp_city & emp_district)
transitively dependent on super key (emp_id). This violates the rule of 3NF.
To make this table complies with 3NF we have to break the table into two tables to
remove the transitive dependency:
Employee table:
Emp_i Emp_name Emp_zip
d
1001 John 282005
1002 Ajeet 222008
1006 Lora 282007
1101 Lilly 292008
1201 Steve 222999
Employee_zip table:
Emp_zi Emp_state Emp_city Emp_district

90
 p
282005 UP Agra Dayal Bagh
222008 TN Chennai M-City
282007 TN Chennai Urrapakkam
292008 UK Pauri Bhagwan
222999 MP Gwalior Ratan
Boyce Codd normal form (BCNF)
It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter
than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X-
>Y, X should be the super key of the table.
Example: Suppose there is a company wherein employees work in more than one department.
They store the data like this:
Emp_id Emp_nationalit Emp_dept Dept_typ Dept_no_of_emp
y e
1001 Austrian Production and planning D001 200
1001 Austrian stores D001 250
1002 American design and technical support D134 100
1002 American Purchasing department D134 600
Functional dependencies in the table above:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate key: {emp_id, emp_dept}
The table is not in BCNF as neither emp_id nor emp_dept alone are keys.
To make the table comply with BCNF we can break the table in three tables like this:
Emp_Nationality Table:
Emp_i Emp_nationality
d
1001 Austrian
1002 American

emp_dept table:
Emp_dept Dept_type Dept_no_of_emp
Production and planning D001 200
stores D001 250
design and technical support D134 100
Purchasing department D134 600
emp_dept_mapping table:

91
 Emp_i Emp_dept
d
1001 Production and planning
1001 stores
1002 design and technical support
1002 Purchasing department
Functional dependencies:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}
This is now in BCNF as in both the functional dependencies left side part is a key.
E-R Diagram :

92
1.0 EXERCISE
Draw an E R diagram for Online shopping management system

93
4.0 RESULT :
The database design using ERmodeling, normalization and implementation for any
application is successfully executed and verified.
REVIEW QUESTIONS:
1. What is normalization in DBMS?

2. Enlist the advantages of normalizing the database?

3. Define denormalization of database?

94
 4. Enlist the various types of relationships in database

EX. No : 9 MINI Project

Title:

Members:

Choose any one

Mini project (Application Development using Oracle/ Mysql/ Visual Basic )
a) Inventory Control System.
b) Material Requirement Processing.
c) Hospital Management System.
d) Railway Reservation System.
e) Personal Information System.
f) Web Based User Identification System.
g) Timetable Management System.
h) Hotel Management System

95
Review Question

1. What is data provider?

2. List any three methods of the connection objects?

3. How would you obtain the no of records in a record set?

4. How many types of procedure are available in VB?

96
EX. NO. 10 SET OPERATORS
Date:
1.0Aim:
To write PL/SQL programs to implement set operators
2.0 PROCEDURE:
SQL supports few Set operations which can be performed on the table data. These are used to get
meaningful results from data stored in the table, under different special conditions.
In this tutorial, we will cover 4 different types of SET operations, along with example:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

UNION is used to combine the results of two or more SELECT statements. However it

will eliminate duplicate rows from its resultset. In case of union, number of columns and
datatype must be same in both the tables, on which UNION operation is being applied.
SELECT * FROM First
UNION
SELECT * FROM Second;

UNION ALL
This operation is similar to Union. But it also shows the duplicate rows.
SELECT * FROM First

97
UNION ALL
SELECT * FROM Second;

Intersect operation is used to combine two SELECT statements, but it only retuns the

records which are common from both SELECT statements. In case of Intersect the
number of columns and datatype must be same.

SELECT * FROM First

INTERSECT
SELECT * FROM Second;

The Minus operation combines results of two SELECT statements and return only those in the
final result, which belongs to the first set of the result.
SELECT * FROM First
MINUS
SELECT * FROM Second;

3.0 PROGRAM
1. Write the SQL statement that creates one table from all the content in
the BOOKS and MOVIES tables? .

2. Write the SQL statement that returns all data from all tables, no matter if it is a duplicate
or not. 

98
 3. Write the SQL statement that returns only book titles that are not also movie titles

4. Write the SQL statement that returns what is common in both the tables.

5.0 RESULT

99
100