St.

Vincent Pallotti College of Engineering & Technology, Nagpur

Department of Mechanical Engineering

Sixth Semester 2019-2020(EVEN)

Course Outcome
COMPUTER APPLICATION-II (BEME607P)

Understand database concepts, Structures and terms related

BEME607P.1
to database design and management.
To use SQL (Structured Query Language) to create,
BEME607P.2
manipulate and query database.
Implement relational database into DBMS and Understand
BEME607P.3
database development and management processes.

Use several commercially available database management

system tools such as Microsoft Access and oracle SQL plus
BEME607P.4
to generate simple databases for material management/
Inventory management/ Office automation etc.
St. Vincent Pallotti College of Engineering and Technology,
Nagpur
Department of Mechanical Engineering
Session :2019-20 (EVEN)

Subject Computer Application – II

List of Experiments

Expt. No. Name of Experiment

1 Introduction to DBMS

2 To study E-R model

3 To create table and implementing DDL commands on it.

4 To implement DML commands on created database.

5 To Implement Various constraints on the table.

6 To implement concept of Indexes and views.

7 To implement various aggregate functions on the given database

8 To perform various set operations.

9 To implement order by, Group by and having clause.

10 To implement SQL nested queries

To implement SQL queries on table related to Material

11 management/Inventory Management/ Office automation (Assertion and
Triggers)
Computer application -II

Experiment No. 1

Aim: To study DBMS structure and DBMS architecture.

Introduction To DBMS:

A database management system is a collection of interrelated data and set of program to access
those data.

The collection of data usually referred as database; contains information relevant to an

enterprise. 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 Users and Administrators:

People work with a database can be categorized as database users

There are four different types of database sytem users, differentiated by the way they expect to
interact with the syatem. Different types of user interface have been designed for different types
of users.

a)Naıve users: They are unsophisticated users who interact with the system by invoking one of
the application programs that have been written previously.

b) Application Programmer: They are computer professionals who write application programs.
Application programmers can choose from many tools to develop user interfaces.

c) Sophisticated User: They interact with the system without writing programs. Instead, they
form their requests either using a database query language or by using tools such as data analysis
software. Analysts who submit queries to explore data in the database fall in this category.

d) Specialized users: They are sophisticated users who write specialized database applications
that do not fit into the traditional data-processing framework. Among these applications are
computer-aided design systems, knowledge base and expert systems, systems that store data with
complex data types (for example, graphics data and audio data), and environment-modeling
systems.

1.1
2019-20
Computer application -II

Database Administrator:

One of the main reasons for using DBMS is to have central control of both the data and the
programs that access those data. A person who has such central control over the system is called
a database administrator (DBA).

The functions of a DBA include:

• Schema definition
• Storage structure to access method definition
• Schema and physical organization modification
• Granting of authorization for data access
• Routine maintenance

The Query Processor:

The query processor componenets include:

• DDL interpreter which interpretes DDL stataements and records the definition in the
data dictionary.
• DML compiler which translates DML statements in a query language into an evaluation
plan consisting of low level instruction that the query evaluation engine understands.
• Query evaluation engine which interprets low level instructions generated by DML
compiler.

DATA storage and Querying

A database system is partitioned into modules that deal with each of the responsibilities of the
overall system. The functional components of a database system can be broadly divided into the
storage manager and the query processor components.
The storage manager is important because databases typically require a large amount of storage
space.
Storage Manager:
The storage manager is the component of a database system that provides the interface between
the low-level data stored in the database and the application programs and queries submitted to
the system. The storage manager is responsible for the interaction with the file manager. The raw
data are stored on the disk using the file system provided by the operating system. The storage
manager translates the various DML statements into low-level file-system commands.

• Transaction manager, which ensures that the database remains in a consistent (correct) state
despite system failures, and that concurrent transaction executions proceed without conflicting.
• File manager, which manages the allocation of space on disk storage and the data structures
used to represent information stored on disk.
• Buffer manager, which is responsible for fetching data from disk storage into main memory,
and deciding what data to cache in main memory. The buffer manager is a critical part of the

1.2
2019-20
Computer application -II

database system, since it enables the database to handle data sizes that are much larger than the
size of main memory.

• Data files, which store the database itself.

• Data dictionary, which stores metadata about the structure of the database, in particular the
schema of the database.
• Indices, which can provide fast access to data items. Like the index in this textbook, a database
index provides pointers to those data items that hold a particular value. For example, we could
use an index to find the instructor record with a particular ID, or all instructor records with a
particular name. Hashing is an alternative to indexing that is faster in some but not all cases.

Transaction Management

A transaction is a collection of operations that performs a single logical function in a database

