CS3492 DBMS (Unit 1)

Unit 1: Relational Databases
Syllabus
Purpose of Database System - Views of data - Data Models - Database System
Architecture - Introduction to relational databases - Relational Model - Keys
Relational Algebra - SQL fundamentals - Advanced SQL features - Embedded
SQL- Dynamic SQL.
Part I: Introduction to DBMS
Introduction
• Definition: A Database Management System (DBMS) is a collection of
interrelated data and various programs that are used to handle that data.
• The primary goal of DBMS is to provide a way to store and retrieve the required
information from the database in convenient and efficient manner.
Database System Applications
There are wide range of applications that make use of database systems. Some of
the applications are -
1) Accounting
2) Manufacturing
3) Universities
4) Banking
5) Reservation systems
6) Telecommunication
Purpose of Database System
AU: May-07, 12, Dec.-04, Marks 8
• Earlier database systems are created in response to manage the commercial data.
These data are typically stored in files.
Before introducing database management system, this file processing system was
in use. However, such a system has many drawbacks. Let us discuss them
Disadvantages of Traditional File Processing System
The traditional file system has following disadvantages:
1) Data redundancy: Data redundancy means duplication of data at several
places. Since different programmers create different files and these files might
have different structures, there are chances that some information may appear
repeatedly in some or more format at several places.
2) Data inconsistency: Data inconsistency occurs when various copies of same
data may no longer get matched. For example, changed address of an employee
may be reflected in one department and may not be available for other
department.
3) Difficulty in accessing data: The conventional file system does not allow to
retrieve the desired data in efficient and convenient manner.
4) Data isolation: As the data is scattered over several files and files may be in
different formats, it becomes to retrieve the desired data from the file for writing
the new application.
5) Integrity problems: Data integrity means data values entered in the database
fall within a specified range and are of correct format. With the use of several
files enforcing such constraint on the data becomes difficult.
6) Atomicity problems: An atomicity means particular operation must be carried
out sib entirely or not at all with the database. It is difficult to ensure atomicity in
conventional file processing system.
7) Concurrent access anomalies: For efficient execution, multiple users update
data simultaneously, in such a case data need to be synchronized. As in traditional
file systems, data is distributed over multiple files, one cannot access these files
concurrently.
8) Security problems: Every user is not allowed to access all the data of
database no system. Since application program in file system are added in an ad
hoc manner, enforcing such security constraints become difficult.
Database systems offer solutions to all the above mentioned problems.
Advantages of Database Systems
1) DBMS removes the data redundancy that means there is no duplication of data
in database.
2) DBMS allows to retrieve the desired data in required format.
3) Data can be accessed efficiently using a simple query language.
4) The data integrity can be maintained.
Disadvantages of Database Systems
1) Complex design
2) Hardware and software cost
Difference between Database System and Conventional File System
Review Questions
1. Compare file system with database system. AU: May-07, Marks 8, May-12, Marks 2
2.What are the advantages and disadvantages of DBMS?
VIEWS OF DATA AU: May-16, Marks 16

• Database is a collection of interrelated data and set of programs that allow users
to access or modify the data.
• Abstract view of the system is a view in which the system hides certain details
of how the data are stored and maintained.
• The main purpose of database systems is to provide users with abstract view of
the data.
• The view of the system helps the user to retrieve data efficiently.
• For simplifying the user interaction with the system there are several levels of
abstraction - these levels are - Physical level, logical level and view level.
Data Abstraction
Data abstraction: Data abstraction means retrieving only required amount of
information /of the system and hiding background details.
There are several levels of abstraction that simplify the user interactions with the
system. These are
1) Physical level:
• This is the lowest level.
• This level describes how actually the data are stored.
• This level describes complex low level data structures.
2) Logical level:
• This is the next higher level, which describes the what data are stored in
database.
• This level also describes the relationship among the data.
• The logical level thus describes then entire database in terms of small number
of relatively simple structures.
• The database administrators use logical level of abstraction for deciding what
information to keep in database.
3) View level:
This is highest level of abstraction that describes only part of the entire database.
• The view level can provide the access to only part of the database.
• This level helps in simplifying the interaction with the system.
• The system can provide multiple views of the same system.
• Clerk at the reservation system, can see only part of the database and can access
the required information of the passenger.
INSTANCES AND SCHEMAS
Schema: The overall design of the database is called schema
For example - In a program we do variable declaration and assignment of values
to the variable. The variable declaration is called schema and the value assigned
to the variable is called instance. The schema for the student record can be
Instances: When information is inserted or deleted from the database then the
database gets changed. The collection of information at particular moment is
called instances. For example - following is an instance of student database
Types of Schema: The database has several schema based on the levels of
abstraction.
(1) Physical Schema: The physical schema is a database design described at the
physical level of abstraction.
(2) Logical Schema: The logical schema is a database design at the logical level
of abstraction.
(3) Subschema: A database may have several views at the view level which are
called subschemas.
DATABASE LANGUAGES
There are two types of languages supported by database systems. These are
(1) DDL
• Data Definition Language (DDL) is a specialized language used to specify a
database schema by a set of definitions.
• It is a language which is used for creating and modifying the structures of
tables, views, indexes and so on.
• DDL is also used to specify additional properties of data.
• Some of the common commands used in DDL are - CREATE, ALTER, DROP.)
The main use of CREATE command is to build a new table. Using ALTER
command, the users can add up some additional column and drop existing
columns. Using DROP command, the user can delete table or view.
(2) DML
• DML stands for Data Manipulation Language.
• This language enables users to access or manipulate data as organized by
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 database.
• There are two types of DML -
• Procedural DML - Require a user to specify what data are needed and how to
get those data.
• Declarative DML - Require a user to specify what data are needed without of
au aw specifying how to get those data.
• Query is a statement used for requesting the retrieval of information. This
retrieval of information using some specific language is called query language.
Review Question
1. Briefly explain about views of data. AU: May-16, Marks 16
DATA MODELS AU: Dec.-14, May-19, Marks 13

