Department of Computer Science & Engineering

Shri. Balasaheb Mane Shikshan Prasarak Mandal’s

ASHOKRAO MANE GROUP OF INSTITUTIONS
Vathar Tarf Vadgaon, Dist. Kolhapur
Affiliated to
Dr. Babasaheb Ambedkar Technological University
Lonere-402103,Tal.Mangaon ,Dist. Raigad(MS)INDIA

Database System Lab

(BTCOL507)
B. Tech – V Semester

Prepared By: Prof.A.A.Mahaldar Verified by: Prof. S. S. Redekar

Subject Teacher HOD CSE

 Department of Computer Science and Engineering

Vision of Department : To be a center of excellence in Computer Science and Engineering through quality
education, research & consultancy

Mission of Department :  To educate students in Computer Science and Engineering through

comprehensive educational programs.
 To inculcate professional, ethical and social responsibilities among the students.
 To enhance consultancy, research and development activities by collaborating
with industries and academic institutions.
 To train students for their placement and higher studies.

Programme Outcomes (POs)

Computer Science and Engineering graduates will be able to

1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering fundamentals, and an
engineering specialization to the solution of complex engineering problems.
2. Problem analysis: Identify, formulate, review research literature, and analyze complex engineering problems
reaching substantiated conclusions using first principles of mathematics, natural sciences, and engineering
sciences.
3. Design/development of solutions: Design solutions for complex engineering problems and design system
components or processes that meet the specified needs with appropriate consideration for the public health and
safety, and the cultural, societal, and environmental considerations.
4. Conduct investigations of complex problems: Use research-based knowledge and research methods including
design of experiments, analysis and interpretation of data, and synthesis of the information to provide valid
conclusions.
5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern engineering and IT
tools including prediction and modeling to complex engineering activities with an understanding of the
limitations.
6. The engineer and society: Apply reasoning informed by the contextual knowledge to assess societal, health,
safety, legal and cultural issues and the consequent responsibilities relevant to the professional engineering
practice.
7. Environment and sustainability: Understand the impact of the professional engineering solutions in societal
and environmental contexts, and demonstrate the knowledge of, and need for sustainable development.
8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and norms of the
engineering practice.
9. Individual and team work: Function effectively as an individual, and as a member or leader in diverse teams,
and in multidisciplinary settings.
10. Communication: Communicate effectively on complex engineering activities with the engineering community
and with society at large, such as, being able to comprehend and write effective reports and design
documentation, make effective presentations, and give and receive clear instructions.
11. Project management and finance: Demonstrate knowledge and understanding of the engineering and
management principles and apply these to one’s own work, as a member and leader in a team, to manage projects
and in multidisciplinary environments.
12. Life-long learning: Recognize the need for, and have the preparation and ability to engage in independent and
life-long learning in the broadest context of technological change.
 Department of Computer Science and Engineering

Program Educational Objective (PEOs):

The Education Objectives of Computer Science and Engineering Programme are:

Within three to four years of graduation, the program expects that Computer Science and Engineering graduates will,
PEO-1: To enhance fundamental understanding of mathematics, science and computer science & engineering
to students.
PEO-2: To provide training & placement to students in IT and allied companies.
PEO-3: To train students to function and communicate effectively, both individually an with multidisciplinary
teams.
PEO-4: To engage students in lifelong learning like postgraduate education, certification courses etc.
PEO-5: To encourage students to participate and become successful in competitive examinations.

Program Specific Outcomes (PSO)

PSO1: Knowledge of recent technology: Demonstrate the knowledge of recent technologies like web
development, mobile computing, grid computing, cloud computing, big data analytics,
Mainframe etc.
PSO2: Knowledge of programming languages: Demonstrate the knowledge of programming
Languages in computer based problem solving.
PSO3: Software development: Demonstrate the ability to analyse, design and implement software products.

Course Outcomes (COs):

Upon completion of this course, students will be able to
CO1 Understand fundamentals of database systems and applications and architecture.
CO2 Apply Relational Data Model, Relational Algebra and Calculus
CO3 Understand SQL, Use of SQL in modification of database through application
programs.
CO4 Apply normalization and indexing, hashing to organizing data.
CO5 Understand the transaction processing and ACID property.
 Department of Computer Science and Engineering

List of Experiment

1. Defining schema for applications.

2. Creating tables, Renaming tables, Data constraints (Primary key, Foreign key, NotNull), Data
insertion into a table.
3. Grouping data, aggregate functions, Oracle functions (mathematical, character Functions).

4. Sub-queries, Set operations, Joins.

5. Creation of databases, writing SQL and PL/SQL queries to retrieve information from the databases.

6. Assignment on Triggers & Cursors.

7. Normal Forms: First, Second, Third and Boyce Codd Normal Forms.

8.Assignment in Design and Implementation of Database systems or packages for application such as
office automation, hotel management, hospital management.

9. Deployment of Forms, Reports Normalization, Query Processing Algorithms in the above

application project.

10. Large objects – CLOB, NCLOB, BLOB and BFILE.

11. Distributed data base Management, creating web-page interfaces for database applications using servlet.
 Department of Computer Science and Engineering

List of Experiment

Exp. No. List of Experiment

Reference

1 Defining schema for applications.

Creating tables, Renaming tables, Data constraints

2 (Primary key, Foreign key, Not Null), Data insertion
into a table.
Grouping data, aggregate functions, Oracle
3
functions (mathematical, character functions).

4 Sub-queries, Set operations, Joins.

Creation of databases, writing SQL and
DBATU Curriculum
5 PL/SQL queries to retrieve information from
the databases.
6 Assignment on Triggers & Cursors.

Normal Forms: First, Second, Third and Boyce

7
Codd Normal Forms.
Assignment in Design and Implementation of
Database systems or packages for applications
8
such as office automation, hotel management,
hospital management.
Deployment of Forms, Reports Normalization,
9 Query Processing Algorithms in the above
application project.
Large objects – CLOB, NCLOB, BLOB and
10
BFILE.
Distributed data base Management, creating web-
11 page interfaces for database applications using
servlet.
 Department of Computer Science and Engineering

EXPERIMENT NO-1
Title:-Defining schema for applications.
Aim:-To study defining schema for different applications.