application. Each transaction is a unit of both atomicity and consistency. Thus, we require that
transactions do not violate any database consistency constraints. That is, if the database was
consistent when a transaction started, the database must be consistent when the transaction
successfully terminates. However, during the execution of a transaction, it may be necessary
temporarily to allow inconsistency, since either the debit of A or the credit of B must be done
before the other. This temporary inconsistency, although necessary, may lead to difficulty if a
failure occurs.

Database Architecture

The architecture of a database system is greatly influenced by the underlying computer system
on which the database system runs. Database systems can be centralized, or client-server, where
one server machine executes work on behalf of multiple client machines. Database systems can
also be designed to exploit parallel computer architectures. Distributed databases span multiple
geographically separated machines.

Database applications are usually partitioned into two or three parts.

In a two-tier architecture, the application resides at the client machine, where it invokes
database system functionality at the server machine through query language statements.
Application program interface standards like ODBC and JDBC are used for interaction between
the client and the server.

In a three-tier architecture, the client machine acts as merely a front end and does not contain
any direct database calls. Instead, the client end communicates with an application server,
usually through a forms interface. The application server in turn communicates with a database
system to access data. The business logic of the application, which says what actions to carry out
under what conditions, is embedded in the application server, instead of being distributed across
multiple clients. Three-tier applications are more appropriate for large applications, and for
applications that run on the WorldWideWeb.

RESULT: Study of DBMS structure and architecture is done

1.3
2019-20
Computer application -II

1.4
2019-20
Computer application -II

1.5
2019-20
Computer application -II

Experiment No. 2

Aim: To study entity relationship model

Introduction:
The entity-relationship (E-R) data model was developed to facilitate database design by allowing
specification of an enterprise schema that represents the overall logical structure of a database.

The E-R model is very useful in mapping the meanings and interactions of real-world enterprises
onto a conceptual schema. Because of this usefulness, many database-design tools draw on
concepts from the E-R model

Entity Relationship Model:

An entity is a “thing” or “object” in the real world that is distinguishable from all other objects.
For example, each person in a university is an entity. An entity has a set of properties, and the
values for some set of properties may uniquely identify an entity. For instance, a person may
have a person id property whose value uniquely identifies that person.

An entity set is a set of entities of the same type that share the same properties, or attributes. The
set of all people who are instructors at a given university, for example, can be defined as the
entity set instructor. Similarly, the entity set student might represent the set of all students in the
university.

Entity sets do not need to be disjoint. For example, it is possible to define the entity set of all
people in a university (person). A person entity may be an instructor entity, a student entity, both,
or neither.

Relationship:
A relationship is an association among several entities. For ex we can define a relationship that
associates customer with loan.

Relationship Sets:

Relationship set is a set of relationships of the same type. Formally, it is a mathematical relation
on n ≥ 2 (possibly nondistinct) entity sets. If E1, E2, . . . , En are entity sets, then a relationship
set R is a subset of

{(e1, e2, . . . , en) | e1 ∈ E1, e2 ∈ E2, . . . , en ∈ En}

where
(e1, e2, . . . , en) is a relationship.

The function that an entity plays in a relationship is called that entity’s role. Since entity sets
participating in a relationship set are generally distinct, roles are implicit and are not usually
specified. However, they are useful when the meaning of a relationship needs clarification. Such
is the case when the entity sets of a relationship set are not distinct; that is, the same entity set
participates in a relationship set more than once, in different roles. In this type of relationship set,

2.1
2019-20
Computer application -II

sometimes called a recursive relationship set, explicit role names are necessary to specify how an
entity participates in a relationship instance.

Attributes:
An attribute of an entity set is a function that maps from the entity set into a domain.

For each attribute, there is a set of permitted values, called the domain, or value set, of that
attribute. The domain of attribute course id might be the set of all text strings of a certain length.
Similarly, the domain of attribute semester might be strings from the set.

Types of Attribute

1. Simple and Composite Attribute:

Simple attributes are those attributes which can not be divided into subparts
Composite attributes, on the other hand, can be divided into subparts.
For example, an attribute name could be structured as a composite attribute consisting of first
name, middle initial, andlast name. Usingcomposite attributes in a design schema is a good
choice if a user will wish to refer to an entire attribute on some occasions, and to only a
component of the attribute on other occasions. Suppose we were to to add an address to the
student entity-set. The address can be defined as the composite attribute address with the
attributes street, city, state, and zip code.3 Composite attributes help us to group together related
attributes, making the modeling cleaner.

Note also that a composite attribute may appear as a hierarchy.

2) Single valued and Multivalued Attribute:

The attributes which have a single value for a particular entity are single valued
For instance, the student ID attribute for a specific student entity refers to only one student ID.
Such attributes are said to be single valued.

There may be instances where an attribute has a set of values for a specific entity. Suppose we
add to the instructor entity set a phone number attribute. An instructor may have zero, one, or
several phone numbers, and different instructors may have different numbers of phones.