Definition: It is a collection of conceptual tools for describing data, relationships
among data, semantics (meaning) of data and constraints.
• Data model is a structure below the database.
• Data model provides a way to describe the design of database at physical, logical
and view level.
• There are various data models used in database systems and these are as follows
(1) Relational model:
• Relation model consists of collection of tables which stores data and also
represents the relationship among the data.
• Table is also known as relation.
• The table contains one or more columns and each column has unique name.
• Each table contains record of particular type, and each record type defines a
fixed number of fields or attributes.
• For example - Following figure shows the relational model by showing the
relationship between Student and Result database. For example - Student Ram
lives in city Chennai and his marks are 78. Thus the relationship between these
two databases is maintained by the SeatNo. Column
Advantages:
(i) Structural Independence: Structural independence is an ability that allows
us to make changes in one database structure without affecting other
(ii)Conceptual Simplicity: The relational model allows the designer to simply
focus on logical design and not on physical design. Hence relational models are
conceptually simple to understand.
(iii) Query Capability: Using simple query language (such as SQL) user can get
information from the database or designer can manipulate the database structure.
(iv) Easy design, maintenance and usage: The relational models can be
designed logically hence they are easy to maintain and use.
Disadvantages:
(i) Relational model requires powerful hardware and large data storage devices.
(ii) May lead to slower processing time.
(iii) Poorly designed systems lead to poor implementation of database systems.
2) Entity relationship model:
• As the name suggests the entity relationship model uses collection of basic
objects called entities and relationships.
• The entity is a thing or object in the real world.
• The entity relationship model is widely used in database design.
• For example - Following is a representation of Entity Relationship model in
which the relationship works_for is between entities Employee and Department.
Advantages:
i) Simple: It is simple to draw ER diagram when we know entities and
relationships.
ii) Easy to understand: The design of ER diagram is very logical and hence they
are easy to design and understand.
iii) Effective: It is effective communication tool.
iv) Easy conversion: ER model can be converted easily into other type of
models.
Disadvantages:
i) Loss of information: While drawing ER model some information can be
hidden or lost.
ii) Limited relationships: The ER model can represent limited relationships as
compared to other models.
iii) No industry standard: There is no industry standard for notations of ER
diagram.
(3) Object Based Data Model:
• The object oriented languages like C++, Java, C# are becoming the dominant in
software development.
• This led to object based data model.
• To The object based data model combines object oriented features with
relational data model.
Advantages:
i) Enriched modelling: The object based data model has capability of modelling
the real world objects.
ii) Reusability: There are certain features of object oriented design such as
inheritance, polymorphism which help in reusability.
iii) Support for schema evolution: There is a tight coupling between data and
applications, hence there is strong support for schema evolution.
Disadvantages:
i) Lack of universal data model: There is no universally agreed data model for
an object based data model, and most models lack a theoretical foundation.
ii) Complex: More functionalities present in object based data model make the
design complex.
(4) Semi-structured data model:
• The semi-structured data model permits the specification of data where
individual data items of same type may have different sets of attributes.
• The Extensible Markup Language (XML) is widely used to represent semi-
structured data model.
Advantages
i) Data is not constrained by fixed schema.
ii) It is flexible.
iii) It is portable.
Disadvantages
i) Queries are less efficient than other types of data model.
Review Questions
1. Write short note on: Data model and its types. AU: Dec.-14, Marks 8
2 Explain three different groups of data models with suitable examples. AU: May-19, Marks13
DATABASE SYSTEM ARCHITECTURE
AU: May-12,13,14,16,17, Dec.- 08,15,17,19, Marks 16
• The typical structure of typical DBMS is based on relational data model as
shown in Fig 1.5.1.
• Consider the top part of Fig. 1.5.1. It shows application interfaces used by naïve
users, application programs created by application programmers, query tools used
by sophisticated users and administration tools used by database administrator.
• The lowest part of the architecture is for disk storage.
• The two important components of database architecture are - Query processor
and storage manager.
Query processor:
• The interactive query processor helps the database system to simplify and
facilitate access to data. It consists of DDL interpreter, DML compiler and query
evaluation engine.
With the following components of query processor, various functionalities are
performed -
i) DDL interpreter: This is basically a translator which interprets the DDL
statements in data dictionaries.
ii) DML compiler: It translates DML statements query language into an
evaluation plan. This plan consists of the instructions which query evaluation
engine understands.
iii) Query evaluation engine: It executes the low-level instructions generated by
the DML compiler.
• When a user issues a query, the parsed query is presented to a query optimizer,
which uses information about how the data is stored to produce an efficient
execution plan for evaluating the query. An execution plan is a blueprint for
evaluating a query. It is evaluated by query evaluation engine.
Storage manager:
• Storage manager is the component of database system that provides 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 storing, retrieving, and updating datain
the database. The storage manager components include -
i) Authorization and integrity manager: Validates the users who want to access
the data and tests for integrity constraints.
ii) Transaction manager: Ensures that the database remains in consistent despite
of system failures and concurrent transaction execution proceeds without
conflicting.
iii) File manager: Manages allocation of space on disk storage and
representation of the information on disk.
iv) Buffer manager: Manages the fetching of data from disk storage into main
memory. The buffer manager also decides what data to cache in main memory.
Buffer manager is a crucial part of database system.
• Storage manager implements several data structures such as -
i) Data files: Used for storing database itself.
ii) Data dictionary: Used for storing metadata, particularly schema of database.
iii) Indices: Indices are used to provide fast access to data items present in the
database
Review Questions
1. Explain the overall architecture of database system in detail. AU: May-14,17, Dec.-17,
Marks 8, May-16, Marks 16
2. With the help of a neat block diagram explain basic architecture of a database management
system. AU May-12, May-13, Marks 16, Dec-15, Marks 8
3. Explain component modules of a DBMS and their interactions with the architecture AU:
Dec.-08, Marks 10
4. Sketch the typical component modules of DBMS. Indicate and explain interactions between
those modules of the system. AU: Dec.-19, Marks 7
DATA INDEPENDENCE
Definition: Data independence is an ability by which one can change the data at
one level without affecting the data at another level. Here level can be physical,
conceptual or external.
By this property, the structure of the database or the values stored in the database
can be easily modified by without changing the application programs.
There are two types of data independence
1. Physical Independence: This is a kind of data independence which allows the

modification of physical schema without requiring any change to the conceptual
schema. For example - if there is any change in memory size of database server
then it will not affect the logical structure of any data object.
2. Logical Independence: This is a kind of data independence which allows the
modification of conceptual schema without requiring any change to the external
schema. For example - Any change in the table structure such as addition or
deletion of some column does not affect user views.
Three Schema Architecture
• Definition: Database schema is a collection of database objects like tables,
views, indexes and so on associated with one particular database username. This
username is called the schema owner.
• For example Student Schema can be owner of STUDENT and MARKS tables.
The Course schema can be the owner of SUBJECT table.
• The goal of three-schema architecture is to separate the user application from
the physical database.
• The architecture of database is divided into three levels based on three types of
schema - internal schema, conceptual schema or external schema.
1. Internal level:
• It contains internal schema.
• This schema represents the physical storage structure of database.
• This schema is maintained by the software and user is not allowed to modify it.
• This level is closest to the physical storage. It typically describes the record
layout of the files and types of files, access paths etc.
2. Conceptual level:
• It contains conceptual schema.
• This schema hides the details of internal level.
• This level is also called as logical level as it contains the constructs used for
designing the database.
• It contains information like table name, their columns, indexes and constraints,
database operations.
• A representational data model is used to describe conceptual schema when a
database system is implemented.
3. External level:
• It contains the external schema or user views.
• At this level, the user will get to see only the data stored in the database. Either
they will see whole data values or any specific records. They will not have any
information about how they are stored in the databases
• The processes of transforming requests and results between levels are called
mappings. • In the three schema architecture there are two mappings -
1) External - Conceptual Mapping and
2) Conceptual - Internal Mapping.
PART II: RELATIONAL DATABASES
INTRODUCTION TO RELATIONAL DATABASES

• Relation database is a collection of tables having unique names.
• For example - Consider the example of Student table in which the information
about the student is stored.
The above table consists of three column headers RollNo, Name and Phone. Each
row of the table indicates the information of each student by means of his Roll
Number, Name and Phone number.
Similarly consider another table named Course as follows –
Clearly, in above table the columns are CourseID, CourseName and Credits. The
CourseID 101 is associated with the course named Mechanical and associated
with the course of mechanical there are 4 credit points. We can establish the
relationship among the two tables by defining the third table. For example -
Consider the table Admission as
From this third table we can easily find out that the course to which the RollNo
001 is admitted is computer Science.
RELATIONAL MODEL
There are some commonly used terms in Relational Model and those are -
Table or relation: In relational model, table is a collection of data items arranged
in rows and columns. The table cannot have duplicate data or rows. Below is an
example of student table
Tuple or record or row: The single entry in the table is called tuple. The tuple
represents a set of related data. In above Student table there are four tuples. One
of the tuple can be represented as
Attribute or columns: It is a part of table that contains several records. Each

record can be broken down into several small parts of data known as attributes.
For example, the above table consists of four attributes such as RollNo, Name,
Marks and Phone.
Relation schema: A relation schema describes the structure of the relation, with
the name of the relation (i.e. name of table), its attributes and their names and
type.
Relation Instance: It refers to specific instance of relation i.e. containing a
specific set of rows. For example - the following is a relation instance - which
contains the records with marks above 80.
Domain: For each attribute of relation, there is a set of permitted values called
domain. For example - in above table, the domain of attribute Marks is set of all
possible permitted marks of the students.
Atomic: The domain is atomic if elements of the domain are considered to be
indivisible units. For example, in above Student table, the attribute Phone is non-
atomic.
NULL attribute: A null is a special symbol, independent of data type, which
means either unknown or inapplicable. It does not mean zero or blank. For
example - Consider a salary table that contains NULL
Degree: It is nothing but total number of columns present in the relational
database. In given Student table –
The degree is 4.
Cardinality: It is total number of tuples present in the relational database. In
above given table the cardinality is 3
Example 1.9.1 Find out following for given Staff table

i) No of Columns ii) No of tuples iii) Different attributes
iv) Degree v) Cardinality
Solution:
i) No of Columns = 6 ii) No of Tuples = 3
iii) Different attributes are StaffID, Name, Sex, Designation, Salary, DOJ
iv) Degree Total number of columns = 6
v) Cardinality =Total number of rows = 3
KEYS AU: May-06, 07, 12, Dec.-06, Marks 4

Keys are used to specify the tuples distinctly in the given relation.
Various types of keys used in relational model are - Super key, Candidate Keys,
primary keys, foreign keys. Let us discuss them with suitable example
1) Super Key(SK): It is a set of one or more attributes within a table that can
uniquely identify each record within a table. For example - Consider the Student
table as follows-
The superkey can be represented as follows-
Clearly using the (RegNo) and (RollNo, Phone,Name) we can identify the records
uniquely but (Name, Marks) of two students can be same, hence this combination
not necessarily help in identifying the record uniquely.
2) Candidate Key(CK): The candidate key is a subset of superkey. In other
words candidate key is a single attribute or least or minimal combination of
attributes that uniquely identify each record in the table. For example - in above
given Student table, the candidate key is RegNo, (RollNo,Phone). The candidate
key can be
Thus every candidate key is a superkey but every superkey is not a candidate key.
3) Primary Key(PK): The primary key is a candidate key chosen by the database
designer to identify the tuple in the relation uniquely. For example - Consider the
following representation of primary key in the student table
Other than the above mentioned primary key, various possible primary keys can
be (RollNo), (RollNo, Name), (RollNo, Phone)
The relation among super key, candidate key and primary can be denoted by
Candidate Key= Super Key- Primary Key
Rules for Primary Key
(i) The primary key may have one or more attributes.
(ii) There is only one primary key in the relation.
(iii) The value of primary key attribute can not be NULL.
(iv) The value of primary key attributes does not be changable.
4) Alternate Key: The alternate key is a candidate key which is not chosen by
the database designer to uniquely identify the tuples. For example-
5) Foreign key: Foreign key is a single attribute or collection of attributes in one
table that refers to the primary key of other table.
• Thus foreign keys refer to primary key.
• The table containing the primary key is called parent table and the table
containing foreign key is called child table.
• Example-
From above example, we can see that two tables are linked. For instance we could
easily find out that the 'Student CCC has opted for ComputerSci course
Review Question - Explain distinction among the terms primary key, candidate
key, foreign key and super key with suitable example. AU: May-06, 07, 12, Dec.-
06, Marks 4 AU: Dec.-05, Marks 10
INTEGRITY CONSTRAINTS
Database integrity means correctness or accuracy of data in the database.
Constraints means condition (or) rules. A database may have number of integrity
constraints.
Entity Integrity Rule

