PREPARED BY SHARIKA T R SNGCE

MODULE 2: Relational Model

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

SYLLABUS
• Structure of Relational Databases - Integrity
Constraints, Synthesizing ER diagram to
relational schema
• Introduction to Relational Algebra - select,
project, cartesian product operations, join -
Equi-join, natural join. query examples,
• Introduction to Structured Query Language
(SQL), Data Definition Language (DDL), Table
definitions and operations – CREATE, DROP,
ALTER, INSERT, DELETE, UPDATE.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relational data model

• Represent database as a collection of relations
• Relation is a table which has values and rows in
table is a collection of related data values
• Each row in table is a fact
• Row in relational table is called a tuple, column
header is attribute and table is a relation

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Attributes

Relation name

EMPLOYEE
EMP_NO Name Address Mobile number Age Salar
y

101 RAM XYZ 9898989898 20 10000

Tuples 102 SAM CVF 9999999999 21 20000

103 SITA FDFD 888888888 22 30000

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• In the relational model, all data is logically

structured within relations (also called table)
• Informally a relation may be viewed as a named
two-dimensional table representing an entity
set.
• A relation has a fixed number of named columns
and variable number of rows.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Components of relational database

• The main components of relational database
structure are as follows:
1. Domains
2. Tuples (rows)
3. Columns
4. Keys
5. Relations (Tables)

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Domain
• It has three parts
▫ Name
▫ Data type
▫ Format
• A Domain is a set of atomic values.
• Atomic means each value in the domain is
indivisible to the relational model.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• A domain has a logical definition:

e.g.“USA_phone_numbers” are the set of 10
digit phone numbers valid in the U.S.
• A domain may have a data-type or a format
defined for it. The USA_phone_numbers may
have a format: (ddd)-ddd-dddd where each d is a
decimal digit. E.g., Dates have various formats
such as month name, date, year or yyyy-mm-dd,
or dd mm,yyyy etc
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Tuples (rows)
• A tuple is an ordered set of values
• Tuple is a portion of a table containing data that
described only entity, relationship, or object
• Also known as record
• Each value is derived from an appropriate
domain.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• <Kumar, Singh, 52/57 store, 223001,

9889898989> is a tuple belonging to the
CUSTOMER relation.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Columns
• Columns in a table are also called attributes or
fields of the relation.
• A single cell in a table called field value, attribute
value or data element.
• For example, for the entity person, attributes
could include eye colour and height.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Key of a Relation
• Each row has a value of a data item (or set of
items) that uniquely identifies that row in the
table
 Called the key
• Sometimes row-ids or sequential numbers are
assigned as keys to identify the rows in a table
 Called artificial key or surrogate key

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relations (Tables)
• A table of values
• A relation may be thought of as a set of rows.
• A relation may alternately be thought of as a set
of columns.
• That is a table is perceived as a two-dimensional
structure composed of rows and columns.
• Each row represents a fact that corresponds to a
real-world entity or relationship.
• Each row has a value of an item or set of items
that uniquely identifies that row in the table
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Sometimes row-ids or sequential numbers are

assigned to identify the rows in the table.
• Each column typically is called by its column
name or column header or attribute name.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Schema of a Relation
• It is basically an outline of how data is organized
• It is denoted by R (A1, A2, .....An)
▫ Here R is relation name and
▫ it has some attributes A1 to An
• Each attribute have some domain and it is
represented by dom(Ai)
• Relation schema is used to describe a relation
and R is name of the relation
• Each attribute has a domain or a set of valid values.
▫ For example, the domain of Cust-id is 6 digit numbers.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Degree of a relation
• Degree of a relation is number of attributes in a
relation
• Eg
• STUDENT(Id, Name, Age, Departmentno)
▫ Has degree 4
• Using datatype of each the definition can be
written as
• STUDENT(Id:Integer, Name:String,Age:integer,
Departmentno:integer)
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relation State
• The relation state is a subset of the Cartesian
product of the domains of its attributes
▫ each domain contains the set of all possible values
the attribute can take.
• Example: attribute Cust-name is defined over
the domain of character strings of maximum
length 25
▫ dom(Cust-name) is varchar(25)
• The role these strings play in the CUSTOMER
relation is that of the name of a customer.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• A relation state r(R) is a mathematical relation

of degree n on the domains dom(A1),
dom(A2)…, dom(An) which is a subset of
Cartesian product(X) of domains that define R

• Cartesian product specifies all possible

combination of values from underlying domains

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Cardinality in domain D by |D| then the total

number of tuples in cartesiean product is

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Current relation state

• Reflects only the valid tuples that represent a
particular state of real world
• As the state of real world changes, so does the
relation state, by being transformed into another
relation state

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Alternative Definition of a
Relation
• ordering of values in a tuple unnecessary
• a relation schema R = {A1, A2, ..., An} is a set of
attributes, and
• a relation state r(R) is a finite set of mappings r =
{t1, t2, ..., tm}, where each tuple ti is a mapping
from R to D, and
• D is the union (denoted by ∪) of the attribute
domains; that is, D = dom(A1) ∪ dom(A2) ∪ ... ∪
dom(An).
• Here t[Ai] must be in dom(Ai) for 1 ≤ i ≤ n for each
mapping t in r.
• Each mapping ti is called a tuple.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• a tuple can be considered as a set of