3) Derived Attributes:
The value for this type of attribute can be derived from the values of other related attributes or
entities. For instance, let us say that the instructor entity set has an attribute students advised,
which represents how many students an instructor advises. We can derive the value for this
attribute by counting the number of student entities associated with that instructor.

Constraints:

An E-R enterprise schema may define certain constraints to which the contents of a database
must conform. In this section, we examine mapping cardinalities and participation constraints.

Mapping Cardinalities

2.2
2019-20
Computer application -II

Mapping cardinalities, or cardinality ratios, express the number of entities to which another
entity can be associated via a relationship set. Mapping cardinalities are most useful in
describing binary relationship sets, although they can contribute to the description of relationship
sets that involve more than two entity sets. In this section, we shall concentrate on only binary
relationship sets.

For a binary relationship set R between entity sets A and B, the mapping cardinality must be one
of the following:
• One-to-one. An entity in A is associated with at most one entity in B, and an entity in B
is associated with at most one entity in A.
• One-to-many. An entity in A is associated with any number (zero or more) of entities in
B. An entity in B, however, can be associated with at most one entity in A.

• Many-to-one. An entity in A is associated with at most one entity in B. An entity in B,

however, can be associated with any number (zero or more) of entities in A.
• Many-to-many. An entity in A is associated with any number (zero or more) of entities in
B, and an entity in B is associated with any number (zero or more) of entities in A.

2.3
2019-20
Computer application -II

2. Participation Constraints:
The participation of an entity set E in a relationship set R is said to be total if every entity in E
participates in at least one relationship in R. If only some entities in E participate in relationships
in R, the participation of entity set E in relationship R is said to be partial.

For example, we expect every student entity to be related to at least one instructor through the
advisor relationship. Therefore the participation of student in the relationship set advisor is total.

Keys
We must have a way to specify how entities within a given entity set are distinguished.
Conceptually, individual entities are distinct; from a database perspective, however, the
differences among them must be expressed in terms of their attributes.

Therefore, the values of the attribute values of an entity must be such that they can uniquely
identify the entity. In other words, no two entities in an entity set are allowed to have exactly the
same value for all attributes.

The notion of a key for a relation schema, applies directly to entity sets. That is, a key for an
entity is a set of attributes that suffice to distinguish entities from each other.

ENTITY-RELATIONSHIP Diagrams:

An E-R diagram can express the overall logical structure of a database graphically. E-R diagrams
are simple and clear—qualities that may well account in large part for the widespread use of the
E-R model.

Such diagram consists of following major components:

• Rectangles divided into two parts represent entity sets. The first part, which in this
textbook is shaded blue, contains the name of the entity set. The second part contains the
names of all the attributes of the entity set.
• Diamonds represent relationship sets.
• Undivided rectangles represent the attributes of a relationship set. Attributes that are part
of the primary key are underlined.
• Lines link entity sets to relationship sets.
• Dashed lines link attributes of a relationship set to the relationship set.
• Double lines indicate total participation of an entity in a relationship set.
• Double diamonds represent identifying relationship sets linked to weak entity sets.

Consider E-R diagram, which consists of two entity sets, Customer and Loan, related through
binary relationship borrower. The attribute associated with customer are Customer_ID,
customer_name,Customer_street and Customer_city. The Attributes associated with Loan are
Loan_no, amount. The relation set borrower may be many to many, many to one or one to one.

E-R Diagram for a Banking Enterprise:

2.4
2019-20
Computer application -II

The figure alongside is the full representation of the conceptual model of bank, expressed in
terms of E-R concept. The diagram includes entity sets, attributes, relationship sets & mapping
cardinalities.

The E-R diagram for our specific view of banking enterprise is very complex. E-R diagram for
realistic enterprise that cannot be drawn on single page & must be split up at multiple points.

Reducing E-R Diagrams to Tables:

We can represent a database that confirms to an E-R database schema by a collection of

relational schema. For each entity set & for each relationship set the database is a unique relation
schema to which we assign the name of corresponding entity set or relationship.

Result: Study of Entity Relationship model is done.

2.5
2019-20
Computer application -II

Experiment No. 3

Aim: To create table and implement various DDL (Data Definition Language) commands.

Introduction:

Data-Definition Language
We specify a database schema by a set of definitions expressed by a special language called a
data-definition language (DDL). The DDL is also used to specify additional properties of the
data.
We specify the storage structure and access methods used by the database system by a set of
statements in a special type of DDL called a data storage and definition language. These
statements define the implementation details of the database schemas, which are usually hidden
from the users.