This rule states that "In the relations, the value of attribute of primary key can not
be null".
The NULL represents a value for an attribute that is currently unknown or is not
applicable for this tuple. The Nulls are always to deal with incomplete or
exceptional data.
The primary key value helps in uniquely identifying every row in the table. Thus
if the users of the database want to retrieve any row from the table or perform any
action on that table, they must know the value of the key for that row. Hence it is
necessary that the primary key should not have the NULL value.
Referential Integrity Rule
• Referential integrity refers to the accuracy and consistency of data within a
relationship.
• The referential integrity rule states that "whenever a foreign key value is used it
must reference a valid, existing primary key in the parent table".
• Example: Consider the situation where you have two tables Employees and
Managers. The Employees table has a foreign key attribute entitled Managed By,
which points to the record for each employee's manager in the Managers table.
Referential integrity enforces the following three rules:
i) You cannot add a record to the Employees table unless the Managed By
attribute points to a valid record in the Managers table. Referential integrity
prevents the insertion of incorrect details into a table. Any operation that doesn't
satisfy referential integrity rule fails.
ii) If the primary key for a record in the Managers table changes, all
corresponding records in the Employees table are modified.
iii) If a record in the Managers table is deleted, all corresponding records in the
Employees table are deleted.
Advantages of Referential Integrity
Referential integrity offers following advantages:
i) Prevents the entry of duplicate data.
ii) Prevents one table from pointing to a non-existent field in another table.
ii) Guaranteed consistency between "partnered" tables.
iii) Prevents the deletion of a record that contains a value referred to by a foreign
key in olds another table.
iv) Prevents the addition of a record to a table that contains a foreign key unless
there is a primary key in the linked table.
Review Question 1. Discuss the entity Integrity and referential integrity
constraints. Why are they important? Explain them with suitable examples.
Database integrity
• The foreign key is a key in one table that refers to the primary key of another
table.
RELATIONAL ALGEBRA
AU: May-03,04,05,14,15,16,17,18, Dec.-02,06,07,08,11,15,16,17, Marks 16
There are two formal query languages associated with relational model and those
are relational algebra and relational calculus.
• Definition: Relational algebra is a procedural query language which is used to
access database tables to read data in different ways.
• The queries present in the relational algebra are denoted using operators.
• Every operator in relational algebra accepts relational instances (tables) as input
and returns relational instance as output. For example-
• Each relational algebra is procedural. That means each relational query

describes a step-by-step procedure for computing the desired answer, based on
the order in which operators are applied in the query.
Relational Operations
Various types of relational operations are as follows-
(1) Selection:
• This operation is used to fetch the rows or tuples from the table(relation).
• Syntax: The syntax is σpredicate(relation)
Where σ represents the select operation. The predicate denotes some logic using
which the data from the relation (table) is selected.
• For example - Consider the relation student as follows-
Query: Fetch students with age more than 18

We can write it in relational algebra as
σage>18 (student)
The output will be-
We can also specify conditions using and, or operators.

(2)Projection :
• Project operation is used to project only a certain set of attributes of a relation.
That means if you want to see only the names all of the students in the Student
table, then you can use Project operation.
• Thus to display particular column from the relation, the projection operator is
used.
• It will only project or show the columns or attributes asked for, and will also
remove duplicate data from the columns.
• Syntax:
ПС1, С2... (r)
where C1, C2 etc. are attribute names (column names).
• For example - Consider the Student table given in Fig. 1.13.2.
Query: Display the name and age for all the students
This can be written in relational algebra as
Пsname, age (Student)
Above statement will show us only the Name and Age columns for all the rows
of data in Student table.
(3) Cartesian product:

• This is used to combine data from two different relations(tables) into one and
fetch data from the combined relation.
• Syntax: A x B
• For example: Suppose there are two tables named Student and Reserve as
follows
Query: Find the details of all the students who have reserved isbn = 005. To
satisfy this query we need to extract data from two table. Hence the cartesian
product operator is used as
(σStudent.sid = Reserve. Sid ˄ Reserve.Isbn = 005 (Student × Reserve)
As an output we will get
Note: that although the Sid columns is same, it is repeated.

(4) Set operations: Various set operations are - union, intersection and set-
difference. Let us understand each of these operations with the help of examples.
(i) Union:
• This operation is used to fetch data from two relations(tables) or temporary
relation (result of another operation).
• For this operation to work, the relations(tables) specified should have same
number of attributes(columns) and same attribute domain. Also the duplicate
tuples are automatically eliminated from the result.
• Syntax: AUB
• Where A and B are relations.
(ii) Intersection:
• This operation is used to fetch data from both tables which is common in both
the tables.
• Syntax: A ∩ B
where A and B are relations.
• Example - Consider two tables - Student and Worker
• Query: If we want to find out the names of the students who are working in a
company then 300
Пname (Student) ∩ Пname (Worker)
(iii) Set-Difference: The result of set difference operation is tuples, which are
present in one relation but are not in the second relation.
Syntax: A - B
For Example: Consider two relations Full_Time_Employee and
Part_Time_Employee, if we want to find out all the employee working for
Fulltime, then the set difference operator is used -
ПEmpName(Full Time_Employee) – ПEmpName(Part_Time_Employee)
(5) Join:The join operation is used to combine information from two or more
relations. Formally join can be defined as a cross-product followed by selections
and projections, joins arise much more frequently in practice than plain cross-
products. The join operator is used as
A) Inner Join
There are three types of joins used in relational algebra
i) Conditional join: This is an operation in which information from two tables is
combined using some condition and this condition is specified along with the join
operator.
A c B = σc (A x B)
Thus is defined to be a cross-product followed by a selection. Note that the
condition c can refer to attributes of both A and B. The condition C can be
specified using <,<,>,< or = operators.
For example consider two table student and reserve as follows-
If we want the names of students with sid(Student) = sid (Reserve) and isbn =
005,then we can write it using Cartesian product as -
(σ((Student.sid = Reserve.sid) ∩(Reserve.(isbn) =005)) (Student × Reserve))
Here there are two conditions as
i) (Student.sid =Reserve.sid) and ii) (Reserve.isbn = 005) which are joined
by∩operator.
Now we can use c instead of above statement and write it as -
(Student ( Student.sid - Reserve.sid) ˄ (Reserve.(Isbn) - 005) Reserve))
The result will be-
ii) Equijoin: This is a kind of join in which there is equality condition between
two attributes(columns) of relations(tables). For example - If there are two table
Book and Reserve table and we want to find the book which is reserved by the
student having isbn 005 and name of the book is 'DBMS', then :
(Obname='DBMS' (Book (Book.isbn = Reserve.isbn) Reserve)

Then we get
iii) Natural Join: When there are common columns and we have to equate these
common columns then we use natural join. The symbol for natural join is
simply without any condition. For example, consider two tables-
Now if we want to list the books that are reserved, then that means we want to
match Books.isbn with Reserve.isbn. Hence it will be simply Books Reserve
B) Outer Join
There are three types of outer joins - Left outer join, Right outer join and Full
outer join.
(1) Left Outer join
• This is a type of join in which all the records from left table are returned and the
matched records from the right table gets returned.
• The result is NULL from the right side, if there is no match.
• The symbol used for left outer join is
• This can be graphically represented as follows
• For example - Consider two tables Student and Course as follows –
The result of Left outer join will be
(2) Right Outer join

• This is a type of join in which all the records from right table are returned and
the matched records from the left table gets returned.
• The result is NULL from the left side, if there is no match.
• The symbol used for right outer join is
• This can be graphically represented as follows
• For example - Consider two tables Student and Course as follows-
The result of Right outer join will be
(3) Full Outer join

• In a full outer join, all tuples from both relations are included in the result,
irrespective of the matching condition.
• It is denoted by
• Graphically it can be represented as
• For example - Consider two tables Student and Course as follows –