Theory:-
A database-management system (DBMS) is a collection of interrelated data and a set of programs to access
those data. The collection of data, usually referred to as the database, contains information relevant to an
enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is
both convenient and efficient. Database systems are designed to manage large bodies of information.
Management of data involves both defining structures for storage of information and providing mechanisms
for the manipulation of information. In addition, the database system must ensure the safety of the
information stored, despite system crashes or attempts at unauthorized access. If data are to be shared among
several users, the system must avoid possible anomalous results.

Database-System Applications :-

• Enterprise Information:-
◦ Sales: For customer, product, and purchase information.

◦ Accounting: For payments, receipts, account balances, assets and other accounting information.

◦ Human resources: For information about employees, salaries, payroll taxes, and benefits, and for
generation of paychecks.

◦ Manufacturing: For management of the supply chain and for tracking production of items in factories,
inventories of items in warehouses and stores, and orders for items.
◦ Online retailers: For sales data noted above plus online order tracking, generation of recommendation lists,
and maintenance of online product evaluations.

• Universities: For student information, course registrations, and grades (in addition to standard enterprise
information such as human resources and accounting).

• Airlines: For reservations and schedule information. Airlines were among the first to use databases in a
geographically distributed manner.
• Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on
prepaid calling cards, and storing information about the communication networks.

Data Abstraction :-
For the system to be usable, it must retrieve data efficiently. The need for efficiency has led designers to use
complex data structures to represent data in the database. Since many database-system users are not computer
trained, developers hide the complexity from users through several levels of abstraction, to simplify users’
interactions with the system:

• Physical level:-The lowest level of abstraction describes how the data are actually stored. The physical
level describes complex low-level data structures in detail.
• Logical level:-The next-higher level of abstraction describes what data are stored in the database, and what
relationships exist among those data. The logical level thus describes the entire database in terms of a small
 Department of Computer Science and Engineering

number of relatively simple structures. Although implementation of the simple structures at the logical
levelmay involve complex physical-level structures, the user of the logical level does not need to be aware of this
complexity. This is referred to as physical data independence. Database administrators, who must decide what
information to keep in the database, use the logical level of abstraction.

• View level:- The highest level of abstraction describes only part of the entire database. Even though the
logical level uses simpler structures, complexity remains because of the variety of information stored in a
large database. Many users of the database system do not need all this information; instead, they need to
access only a part of the database. The view level of abstraction exists to simplify their interaction with the
system. The system may provide many views for the same database.

Figure :- The three levels of data abstraction

Instances and Schemas:-

Databases change over time as information is inserted and deleted. The collection of information stored in the
database at a particular moment is called an instance of the database.

The overall design of the database is called the database schema. Schemas are changed infrequently
Database systems have several schemas, partitioned according to the levels of abstraction. The physical
schema describes the database design at the physical level.

The logical schema describes the database design at the logical level.
A database may also have several schemas at the view level, sometimes called sub-schemas, that describe
different views of the database.

The logical schema is by far the most important, in terms of its effect on application programs, since
programmers construct applications by using the logical schema. The physical schema is hidden beneath the
logical schema, and can usually be changed easily without affecting application programs. Application
 Department of Computer Science and Engineering

programs are said to exhibit physical data independence if they do not depend on the physical schema, and
thus need not be rewritten if the physical schema changes.
The Entity-Relationship Model:-
The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships
among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other
objects.

For example, each person is an entity, and bank accounts can be considered as entities. Entities are described
in a database by a set of attributes.
For example, the attributes dept name, building, and budget may describe one particular department in a
university, and they form attributes of the department entity set. Similarly, attributes ID, name, and salary
may describe an instructor entity.

A relationship is an association among several entities. The set of all entities of the same type and the set of
all relationships of the same type are termed an entity set and relationship set. The overall logical structure
(schema) of a database can be expressed graphically by an entity-relationship (E-R) diagram.

Figure :- A sample E-R diagram

• Entity sets are represented by a rectangular box with the entity set name in the header and the attributes
listed below it.
• Relationship sets are represented by a diamond connecting a pair of related entity sets. The name of the
relationship is placed inside the diamond.
As an illustration, consider part of a university database consisting of instructors and the departments with
which they are associated. Figure shows the corresponding E-R diagram. The E-R diagram indicates that
there are two entity sets, instructor and department, with attributes as outlined earlier. The diagram also
shows a relationship member between instructor and department.
 Department of Computer Science and Engineering

Different symbols use to represent E-R diagram:-

Conclusion:- Thus we have studied defining schema for applications and also E-R diagram for different
applications.
 Department of Computer Science and Engineering

EXPERIMENT NO-2
Title:- Creating tables, Renaming tables, Data constraints (Primary key, Foreign key, Not Null), Data
insertion into a table.

Aim:-To study DDL commands and DML commands.

Theory:-
SQL is Structured Query Language, which is a computer language for storing, manipulating and
retrieving data stored in a relational database.SQL is the standard language for Relational Database System.
All the Relational Database Management Systems (RDMS) like My SQL, MS Access, Oracle, Sybase,
Informix, Postures and SQL Server use SQL as their standard database language.
Also, they are using different dialects, such as −

 MS SQL Server using T-SQL,

 Oracle using PL/SQL,
 MS Access version of SQL is called JET SQL (native format) etc.

SQL Process:-
When you are executing an SQL command for any RDBMS, the system determines the best way to carry
out your request and SQL engine figures out how to interpret the task.There are various components
included in this process.
These components are :-

 Query Dispatcher
 Optimization Engines
 Classic Query Engine
 SQL Query Engine, etc.
A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical files.

SQL is widely popular because it offers the following advantages

 Allows users to access data in the relational database management systems.
 Allows users to define the data in a database and manipulate that data.
 Allows to embed within other languages using SQL modules, libraries & pre-compilers.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
 Allows users to set permissions on tables, procedures and views.
 Department of Computer Science and Engineering

SQL Architecture:-

SQL Commands:-

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT,
UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their
nature.

DDL - Data Definition Language:-

Sr.No. Command & Description
1 CREATE
Creates a new table, a view of a table, or other object in the database.

2 ALTER
Modifies an existing database object, such as a table.

3 DROP
Deletes an entire table, a view of a table or other objects in the database.
 Department of Computer Science and Engineering

DML - Data Manipulation Language:-

Sr.No. Command & Description
1
SELECT
Retrieves certain records from one or more tables.

2
INSERT
Creates a record.

3
UPDATE
Modifies records.
4
DELETE
Deletes records.