The data values stored in the database must satisfy certain consistency constraints. For example,
suppose the university requires that the account balance of a department must never be negative.
The DDL provides facilities to specify such constraints. The database system checks these
constraints every time the database is updated. In general, a constraint can be an arbitrary
predicate pertaining to the database.However, arbitrary predicates may be costly to test.

Verview of SQL:

IBM developed the original version of SQL, originally called Sequel, as part of the System R
project in the early 1970s. The Sequel language has evolved since then, and its name has
changed to SQL (Structured Query Language). Many products now support the SQL language.
SQL has clearly established itself as the standard relational database language.

In 1986, the American National Standards Institute (ANSI) and the International Organization
for Standardization (ISO) published an SQL standard, called SQL-86.
ANSIpublishedanextendedstandard for SQL, SQL-89, in 1989. The next version of the
standardwas SQL-92 standard, followed by SQL:1999, SQL:2003, SQL:2006, and most recently
SQL:2008. The bibliographic notes provide references to these standards.

SQL Data Definition:

The set of relations in a database must be specified to the system by means of a data-definition
language (DDL). The SQL DDL allows specification of not only a set of relations, but also
information about each relation, including:

• The schema for each relation.

• The types of values associated with each attribute.
• The integrity constraints.
• The set of indices to be maintained for each relation.
• The security and authorization information for each relation.
• The physical storage structure of each relation on disk.

3.1
2019-20
Computer application -II

Basic Datatypes:

1) Char (n): A fixed-length character string with user-specified length n. The full form,
character, can be used instead.
2) varchar(n): A variable-length character string with user-specified maximum
length n. The full form, character varying, is equivalent.
3) int: An integer (a finite subset of the integers that ismachine dependent). The full form,
integer, is equivalent.
4) smallint: A small integer (a machine-dependent subset of the integer type).
5) numeric(p, d): A fixed-point numberwith user-specified precision. The number consists of p
digits (plus a sign), and d of the p digits are to the right of the decimal point. Thus, numeric(3,1)
allows 44.5 to be stored exactly, but neither 444.5 or 0.32 can be stored exactly in a field of this
type.
6) real, double precision: Floating-point and double-precision floating-point numbers with
machine-dependent precision.
7) float(n): A floating-point number, with precision of at least n digits.
8) date : In mysql , date is displayed in the format YYYY-MM-DD eg: 2015-01-29.
9) Time: Time format in mysql is HH:MM:SS. Eg: 23:59:59.
10)Timestamp: Time stamp datat is used for values that contain both date and time parts. Time
stamp has a range ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:14:07.

DDL Mysql Commands:

Data Definition Language normally known as DDL are the mysql statements or query which are
used to define and modify the structure of database schema or tables. DDL have these
commands: CREATE, ALTER, TRUNCATE, DROP, COMMENT, RENAME.

1) CREATE :
It creates a table with the given name. You must have a CREATE privelege for the table:

Syntax:
Create table <table_name> (attr1 datatype1, attr2 datatype2, attr3 datatype3, ……);

Note: To check the ouptput for each command DESCRIBE TABLE command is used. Following is the
Syntax for DESCRIBE command:
DESC <table_name>;

2)ALTER:
ALTER query is used to make modifications like Key constraints, Column size, etc in the table
structure.

2.1) To add new column in the existing table

Syntax:
ALTER TABLE <table_name> add column <column_name> datatype;

3.2
2019-20
Computer application -II

2.2) To delete attribute/ column from existing table:

Syntax:
ALTER TABLE <table_name> drop column <column_name>;
2.3) To change attribute name:
Syntax:
ALTER TABLE <table_name> change <old_column_name> <new_column_name> datatype;

2.4) To change Table name:

Syntax:
ALTER TABLE <table_name> rename to <new_table_name>;

2.5) To change Datatype of attribute:

Syntax:
ALTER TABLE <table_name> modify <attribute_name> datatype;

3) DROP:
DROP command is used to delete database table. It delete all the records and database table
schema .
Syntax:
DROP TABLE table_name;

4) COMMENT:
COMMENT can be used in mysql by three types which are given below

# Comment line goes here

— Cooment line goes here

/* Comment lne goes here*/

5) RENAME
RENAME command can be used to rename the database table. Here is an example for this
statement.

RENAME TABLE `employee` TO `employees` ;

RESULT:
Study of all the DDL statements is completed and implemented successfully.

3.3
2019-20
Computer Application- II Experiment no 4

EXPERIMENT NUMBER 04
AIM: To implement DML commands on created table.

1) Data Manipulation Language:

A data-manipulation language (DML) is a language that enables users to access or
manipulate data as organized by the appropriate data model. The types of access are:

• Retrieval of information stored in the database

• Insertion of new information into the database
• Deletion of information from the database
• Modification of information stored in the database

There are basically two types:

• Procedural DMLs require a user to specify what data are needed and how to get those data.

• Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify

what data are needed without specifying how to get those data. Declarative DMLs are usually
easier to learn and use than are procedural DMLs. However, since a user does not have to
specify how to get the data, the database system has to figure out an efficient means of
accessing data

2) DML Queries:
3.1 Insert Command:
The command is used to insert values in the created table in the form of rows.
Syntax:
Insert into <table_name> values (val1,val2,val3…..valn);

3.2 Update command:

It update the values inserted in the rows of given table.

1.To update all the rows of column:

Syntax:
Update <table_name> set <att1>=<val1>;
2. To update specific row in inserted table:
Syntax:
Update <table_name> set <att1>=<val1> where <predicate>;

3.3 Select:
This command is used to select and view the table in various ways. It is used to retrieve and
extract various information from a given table.

1. To view complete table:

Syntax:
Select * from <table_name>;
2. To view specific columns in table:

2019-20 1
Computer Application- II Experiment no 4

Syntax:
Select <col1,col2,col3….> from <table_name>;
3.To retrieve data based on condition:
Syntax:
Select<col1,col2….> from <table_name> where <condition>;

3.4 Delete Command:

It is used to delete a particular row with certain predicate of the given value
Syntax:
Delete from <table_name> where <predicate>;

3.5 Truncate Command:

This command is used to delete whole table and its values.
Syntax:
Truncate table <table_name>;

Result:
Thus all the DML queries are studied and implemented on table.

2019-20 2
Computer Application- II Experiment No 5

Experiment No. 5
Aim: To implement various constraints on table

SQL constraints:
SQL constraints are used to specify rules for the data in a table. If there is any violation between
the constraint and the data action, the action is aborted by the constraint. Constraints can be
specified when the table is created (inside the CREATE TABLE statement) or after the table is
created (inside the ALTER TABLE statement). Constraints are the rules enforced on data
columns on 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 could be column level or table
level. Column level constraints are applied only to one column, whereas table level constraints
are applied to the whole table. Constraints can be divided into following two types: Column level
constraints: limits only column data Table level constraints: limits whole table data Constraints
are used to makesure that the integrity of data is maintained in the database. Following are the
most used constraints that can be applied on table.

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

• INDEX

• DEFAULT

NOT NULL constraint: Indicates that a column cannot store NULL value. By default, a column
can hold NULL values. If you do not want a column to have a NULL value, then you need to
define such constraint on this column specifying that NULL is now not allowed for that column.
A NULL is not the same as no data, rather, it represents unknown data.

Syntax:
Create table <table name> (column1_name datatype NOT NULL, column2_name datatype);

UNIQUE constraint: The UNIQUE Constraint prevents two records from having identical
values in a particular column. A UNIQUE constraint field will not have duplicate data. In the
CUSTOMERS table, for example, you might want to prevent two or more people from having
identical age.

2019-20 1
Computer Application- II Experiment No 5

Syntax:

Create table <table name> (column1_name datatype NOT NULL UNIQUE, column2_name
datatype NOT NULL);

Alter table <table_name> modify column_name datatype UNIQUE;

PRIMARY KEY constraint:

A primary key is a field in a table which uniquely identifies each row/record in a database table.
Primary keys must contain unique values. A primary key column cannot have NULL values. A
table can have only one primary key, which may consist of single or multiple fields. When
multiple fields are used as a primary key, they are called a composite key/candidate key. If a
table has a primary key defined on any field(s), then you cannot have two records having the
same value of that field(s).

Syntax:

Create table <table name> (column1_name datatype NOT NULL UNIQUE, column2_name
datatype NOT NULL, PRIMARY KEY (column1_name));

Alter table <table_name> add primary key(coumn_name);

FOREIGN KEY constraint: A foreign key is a key used to link two tables together. This is
sometimes called a referencing key. Foreign Key is a column or a combination of columns
whose values match a Primary Key in a different table. The relationship between 2 tables
matches the Primary Key in one of the tables with a Foreign Key in the second table. If a table
has a primary key defined on any field(s), then you cannot have two records having the same
value of that field(s).

Syntax:
❖ Create table <table_name1> (column1.1_name datatype NOT NULL UNIQUE,
column1.2_name datatype NOT NULL, PRIMARY KEY (column1_name), FOREIGN KEY
(column_name2.3) references <table_name2> );
❖ Alter table <table_name1> add foreign key (cloumn_name) references <table_name2>;
CHECK constraint: The CHECK Constraint enables a condition to check the value being
entered into a record. If the condition evaluates to false, the record violates the constraint and
isn't entered into the table. CHECK constraint is used to limit values of column between a range.

Syntax:
Create table <table name> (column1_name datatype CHECK (<predicate>), column2_name
datatype NOT NULL, PRIMARY KEY (column1_name));