The result of Right outer join will be
The symbols used are –
(6) Rename operation: This operation is used to rename the output relation for
any query operation which returns result like Select, Project etc. Or to simply
rename a relation(table). The operator ρ(rho) is used for renaming.
Syntax: ρ (RelationNew, RelationOld)
For example: If you want to create a relation Student_names with sid and sname
from Student, it can be done using rename operator as:
ρ(Student_names, (Πsid.sname (Student))
(7) Divide operation:The division operator is used when we have to evaluate
queries which contain the keyword ALL.
It is denoted by A/B where A and B are instances of relation.
Formal Definition of Division Operation: The operation A/B is define as the
set of all x values (in the form of unary tuples) such that for every y value in (a
tuple of) B, there is a tuple <x,y> in A.
For example - Find all the customers having accounts in all the branches. For that
consider two tables - Customer and Account as
Here We check all the branches from Account table against all the names from
Customer table. We can then find that only customer A has all the accounts in all
the branches.
Review Questions
1. Explain select, project, cartesian product and join operations in relational algebra with an
example lebeidi enne roberts AU: May-18, Marks 13, Dec.-16, Marks 6
2. List operations of relational algebra and purpose of each with example. AU: May-17,
Marks 5
3.Differentiate between foreign key constraints and referential integrity constraints with
suitable example. AU: Dec.-17, Marks 6
4. Explain various operations in relational algebra with examples
AU: May-03, Marks 10, Dec-07, Marks 8, Dec.- 08, Marks 10, May-14, Marks 16
5. Explain all join operations in relational algebra. AU: May-05, Marks 8
6. Briefly explain relational algebra.AU: May-04, Marks 8
7. What is rename operation in relational algebra ? Illustrate your answer with example.
AU: Dec.-02, Marks 2
EXAMPLE OF RELATIONAL ALGEBRA
• Definition: Relational algebra is a procedural query language which is used to
access database tables to read data in different ways.
Example 1.13.1 Consider following databases reserves(sid, bid, day) sailors
(sid, sname, rating, age) boats (bid,bname,color)
(i) Find the names of sailors who have reserved boat number 103
(ii) Find the names of sailors who have reserved a red boat
(iii) Find the id of sailors with age over 20 who have not reserved red boat
(iv) Find the names of sailors who have reserved at least one boat
Solution:
(i)(IIsname((σbid-103 Reserves) Sailors)
(ii) (IIsname((σcolor='red' Boats) Reserves Sailors)
(iii) (Πsid((σage>20 Sailors) – Πsid((σcolor='red'Boats) Reserves)
(iv) (IIsname (Sailors Reserves)
Example 1.13.2 Consider the following expressions, which use the result of a
redational algebra operation as the input to another operation. For each
expression explain in words what the expression does:
a) σyear ≥2009(takes) Student b) σyear ≥2009(takes) Student c) ΠID,name,
course-id(Student takes)
Solution:
a. Select each student who takes at least one course in 2009, display the student
information along with the information about what the courses the student took.
b. Select each student who takes at least one course in 2009, display the student
information along with the information about what the courses the student took
but the selection must be before join operation.
c. Display the ID, Name and Course_id of all the students who took any course
in the university.
Example 1.13.3 Consider following relational database
branch(branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)
loan (loan_number, branch_name,amount)
borrower(customer_name, loan_number)
account (account_number, branch_name, balance)
depositor (customer_name, account_number)
i) Find the names of all branches located in "Chennai".
ii) Find the names of all borrowers who have a loan in branch "ABC".
Solution:
i) Π branch_name(σbranch_city ='Chennai')(branch))
ii) Π customer_name(σbranch_name='ABC') (borrower loan))
PART III: STRUCTURED QUERY LANGUAGE
SQL FUNDAMENTALS
AU: Dec.-14,15,17,19, May-15,16,17,18, Marks 15
• Structure Query Language(SQL) is a database query language used for storing
and managing data in Relational DBMS.
• Various parts of SQL are -
• Data Definition Language(DDL): It consists of a set of commands for defining
relation schema, deleting relations, and modifying relation schemas.
• Data Manipulation Language(DML): It consists of set of SQL commands for
inserting tuples into relational schema, deleting tuples from or modifying tuples
in databases.
• Integrity: The SQL DDL includes commands for specifying integrity
constraints. These constraints must be satisfied by the databases.
• View definition: The SQL DDL contains the commands for defining views for
database.
• Transaction control: The SQL also includes the set of commands that indicate
beginning and ending of the transactions.
• Embedded SQL and Dynamic SQL: There is a facility of including SQL
commands in the programming languages like C,C++, COBOL or Java.
• Authorization: The SQL DDL includes the commands for specifying access
rights to relations and views.
Data Abstraction
The Basic data types used in SQL are -
(1) char(n): For representing the fixed length character string this data type is
used. For instance to represent name, designation, coursename, we use this data
type. Instead of char we can also use character. The n is specified by the user.
(2) varchar(n): The varchar means character varying. That means - for denoting
the variable length character strings this data type is used. The n is user specified
maximum character length.
(3) int: For representing the numeric values without precision, the int data type
is used.
(4) numeric: For representing, a fixed point number with user-specified precision
this data type is used. The number consists of m digits plus sign k digits are to the
right of precision. For instance the numeric(3,2) allows 333.11 but it does not
allow 3333.11
(5) small int: It is used to store small integer value. It allows machine dependent
subset of integer type.
(6) real: It allows the floating point, double precision numbers.
(7) float(n): For representing the floating point number with precision of at least
n digits this data type is used.
Basic Schema Definition

In this section, we will discuss various SQL commands
for creating the schema definition.
There are three categories of SQL commands.
Data Definition Language

Data Manipulation Language
Data Control Language
1. Creation
• A database can be considered as a container for tables and a table is a grid with
rows and columns to hold data.
• Individual statements in SQL are called queries.
• We can execute SQL queries for various tasks such as creation of tables,
insertion of data into the tables, deletion of record from table, and so on.
In this section we will discuss how to create a table.
Step 1: We normally create a database using following SQL statement.
Syntax CREATE DATABASE database_name;
Example
CREATE DATABASE Person _DB
Step 2: The table can be created inside the database as follows -
CREATE TABLE tablename (
Col1_name datatype,
col2 _name datatype,
……
coln_name datatype
);
Example
CREATE TABLE person_details(AdharNo int, FirstName varchar(20),
MiddleName varchar(20),LastName varchar(20),Address varchar(30),
City varchar(10))
The blank table will be created with following structure
Person_details
2. Insertion
We can insert data into the table using INSERT statement.
Syntax
insert into table_name (col1, col2,...,coln)values (value1,value,...., valuen)
Example
insert into person_details (AdharNo, FirstName, MiddleName, LastName,
Address, City)values (111, 'AAA','BBB','CCC','M.G. Road', 'Pune')
The above query will result into –
3. Select
• The Select statement is used to fetch the data from the database table.
• We can use the keyword DISTINCT. It is an optional keyword indicating that
the answer should not contain duplicates. Normally if we write the SQL without
DISTINCT operator then it does not eliminate the duplicates.
Syntax
SELECT col1, col2, ...,coln FROM table_name;
Example
SELECT AdharNo, FirstName, Address, City from person_details
The result of above query will be
• If we want to select all the records present in the table we make use of *
character.
Syntax
SELECT * FROM table_name;
Example
SELECT * FROM person_details;
The above query will result into
4. Where Clause
The WHERE command is used to specify some condition. Based on this
condition the data present in the table can be displayed or can be updated or
deleted.
Syntax
SELECT col1,col2, ...,coln FROM table_name WHERE condition;
Example
Consider following table-
If we execute the following query
SELECT AdharNo,City FROM person_details WHERE city='Pune';
The result will be
If we want records of all those person who live in city Pune then we can write the
query using WHERE clause as
SELECT * FROM person_details WHERE city='Pune';
The result of above query will be
5. Update
• For modifying the existing record of a table, update query is used.
Syntax
UPDATE table name SET col1-value1, col2-value2,...WHERE condition;
Example
Consider following table
Person_details table
If we execute following query
UPDATE person_details SET city='Chennai' WHERE AdharNo=333;
The result will be
6. Deletion
We can delete one or more records based on some condition. The syntax is as
follows -
Syntax
DELETE FROM table_name WHERE condition;
Example
DELETE FROM person_details WHERE AdharNo=333;
The result will be –
We can delete all the records from table. But in this deletion, all the records get
deleted without deleting table. For that purpose the SQL statement will be
DELETE FROM person_details;
7. Logical Operators
• Using WHERE clause we can use the operators such as AND, OR and NOT.
• AND operator displays the records if all the conditions that are separated using
AND operator are true.
• OR operator displays the records if any one of the condition separated using OR
operator is true.
• NOT operator displays a record if the condition is NOT TRUE.
Syntax of AND
SELECT col1, col2, ...FROM table_name WHERE condition1 AND condition2
AND condition3...;
Example of AND
If we execute following query-
SELECT AdharNo, FirstName, City FROM person_details WHERE
AdharNo=222 AND City= 'Pune';
Syntax of OR
SELECT col1, col2, ...FROM table_name WHERE condition1 OR condition2 OR
condition3 ...;
Example of OR
SELECT AdharNo, FirstName, City FROM person_details WHERE City='Pune'
OR City='Mumbai';
Syntax of NOT
SELECT col1, col2, ...FROM table_name WHERE NOT condition;
Example of NOT
SELECT AdharNo, FirstName, City FROM person_details WHERE NOT
City='Pune';
The result will be
8. Order By Clause
• Many times we need the records in the table to be in sorted order.
• If the records are arranged in increasing order of some column then it is called
ascending order.
• If the records are arranged in decreasing order of some column then it is called
descending order.
• For getting the sorted records in the table we use ORDER BY command.
• The ORDER BY keyword sorts the records in ascending order by default.
Syntax
SELECT col1, col2,...,coln FROM table_name ORDER BY col1,col2.... ASC |
DESC
Here ASC is for ascending order display and DESC is for descending order
display.
Example
SELECT * FROM person_details ORDER BY AdharNo DESC;
The above query will result in
9. Alteration
There are SQL command for alteration of table. That means we can add new
column or delete some column from the table using these alteration commands.
Syntax for Adding columns
ALTER TABLE table_name ADD column_name datatype;
Example
If we execute following command