SQL Constraints :-
Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that
can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints can either be column level or table level. Column level constraints are applied only to one
column where as, table level constraints are applied to the entire table.
Following are some of the most commonly used constraints available in SQL −
 NOT NULL Constraint − Ensures that a column cannot have a NULL value.

 DEFAULT Constraint − Provides a default value for a column when none is specified.

 UNIQUE Constraint − Ensures that all the values in a column are different.

 PRIMARY Key − Uniquely identifies each row/record in a database table.

 FOREIGN Key − Uniquely identifies a row/record in any another database table.

 CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain
conditions.
 INDEX − Used to create and retrieve data from the database very quickly.

SQL - Data Types:-

Exact Numeric Data Types:-

DATA TYPE FROM TO

int -2,147,483,648 2,147,483,647

numeric -10^38 +1 10^38 -1
float -1.79E + 308 1.79E + 308

real -3.40E + 38 3.40E + 38

 Department of Computer Science and Engineering

Date and Time Data Types:-

DATA TYPE FROM TO

Date time Jan 1, 1753 Dec 31, 9999

Small date time Jan 1, 1900 Jun 6, 2079

date Stores a date like June 30, 1991

time Stores a time of day like 12:30 P.M.

Character Strings Data Types:-

Sr.No. DATA TYPE & Description

1 char Maximum length of 8,000 characters.( Fixed length non-Unicode characters)

2 varchar Maximum of 8,000 characters.(Variable-length non-Unicode data).

varchar(max) Maximum length of 2E + 31 characters, Variable-length non-

3 Unicode data (SQL Server 2005 only).

text Variable-length non-Unicode data with a maximum length of 2,147,483,647

4
characters.

Unicode Character Strings Data Types:-

Sr.No. DATA TYPE & Description

1 nchar
Maximum length of 4,000 characters.( Fixed length Unicode)

nvarchar
2
Maximum length of 4,000 characters.(Variable length Unicode)

nvarchar(max)
3
Maximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode)

ntext
4
Maximum length of 1,073,741,823 characters. ( Variable length Unicode )
 Department of Computer Science and Engineering

 DDL commands:-

1. Create database:-
Syntax :- Create database <your database name>

Example:- To create database called ‘Testdb’, run the following query.

Create database Testdb
2. Use database:-
Syntax :- Use Database Name;
Example:- To use database called ‘Testdb’, run the following query.
Use Testdb;

3. Create table:- Creating a basic table involves naming the table and defining its columns and each
column's data type.
The SQL CREATE TABLE statement is used to create a new table.

Syntax :- CREATE TABLE table_name(

column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
Example:- CREATE TABLE CUSTOMERS(
ID INT(11),
NAME VARCHAR(20),
AGE INT (11),
ADDRESS CHAR (25) ,
SALARY INT(18)
);
OutPut:-
DESC CUSTOMERS;
+ + +
| Field | Type |
+ + +
| ID | int(11) |
| NAME | varchar(20) |
| AGE | int(11) |
| ADDRESS | char(25) |
| SALARY | decimal(18,2) |
+ + +
5 rows in set (0.00 sec)
 Department of Computer Science and Engineering

 Data Constraints:-

 Primary Key:-

Syntax :- CREATE TABLE table_name(

column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns)
);
Example:-
CREATE TABLE CUSTOMERS(
ID INT(11),
NAME VARCHAR (20),
AGE INT(11),
ADDRESS CHAR(25) ,
SALARY DECIMAL (18,2),
PRIMARY KEY (ID)
);
OutPut:- DESC CUSTOMERS;
+ + + +
| Field | Type | Key |
+ + + +
| ID | int(11) | PRI |
| NAME | varchar(20) | |
| AGE | int(11) | |
| ADDRESS | char(25) | |
| SALARY | decimal(18,2) | |
+ + + +
5 rows in set (0.00 sec)

 Not-Null:-

Example :- CREATE TABLE CUSTOMERS(

ID INT(11)NOT NULL,
NAME VARCHAR(20)NOT NULL,
AGE INT(11)NOT NULL,
ADDRESS CHAR(25) ,
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);
OutPut:-
DESC CUSTOMERS;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| ID | int(11) | NO | PRI | | |
| NAME | varchar(20) | NO | | | |
| AGE | int(11) | NO | | | |
| ADDRESS | char(25) | YES | | NULL | |
| SALARY | decimal(18,2) | YES | | NULL | |
+ + + + + + +
5 rows in set (0.00 sec)
 Department of Computer Science and Engineering

 DML commands:-
1. INSERT- Creates a record.
Syntax :- INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Example:-
INSERT INTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00);

INSERT INTO CUSTOMERS VALUES(2,'Khilan',25,'Delhi',1500.00);

INSERT INTO CUSTOMERS VALUES(3,'kaushik',23,'Kota',2000.00);

INSERT INTO CUSTOMERS VALUES(4,'Chaitali',25,'Mumbai',6500.00);

INSERT INTO CUSTOMERS VALUES(5,'Hardik',27,'Bhopal',8500.00);

INSERT INTO CUSTOMERS VALUES(6,'Komal',22,'MP',4500.00);

INSERT INTO CUSTOMERS VALUES(7,'Muffy',24,'Indore',10000.00);

OutPut:-
DESC CUSTOMERS;
+ + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
7 rows in set (0.00 sec)
2. SELECT:-
SQL SELECT statement is used to fetch the data from a database table which returns this data in the form
of a result table.

Syntax :-
SELECT * FROM table_name;
Example:-
SELECT * FROM CUSTOMERS;
OutPut:-
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
7 rows in set (0.00 sec)
Conclusion:- Thus we have studied Creating tables, Renaming tables, Data constraints (Primary key,
Foreign key, Not Null), Data insertion into a table.
 Department of Computer Science and Engineering

EXPERIMENT NO-3
Title:-Grouping data, aggregate functions, Oracle functions (mathematical, character Functions).
Aim:-To study Grouping data, aggregate functions, Oracle functions (mathematical, character Functions).

Theory:-
A. Grouping data:-
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some
functions. i.e if a particular column has same values in different rows then it will arrange these rows
in a group.

Important Points:
 GROUP BY clause is used with the SELECT statement.
 In the query, GROUP BY clause is placed after the WHERE clause.
 In the query, GROUP BY clause is placed before ORDER BY clause if used any.
Syntax :-
SELECT column1,function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1,column2
Example:-
Employee

i. Group By single column: Group By single column means, to place all the rows with same value of only that
particular column in one group. Consider the query as shown below.