INDEX constraint:

The INDEX is used to create and retrieve data from the database very quickly. Index can be
created by using single or group of columns in a table. When index is created, it is assigned a

2019-20 2
Computer Application- II Experiment No 5

ROWID for each row before it sorts out the data. Proper indexes are good for performance in
large databases, but you need to be careful while creating index. Selection of fields depends on
what you are using in your SQL queries.

Syntax:
Create INDEX <index_name> on <table_name> (column1,column2……);

DEFAULT constraint:

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.
Syntax:
create table <table_name> (column1 datatype, column2 datatype, column3 datatype DEFAULT
‘value1’);
NOTE:

1. All the constraints {except NOT NULL constraint} can be applied after creating table using
ALTER TABLE command. Also, can be deleted using DROP TABLE command.

2. To view the implemented query output use desc command.

RESULT: All the constraints are studied and applied on database table.

2019-20 3
COMPUTER APPLICATION –II Ex. No.6

Experiment No:6
Aim: To implement concept of index and views.
Theory:
INDEX:

A database index is a data structure that improves the speed of data retrieval
operations on a database table at the cost of additional writes and storage space to
maintain the index data structure. Indexes are used to quickly locate data without
having to search every row in a database table every time a database table is
accessed. Indexes can be created using one or more columns of a database table,
providing the basis for both rapid random lookups and efficient access of ordered
records.
An index is a copy of select columns of data from a table that can be searched very
efficiently that also includes a low-level disk block address or direct link to the
complete row of data it was copied from. Some databases extend the power of
indexing by letting developers create indices on functions or expressions
Information in the DBMS files is stored in form of records. Every record is
equipped with some key field, which helps it to be recognized uniquely.
Indexing is a data structure technique to efficiently retrieve records from database
files based on some attributes on which the indexing has been done. Indexing in
database systems is similar to the one we see in books.
Indexing is defined based on its indexing attributes. Indexing can be one of the
following types:

• Primary Index: If index is built on ordering 'key-field' of file it is called

Primary Index. Generally it is the primary key of the relation.
• Secondary Index: If index is built on non-ordering field of file it is called
Secondary Index.
• Clustering Index: If index is built on ordering non-key field of file it is
called Clustering Index.
Ordering field is the field on which the records of file are ordered. It can be
different from primary or candidate key of a file.

1
2017-2018
COMPUTER APPLICATION –II Ex. No.6

Ordered Indexing is of two types:

• Dense Index
• Sparse Index

Dense Index
In dense index, there is an index record for every search key value in the database.
This makes searching faster but requires more space to store index records itself.
Index record contains search key value and a pointer to the actual record on the
disk.

Sparse Index
In sparse index, index records are not created for every search key. An index
record here contains search key and actual pointer to the data on the disk. To
search a record we first proceed by index record and reach at the actual location of
the data. If the data we are looking for is not where we directly reach by following
index, the system starts sequential search until the desired data is found.

Multilevel Index
Index records are comprised of search-key value and data pointers. This index
itself is stored on the disk along with the actual database files. As the size of
database grows so does the size of indices. There is an immense need to keep the
2
2017-2018
COMPUTER APPLICATION –II Ex. No.6

index records in the main memory so that the search can speed up. If single level
index is used then a large size index cannot be kept in memory as whole and this
leads to multiple disk accesses.

The SQL CREATE INDEX statement:

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole
table.
An index can be created in a table to find data more quickly and efficiently.The
users cannot see the indexes; they are just used to speed up searches/queries.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:
3
2017-2018
COMPUTER APPLICATION –II Ex. No.6

CREATE INDEX index_name

ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

To display index use following query

SYNTAX: show index from <table_name>;

VIEWS
A view is nothing more than a SQL statement that is stored in the database with an
associated name. A view is actually a composition of a table in the form of a
predefined SQL query.

A view can contain all rows of a table or select rows from a table. A view can be
created from one or many tables which depends on the written SQL query to create
a view.

Views, which are kind of virtual tables, allow users to do the following:
• Structure data in a way that users or classes of users find natural or intuitive.

• Restrict access to the data such that a user can see and (sometimes) modify
exactly what they need and no more.

• Summarize data from various tables which can be used to generate repor

Creating Views:
Database views are created using the CREATE VIEW statement. Views can be
created from a single table, multiple tables, or another view.
To create a view, a user must have the appropriate system privilege according to
the specific implementation.
The basic CREATE VIEW syntax is as follows:
4
2017-2018
COMPUTER APPLICATION –II Ex. No.6

CREATE VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in very similar way as
you use them in normal SQL SELECT query.

For output please use select query.

RESULT:
The concept of INDEX and VIEWS is studied and implemented successfully.

5
2017-2018
Computer application – II Experiment no 7