ALTER TABLE Customers ADD Email varchar(30);
Then the result will be as follows –

Syntax for Deleting columns
ALTER TABLE table_name DROP COLUMN column _name;
Example
If we execute following command

ALTER TABLE person_details DROP COLUMN Address;
Then the result will be as follows –
10. Defining Constraints

• We can specify rules for data in a table.
• When the table is created at that time we can define the constraints.
• The constraint can be column level i.e. we can impose constraint on the column
and table level i.e we can impose constraint on the entire table.
There are various types of constraints that can be defined are as follows -
1) Primary key: The primary key constraint is defined to uniquely identify the
records from the table.
The primary key must contain unique values. Hence database designer should
choose primary key very carefully.
For example
Consider that we have to create a person_details table with AdharNo, FirstName,
MiddleName, LastName, Address and City.
Now making AdharNo as a primary key is helpful here as using this field it
becomes easy to identify the records correctly.
The result will be
CREATE TABLE person_details (
AdharNo int,
FirstName VARCHAR(20),
MiddleName VARCHAR(20),
LastName VARCHAR(20),
Address VARCHAR(30),
City VARCHAR(10),
PRIMARY KEY(AdharNo)
);
We can create a composite key as a primary key using CONSTRAINT keyword.
For example
CREATE TABLE person_details (
AdharNo int NOT NULL,
FirstName VARCHAR(20),
MiddleName VARCHAR(20),
LastName VARCHAR(20) NOT NULL,
Address VARCHAR(30),
City VARCHAR(10),
CONSTRAINT PK_person_details PRIMARY KEY(AdharNo, LastName)
);
(2) Foreign Key
• Foreign key is used to link two tables.
• Foreign key for one table is actually a primary key of another table.
• The table containing foreign key is called child table and the table containing
candidate primary key is called parent key.
• Consider
Employee Table
Dept Table:
• Notice that the "EmpID" column in the "Dept" table points to the "EmpID"
column in the "Employee" table.
• The "EmpID" column in the "Employee" table is the PRIMARY KEY in the
"Employee" table.
• The "EmpID" column in the "Dept" table is a FOREIGN KEY in the "Dept"
table.
• The FOREIGN KEY constraint is used to prevent actions that would destroy
links between tables.
• The FOREIGN KEY constraint also prevents invalid data from being inserted
into the foreign key column, because it has to be one of the values contained in
the table it points to.
• The purpose of the foreign key constraint is to enforce referential integrity but
there are also performance benefits to be had by including them in your database
design.
The table Dept can be created as follows with foreign key constraint.
CREATE TABLE DEPT (
DeptID int
DeptName VARCHAR(20),
EmpID int,
PRIMARY KEY(DeptID),
FOREIGN KEY (EmpID) REFERENCES EMPLOYEE(EmpID)
);
(3) Unique
Unique constraint is used to prevent same values in a column.
We can set the constraint as unique at the time of creation of table, or if the table
is already created and we want to add the unique constraint then we can use
ALTER command.
For example -
CREATE TABLE EMPLOYEE(EmpID INT NOT NULL,
Name VARCHAR (20) NOT NULL,
Designation VARCHAR(20) NOT NULL UNIQUE,
Salary DECIMAL (12, 2),
PRIMARY KEY (EmpID));
If table is already created then also we can add the unique constraint as follows -
ALTER TABLE EMPLOYEE
MODIFY Designation VARCHAR(20) NOT NULL UNIQUE;
(4) NOT NULL
• By default the column can have NULL values.
• NULL means unknown values.
• We can set the column values as non NULL by using the constraint NOT NULL.
• For example
CREATE TABLE EMPLOYEE(
EmpID INT NOT NULL,
Name VARCHAR (20) NOT NULL,
Designation VARCHAR(20) NOT NULL,
Salary DECIMAL (12, 2) NOT NULL,
PRIMARY KEY (EmpID)
);
(5) CHECK
The CHECK constraint is used to limit the value range that can be placed in a
column.
For example
CREATE TABLE parts ( Part_no int PRIMARY KEY,
Description VARCHAR(40),
Price DECIMAL(10, 2) NOT NULL CHECK(cost > 0));
(6) IN operator
The IN operator is just similar to OR operator.
Syntax SELECT col1,col2,.... FROM table_name
WHERE column-name IN (value1, value2,...);
Example
Employee
SELECT * FROM Employee WHERE empID IN (1, 3);

The result will be
BASIC STRUCTURE OF SQL QUERIES
The basic form of SQL queries is SELECT-FROM-WHERE.
The syntax is as follows:
SELECT [DISTINCT] target-list FROM Relation-list WHERE Qualification;
• SELECT: This is one of the fundamental query command of SQL. It is similar
to the projection operation of relational algebra. It selects the attributes based on
the condition described by WHERE clause.
• FROM: This clause takes a relation name as an argument from which attributes
are to be selected/projected. In case more than one relation names are given, this
clause corresponds to Cartesian product.
• WHERE: This clause defines predicate or conditions, which must match in
order to qualify the attributes to be projected.
• Relation-list: A list of relation names(tables)
• Target-list: A list of attributes of relations from relation list(tables)
• Qualification: Comparisons of attributes with values or with other attributes
combined using AND, OR and NOT.
• DISTINCT is an optional keyword indicating that the answer should not
contain duplicates. Normally if we write the SQL without DISTINCT operator
then it does not eliminate the duplicates.
Example
SELECT sname FROM Student WHERE age>18;
• The above query will return names of all the students from student table where
age of each student is greater than 18
Queries on Multiple Relations

Many times it is required to access multiple relations (tables) to operate on some
information. For example, consider two tables as Student and Reserve.
Query: Find the names of students who have reserved the books with book isbn
Select Student.sname,Reserve.isbn From Student, Reserve
Where Student.sid=Reserve.sid
Use of SQL Join
The SQL Joins clause is used to combine records from two or more tables in a
database. A JOIN is a means for combining fields from two tables by using values
common to each.
Example: Consider two tables for using the joins in SQL. Note that cid is
common column in following tables.
1) Inner Join:
• The most important and frequently used of the joins is the INNER JOIN. They
are also known as an EQUIJOIN.
• The INNER JOIN creates a new result table by combining column values of two
tables (Table1 and Table2) based upon the join-predicate.
• The query compares each row of table l with each row of Table2 to find all pairs
of rows which satisfy the join-predicate.
• When the join-predicate is satisfied, column values for each matched pair of
rows of A and B are combined into a result row. It can be represented as:
• Syntax: The basic syntax of the INNER JOIN is as follows.
SELECT Table1.column1, Table2.column2... FROM Table1 INNER JOIN Table2
ON Table1.common_field = Table2.common_field;
• Example: For above given two tables namely Student and City, we can apply
inner join. It will return the record that are matching in both tables using the
common column cid. The query will be
SELECT *FROM Student Inner Join City on Student.cid=City.cid;
The result will be
2) Left Join:
• The SQL LEFT JOIN returns all rows from the left table, even if there are no
matches in the right table. This means that if the ON clause matches 0 (zero)
records in the right table; the join will still return a row in the result, but with
NULL in each column from the right table.
• This means that a left join returns all the values from the left table, plus matched
values from the right table or NULL in case of no matching join predicate.
• It can be represented as –
• Syntax: The basic syntax of a LEFT JOIN is as follows.

SELECT SELECT Table1.column1, Table2.column2... FROM Table1
LEFT JOIN Table2 ON Table1.common field Table2.common field;
Left join. It will Return all records from the left table, and the matched records
from the right table using the common column cid. The query will be
SELECT * FROM Student Left Join City on Student.cid=City.cid;
The result will be
3) Right Join:
• The SQL RIGHT JOIN returns all rows from the right table, even if there are
no matches in the left table.
• This means that if the ON clause matches 0 (zero) records in the left table; the
join will still return a row in the result, but with NULL in each column from the
left table.
• This means that a right join returns all the values from the right table, plus
matched values from the left table or NULL in case of no matching join predicate.
• It can be represented as follows:
• Syntax: The basic syntax of a RIGHT JOIN is as follow-
SELECT Table1.column1, Table2.column2... FROM Table1