Example:-
SELECT NAME,SUM(SALARY)
FROM Employee
GROUP BY NAME;
The above query will produce the below output:
 Department of Computer Science and Engineering

Output:- Employee

ii. Group By multiple columns: Group by multiple column is say for example, GROUP BY column1, column2.
This means to place all the rows with same values of both the columns column1 and column2 in one group.
Consider the below query:

Table:-
Student

Example:-
SELECT SUBJECT, YEAR, Count(*)
FROM Student
GROUP BY SUBJECT, YEAR;

OutPut:-
Student

b) HAVING Clause :-
This is where HAVING clause comes into use. We can use HAVING clause to place conditions to
decide which group will be the part of final result-set. Also we can not use the aggregate functions like SUM(),
COUNT() etc. with WHERE clause. So we have to use HAVING clause if we want to use any of these
functions in the conditions.
 Department of Computer Science and Engineering

Syntax :-
SELECT column1,function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1,column2
HAVING condition
ORDER BY column1,column2;
Example:-
SELECT NAME, SUM(SALARY) FROM Employee
GROUP BY NAME
HAVING SUM(SALARY)>3000;
OutPut:-
Employee

c) WHERE Clause :-

WHERE keyword is used for fetching filtered data in a result set.

 It is used to fetch data according to particular criteria.
 WHERE keyword can also be used to filter data by matching patterns.
Syntax :-
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Table:-
Employee
 Department of Computer Science and Engineering

Example:-

SELECT NAME
FROM Employee
Where salary>=3000;
OutPut:-

Employee

Operators used in the WHERE Clause:-

The following operators can be used in the WHERE clause:

SQL Meaning
Operators

= Equal to

> Greater than

>= Greater than or equal to

< Less than

<= Less than or equal to

<> Not equal to

 Department of Computer Science and Engineering

B. Aggregate function:-

 Aggregate functions perform a calculation on a set of values and return a single value.
 Aggregate functions ignore NULL values except COUNT.
 It is used with the GROUP BY clause of the SELECT statement.

Following are the Aggregate functions:

1. AVG
2. MAX
3. MIN
4. SUM
5. COUNT()
6. COUNT(*)
Example:-
Table-
Employee
Eid Ename Age City Salary
E001 ABC 29 Pune 20000
E002 PQR 30 Pune 30000
E003 LMN 25 Mumbai 5000
E004 XYZ 24 Mumbai 4000
E005 STU 32 Bangalore 25000

Aggregate
Description Syntax Example Output
Functions
SELECT AVG
It returns the average of <column_name> SELECT AVG(Salary) AVG(Salary)
AVG
the data values. FROM FROM Employee; 16800
<table_name>;
SELECT MAX
It returns the maximum <column_name> SELECT MAX(Salary) MAX(Salary)
MAX
value for a column. FROM FROM Employee; 30000
<table_name>;
SELECT MIN
It returns the minimum <column_name> SELECT MIN(Salary) MIN(Salary)
MIN
value for a column. FROM FROM Employee; 4000
<table_name>;
SELECT SUM
It returns the sum SELECT SUM(Salary)
<column_name> SUM(Salary)
SUM (addition) of the data FROM Employee
FROM 50000
values. WHERE City='Pune';
<table_name>;
SELECT COUNT
SELECT
It returns total number of <column_name> COUNT(Empid)
COUNT() COUNT(Empid)
values in a given column. FROM 5
FROM Employee;
<table_name>;
SELECT COUNT(*)
It returns the number of SELECT COUNT(*) COUNT(*)
COUNT(*) FROM
rows in a table. FROM Employee; 5
<table_name>;
 Department of Computer Science and Engineering

C. Numeric Functions :-
Numeric Functions are used to perform operations on numbers and return numbers.
Following are the numeric functions defined in SQL:
 ABS(): It returns the absolute value of a number.
Syntax: SELECT ABS(-243.5);

Output: 243.5

Example:-
SQL> SELECT ABS(-10);
+ +
| ABS(10)
+ +
| 10
+ +
 ACOS(): It returns the cosine of a number.
Syntax: SELECT ACOS(0.25);

Output: 1.318116071652818

 ASIN(): It returns the arc sine of a number.

Syntax: SELECT ASIN(0.25);

Output: 0.25268025514207865

 ATAN(): It returns the arc tangent of a number.

Syntax: SELECT ATAN(2.5);

Output: 1.1902899496825317

 COS(): It returns the cosine of a number.

Syntax: SELECT COS(30);

Output: 0.15425144988758405

 COT(): It returns the cotangent of a number.

Syntax: SELECT COT(6);

Output: -3.436353004180128

 DEGREES(): It converts a radian value into degrees.

Syntax: SELECT DEGREES(1.5);

Output: 85.94366926962348
 Department of Computer Science and Engineering

 LOG10(): It returns the base-10 logarithm of a number.

Syntax: SELECT LOG(2);
Output: 0.6931471805599453

 LOG2(): It returns the base-2 logarithm of a number.

Syntax: SELECT LOG2(6);

Output: 2.584962500721156

 MOD(): It returns the remainder of n divided by m.

Syntax: SELECT MOD(18, 4);

Output: 2

 PI(): It returns the value of PI displayed with 6 decimal places.

Syntax: SELECT PI();

Output: 3.141593

 SIN(): It returns the sine of a number.

Syntax: SELECT SIN(2);

Output: 0.9092974268256817

 SQRT(): It returns the square root of a number.

Syntax: SELECT SQRT(25);

Output: 5

 TAN(): It returns the tangent of a number.

Syntax: SELECT TAN(1.75);

Output: -5.52037992250933

D. String functions:
 Upper ( ): This function converts a string into upper case.

Syntax: upper (‘string’)

Example: Sql> select upper (‘hello’) from dual;

Output: HELLO

 Lower ( ): This function converts a string into lower case.

Syntax: lower (‘string’)

 Department of Computer Science and Engineering

Example: Sql> select lower (‘HELLO’) from dual;

Output: hello
 Initcap ( ): This function converts a string into initial caps.

Syntax: initcap (‘string’)

Example: Sql> select initcap (‘computer education’) from dual;

Output: Computer Education

 Itrim ( ): This function is availed to remove the unnecessary spaces or characters available to a string.

Syntax: Itrim (‘string’, ‘unnecessary char’)

Example: Sql> select Itrim (‘xyxyhello’, ‘xy’)