Experiment No.: 7

Aim: To implement various aggregate functions

Theory:
Aggregate Functions:
Aggregate Functions are functions that take a collection (a set or multiset) of
values as input and return a single value. SQL offers fine built in aggregate
functions:
• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count
The input to sum and avg must be a collection of numbers, but the other operators
can operate on collection of non-numeric data types such as strings as well.

1) Average: (avg)
This operator is used to find average of the numbers in the table the query might
be in relation with a single attribute containing single tuple with numerical valu.
- Select avg(attribute_name) from <table_name>;
- Select avg(attribute_name) from <table_name> where <predicate>;
E.g.:

2) Minimum: (min)
It finds the minimum value from the numerical column of the table. A particular
predicate must be given to find minimum value at a particular condition.
- Select min(attribute_name) from <table_name>
- Select min(attribute_name) from <table_name> where <predicate>;

3) Maximum: (max)
It is used to find maximum value from the column of table or the maximum value
of a particular attribute at some condition.
- Select max(attribute_name) from <table_name>;
- Select min(attribute_name) from <table_name> where <predicate>;

2019-20 1
Computer application – II Experiment no 7

-
4) Total: (sum)
It is used to add the numbers in a column. It also adds the number with common
attribute
- Select sum(attribute_name) from <table_name>;
- Select min(attribute_name) from <table_name> where <predicate>;
Eg:

5) Count: (count)
It is used to count the total number of rows in the table. It is classified in three
types.
-Select count(*) from <table_name>
- Select count(attribute name) from <table_name>;
- Select count (distinct <attribute_name>) from <table_name>;

Result: Vagarious aggregate functions are studied and implemented.

2019-20 2
COMPUTER APPLICATION –II Experiment No. 8

Experiment No: 8
Aim: To study various set operations

THEORY:

Following are the various set operations

UNION
INTERSECT
EXCEPT

UNION:
The SQL UNION clause/operator is used to combine the results of two or more SELECT statements
without returning any duplicate rows.
To use UNION, each SELECT must have the same number of columns selected, the same number of
column expressions, the same data type, and have them in the same order, but they do not have to be
the same length.
SYNTAX:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]

INTERSECT:
The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows
only from the first SELECT statement that are identical to a row in the second SELECT statement. This
means INTERSECT returns only common rows returned by the two SELECT statements.
Just as with the UNION operator, the same rules apply when using the INTERSECT operator. MySQL
does not support INTERSECT operator
SYNTAX:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]

2019-20 1
COMPUTER APPLICATION –II Experiment No. 8

EXCEPT:
The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from
the first SELECT statement that are not returned by the second SELECT statement. This means
EXCEPT returns only rows, which are not available in second SELECT statement.
Just as with the UNION operator, the same rules apply when using the EXCEPT operator. MySQL does
not support EXCEPT operator.

SYNTAX:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]

The SET OPERATIONS automatically eleiminates the duplicates, unlike the se;ect clause. If we want
to retain duplicates we must write ALL after set operation clause (ie. UNION ALL, INTERSECT ALL,
EXCEPT ALL).

RESULT:

Various SET OPERATORS are studied and implemented on SQL.

2019-20 2
Computer Application- II

Experiment No. 9(A)

Aim: To implement the concept of ORDER BY, GROUP BY and HAVING clause.

SQL clauses:
In MYSQL we use various clauses to get data based on different conditions. We have following
type of SQL clauses to study.
• ORDER BY
• GROUP BY
• HAVING
ORDER BY clause:

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on
one or more columns. Some database sorts query results in ascending order by default. You can
use more than one column in the ORDER BY clause. Make sure whatever column you are using
to sort, that column should be in column-list.

Syntax:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Example:

GROUP BY clause:

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange
identical data into groups. The GROUP BY clause follows the WHERE clause in a SELECT
statement and precedes the ORDER BY clause.

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the
conditions in the WHERE clause and must precede the ORDER BY clause if one is used.
Syntax:

SELECT column1, column2

FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

2019-20 9.1
Computer Application- II

Example:

HAVING clause:

A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows
where aggregate values meet the specified conditions. It was added to the SQL language because
the WHERE keyword could not be used with aggregate functions.
The HAVING clause enables you to specify conditions that filter which group results appear in
the final results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause
places conditions on groups created by the GROUP BY clause.

Syntax:

SELECT column1, column2

FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example:

RESULT:

Study and implementation of SQL clauses is performed.

2019-20 9.2
Computer Application- II

Experiment No. 9 (B)

Aim: To implement and perform various STRING operations.

SQL specifies strings by enclosing them in single quotes, for example, ’Computer’. A single
quote character that is part of a string can be specified by using two single quote characters; for
example, the string “It’s right” can be specified by “It”s right”.