RIGHT JOIN Table2 ON Table1.common_field = Table2.common_field;
Right join. It will return all records from the right table, and the matched records
from the left table using the common column cid. The query will be,
SELECT * FROM Student Right Join City on Student.cid=City.cid;
4) Full Join:
• The SQL FULL JOIN combines the results of both left and right outer joins.
• The joined table will contain all records from both the tables and fill in NULLS
for missing matches on either side.
• It can be represented as
• Syntax: The basic syntax of a FULL JOIN is as follows

SELECT Table1.column1, Table2.column2... FROM Table1 FULL JOIN Table2
ON Table1.common_field = Table2.common_field;
The result will be -
Example: For above given two tables namely Student and City, we can apply
Full join. It will return returns rows when there is a match in one of the tables
using the common column cid. The query will be -
SELECT * FROM Student Full Join City on Student.cid=City.cid;
Additional Basic Operations
1) The Rename Operation: The SQL AS is used to assign temporarily a new
name to a table column or table(relation) itself. One reason to rename a relation
is to replace a long relation name with a shortened version that is more convenient
to use elsewhere in the query. For example - "Find the names of students and isbn
of book who reserve the books".
Student
Select S.sname,R.isbn From Student as S, Reserve as R Where S.sid=R.sid;

In above case we could shorten the names of tables Student and Reserve as S and
R respectively.
Another reason to rename a relation is a case where we wish to compare tuples in
the same relation. We then need to take the Cartesian product of a relation with
itself. For example-
If the query is - Find the names of students who reserve the book of isbn005. Then
the SQL statement will be -
Select S.sname, R.isbn From Student as S, Reserve as R Where S.sid=R.sid and
S.isbn=005;
2) Attribute Specification in Select clause: The symbol * is used in select clause
to denote all attributes. For example - To select all the records from Student table
we can write
Select* from Student;
3) Ordering the display of tuples: For displaying the records in particular order
we use order by clause.
The general syntax with ORDER BY is
SELECT column_name(s) FROM table_name WHERE condition
ORDER BY column_name(s);
• Example: Consider the Student table as follows-
Query: Find the names of students from highest marks to lowest