Output: hello

 Rtrim ( ): This function is used to remove the unnecessary characters or spaces available on the right side
of a string.

Syntax: rtrim (‘string’, ‘unnecessary char’)

Example: Sql> select rtrim (‘computerxyzxy’, ‘xy’) from dual;

Output: computerxyz

 Concat ( ): This function always appends ( concatenates ) string2 to the end of string1. If either of the string is
NULL, CONCAT function returns the non-NULL argument. If both strings are NULL, CONCAT returns NULL.
Syntax: CONCAT('String1', 'String2')
Example: Sql>SELECT CONCAT('computer' ,'science') FROM DUAL;
Output1:computerscience
 Substr ( ): This function returns a portion of a string from a given start point to an end point. If a
substring length is not given, then SUBSTR returns all the characters till the end of string (from the starting
position specified).
Syntax: Sql> SELECT SUBSTR('String',start-index,length_of_extracted_string)
Example: SELECT SUBSTR('Database Management System', 9) FROM DUAL;
Output: Management System

 Replace ( ): This function searches for a character string and, if found, replaces it with a given replacement string at
all the occurrences of the string. REPLACE is useful for searching patterns of characters and then changing all
instances of that pattern in a single function call. If a replacement string is not given, then REPLACE function
removes all the occurrences of that character string in the input string. If neither a match string nor a replacement
string is specified, then REPLACE returns NULL.
 Department of Computer Science and Engineering

Syntax: Sql> SELECT REPLACE(Text, search_string, replacement_string)

Example:SELECT REPLACE('DATA MANAGEMENT', 'DATA','DATABASE') FROM DUAL;
Output: DATABASE MANAGEMENT

 Instr ( ) : This function returns numeric position of a character or a string in a given string. Optionally, you can
provide a position m to start searching, and the occurrence n of string. Also, if the starting position is not given, then
it starts search from index 1, by default. If after searching in the string, no match is found then, INSTR function
returns 0.
Syntax: Sql> SELECT INSTR(Column|Expression, 'String', [,m], [n])
Example:SELECT INSTR('Google apps are great applications','app',1,2) FROM
DUAL;
Output :23

 Format(): This function is used to display a number in the given format.

Syntax: Format("0.981", "Percent");
Output: ‘98.10%’

Conclusion:- Thus we have studied Grouping data, aggregate functions, Oracle functions (mathematical,
character Functions).
.
 Department of Computer Science and Engineering

EXPERIMENT NO-4
Title:- Sub-queries, Set operations, Joins.
Aim:- To study Sub-queries, Set operations, Joins.

Theory:-
A. Sub queries :-
A Sub query or Inner query or a Nested query in SQL is a query inside another SQL query and
inserted inside the WHERE clause. A SQL Sub query is used to return information that will be used in the primary
query as a condition to additionally limit the information to be recovered.

Sub queries in SQL can be used with the SELECT, INSERT, UPDATE, and DELETE command alongside the
operators like =, <, >, >=, <=, IN, BETWEEN, and so on.
There are a couple of standards that we should consider in SQL Sub query–
 Sub queries in SQL must be encased inside brackets.
 A SQL sub query can have just a single section in the SELECT clause, except if different segments are in the
principal query for the sub query to analyze its chose segments.
 An ORDER BY charge can’t be used in a sub query, in spite of the fact that the principal query can use an
ORDER BY. The GROUP BY charge can be used to play out indistinguishable capacity from the ORDER BY in
a sub query.
 SQL Sub queries that arrival in excess of one column must be used with different value operators, for example,
the IN operator.
 The SELECT rundown cannot exclude any references to values that evaluate to a BLOB, ARRAY, CLOB, or
NCLOB.
 A SQL subquery can’t be quickly encased in a set function.
 The BETWEEN operator can’t be used with a subquery. Nonetheless, the BETWEEN operator can be used inside
the subquery.

a. Subqueries with the SELECT Statement :-

Table:- CUSTOMERS

Syntax:-
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
 Department of Computer Science and Engineering

Example :-
SQL> SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;
Output:-

b. Subqueries with the INSERT Statement :-

Subqueries additionally can be used with INSERT command. The INSERT statement uses the
information came back from the subquery to embed into another table. The chose information in the subquery
can be altered with any of the character, date or number values.
Syntax:-
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
Example :-
SQL> INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

c. Subqueries with the UPDATE Statement :-

The subquery can be used in conjunction with the UPDATE command. Either single or various
sections in a table can be refreshed when utilizing a subquery with the UPDATE clause.

Syntax:-
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Example :-
SQL> UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
 Department of Computer Science and Engineering

Output:-

d. Subqueries with the DELETE Statement :-

Syntax:-

Delete FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example :-
SQL> DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
Output:-

B. Set operations :-

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.
There are 4 different types of SET operations:-
1) UNION
2) UNION ALL
3) INTERSECT
4) MINUS
 Department of Computer Science and Engineering

1) UNION:-
UNION is used to combine the results of two or more SELECT statements. However it will eliminate
duplicate rows from its result set. In case of union, number of columns and data type must be same in
both the tables, on which UNION operation is being applied .

First

ID Name

1 Abhi

2 Adam

Second

ID Name

2 Adam

3 Chester

Example :-

Select * from first UNION select * from second;

ID Name

1 Abhi

2 Adam

3 Chester

2) UNION ALL :-
This operation is similar to Union. But it also shows the duplicate rows.
 Department of Computer Science and Engineering

Example:-

Select * from first UNION ALL select * from second;

ID Name

1 Abhi

2 Adam

2 Adam

3 Chester

3) INTERSECT :-
Intersect operation is used to combine two SELECT statements, but it only retunes the
records which are common from both SELECT statements. In case of Intersect the number of
columns and data type must be same.

Example:-

Select * from first INTERSECT select * from second;

ID Name

2 Adam

4) MINUS :-
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.
 Department of Computer Science and Engineering

Example:-

Select * from first MINUS select * from second;

ID Name

1 Abhi

C. Joins :-
The join clause allows us to retrieve data from two or more related tables into a meaningful
result set. We can join the table using a SELECT statement and a join condition.

Types of JOINS in SQL Server :-

SQL Server mainly supports four types of JOINS, and each join type defines how two tables are related in a
query. The following are types of join supports in SQL Server.

1. INNER JOIN
2. OUTER JOIN

1. INNER JOIN :-