The SQL standard specifies that the equality operation on strings is case sensitive; as a result the
expression “’comp. sci.’ = ’Comp. Sci.’” evaluates to false. However, some database systems,
such as MySQL and SQL Server, do not distinguish uppercase from lowercase when matching
strings; as a result “’comp. sci.’ = ’Comp. Sci.’” would evaluate to true on these databases. This
default behavior can, however, be changed, either at the database level or at the level of specific
attributes.

SQL also permits a variety of functions on character strings, such as concatenating (using “_”),
extracting substrings, finding the length of strings, converting strings to uppercase (using the
function upper(s) where s is a string) and lowercase (using the function lower(s)), removing
spaces at the end of the string (using trim(s)) and so on. There are variations on the exact set of
string functions supported by different database systems. See your database system’s manual for
more details on exactly what string functions it supports.

Pattern matching can be performed on strings, using the operator like. We describe patterns by
using two special characters:

• Percent (%): The % character matches any substring.

• Underscore ( _ ): The character matches any character.

Patterns are case sensitive; that is, uppercase characters do not match lowercase characters, or
vice versa. To illustrate pattern matching,we consider the following

examples:

• ’Intro%’ matches any string beginning with “Intro”.

• ’%Comp%’ matches any string containing “Comp” as a substring, for example, ’Intro. to
Computer Science’, and ’Computational Biology’.

• ’_ _ _’ matches any string of exactly three characters.

• ’_ _ _ %’ matches any string of at least three characters.

2019-20 9.3
Computer Application- II

SQL expresses patterns by using the like comparison operator. Consider the query “Find the
names of all departments whose building name includes the substring ‘Watson’.” This query can
be written as:

select dept name

from department
where building like ’%Watson%’;

Result:
Thus we have implemented and studied the string operators.

2019-20 9.4
Computer Application- II

Experiment No. 10
Aim: To implement the concept Nested Queries.

THEORY:
A Sub query or Inner query or Nested query is a query within another SQL query and embedded
within the WHERE clause.

A Sub query is used to return data that will be used in the main query as a condition to further
restrict the data to be retrieved.

Sub queries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along
with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that sub queries must follow:

• Sub queries must be enclosed within parentheses.

• A Sub query can have only one column in the SELECT clause, unless multiple columns
are in the main query for the Sub query to compare its selected columns.

• An ORDER BY cannot be used in a Sub query, although the main query can use an
ORDER BY. The GROUP BY can be used to perform the same function as the ORDER
BY in a Sub query.

• Subqueries that return more than one row can only be used with multiple value operators,
such as the IN operator.

• The SELECT list cannot include any references to values that evaluate to a BLOB,
ARRAY, CLOB, or NCLOB.

• A Sub query cannot be immediately enclosed in a set function.

• The BETWEEN operator cannot be used with a Sub query; however, the BETWEEN
operator can be used within the Sub query.

Subqueries with the SELECT Statement:

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows:

SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

2019-20 10.1
Computer Application- II

Example:
Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | 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 |
+----+----------+-----+-----------+----------+

Now, let us check following Sub query with SELECT statement:

SQL> SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500);

This would produce the following result:

+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

Subqueries with the INSERT Statement:

Subqueries also can be used with INSERT statements. The INSERT statement uses the data
returned from the Sub query to insert into another table. The selected data in the Sub query can
be modified with any of the character, date or number functions.

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ]) ]

SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]

Example:
Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to
copy complete CUSTOMERS table into CUSTOMERS_BKP, following is the syntax:

SQL> INSERT INTO CUSTOMERS_BKP

SELECT * FROM CUSTOMERS

2019-20 10.2
Computer Application- II

WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

Sub queries with the UPDATE Statement:

The Sub query can be used in conjunction with the UPDATE statement. Either single or multiple
columns in a table can be updated when using a Sub query with the UPDATE statement.

The basic syntax is as follows:

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

Example:
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS
table.

Following example updates SALARY by 0.25 times in CUSTOMERS table for all the customers
whose AGE is greater than or equal to 27:

SQL> UPDATE CUSTOMERS

SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

This would impact two rows and finally CUSTOMERS table would have the following records:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 125.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Sub queries with the DELETE Statement:

The sub query can be used in conjunction with the DELETE statement like with any other
statements mentioned above.

The basic syntax is as follows:

DELETE FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME

2019-20 10.3
Computer Application- II

FROM TABLE_NAME)
[ WHERE) ]

Example:
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS
table.

Following example deletes records from CUSTOMERS table for all the customers whose AGE
is greater than or equal to 27:

SQL> DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE > 27 );

This would impact two rows and finally CUSTOMERS table would have the following records:

+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

Result: The concept of nestet query in SQL is implemented.

2019-20 10.4