Select sname From Student Order By marks;
We can also use the desc for descending order and asc for ascending order. For
example:
In order to display names of the students in descending order of city - we can
specify
Select sname From Student Order by city desc;
(4) Where clause Predicate
(i) The between operator can be used to simplify the where clause which is used
to denote the value be less than or equal to some value and greater than or equal
to some other value. For example - of we want the names of the students whose
marks are between 80 and 90 then SQL statement will be
Select name From Students Where marks between 80 and 90;
Similarly we can make use of the comparison operators for various attributes. For
example - If the query is - Find the names of students who reserve the book of
isbn005. Then the SQL statement will be -
Select sname From Student, Reserve Where
(Student.sid,Reserve.isbn)=(Reserve.sid,005);
(ii) We can use AND, OR and NOT operators in the Where clause. For filtering
the records based on more than one condition, the AND and OR operators can be
used. The NOT operator is used to demonstrate when the condition is not TRUE.
Syntax of AND
SELECT column1, column2, .... FROM table name
WHERE condition1 AND condition2 AND condition3 ...;
Example: Find the student having name "AAA" and lives in city "Pune"
SELECT FROM Students Where sname='AAA' AND city= 'Pune'
Output
Syntax OR
SELECT column1, column2, ... FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Example: Find the student having name "AAA" OR lives in city "Pune"
SELECT * FROM Students Where sname='AAA' OR city='Pune'
Output
Syntax NOT
SELECT column1, column2, ..FROM table name WHERE NOT condition;
Example: Find the student who do not have city "Pune"
SELECT * FROM Students Where NOT city='Pune';
Output
Domain and Key Constraint
Domain Constraint
A domain is defined as the set of all unique values permitted for an attribute. For
example, a domain of date is the set of all possible valid dates, a domain of Integer
is all possible whole numbers, and a domain of day-of-week is Monday, Tuesday
Sunday.
Domain constraints are user defined data type and we can define them like this:
Domain constraint = Data type + Constraints
The constraints can be specified using NOT NULL / UNIQUE / PRIMARY KEY
/ FOREIGN KEY/CHECK/DEFAULT.
For example-
Create domain id_value integer
constraint id_test
check(value > 100); ← cheking if stud_id value is greater than 100
create table student (
stu_id id_value PRIMARY KEY,
stu_name CHAR(30),
stu_age integer
);
Key Constraint
• A key constraint is a statement that a certain minimal subset of the fields of a
relation is a unique identifier for a tuple.
• For example - Consider the students relation and the constraint that no two
students have the same student id. This IC is an example of a key constraint.
• The definition of key constraints contain two parts -
• Two distinct tuples in a legal instance (an instance that satisfies all Integrity
Constraints including the key constraint) cannot have identical values in all the
fields of a key.
• No subset of the set of fields in a key is a unique identifier for a tuple.
• The first part of the definition means that, in any legal instance, the values inthe
key fields uniquely identify a tuple in the instance. When specifying a key
constraint, the DBA or user must be sure that this constraint will not prevent them
from storing a 'correct' set of tuples. For example, several students may have the
same name, although each student has a unique student id. If the name field is
declared to be a key, the DBMS will not allow the Students relation to contain
two tuples describing different students with the same name.
• The second part of the definition means, for example, that the set of fields
(RollNo, Name} is not a key for Students, because this set properly contains the
key {RollNo}. The set {RollNo, Name} is an example of a superkey, which is a
set of fields that contains a key.
• The key constraint can be specified using SQL as follows -
• In SQL, we can declare that a subset of the columns of a table constitute a
key by using the UNIQUE constraint.
• At most one of these candidate keys can be declared to be a primary key,
using the PRIMARY KEY constraint. For example -
CREATE TABLE Student (RollNo integer, Name CHAR(20), age integer,
UNIQUE (Name, age), CONSTRAINT StudentKey PRIMARY KEY(RollNo))
This definition says that RollNo is a Primary key and Combination of Name and
age is also a key.
STRING OPERATIONS
• For string comparisons, we can use the comparison operators =, <, >,<,>=> with
the ordering of strings determined alphabetically as usual.
• SQL also permits a variety of functions on character strings such as
concatenation string operator, extracting substrings, finding length of string,
converting strings to upper case(using function upper(s)) and lowercase(using
function lower(s)), removing spaces at the end of string(using function(trim(s))
and so on.
• Pattern matching can also be performed on strings using two types of special
characters - • Percent(%): It matches zero, one or multiple characters
• Underscore(_): The _ character matches any single character.
• The percentage and underscore can be used in combinations.
• Patterns are case sensitive. That means upper case characters do not match
lowercase characters or vice versa.
• For instance:
• 'Data%' matches any string beginning with "Data", For instance it could be
with a "Database", "DataMining","DataStructure"
• ' _ _ _' matches any string of exactly three characters.
• ' _ _ _'%'matches any string of at least length 3 characters.
• The LIKE clause can be used in WHERE clause to search for specific patterns.
• For example - Consider following Employee Database
(1) Find all the employee with EmpName starting with "s"
SQL Statement:
SELECT * FROM Employee WHERE EmpName LIKE 's%'
Output
(2) Find the names of employee whose name begin with S and end with a
SQL Statement:
SELECT EmpName FROM Employee WHERE EmpName LIKE 'S%a'
Output
(3) Find the names of employee whose name begin with S and followed by
exactly four characters
SELECT EmpName FROM Employee WHERE EmpName LIKE 'S_ _ _ _’
Output
SET OPERATIONS
1) UNION: To use this UNION clause, each SELECT statement must have
i) The same number of columns selected
ii) The same number of column expressions
iii) The same data type and
iv) Have them in the same order
This clause is used to combine two tables using UNION operator. It replaces the
OR operator in the query. The union operator eliminates duplicate while the union
all query will retain the duplicates.
Syntax
The basic syntax of a UNION clause is as follows -
SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition]
UNION
Here, the given condition could be any given expression based on your
requirement.
Consider Following relations –
Example: Find the names of the students who have reserved the 'DBMS' book or
'OS' Book
The query can then be written by considering the Student, Reserve and Book table
as
SELECT S.sname FROM Student S, Reserve R, Book B
WHERE S.sid-R.sid AND R.isbn=B.isbn AND B.bname='DBMS'
UNION SELECT S.sname FROM Student S, Reserve R, Book B
WHERE S.sid-R.sid AND R.isbn=B.isbn AND B.bname='OS';
2) INTERSECT: The common entries between the two tables can be represented
with the help of Intersect operator. It replaces the AND operator in the query.
Syntax The basic syntax of a INTERSECT clause is as follows --
INTERSECT SELECT column1 [, column2] FROM table1 [, table2] [WHERE
condition]
Example: Find the students who have reserved both the 'DBMS' book and 'OS'
Book
as
SELECT S.sid, S.sname FROM Student S, Reserve R, Book B
WHERE S.sid-R.sid AND R.isbn=B.isbn AND B.bname='DBMS'
INTERSECT SELECT S.sname FROM Student S, Reserve R, Book B
WHERE S.sid-R.sid AND R.isbn=B.isbn AND B.bname='OS'
3) EXCEPT: The EXCEPT clause is used to represent the set-difference in the
query. This query is used to represent the entries that are present in one table and
not in other.
Syntax:
The basic syntax of a EXCEPT clause is as follows -
SELECT column1 [, column2 | FROM table1 [, table2 ] [WHERE condition]
EXCEPT SELECT column1 [, column2] FROM table1 [, table2] [WHERE
condition]
Example: Find the students who have reserved both the 'DBMS' book but not
reserved 'OS' Book
as
SELECT S.sid, S.sname FROM Student S, Reserve R, Book B
WHERE S.sid=R.sid AND R.isbn=B.isbn AND B.bname='DBMS'
EXCEPT SELECT S.sname FROM Student S, Reserve R, Book B
WHERE S.sid=R.sid AND R.isbn=B.isbn AND B.bname='OS'
AGGREGATE FUNCTIONS
• An aggregate function allows you to perform a calculation on a set of values to
return a single scalar value.
• SQL offers five built-in aggregate functions:
1. Average: avg
2. Minimum: min
3. Maximum : max
4. Total: sum
5. Count:
1. Basic Aggregation
The aggregate functions that accept an expression parameter can be modified by
the keywords DISTINCT or ALL. If neither is specified, the result is the same as
if ALL were specified.
Syntax of all the Aggregate Functions

AVG([ DISTINCT | ALL ] expression)
COUNT(*)
COUNT([ DISTINCT | ALL | expression)
MAX( | DISTINCT | ALL ] expression)
MIN([ DISTINCT | ALL ] expression)
SUM([DISTINCT | ALL ] expression)
• The avg function is used to compute average value. For example - To compute
average marks of the students we can use
SQL Statement
SELECT AVG(value) FROM test;
• The Count function is used to count the total number of values in the specified
field. It works on both numeric and non-numeric data type. COUNT (*) is a
special implementation of the COUNT function that returns the count of all the
rows in a specified table. COUNT (*) also considers Nulls and duplicates. For
example, consider following table
SQL Statement
SELECT COUNT(*) FROM Test;
Output
4
SELECT COUNT(ALL id) FROM Test;
Output
3
• The min function is used to get the minimum value from the specified column.
For example - Consider the above created Test table
SQL Statement
SELECT Min(value) FROM Test
Output
100
• The max function is used to get the maximum value from the specified column.
For example - - Consider the above created Test table
SQL Statement
SELECT Max(value) FROM Test;
Output
400
• The sum function is used to get total sum value from the specified column. For
example - Consider the above created Test table
SOL Statement
SELECT sum(value) FROM test;
Output
1000
2. USE OF GROUP BY AND HAVING CLAUSE

(i) Group By:
• The GROUP BY clause is a SQL command that is used to group rows that have
the same values.
• The GROUP BY clause is used in the SELECT statement.
• Optionally it is used in conjunction with aggregate functions.
• The queries that contain the GROUP BY clause are called grouped queries
• This query returns a single row for every grouped item.
• Syntax :
GROUP BY column_name(s);
The general syntax with ORDER BY is
GROUP BY column name(s) ORDER BY column_name(s);
Example : Consider the Student table as follows-
Query: Find the total marks of each student in each city

SELECT SUM(marks), city FROM Student GROUP BY city
Output
(ii) Having:
• HAVING filters records that work on summarized GROUP BY results.
• HAVING applies to summarized group records, whereas WHERE applies to
been individual records.
• Only the groups that meet the HAVING criteria will be returned.
• HAVING requires that a GROUP BY clause is present.
• WHERE and HAVING can be in the same query.
• Syntax:
SELECT column-names FROM table-name WHERE condition
GROUP BY column-names HAVING condition;
Example: Consider the Student table as follows –
Query: Find the total marks of each student in the city named 'Pune' and
'Mumbai' Only
SELECT SUM(marks), city FROM Student GROUP BY city
HAVING city IN('Pune', 'Mumbai')
Output
• The result will be as follows-
NESTED QUERIES
In nested queries, a query is written inside a query. The result of inner query is
used in execution of outer query.
There are two types of nested queries:
i) Independent Query:
• In independent nested queries, query execution starts from innermost query to
outermost queries.
• The execution of inner query is independent of outer query, but the result of
inner query is used in execution of outer query.
• Various operators like IN, NOT IN, ANY, ALL etc are used in writing
independent nested queries.
• For example - Consider three tables namely Student, City and Student_City as
follows-
• Example 1 - If we want to find out sid who live in city 'Pune' or 'Chennai'.
We can then write independent nested query using IN operator. The IN operator
is a shorthand for multiple OR conditions.
Step 1: Find cid for cname='Pune' or 'Chennai'. The query will be
SELECT cid FROM City WHERE cname='Pune' or 'Chennai'
Step 2: Using cid obtained in step 1 we can find the sid. The query will be
SELECT sid FROM Student_City WHERE cid IN
(SELECT cid FROM City WHERE cname='Pune' or cname='Chennai')
The inner query will return a set with members 101 and 103 and outer query will
return those sid for which cid is equal to any member of set (101 and 103 in this
case). So, it will return 1, 2 and 4.
Example 2: If we want to find out sname who live in city 'Pune' or 'Chennai'.
SELECT sname FROM Student WHERE sid IN
(SELECT sid FROM Student City WHERE cid IN
(SELECT cid FROM City WHERE cname='Pune' or cname='Chennai'))
ii) Co-related Query:
In co-related nested queries, the output of inner query depends on the row which
is being currently executed in outer query. For example
If we want to find out sname of Student who live in city with cid as 101, it can be
done with the help of co-related nested query as:
SELECT sname FROM Student S WHERE EXISTS
(SELECT * FROM Student_City SC WHERE S.sid=SC.sid and SC.cid=101)
Here For each row of Student S, it will find the rows from Student_City where
S.sid SC.sid and SC.cid=101.
If for a sid from Student S, atleast a row exists in Student City SC with cid=101,
then inner query will return true and corresponding sid will be returned as output.
MODIFICATION OF DATABASES
The modification of database is an operation for making changes in the existing
databases. Various operations of modification of database are insertion, deletion
and updation of databases.
1. Deletion: The delete command is used to delete the existing record.
Syntax
delete from table_name where condition;
Example
delete from student where RollNo=10
2. Insertion: The insert command is used to insert data into the table. There are
two syntaxes of inserting data into SQL
Syntax
(i) Insert into table_name (column1, column2, column3, ...)
values (value1, value2, value3, ...);
(ii) insert into table_name values (value1, value2, value3, ...);
Example
(i) insert into Student (RollNo,Name, Makrs) values (101, 'AAA',56.45)
(ii) insert into Student values(101,'AAA',56.45)
3. Update: The update statement is used to modify the existing records in the
table.
update table_name set column1-value1, column2=value2,...where condition;
Example :
Delete student Set Name='WWW' where Roll No=101
Review Questions
1. Explain aggregate functions in SQL with example. AU: May-18, Marks 13
2. Write DDL, DML, DCL commands for the students database. AU: Dec.-17, Marks 7
3. Explain about SQL fundamentals. AU: May-16, Marks 8
4. Explain about Data Definition Language. AU: May-16, Marks 8
5. Explain the six clauses in the syntax of SQL query and show what type of constructs can be
specified in each of the six clauses. Which of the six clauses are required and which are
optional? AU: Dec.-15, Marks 16
6. Explain- DDL and DML. AU: Dec.-14, Marks 8
EXAMPLES OF SQL FUNDAMENTALS
AU: Dec.-14,15,17,19, May-15,16,17,18, Marks 15
Example 1.14.1 Write the DDL, DML, DCL for the students database. Which
contains student details: name, id,DOB, branch, DOJ.
Course details: Course name, Course id, Stud.id,Faculty name, id, mark
AU: Dec.-17, Marks 15
Solution:
DDL Commands
CREATE TABLE Student
(
stud_name varchar(20),
stud_id int(3),
DOB varchar(15),
branch varchar(10),
DOJ varchar(15),
);
CREATE TABLE Course(
course_name varchar(20),
course_id int(5),
stud_id int(3),
facult_name varchar(20),
faculty_id varchar(5),
marks real
);
DML COMMANDS
The commands which we will use here are insert and select. The insert command
is used to insert the values into database tables. Using the select command, the
database values can be displayed.
(1) Inserting values into Student table
insert into Student(stud_name,stud_id,DOB,branch,DOJ)
values('AAA',11,'01-10-1999', 'computers','5-3-2018')
insert into Student(stud_name,stud_id,DOB,branch,DOJ)
values('BBB',12, 24-5-1988', 'Mechanical', '17-2-2016')
insert into Student(stud_name, stud_id,DOB,branch,DOJ)
values('CCC',13,'8-1-1990', 'Electrical','22-9-2017')
(2) Inserting values into Course table
insert into Course (course_name,course_id,stud_id,faculty_name,
faculty_id,marks) values('Basic',101,11,'Archana', 'F001','50')
insert into Course(course_name,course_id,stud_id,faculty_name,
faculty_id,marks) values('Intermediate',102,12,'Rupali', 'F002','70')
insert into Course (course_name,course_id,stud_id,faculty_name,
faculty_id,marks) values('Advanced',103,13,'Sunil','F003', '100')
(3) Displaying records of Student table
Select * from Student;
(4) Displaying records of Course table
Select * from Course;
DCL COMMANDS
The DCL command is used to control privileges in Database. To perform any
operation in the database, such as for creating tables, sequences or views, a user
needs privileges.
(1) GRANT Command
SQL GRANT is a command used to provide access or privileges on the database
objects to the users.
Syntax
GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
privilege_name is the access right or privilege granted to the user. Some of the
access rights are ALL, EXECUTE, and SELECT.
• object_name is the name of a database object like TABLE, VIEW, STORED
PROC and SEQUENCE.
• user_name is the name of the user to whom an access right is being granted.
• PUBLIC is used to grant access rights to all users.
• roll_name is a set of privileges grouped together.
• WITH GRANT OPTION - allows a user to grant access rights to other users.
Example
GRANT SELECT ON student_details TO user1
This query will grant the SELECT permission to student_details table to user
named user1.
Similarly, we can GRANT more than one privileges to user in a table
GRANT SELECT, INSERT, DELETE, UPDATE ON student details TO user1
For granting all privileges to user we use sysdba. The sysdba is a set of priviliges
which has all the permissions in it.
GRANT sysdba TO user1
(2) REVOKE
The REVOKE command removes user access rights or privileges to the database
objects.
Syntax
REVOKE privilege_name ON object_name
FROM {user_name |PUBLIC |role_name}
Example
To remove access right for SELECT to the table student_details for userl we write
the query
REVOKE SELECT ON student details FROM user1;
Example
1) Write the following queries in relational algebra and SQL
(i) Find the names of employee who have borrowed a book published by McGraw
Hill
(ii) Find the names of employees who have borrowed all books published by
McGraw-Hill AU: May-17, Marks 10
Solution:
We will assume the databases as -
member(memb_no, name, dob)
books(isbn, title, authors, publisher)
borrowed(memb_no, isbn, date)
(i) Relational Algebra:
Πname((publisher McGraw Hill' books) borrowed member)
SQL:
SELECT name FROM member WHERE meber.memb_no=borrowed.memb_no
AND books.isbn=borrowed.isbn AND books.publisher 'McGraw Hill';
(ii) Relational Algebra
ρ(Tempname,( Πmemb_no,isbn borrowed)/ Πisbn (σpublisher-McGraw
Hill' books))) Πname (Tempname member)
SQL:
SELECT distinct M.name FROM Member M, WHERE NOT EXIST
( (SELECT isbn FROM books WHERE publisher = 'McGraw Hill' )
EXCEPT (SELECT isbn FROM borrowed R WHERE R.memb_no = M.memb_no
))
Example 2) Assume the following table.
Degree (degcode, name, subject)
Candidate (seatno, degcode, name, semester, month, year, result)
Marks (seatno, degcode, semester, month, year, papcode, marks)
[degcode - degree code, name - name of the degree (Eg. MSc.), subject - subject
of the course (Eg. Physis), papcode - paper code (Eg. A1)]
Solve the following queries using SQL;
Write a SELECT statement to display,
(i) all the degree codes which are there in the candidate table but not present in
degree table in the order of degcode.
(ii) the name of all the candidates who have got less than 40 marks in exactly 2
subjects.
(iii) the name, subject and number of candidates for all degrees in which there
are less than 5 candidates.
(iv) the names of all the candidate who have got highest total marks in MSc.
Maths. AU: Dec.-15, Marks 4+4 +4 +4
Solution:
(i) SELECT C.degcode FROM Candidate C, WHERE NOT EXISTS (SELECT
D.degcode FROM Degree D WHERE D.degcode=C.degcode)
ORDER by C.degcode
(ii) SELECT C.name FROM Candidate C, Degree D, Marks M WHERE C.seatno
M.seatno AND C.degcode=D.degcode AND C.degcode-M.degcode AND
M.marks <40 GROUP BY C.seatno HAVING count(D.subject)=2;
(iii) SELECT D.name,D.subject,count(*) FROM degree D, Candidate C
WHERE D.degcode=C.degcode HAVING( SELECT count(*) FROM Candidate
<5);
(iv) SELECT C.name FROM Candidate C, Degree D, Marks M
WHERE D.degname='MSc' AND D.subject='Maths' AND
C.degcode=D.degcode AND C.seatno-M.seatno AND M.marks (SELECT
max(M.marks) FROM Marks M)
ADVANCED SQL FEATURES

EMBEDDED SQL
AU: May-14,17, Dec.-14,16,17, Marks 8
• The programing module in which the SQL Statements are embedded is called
Embedded SQL module.
• It is possible to embed SQL statements inside the programming language such
as C, C++, PASCAL, Java and so on.
• It allows the application languages to communicate with DB and get requested
result.
• The high level languages which supports embedding SQLS within it are also
known as host language.
• An embedded SQL program must be processed by a special pre-processor prior
to compilation. The pre-processor replaces embedded SQL requests with host-
language declarations and procedure calls that allow runtime execution of the
database accesses. Then, the resulting program is compiled by the host-language
compiler. This is the main distinction between embedded SQL and JDBC or
ODBC.
Example of Embedded SQL - Following program prompts the user for an order
number,
retrieves the customer number, salesperson, and status of the order, and displays
the retrieved information on the screen.
int main() {
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
int OrderID;/* Employee ID (from user) */
int CustID;/* Retrieved customer ID */
char SalesPerson[10] /* Retrieved salesperson name */
char Status[6]/* Retrieved order status */
EXEC SQL END DECLARE SECTION;
/* Set up error processing */
EXEC SQL WHENEVER SOLERROR GOTO query_error;
EXEC SQL WHENEVER NOT FOUND GOTO bad_number;
/* Prompt the user for order number */
printf ("Enter order number: ");
scanf("%d", &OrderID);
/* Execute the SQL query */
EXEC SQL SELECT CustID, SalesPerson, Status
FROM Orders
WHERE OrderID = :OrderID
INTO:CustID, :SalesPerson, :Status;
/* Display the results */
printf ("Customer number: %d\n", CustID);
printf ("Salesperson: %s\n", SalesPerson);
printf ("Status: %s\n", Status);
exit():
query_error:
printf ("SQL error: %ld\n", sqlca->sqlcode);
exit();
bad_number:
printf ("Invalid order number.\n");
exit();
}
Features of Embedded SQL
(1) It is easy to use.
(2) It is ANSI/ISO standard programming language.
(3) It requires less coding
(4) The pre compiler can optimize execution time by generating stored procedures
for the Embedded SQL statements.
(5) It is identical over different host languages, hence writing applications using
different programming languages is quite easy.
Review Questions
1. What is the need of embedded SQL? AU: May-17, Dec.-17, Marks 2
2. What is embedded SQL? Give an example. AU: Dec.-16, Marks 5, May-14,
Dec.-14, Marks 8

CS3492 DBMS (Unit 1)

Uploaded by

Copyright:

Available Formats

CS3492 DBMS (Unit 1)

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

CS3492 DBMS (Unit 1)

Uploaded by

Copyright:

Available Formats

Unit 1: Relational Databases

VIEWS OF DATA AU: May-16, Marks 16

DATA MODELS AU: Dec.-14, May-19, Marks 13

1. Physical Independence: This is a kind of data independence which allows the

PART II: RELATIONAL DATABASES

INTRODUCTION TO RELATIONAL DATABASES

Attribute or columns: It is a part of table that contains several records. Each

Example 1.9.1 Find out following for given Staff table

KEYS AU: May-06, 07, 12, Dec.-06, Marks 4

Entity Integrity Rule

• Each relational algebra is procedural. That means each relational query

Query: Fetch students with age more than 18

We can also specify conditions using and, or operators.

(3) Cartesian product:

Note: that although the Sid columns is same, it is repeated.

(Obname='DBMS' (Book (Book.isbn = Reserve.isbn) Reserve)

• For example - Consider two tables Student and Course as follows –

The result of Left outer join will be

(2) Right Outer join

The result of Right outer join will be

(3) Full Outer join

• For example - Consider two tables Student and Course as follows –

The symbols used are –

PART III: STRUCTURED QUERY LANGUAGE

Basic Schema Definition

Data Definition Language

Data Control Language

The result will be –

If we execute following command

Then the result will be as follows –

If we execute following command

10. Defining Constraints

SELECT * FROM Employee WHERE empID IN (1, 3);

Queries on Multiple Relations

• Syntax: The basic syntax of a LEFT JOIN is as follows.

SELECT Table1.column1, Table2.column2... FROM Table1

• Syntax: The basic syntax of a FULL JOIN is as follows

Select S.sname,R.isbn From Student as S, Reserve as R Where S.sid=R.sid;

Query: Find the names of students from highest marks to lowest

Syntax of all the Aggregate Functions

2. USE OF GROUP BY AND HAVING CLAUSE

Query: Find the total marks of each student in each city

ADVANCED SQL FEATURES

You might also like