This JOIN returns all records from multiple tables that satisfy the specified join condition. It is the simple and
most popular form of join and assumes as a default join. If we omit the INNER keyword with the JOIN
query, we will get the same output.

Syntax :-

SELECT columns
FROM table1
INNER JOIN table2 ON condition1
 Department of Computer Science and Engineering

Example:-
Student Fee

SELECT Student.admission_no, Student.first_name, Student.last_name, Fee. course, Fee.amount_paid

FROM Student
INNER JOIN Fee
ON Student.admission_no = Fee.admission_no;

Output:-

2. OUTER JOIN :-

OUTER JOIN in SQL Server returns all records from both tables that satisfy the join condition. In
other words, this join will not return only the matching record but also return all unmatched rows from one or
both tables.

We can categories the OUTER JOIN further into three types:

A. Left outer join

B. Right outer join

C. Full outer join

A. Left outer join :-

The LEFT OUTER JOIN retrieves all the records from the left table and matching rows from the right table.
It will return NULL when no matching record is found in the right side table. Since OUTER is an optional
keyword, it is also known as LEFT JOIN.
 Department of Computer Science and Engineering

Example:-
SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid
FROM Student
LEFT OUTER JOIN Fee
ON Student.admission_no = Fee.admission_no;

Output:-

B. Right outer join :-

The RIGHT OUTER JOIN retrieves all the records from the right-hand table and matched rows from the left-hand
table. It will return NULL when no matching record is found in the left-hand table. Since OUTER is an optional
keyword, it is also known as RIGHT JOIN.

Example:-
SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid
FROM Student
RIGHT OUTER JOIN Fee
ON Student.admission_no = Fee.admission_no;

Output:-
 Department of Computer Science and Engineering

C. Full outer join:-

The FULL OUTER JOIN in SQL Server returns a result that includes all rows from both tables. The columns of the
right-hand table return NULL when no matching records are found in the left-hand table. And if no matching records
are found in the right-hand table, the left-hand table column returns NULL.

Example:-
SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee.amount_paid
FROM Student
FULL OUTER JOIN Fee
ON Student.admission_no = Fee.admission_no;

Output:-

Conclusion:- Thus we have studied Sub-queries, Set operations, Joins.

 Department of Computer Science and Engineering

EXPERIMENT NO-5
Title:- Creation of databases, writing SQL and PL/SQL queries to retrieve information from the databases.
Aim:- To study Creation of databases, writing SQL and PL/SQL queries to retrieve information from the
databases.
Theory:-
SQL:-
Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting
information from databases. SQL is an ANSI and ISO standard, and is the de facto standard database query
language. A variety of established database products support SQL, including products from Oracle and
Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex
databases.

Some SQL Commands :-

The SQL commands help in creating and managing the database. The most common SQL commands which
are highly used are mentioned below:

1. CREATE command
2. UPDATE command
3. DELETE command
4. SELECT command
5. DROP command
6. INSERT command

SQL Create Database :

The database developers and the users use this statement in SQL for creating the new database in the database
systems. It creates the database with the name which has been specified in the Create Database statement.

Syntax :-
CREATE DATABASE Database_Name;
In this syntax, Database_Name specifies the name of the database which we want to create in the system. We
have to type the database name in query just after the 'Create Database' keyword.

PL/SQL :-

PL/SQL provides the different queries, or we can say commands to the user. Basically, PL/SQL is similar to
the SQL language. But PL/SQL introduces the different methods that consist of the block structure, and it is
helpful for well suitable development. PL/SQL also provides the variable in which we can perform the
variable deceleration as well as we can assign the name as well. PL/SQL structure divides into different
sections, and we can write the different queries into an execution section as per user requirement. We can
perform the update, create, insert and delete, etc. as per requirement.
 Department of Computer Science and Engineering

Features of PL/SQL :-
 PL SQL comes with a default, interpreted, and operating system independent language.
 It can be used from the command line.
 It is a portable, efficient, and transaction processing language.
 PL/SQL syntax is derived from Pascal programming language
 It has an efficient exception handling, error checks, and data hiding features.
 Works in close connection with SQL.
 It supports multiple data types.
 Assists a large variety of programming structures with the help of functions and procedures.
 It is used for web and server page development.
 It can be used for decision making and iteration programming.
 PL/SQL executes multiple queries with a single command.
 Functions, procedures, triggers, and so on are kept in the database after generation for reuse.
 Applications developed in PL/SQL are portable to other OS and hardware.
 PL/SQL allows the use of loops, conditions, constants, variables, procedures, functions, triggers,
arrays, and so on.
 PL/SQL is case insensitive. A text in PL/SQL comprises either delimiters, comments, identifiers, and
literals.

Conclusion:- Thus we have studied SQL and PL/SQL queries to retrieve information from the databases.
 Department of Computer Science and Engineering

EXPERIMENT NO-6
Title:- Assignment on Triggers & Cursors.
Aim:- To study Assignment on Triggers & Cursors.
Theory :-
A. Triggers:-
A Trigger is basically a program which gets automatically executed in response to some events such as
modification in the database. Some of the events for their execution are DDL statement, DML statement or any
Database operation. Triggers are thus stored within the database and come into action when specific conditions
match. Hence, they can be defined on any schema, table, view etc. There are six types of triggers: BEFORE
INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER
DELETE.
Advantages of Trigger :-
 They are helpful in keeping the track of all the changes within the database.
 They also help in maintaining the integrity constraints.
Disadvantages of Trigger :-
 They are very difficult to view which makes the debugging also difficult.
 Too much use of the triggers or writing complex codes within a trigger can slow down the performance.
The syntax for creating a trigger :-
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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

B. Cursors :-
A cursor can be basically referred to as a pointer to the context area. Context area is a memory
area that is created by Oracle when SQL statement is processed. The cursor is thus responsible for holding
the rows that have been returned by a SQL statement. Thus the PL/SQL controls the context area by the help
of cursor. An Active set is basically the set of rows that the cursor holds. The cursor can be of two types:
Implicit Cursor, and Explicit Cursor.
 Department of Computer Science and Engineering

Advantages of Cursor :-
 They are helpful in performing the row by row processing and also row wise validation on each row.
 Better concurrency control can be achieved by using cursors.
 Cursors are faster than while loops.
Disadvantages of Cursor :-
 They use more resources each time and thus may result in network round trip.
 More number of network round trips can degrade the performance and reduce the speed.

There are two types of cursors :-

 Implicit cursors
 Explicit cursors