(<attribute>, <value>) pairs, where each pair
gives the value of the mapping from an attribute
Ai to a value vi from dom(Ai).
• The ordering of attributes is not important,
because the attribute name appears with its
value.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Formal Definitions - Summary

• Formally,
▫ Given R(A1, A2, .........., An)
▫ r(R)  dom (A1) X dom (A2) X ....X dom(An)
• R(A1, A2, …, An) is the schema of the relation
• R is the name of the relation
• A1, A2, …, An are the attributes of the relation
• r(R): a specific state (or "value" or “population”) of
relation R – this is a set of tuples (rows)
▫ r(R) = {t1, t2, …, tn} where each ti is an n-tuple
▫ ti = <v1, v2, …, vn> where each vj element-of dom(Aj)

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Formal Definitions - Example

• Let R(A1, A2) be a relation schema:
▫ Let dom(A1) = {0,1}
▫ Let dom(A2) = {a,b,c}
• Then: dom(A1) X dom(A2) is all possible combinations:
{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }

• The relation state r(R)  dom(A1) X dom(A2)

• For example: r(R) could be {<0,a> , <0,b> , <1,c> }
▫ this is one possible state (or “population” or “extension”) r
of the relation R, defined over A1 and A2.
▫ It has three 2-tuples: <0,a> , <0,b> , <1,c>
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Definition Summary
Informal Terms Formal Terms
Table Relation
Column Header Attribute
All possible Column Domain
Values
Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Characteristics of Relation
• Ordering of tuples in a relation r(R):
▫ The tuples are not considered to be ordered,
even though they appear to be in the tabular
form.
• Ordering of attributes in a relation schema R
(and of values within each tuple):
▫ We will consider the attributes in R(A1, A2, ...,
An) and the values in t=<v1, v2, ..., vn> to be
ordered .
 (However, a more general alternative definition of
relation does not require this ordering).

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Values in a tuple:
▫ All values are considered atomic (indivisible).
▫ Each value in a tuple must be from the domain of
the attribute for that column
 If tuple t = <v1, v2, …, vn> is a tuple (row) in the
relation state r of R(A1, A2, …, An)
 Then each vi must be a value from dom(Ai)

▫ A special null value is used to represent values

that are unknown or inapplicable to certain tuples.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Notation:
▫ We refer to component values of a tuple t by:
 t[Ai] or t.Ai
 This is the value vi of attribute Ai for tuple t
▫ Similarly, t[Au, Av, ..., Aw] refers to the subtuple
of t containing the values of attributes Au, Av, ...,
Aw, respectively in t

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relational Integrity Constraints

• Constraints are conditions that must hold on all valid
relation states.
• There are three main types of constraints in the
relational model:
▫ Key constraints
▫ Entity integrity constraints
▫ Referential integrity constraints
• Another implicit constraint is the domain constraint
▫ Every value in a tuple must be from the domain of its
attribute (or it could be null, if allowed for that attribute)

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Key Constraints
• Superkey of R:
▫ Is a set of attributes SK of R with the following
condition:
 No two tuples in any valid relation state r(R)
will have the same value for SK
 That is, for any distinct tuples t1 and t2 in
r(R), t1[SK]  t2[SK]
 This condition must hold in any valid state
r(R)
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Key of R:
▫ A "minimal" superkey
▫ That is, a key is a superkey K such that
removal of any attribute from K results in a set
of attributes that is not a superkey (does not
possess the superkey uniqueness property)

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Example: Consider the CAR relation