Implicit Cursors :-

 Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when
there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the
information in it.
 Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs to be
inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
Explicit Cursors :-
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An
explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT
Statement which returns more than one row.
Example :-
We will be using the CUSTOMERS table

Select * from customers;

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2500.00
2 Khilan 25 Delhi 2000.00
3 Kaushik 23 Kota 2500.00
4 Chaitali 25 Mumbai 7000.00
5 Hardik 27 Bhopal 9000.00
6 Konal 22 MP 5000.00

The syntax for creating an explicit cursor

CURSOR cursor_name IS select_statement;
 Department of Computer Science and Engineering

Declaring the Cursor :-

Declaring the cursor defines the cursor with a name and the associated SELECT statement.
For example –
CURSOR c_customers IS
SELECT id, name, address FROM customers;

Opening the Cursor :-

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by
the SQL statement into it. For example, we will open the above defined cursor as follows –

OPEN c_customers;

Fetching the Cursor :-

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-
opened cursor as follows –

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor :-

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened
cursor as follows –

CLOSE c_customers;

Example :-

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;
CLOSE c_customers;
END;
/
 Department of Computer Science and Engineering

Output :-

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

Conclusion:- Thus we have studied Triggers & Cursors.

 Department of Computer Science and Engineering

EXPERIMENT NO-7
Title:- Normal Forms: First, Second, Third, Fourth and Boyce Codd Normal Forms.
Aim:- To study Normal Forms: First, Second, Third Fourth and Boyce Codd Normal Forms.
Theory :-
Normalization :-

 Normalization is the process of organizing the data in the database.

 Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to

eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.

 Normalization divides the larger table into smaller and links them using relationships.

 The normal form is used to reduce redundancy from the database table.

Types of Normal Forms :-

 1NF
 2NF
 3NF
 BCNF
 4NF

1. First Normal Form (1NF) :-

For a table to be in the First Normal Form, it should follow the following 4 rules:

 It should only have single (atomic) valued attributes/columns.

 Values stored in a column should be of the same domain

 All the columns in a table should have unique names.

 And the order in which data is stored, does not matter.

Example :-

Roll No Name Subject

101 AKON OS,CN
103 BKON JAVA
102 CKON C,C++
 Department of Computer Science and Engineering

OutPut :-

Roll No Name Subject

101 AKON OS,CN
101 AKON CN
103 BKON JAVA
102 CKON C,C++
102 CKON C++

2. Second Normal Form (2NF) :-

For a table to be in the Second Normal Form,

 It should be in the First Normal form.
 It should not have Partial Dependency.
Example :-
Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a
teacher can teach more than one subject.
TEACHER Table
TEACHER_ID SUBJECT TEACHER_AGE
25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38
In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper
subset of a candidate key. That's why it violates the rule for 2NF.
TEACHER_DETAIL table
TEACHER_ID TEACHER_AGE
25 30
47 35
83 38

TEACHER_SUBJECT table

TEACHER_ID SUBJECT
25 Chemistry
25 Biology
47 English
83 Math
83 Computer
 Department of Computer Science and Engineering

3. Third Normal Form (3NF) :-

A table is said to be in the Third Normal Form when,

 It is in the Second Normal form.

 It doesn't have Transitive Dependency.

 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.

 If there is no transitive dependency for non-prime attributes, then the relation must be in third normal
form.

A relation is in third normal form if it holds at least one of the following conditions for every non-trivial
function dependency X → Y.
1. X is a super key.
2. Y is a prime attribute i.e. each element of Y is part of some candidate key.

Example :-

EMPLOYEE_DETAIL Table

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida
333 Stephan 02228 US Boston
444 Lan 60007 US Chicago
555 Katharine 06389 UK Norwich
666 John 462007 MP Bhopal

Super key in the table above:

{EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP} .... so on

Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID.
The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID).
It violates the rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP>
table, with EMP_ZIP as a Primary key.

EMPLOYEE Table

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010
333 Stephan 02228
444 Lan 60007
555 Katharine 06389
666 John 462007
 Department of Computer Science and Engineering

EMPLOYEE_ZIP Table

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida
02228 US Boston
60007 US Chicago
06389 UK Norwich
462007 MP Bhopal
4. Boyce Codd normal form (BCNF) :-
It is a higher version of the Third Normal form. This form deals with certain type of anomaly
that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to
be in BCNF.

For a table to be in BCNF, following conditions must be satisfied:

 R must be in 3rd Normal Form

 For each functional dependency ( X → Y ), X should be a super Key.

Example :-

EMPLOYEE Table
EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO
264 India Designing D394 283
264 India Testing D394 300
364 UK Stores D283 232
364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:
EMP_COUNTRY Table

EMP_ID EMP_COUNTRY
264 India
264 India

EMP_DEPT Table

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283
Testing D394 300
Stores D283 232
Developing D283 549
 Department of Computer Science and Engineering

EMP_DEPT_MAPPING Table

EMP_ID EMP_DEPT_NO
264 283
264 300
364 232
364 549
Functional dependencies:-

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:-

For the first table: EMP_ID

For the second table: EMP_DEPT

For the third table: {EMP_ID, EMP_DEPT}

5. Fourth Normal Form (4NF) :-

Fourth Normal Form comes into picture when Multi-valued Dependency occur in any relation. In
this tutorial we will learn about Multi-valued Dependency, how to remove it and how to make any table
satisfy the fourth normal form.

A table is said to be in the Fourth Normal Form when,

 A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued dependency.

 For a dependency A → B, if for a single value of A, multiple values of B exists, then the relation will
be a multi-valued dependency.

Example :-

STUDENT

STU_ID COURSE HOBBY

21 Computer Dancing
21 Math Singing
34 Chemistry Dancing
74 Biology Cricket
59 Physics Hockey

The given STUDENT table is in 3NF, but the COURSE and HOBBY are two independent entity. Hence,
there is no relationship between COURSE and HOBBY.

In the STUDENT relation, a student with STU_ID, 21 contains two courses, Computer and Math and two
hobbies, Dancing and Singing. So there is a Multi-valued dependency on STU_ID, which leads to
unnecessary repetition of data.
 Department of Computer Science and Engineering

So to make the above table into 4NF, we can decompose it into two tables:

STUDENT_COURSE

STU_ID COURSE
21 Computer
21 Math
34 Chemistry
74 Biology
59 Physics

STUDENT_HOBBY

STU_ID HOBBY
21 Dancing
21 Singing
34 Dancing
74 Cricket
59 Hockey

Conclusion:- Thus we have studied First, Second, Third Fourth and Boyce Codd Normal Forms..
 Department of Computer Science and Engineering

EXPERIMENT NO-8

Title:- Assignment in Design and Implementation of Database systems or packages for application such as
office automation, hotel management, hospital management.

Aim:- To study Database systems or packages for application such as office automation, hotel management,
hospital management .
Theory :-

Write theory about DBMS.

Types of DBMS.

Write down about packages used in your applications.

Conclusion:- Thus we have studied Database systems or packages for application such as office automation,
hotel management, hospital management
 Department of Computer Science and Engineering

EXPERIMENT NO-9

Title:- Deployment of Forms, Reports Normalization, Query Processing Algorithms in the above application
project..

Aim:- To study Deployment of Forms, Reports Normalization, Query Processing Algorithms in the above
application project.
Theory :-
Write theory about forms of your application.

Write theory about Database connectivity.

Write about queries which are used in your application.

Conclusion:- Thus we have studied Deployment of Forms, Reports Normalization, Query Processing
Algorithms in the above application project.
 Department of Computer Science and Engineering

EXPERIMENT NO-10

Title:- Large objects – CLOB, NCLOB, BLOB and BFILE.

Aim:- To study large objects – CLOB, NCLOB, BLOB and BFILE.
Theory :-
The term large object (LOB) are a collection of data types which is designed to hold a large amount
of data. A LOB is able to hold up to maximum size ranging from 8 terabytes to 128 terabytes, depending
upon how your database is configured. Storing data into LOBs helps you to manipulate & access data
efficiently in your own application.
1) CLOB :-
A character large object (CLOB) is a varying-length string with a maximum length of 2,147,483,647
bytes (2 gigabytes minus 1 byte). A CLOB is designed to store large SBCS data or mixed data, such as
lengthy documents. For example, you can store information such as an employee resume, the script of a play,
or the text of novel in a CLOB. Alternatively, you can store such information in UTF-8 in a mixed CLOB. A
CLOB is a varying-length character string.
2) BLOB :-
A binary large object (BLOB) is a varying-length string with a maximum length of 2,147,483,647
bytes (2 gigabytes minus 1 byte). A BLOB is designed to store non-traditional data such as pictures, voice,
and mixed media. BLOBs can also store structured data for use by distinct types and user-defined functions.
A BLOB is a binary string. Although BLOB strings and FOR BIT DATA character strings might be used for
similar purposes, the two data types are not compatible. The BLOB function can be used to change a FOR
BIT DATA character string into a BLOB string.
3) NCLOBs :-
An SQL NCLOB is a built-in type that stores a Character Large Object using the National
Character Set as a column value in a row of a database table. LOBs (large objects) are database types that are
used to store large amounts of data (maximum size is 4 Gigabytes) such as ASCII text, text in National
Characters, files in various graphics formats, and sound wave forms.

4) Internal LOBs :-

Internal LOBs (BLOBs, CLOBs, NCLOBs) are stored in database table spaces and have transactional support
of the database server. (COMMITT, ROLLBACK, and so forth work with them.)

5) External LOBs :-

External LOBs are operating system files outside the database tablespaces, that have no transactional support
from the database server.
 Department of Computer Science and Engineering

6) BFILE :-

The Oracle BFILE data type is an Oracle LOB data type that contains a reference to binary data with a
maximum size of 4 gigabytes. An Oracle BFILE differs from other Oracle LOB data types in that its data is
stored in a physical file in the operating system instead of on the server.

Note that the BFILE data type provides read-only access to data.

Other characteristics of a BFILE data type that distinguish it from a LOB data type are that it:

 Contains unstructured data.

 Supports server-side chunking.
 Uses reference copy semantics. For example, if you perform a copy operation on a BFILE, only
the BFILE locator (which is a reference to the file) is copied. The data in the file is not copied.

The BFILE data type should be used for referencing LOBs that are large in size, and therefore, not practical
to store in the database. More client, server, and communication overhead is involved when using
a BFILE data type compared with the LOB data type. It is more efficient to access a BFILE if you only
need to obtain a small amount of data. It is more efficient to access database-resident LOBs if you need to
obtain the entire object.

Conclusion:- Thus we have studied Large objects – CLOB, NCLOB, BLOB and BFILE.
 Department of Computer Science and Engineering

EXPERIMENT NO-11

Title:- Distributed data base Management, creating web-page interfaces for database applications using
servlet.

Aim:- To study Distributed data base Management, creating web-page interfaces for database applications
using servlet..
Theory :-
Distributed Database System :-
A distributed database is basically a database that is not limited to one system, it is
spread over different sites, i.e, on multiple computers or over a network of computers. A distributed database
system is located on various sites that don’t share physical components. This may be required when a
particular database needs to be accessed by various users globally. It needs to be managed such that for the
users it looks like one single database .
Types :-
1. Homogeneous Database:-
In a homogeneous database, all different sites store database identically. The operating
system, database management system, and the data structures used – all are the same at all sites. Hence,
they’re easy to manage.
2. Heterogeneous Database:-
In a heterogeneous distributed database, different sites can use different schema and
software that can lead to problems in query processing and transactions. Also, a particular site might be
completely unaware of the other sites. Different computers may use a different operating system, different
database application. They may even use different data models for the database. Hence, translations are
required for different sites to communicate.
Applications of Distributed Database:-
 It is used in Corporate Management Information System.
 It is used in multimedia applications.
 Used in Military’s control system, Hotel chains etc.
 It is also used in manufacturing control system.
Advantages of DDBMS :-

 The database is easier to expand as it is already spread across multiple systems and it is not too
complicated to add a system.
 The distributed database can have the data arranged according to different levels of transparency i.e data
with different transparency levels can be stored at different locations.
 The database can be stored according to the departmental information in an organisation. In that case, it is
easier for a organisational hierarchical access.
 Department of Computer Science and Engineering

 There were a natural catastrophe such as fire or an earthquake all the data would not be destroyed it is
stored at different locations.
 It is cheaper to create a network of systems containing a part of the database. This database can also be
easily increased or decreased.
 Even if some of the data nodes go offline, the rest of the database can continue its normal functions.

Servlet :-

Write theory about servlet. Use of web-page interfaces for database applications using
servlet.

Conclusion:- Thus we have studied creating web-page interfaces for database applications using servlet..