schema:
▫ CAR(State, Reg#, SerialNo, Make, Model,
Year)
▫ CAR has two keys:
 Key1 = {State, Reg#}
 Key2 = {SerialNo}
▫ Both are also superkeys of CAR
▫ {SerialNo, Make} is a superkey but not a key.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• In general:
▫ Any key is a superkey (but not vice versa)
▫ Any set of attributes that includes a key is a
superkey
▫ A minimal superkey is also a key

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• If a relation has several candidate keys, one is

chosen arbitrarily to be the primary key.
▫ The primary key attributes are underlined.
• Example: Consider the CAR relation schema:
▫ CAR(State, Reg#, SerialNo, Make, Model, Year)
▫ We chose SerialNo as the primary key
• The primary key value is used to uniquely
identify each tuple in a relation
▫ Provides the tuple identity
• Also used to reference the tuple from another
tuple
▫ General rule: Choose as primary key the smallest of
the candidate keys (in terms of size)
▫ Not always applicable – choice is sometimes subjective
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

CAR table with two candidate keys –

LicenseNumber chosen as Primary Key

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relational Database Schema

• Relational Database Schema:
▫ A set S of relation schemas that belong to the same
database.
▫ S is the name of the whole database schema
▫ S = {R1, R2, ..., Rn}
▫ R1, R2, …, Rn are the names of the individual
relation schemas within the database S
• Following slide shows a COMPANY database
schema with 6 relation schemas

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Entity Integrity
• Entity Integrity:
▫ The primary key attributes PK of each relation
schema R in S cannot have null values in any tuple of
r(R).
 This is because primary key values are used to
identify the individual tuples.
 t[PK]  null for any tuple t in r(R)
 If PK has several attributes, null is not allowed in
any of these attributes
▫ Note: Other attributes of R may be constrained to
disallow null values, even though they are not
members of the primary key.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Referential Integrity
• A constraint involving two relations
▫ The previous constraints involve a single relation.
• Used to specify a relationship among tuples in
two relations:
▫ The referencing relation and the referenced
relation.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Tuples in the referencing relation R1 have

attributes FK (called foreign key attributes)
that reference the primary key attributes PK of
the referenced relation R2.
▫ A tuple t1 in R1 is said to reference a tuple t2 in
R2 if t1[FK] = t2[PK].
• A referential integrity constraint can be
displayed in a relational database schema as a
directed arc from R1.FK to R2.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Statement of the constraint

▫ The value in the foreign key column (or columns)
FK of the the referencing relation R1 can be
either:
 (1) a value of an existing primary key value of a
corresponding primary key PK in the referenced
relation R2, or
 (2) a null.
• In case (2), the FK in R1 should not be a part of
its own primary key.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Displaying a relational database

schema and its constraints
• Each relation schema can be displayed as a row of
attribute names
• The name of the relation is written above the attribute
names
• The primary key attribute (or attributes) will be
underlined
• A foreign key (referential integrity) constraints is
displayed as a directed arc (arrow) from the foreign key
attributes to the referenced table
▫ Can also point the the primary key of the referenced
relation for clarity
• Next slide shows the COMPANY relational schema
diagram

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Other Types of Constraints

• Semantic Integrity Constraints:
▫ based on application semantics and cannot be
expressed by the model per se
▫ Example: “the max. no. of hours per employee for
all projects he or she works on is 56 hrs per week”
• A constraint specification language may
have to be used to express these
• SQL-99 allows triggers and ASSERTIONS to
express for some of these

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Populated database state

• Each relation will have many tuples in its current
relation state
• The relational database state is a union of all the
individual relation states
• Whenever the database is changed, a new state arises
• Basic operations for changing the database:
▫ INSERT a new tuple in a relation
▫ DELETE an existing tuple from a relation
▫ MODIFY an attribute of an existing tuple
• Next slide shows an example state for the COMPANY
database
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Update Operations on Relations

• INSERT a tuple.
• DELETE a tuple.
• MODIFY a tuple.
• Integrity constraints should not be violated by
the update operations.
• Several update operations may have to be
grouped together.
• Updates may propagate to cause other
updates automatically. This may be necessary to
maintain integrity constraints.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Update Operations on Relations

• In case of integrity violation, several actions can
be taken:
▫ Cancel the operation that causes the violation
(RESTRICT or REJECT option)
▫ Perform the operation but inform the user of the
violation
▫ Trigger additional updates so the violation is
corrected (CASCADE option, SET NULL option)
▫ Execute a user-specified error-correction routine

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Possible violations for each operation

• INSERT may violate any of the constraints:
▫ Domain constraint:
 if one of the attribute values provided for the new
tuple is not of the specified attribute domain
▫ Key constraint:
 if the value of a key attribute in the new tuple
already exists in another tuple in the relation
▫ Referential integrity:
 if a foreign key value in the new tuple references a
primary key value that does not exist in the
referenced relation
▫ Entity integrity:
 if the primary key value is null in the new tuple
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• DELETE may violate only referential integrity:

▫ If the primary key value of the tuple being deleted
is referenced from other tuples in the database
 Can be remedied by several actions: RESTRICT,
CASCADE, SET NULL (see Chapter 8 for more
details)
 RESTRICT option: reject the deletion
 CASCADE option: propagate the new primary key value
into the foreign keys of the referencing tuples
 SET NULL option: set the foreign keys of the
referencing tuples to NULL
▫ One of the above options must be specified during
database design for each foreign key constraint

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• UPDATE may violate domain constraint and

NOT NULL constraint on an attribute being
modified
• Any of the other constraints may also be
violated, depending on the attribute being
updated:
▫ Updating the primary key (PK):
 Similar to a DELETE followed by an INSERT
 Need to specify similar options to DELETE
▫ Updating a foreign key (FK):
 May violate referential integrity
▫ Updating an ordinary attribute (neither PK nor FK):
 Can only violate domain constraints
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

homework
• Consider the following relations for a database that keeps
track of student enrollment in courses and the books adopted
for each course:
• STUDENT(SSN, Name, Major, Bdate)
• COURSE(Course#, Cname, Dept)
• ENROLL(SSN, Course#, Quarter, Grade)
• BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
• TEXT(Book_ISBN, Book_Title, Publisher, Author)
• Draw a relational schema diagram specifying the foreign
keys for this schema.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Introduction to Relational Algebra

• select,
• project,
• cartesian product operations,
• join - Equi-join,
• natural join
• query examples

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relational algebra
• Relational algebra is a procedural query
language, which takes instances of relations as
input and yields instances of relations as output.
• It uses operators to perform queries. An
operator can be either unary or binary.
• They accept relations as their input and yield
relations as their output.
• Relational algebra is performed recursively on a
relation and intermediate results are also
considered relations.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The basic set of operations for the relational model is the

relational algebra.
• These operations enable a user to specify basic retrieval
requests as relational algebra expressions.
• The result of a retrieval is a new relation, which may
have been formed from one or more relations.
• The algebra operations thus produce new relations,
which can be further manipulated using operations of
the same algebra.
• A sequence of relational algebra operations forms a
relational algebra expression, whose result will also be a
relation that represents the result of a database query
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relational Calculus
• Relational calculus is a non-procedural query language,
and instead of algebra, it uses mathematical predicate
calculus.
• When applied to databases, it is found in two forms.
▫ Tuple relational calculus which was originally proposed by
Codd in the year 1972 and
▫ Domain relational calculus which was proposed by Lacroix
and Pirotte in the year 1977
• In first-order logic or predicate calculus, a predicate is a
truth-valued function with arguments. When we replace
with values for the arguments, the function yields an
expression, called a proposition, which will be either true
or false.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Unary Relational Operations:

1. SELECT and
2. PROJECT

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

The SELECT Operation

• The SELECT operation is used to choose a subset of
the tuples from a relation that satisfies a selection
condition
• SELECT operation restricts the tuples in a relation
to only those tuples that satisfy the condition
• horizontal partition of the relation into two sets of
tuples
• those tuples that satisfy the condition and are
selected, and those tuples that do not satisfy the
conditionand are discarded.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Select the EMPLOYEE tuples whose department

is 4, or those whose salary is greater than
$30,000

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• In general, the SELECT operation is denoted by

• R is generally a relational algebra expression

whose result is a relation—the simplest such
expression is just the name of a database
relation.
• Therelation resulting from the SELECT
operation has the same attributes as R.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The Boolean expression specified in <selection

condition> is made up of a number of clauses
of the form

• Clauses can be connected by the standard

Boolean operators and, or, and not to form a
general selection condition
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Select the tuples for all employees who either

work in department 4 and make over $25,000
per year, or work in department 5 and make over
$30,000,

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• the result of a SELECT operation can be

determined as follows:
• The <selection condition> is applied
independently to each individual tuple t in R.
• This is done by substituting each occurrence of
an attribute Ai in the selection condition with its
value in the tuple t[Ai].
• If the condition evaluates to TRUE, then tuple t
is selected.
• All the selected tuples appear in the result of the
SELECT operation.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Interpretation of Boolean conditions

AND, OR, and NOT
• (cond1 AND cond2) is TRUE
▫ if both (cond1) and (cond2) are TRUE;
▫ otherwise, it is FALSE.
• (cond1 OR cond2) is TRUE
▫ if either (cond1) or (cond2) or both are TRUE;
▫ otherwise, it is FALSE.
• (NOT cond) is TRUE
▫ if cond is FALSE;
▫ otherwise, it is FALSE.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Degree of the relation from SELECT

operation
• its number of attributes
• is the same as the degree of R.
• The number of tuples in the resulting relation is
always less than or equal to the number of
tuples in R.

• The fraction of tuples selected by a selection

condition is referred to as the selectivity of the
condition.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Commutative Property of SELECT

• SELECT operation is commutative

• a sequence of SELECTs can be applied in any

order.
• we can always combine a cascade (or
sequence) of SELECT operations into a
single SELECT operation with a conjunctive
(AND) condition; that is,

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

PROJECT Operation
• PROJECT operation selects certain columns
from the table and discards the other columns.
• If we are interested in only certain attributes of a
relation, we use the PROJECT operation to
project the relation over these attributes only
• vertical partition of the relation into two
relations
▫ one has the needed columns (attributes) and
contains the result of the operation, and
▫ the other contains the discarded columns.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

The general form of PROJECT

• Example: list each employee’s first and last

name and salary

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The result of the PROJECT operation has only

the attributes specified in <attribute list> in the
same order as they appear in the list.
• Hence, its degree is equal to the number of
attributes in <attribute list>.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Duplicate Elimination in PROJECT

• The PROJECT operation removes any duplicate
tuples, so the result of the PROJECT operation is
a set of distinct tuples, and hence a valid
relation.
• This is known as duplicate elimination.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The number of tuples in a relation resulting from

a PROJECT operation is always less than or
equal to the number of tuples in R.

• as long as <list2> contains the attributes in

<list1>;
• otherwise, the left-hand side is an incorrect
expression.
• commutativity does not hold on PROJECT.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Sequences of Operations and the

RENAME Operation
• for most queries, we need to apply several
relational algebra operations one after the other
▫ Either we can write the operations as a single
relational algebra expression by nesting the
operations, or
▫ we can apply one operation at a time and create
intermediate result relations
 we must give names to the relations that hold the
intermediate results.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example- In-Line relational algebra

expression
• retrieve the first name, last name, and salary of
all employees who work in department number
5, we must apply a SELECT and a PROJECT
operation

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example- Intermediate Relation

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Rename

• To rename the attributes in a relation, we simply

list the new attribute names in parentheses,

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• If no renaming is applied, the names of the

attributes in the resulting relation of a SELECT
operation are the same as those in the original
relation and in the same order.
• a PROJECT operation with no renaming, the
resulting relation has the same attribute names
as those in the projection list and in the same
order in which they appear in the list.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY

RENAME Operation General form SHARIKA T R, SNGCE

• RENAME operation when applied to a relation R of

degree n is denoted by any of the following three
forms

• symbol ρ (rho) is used to denote the RENAME

operator,
• S is the new relation name, and
• B1, B2, ..., Bn are the new attribute names.
▫ The first expression renames both the relation and its
attributes,
▫ the second renames the relation only, and
▫ the third renames the attributes only.
• If the attributes of R are (A1, A2, ..., An) in that
order, then each Ai is renamed as Bi.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY

The UNION, INTERSECTION, and SHARIKA T R, SNGCE

MINUS Operations
• UNION:
▫ The result of this operation, denoted by R ∪ S, is a
relation that includes all tuples that are either in R or
in S or in both R and S.
▫ Duplicate tuples are eliminated.
• INTERSECTION:
▫ The result of this operation, denoted by R ∩ S, is a
relation that includes all tuples that are in both R and
S.
• SET DIFFERENCE (or MINUS):
▫ The result of this operation, denoted by R – S, is a
relation that includes all tuples that are in R but not
in S.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example
• retrieve the Social Security numbers of all
employees who either work in department 5 or
directly supervise an employee who works in
department 5, we can use the UNION operation
as follows

• UNION operation produces the tuples that are in

either RESULT1 or RESULT2 or both, while
eliminating any duplicates.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

DEP5_EMPS

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• UNION, INTERSECTION, and SET

DIFFERENCE are binary operations;
▫ that is, each is applied to two sets

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Union compatibility or Type

compatibility
• Two relations R(A1, A2, ..., An) and S(B1, B2, ...,
Bn) are said to be union compatible (or
• type compatible)
▫ if they have the same degree n and if dom(Ai) =
dom(Bi) for 1 <=i<=n.
▫ This means that the two relations have the same
number of attributes and each corresponding pair
of attributes has the same domain.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

(a) Two union-compatible relations.

(b) STUDENT ∪ INSTRUCTOR

(c) STUDENT ∩ INSTRUCTOR

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

(d) STUDENT − INSTRUCTOR

(e) INSTRUCTOR − STUDENT

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

The UNION, INTERSECTION, and

MINUS Properties
• UNION and INTERSECTION are commutative
operations

• Both UNION and INTERSECTION can be

treated as n-ary operations applicable to any
number of relations because both are also
associative operations

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The MINUS operation is not commutative; that

is, in general,

• INTERSECTION can be expressed in terms of

union and set difference as follows

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

CARTESIAN PRODUCT (CROSS

PRODUCT) OR CROSS JOIN
• denoted by ×
• This is also a binary set operation, but the relations
on which it is applied do not have to be union
compatible
• the result of R(A1, A2, ..., An) × S(B1, B2, ..., Bm) is
a relation Q with degree n + m attributes Q(A1, A2,
..., An, B1, B2, ..., Bm), in that order.
• The resulting relation Q has one tuple for each
combination of tuples—one from R and one from S.
• Hence, if R has nR tuples (denoted as |R| = nR), and
S has nS tuples, then R × S will have nR * nS tuples.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

n-ary CARTESIAN PRODUCT

• produces new tuples by concatenating all
possible combinations of tuples from n
underlying relations

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example
• We want to retrieve a list of names of each
female employee’s dependents

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The CARTESIAN PRODUCT creates tuples with

the combined attributes of two relations.
• We can SELECT related tuples only from the
two relations by specifying an appropriate
selection condition after the Cartesian product,
as we did in the preceding example.
• Because this sequence of CARTESIAN
PRODUCT followed by SELECT is quite
commonly used to combine related tuples from
two relations, a special operation, called JOIN,
was created to specify this sequence as a single
operation
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

The JOIN Operation

• The JOIN operation, denoted by , is used to
combine related tuples from two relations into
single “longer” tuples.
• This operation is very important for any
relational database with more than a single
relation because it allows us to process
relationships among relations.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example
• Retrieve the name of the manager of each
department.
▫ To get the manager’s name, we need to combine
each department tuple with the employee tuple
whose Ssn value matches the Mgr_ssn value in the
department tuple.
▫ We do this by using the JOIN operation and then
projecting the result over the necessary attributes,
as follows

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

RESULT
Dname Lname Fname
Research Wong Franklin
Administration Wallance Jennifer
Headquaters Borg James
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The JOIN operation can be specified as a

CARTESIAN PRODUCT operation followed by a
SELECT operation.

• These two operations can be replaced with a

single JOIN operation as follows

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

General form of a JOIN

• JOIN operation on two relations5 R(A1, A2, ...,
An) and S(B1, B2, ..., Bm) is

• The result of the JOIN is a relation Q with n + m

attributes Q(A1, A2, ..., An, B1, B2, ... , Bm) in
that order;
• Q has one tuple for each combination of tuples—
one from R and one from S—whenever the
combination satisfies the join condition
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Difference between JOIN and

CARTESIAN PRODUCT
• In JOIN, only combinations of tuples satisfying the
join condition appear in the result,
• whereas in the CARTESIAN PRODUCT all
combinations of tuples are included in the result.
• The join condition is specified on attributes from the
two relations R and S and is evaluated for each
combination of tuples.
• Each tuple combination for which the join condition
evaluates to TRUE is included in the resulting
relation Q as a single combined tuple.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

THETA JOIN
• where each <condition> is of the form Ai θ Bj, Ai is
an attribute of R, Bj is an attribute of S, Ai and Bj
have the same domain, and
• θ (theta) is one of the comparison operators {=, <,
≤, >, ≥, ≠}.
• Tuples whose join attributes are NULL or for which
the join condition is FALSE do not appear in the
result
• the JOIN operation does not necessarily preserve
all of the information in the participating relations,
▫ because tuples that do not get combined with
matching ones in the other relation do not appear in
the result.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

EQUIJOIN
• a JOIN, where the only comparison operator
used is =, is called an EQUIJOIN
• in the result of an EQUIJOIN we always have
one or more pairs of attributes that have
identical values in every tuple

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

NATURAL JOIN
• NATURAL JOIN requires that the two join
attributes (or each pair of join attributes) have
the same name in both relations.
• If this is not the case, a renaming operation is
applied first.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Example
• combine each PROJECT tuple with the
DEPARTMENT tuple that controls the project
▫ first we rename the Dnumber attribute of
DEPARTMENT to Dnum
▫ so that it has the same name as the Dnum attribute
in PROJECT—and then we apply NATURAL JOIN

• The same query can be done in two steps by

creating an intermediate table DEPT

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• In the PROJ_DEPT relation, each tuple

combines a PROJECT tuple with the
DEPARTMENT tuple for the department that
controls the project, but only one join attribute
value is kept.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• If the attributes on which the natural join is

specified already have the same names in both
relations, renaming is unnecessary
• to apply a natural join on the Dnumber
attributes of DEPARTMENT and
DEPT_LOCATIONS

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• the join condition for NATURAL JOIN is

constructed by equating each pair of join
attributes that have the same name in the two
relations and combining these conditions with
AND.
• There can be a list of join attributes from each
relation, and each corresponding pair must have
the same name.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• more general, but nonstandard definition for

NATURAL JOIN is

• <list1> specifies a list of i attributes from R, and

• <list2> specifies a list of i attributes from S.
• The lists are used to form equality comparison
conditions between pairs of corresponding
attributes, and
• the conditions are then ANDed together.
• Only the list corresponding to attributes of the first
relation R—<list1>— is kept in the result Q.
• if no combination of tuples satisfies the join
condition, the result of a JOIN is an empty relation
with zero tuples.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Different Types of SQL JOINs

• INNER JOIN
▫ Returns records that have matching values in both tables
1. Theta join
2. EQUI join
3. Natural join
• OUTER JOIN
▫ In an outer join, along with tuples that satisfy the
matching criteria, we also include some or all tuples that
do not match the criteria.
1. Left Outer JOIN
2. Right Outer Join
3. Full Outer Join
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Left Outer Join

• In the left outer join, operation allows keeping all tuple
in the left relation.
• if there is no matching tuple is found in right relation,
then the attributes of right relation in the join result are
filled with null values.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Right Outer Join

• In the right outer join, operation allows keeping all tuple
in the right relation.
• However, if there is no matching tuple is found in the left
relation, then the attributes of the left relation in the join
result are filled with null values.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Full Outer Join

• In a full outer join, all tuples from both relations are
included in the result, irrespective of the matching
condition.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

DIVISION Operation
• Retrieve the names of employees who work on all the projects that
‘John Smith’ works on.
▫ query using the DIVISION operation, proceed as follows.
▫ First, retrieve the list of project numbers that ‘John Smith’ works on in
the intermediate relation SMITH_PNOS:

▫ Next, create a relation that includes a tuple <Pno, Essn> whenever the
employee whose Ssn is Essn works on the project whose number is Pno
in the intermediate relation SSN_PNOS:

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Finally, apply the DIVISION operation to the

two relations, which gives the desired
employees’ Social Security numbers:

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Query 1. Retrieve the name and address of all

employees who work for the ‘Research’
department.

• This query could be specified in other ways; for

example, the order of the JOIN and
• SELECT operations could be reversed, or the JOIN
could be replaced by a NATURAL JOIN after
renaming one of the join attributes to match the
other join attribute name.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Query 2. For every project located in

‘Stafford’, list the project number, the
controlling department number, and the
department manager’s last name, address, and birth
date.

• we first select the projects located in Stafford, then

join them with their controlling departments, and
then join the result with the department managers.
• Finally, we apply a project operation on the desired
attributes

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Query 3. Make a list of project numbers

for projects that involve an employee
whose last name is ‘Smith’, either as a worker or
as a manager of the department that controls the
project.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

SYLLABUS
• Structure of Relational Databases - Integrity
Constraints, Synthesizing ER diagram to
relational schema
• Introduction to Relational Algebra - select,
project, cartesian product operations, join -
Equi-join, natural join. query examples,
• Introduction to Structured Query Language
(SQL), Data Definition Language (DDL), Table
definitions and operations – CREATE, DROP,
ALTER, INSERT, DELETE, UPDATE.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

SQL
• SQL provides
▫ A data definition language (DDL)
▫ A data manipulation language (DML)
▫ A data control language (DCL)
• In addition SQL
▫ Can be used from other languages
▫ Is often extended to provide common
programming constructs (such as if-then tests,
loops, variables, etc.)
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Cont..
• SQL is a declarative (non-procedural) language
▫ Procedural - say exactly what the computer has
to do
▫ Non-procedural – describe the required result
(not the way to compute it)
• SQL is based on the relational model
▫ It has many of the same ideas
▫ Databases that support SQL are often
described as relational databases
▫ It is not always true to the model

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Cont..
• E/R designs can be implemented in SQL
▫ Entities, attributes, and relationships can all
be expressed in terms of SQL
▫ Many-to-many relationships are a problem, so
should be removed

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Relations, Entities, Tables

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Implementing E/R Designs

• Given an E/R design
▫ The entities become SQL tables
▫ Attributes of an entity become columns in the
corresponding table
▫ Relationships may be represented by foreign
keys

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Each entity becomes a

table in the database
▫ The name of the
table is often the
name of the entity
▫ The attributes
become columns of
the table with the
same name
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Schema and Catalog Concepts in SQL

• An SQL schema is identified by a schema name, and
includes an authorization identifier to indicate the
user or account who owns the schema, as well as
descriptors for each element in the schema.
• Schema elements include tables, constraints, views,
domains, and other constructs that describe the
schema
• A schema is created via the CREATE SCHEMA
statement, which can include all the schema elements
definitions.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Creates a schema called COMPANY, owned by

the user with authorization identifier ‘Jsmith’.

• not all users are authorized to create schemas

and schema elements.
• The privilege to create schemas, tables, and
other constructs must be explicitly granted to
the relevant user accounts by the system
administrator or DBA.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

SQL environment
• SQL environment is basically an installation of
an SQL-compliant RDBMS on a computer
system
• SQL uses the concept of a catalog a named
collection of schemas in an SQL environment.
• A catalog always contains a special schema
called INFORMATION_SCHEMA,
▫ which provides information on all the schemas in
the catalog and all the element descriptors in these
schemas

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Cont..
• Integrity constraints such as referential integrity
can be defined between relations only if they
exist in schemas within the same catalog.
• Schemas within the same catalog can also share
certain elements, such as domain definitions.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

DATABASE LANGUAGES
1. Data Definition Language (DDL):
▫ It is used to specify a database conceptual schema using set of
definitions.
▫ It supports the definition or declaration of database objects.
▫ The more common DDL commands are
 a.CREATE TABLE:
 ALTER TABLE
 DROP TABLE
 TRUNCATE
 COMMENT
 RENAME

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

2. Data Manipulation Language (DML)

▫ It provides a set of operations to support the basic data
manipulation operations on the data held in the database.
▫ It is used to query, update or retrieve data stored in a
database.
▫ Some of the tasks that come under DML are
 SELECT
 Used to query and display data from a database.
 INSERT
 Adds new rows to a table.
 UPDATE
 Changes an existing value in a column or group of columns
in a table.
 DELETE:
 Removes a specified row or set of rows from a table.
 MERGE.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

SQL Data Definition

• The set of relations in a database are specified using 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.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Basic Schema Definition: CREATE

TABLE Command in SQL
• CREATE TABLE command is used to specify a
new relation by giving it a name and specifying
its attributes and initial constraints.
• The attributes are specified first, and each
attribute is given a name, a data type to specify
its domain of values, and any attribute
constraints, such as NOT NULL.
• The key, entity integrity, and referential integrity
constraints can be specified within the CREATE
TABLE statement
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

CREATE TABLE
• Specifies a new base relation by giving it a name, and
specifying each of its attributes and their data types
(INTEGER, FLOAT, DECIMAL(i,j), CHAR(n),
VARCHAR(n))
• A constraint NOT NULL may be specified on an
attribute
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(10) NOT
NULL,
DNUMBER INTEGER NOT
NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9) );
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The relations declared through CREATE TABLE

statements are called base tables
• this means that the relation and its tuples are
actually created and stored as a file by the DBMS

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• In SQL2, can use the CREATE TABLE command for

specifying the primary key attributes, secondary keys,
and referential integrity constraints (foreign keys).
• Key attributes can be specified via the PRIMARY KEY
and UNIQUE phrases
CREATE TABLE DEPT (
DNAME VARCHAR(10) NOT
NULL,
DNUMBER INTEGER NOT
NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
);
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Attribute Data Types and Domains in

SQL
• The basic data types available for attributes
include
▫ numeric,
▫ character string,
▫ bit string,
▫ Boolean,
▫ date, and time
▫ timestamp

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Specifying Constraints in SQL

• Specifying Attribute Constraints and Attribute
Defaults
• Specifying Key and Referential Integrity
Constraints
• Giving Names to Constraints
• Specifying Constraints on Tuples Using CHECK

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Specifying Attribute Constraints and

Attribute Defaults
• a constraint NOT NULL may be specified if
NULL is not permitted for a particular attribute.
• This is always implicitly specified for the
attributes that are part of the primary key of
each relation,
▫ but it can be specified for any other attributes
whose values are required not to be NULL
• It is also possible to define a default value for an
attribute by appending the clause DEFAULT
<value> to an attribute definition.
Downloaded from Ktunotes.in
 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• If no default clause is specified, the default

default value is NULL for attributes that do not
have the NOT NULL constraint.
• Another type of constraint can restrict attribute
or domain values using the CHECK clause
following an attribute or domain definition.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• suppose that department numbers are restricted

to integer numbers between 1 and 20;
• then, we can change the attribute declaration of
Dnumber in the DEPARTMENT table

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Specifiying Key and Referiential

• PRIMARY KEY
▫ specifies one or more attributes that make up the
primary key of a relation.
▫ If a primary key has a single attribute, the clause
can follow the attribute directly

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• UNIQUE
▫ specifies alternate (secondary) keys
▫ can also be specified directly for a secondary key if
the secondary key is a single attribute

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY

• FOREIGN KEY SHARIKA T R, SNGCE

▫ a referential integrity constraint can be

violated when tuples are inserted or deleted, or
when a foreign key or primary key attribute
value is modified
▫ the schema designer
 can specify an alternative action to be taken by
attaching a referential triggered action clause to
any foreign key constraint. The options include:
SET NULL,
CASCADE, and
SET DEFAULT.
 An option must be qualified with either
ON DELETE or
 ON UPDATE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• ON DELETE SET NULL and ON UPDATE CASCADE for the

foreign key Super_ssn of EMPLOYEE means
• if the tuple for a supervising employee is deleted,
• the value of Super_ssn is automatically set to NULL for all
employee tuples that were referencing the deleted employee
tuple
• if the Ssn value for a supervising employee is updated the new
value is cascaded to Super_ssn for all employee tuples
referencing the updated employee tuple

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Giving Names to Constraints

• a constraint may be given a constraint name,
following the keyword CONSTRAINT
• The names of all constraints within a particular
schema must be unique.
• A constraint name is used to identify a particular
constraint
▫ in case the constraint must be dropped later and
replaced with another constraint

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Specifying Constraints on Tuples Using

CHECK
• CHECK clauses is specified at the end of a
CREATE TABLE statement
• These can be called tuple-based constraints
because they apply to each tuple individually
and are checked whenever a tuple is inserted or
modified

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The following SQL creates a CHECK constraint

on the "Age" column when the "Persons" table is
created. The CHECK constraint ensures that the
age of a person must be 18, or older:

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

DROP TABLE
• The SQL DROP TABLE statement is used to
remove a table definition and all the data,
indexes, triggers, constraints and permission
specifications for that table.
• You should be very careful while using this
command because once a table is deleted then
all the information available in that table will
also be lost forever.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

ALTER TABLE
• The SQL ALTER TABLE command is used to add, delete
or modify columns in an existing table.
• You should also use the ALTER TABLE command to add
and drop various constraints on an existing table.
• The basic syntax of an ALTER TABLE command to add a
New Column in an existing table is as follows.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The basic syntax of an ALTER TABLE command

to DROP COLUMN in an existing table is as
follows.

• The basic syntax of an ALTER TABLE command

to change the DATA TYPE of a column in a table
is as follows.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The basic syntax of an ALTER TABLE command

to add a NOT NULL constraint to a column in a
table is as follows.

• The basic syntax of ALTER TABLE to ADD

UNIQUE CONSTRAINT to a table is as follows

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The basic syntax of an ALTER TABLE command

to ADD CHECK CONSTRAINT to a table is as
follows.

• The basic syntax of an ALTER TABLE command

to ADD PRIMARY KEY constraint to a table is as
follows.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• The basic syntax of an ALTER TABLE command

to DROP CONSTRAINT from a table is as
follows.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

INSERT Command
• INSERT is used to add a single tuple to a
relation.
• We must specify the relation name and a list of
values for the tuple.
• The values should be listed in the same order in
which the corresponding attributes were
specified in the CREATE TABLE command.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• A second form of the INSERT statement allows the user to

specify explicit attribute names that correspond to the values
provided in the INSERT command.
• This is useful if a relation has many attributes but only a few
of those attributes are assigned values in the new tuple.
• However, the values must include all attributes with NOT
NULL specification and no default value.
• Attributes with NULL allowed or DEFAULT values are the
ones that can be left out.
• For example, to enter a tuple for a new EMPLOYEE for whom
we know only the Fname, Lname, Dno, and Ssn attributes

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Attributes not specified in U1A are set to their

DEFAULT or to NULL, and the values are listed
in the same order as the attributes are listed in
the INSERT command itself.
• It is also possible to insert into a relation
multiple tuples separated by commas in a single
INSERT command.
• The attribute values forming each tuple are
enclosed in parentheses.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• A DBMS that fully implements SQL should

support and enforce all the integrity constraints
that can be specified in the DDL
no department
no 2 is available

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• A variation of the INSERT command inserts multiple

tuples into a relation in conjunction with creating the
relation and loading it with the result of a query.
• For example,
▫ to create a temporary table that has the employee last
name, project name, and hours per week for each
employee working on a project

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

The DELETE Command

• The DELETE command removes tuples from a
relation.
• It includes a WHERE clause, similar to that used
in an SQL query, to select the tuples to be
deleted.
• Tuples are explicitly deleted from only one table
at a time.
• However, the deletion may propagate to tuples
in other relations if referential triggered actions
are specified in the referential integrity
constraints of the DDL

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• Depending on the number of tuples selected by

the condition in the WHERE clause, zero, one,
or sev_x0002_eral tuples can be deleted by a
single DELETE command.
• A missing WHERE clause specifies that all
tuples in the relation are to be deleted; however,
the table remains in the database as an empty
table.
• We must use the DROP TABLE command to
remove the table definition

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

TRUNCATE TABLE COMMAND

• The SQL TRUNCATE TABLE command is used
to delete complete data from an existing table.
• You can also use DROP TABLE command to
delete complete table but it would remove
complete table structure form the database and
you would need to re-create this table once again
if you wish you store some data.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

The UPDATE Command

• The UPDATE command is used to modify
attribute values of one or more selected tuples.
• As in the DELETE command, a WHERE clause
in the UPDATE command selects the tuples to
be modified from a single relation

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

• updating a primary key value may propagate to

the foreign key values of tuples in other relations
if such a referential triggered action is specified
in the referential integrity constraints of the
DDL
• An additional SET clause in the UPDATE
command specifies the attributes to be modified
and their new values.
• It is also possible to specify NULL or DEFAULT
as the new attribute value
• each UPDATE command explicitly refers to a
single relation only.
• To modify multiple relations, we must issue
several UPDATE commands.

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

Downloaded from Ktunotes.in

 PREPARED BY SHARIKA T R SNGCE

PREPARED BY
SHARIKA T R, SNGCE

MODULE 2 ENDS

Downloaded from Ktunotes.in