Module 2 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.
Database Management System
Module 2

Lect 1 : Relational data model

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
 Row in relational table is called a tuple,
column is attribute and table is a relation
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)
Domain
 A domain is a unique set of values permitted
for an attribute in a table
 It has three parts
◦ Name
◦ Data type
◦ Values
 Eg: a domain of month-of-year have
Domain Name : Month
Data type :Data
Values : Jan, Feb, Mar.....
Tuples (rows)
 A tuple is an ordered set of values
 Tuple is a portion of a table containing
data that described only entity,
 Also known as record
 Each value is derived from an appropriate
domain.
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.
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
Relations (Tables)
 A table of values
 A relation may be thought of as a set of
rows & 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.
Terms related to Relational Model

 Schema of a Relation
 Degree of a Relation
 Relation state
Schema of a Relation
 It is basically an outline of how data is
organized
 It is denoted by R (A1, A2, .....An)
◦ R is relation name and it has some attributes
A1 to An
 Each attribute have some domain and it is
represented by dom(Ai)
 For example, the domain of Cust-id is 6
digit numbers
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:in
teger)
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
overthe domain of character strings of
maximumlength 25
◦ dom(Cust-name) is varchar(25)
 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
Database Management System
Module 2

Lect 2 : Relational Integrity

Constraint
Relational Integrity Constraints
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
Key Constraints
 Superkey :
 It is the combination of one or more
attribute which can be used to uniquely
identify a tuple/row in a relation R
 Consider the following relation Student

Student Relation
Rollno Name Class Section Age Address
1 Akhil 10 A 16 EKM
2 Amal 10 A 16 TVM
3 Aji 10 A 16 EKM
1 Ali 10 B 16 TVM
2 Akhil 10 B 16 TVM
 Student Relation
Rollno Name Class Section Age Address
1 Akhil 10 A 16 EKM
2 Amal 10 A 16 TVM
3 Aji 10 A 16 EKM
1 Ali 10 B 16 TVM
2 Akhil 10 B 16 TRC

 Super Key – {Rollno,Name}

 Super Key – {Rollno,Name,Section}
 Super Key – {Rollno,Name,Section,Address}
 Super Key – {Rollno,Name,Address}
 Super Key – {Rollno,Address}
Candidate Key
 It is minimal Super Key
 It is a Super Key which cannot be
reducible further
 A candidate key is a subset of a super key
set
 A Candidate Key can be a Super Key but
Super Key cannot be candidate Key
Eg Consider the following Super Key sets
 Super Key – {Rollno,Name}
 Super Key – {Rollno,Name,Section}
 Super Key –{Rollno,Name,Section,Address}
 Super Key – {Rollno,Name,Address}
 Super Key – {Rollno,Address}
Candidate Keys - {Rollno,Name}, {Rollno,Address}

Student Relation
Rollno Name Class Section Age Address
1 Akhil 10 A 16 EKM
2 Amal 10 A 16 TVM
3 Aji 10 A 16 EKM
1 Ali 10 B 16 TVM
2 Akhil 10 B 16 TRC
Primary Key
 Data base designers while designing the db
which choose any of the candidate key to
uniquely identify a record in a relation which
will be considered as Primary Key

 Consider we have 2 Candidate Key sets

Candidate Keys - {Rollno,Name},

{Rollno,Address}

Any one set can be considered as primary key

Primary Key - {Rollno,Name},
Entity Integrity
 The entity integrity constraint states
that no primary key value can be
NULL.
 This is because the primary key value is
used to identify individual tuples in a
relation.
 Having NULL values for the primary key
implies that we cannot identify some
tuples.
Referential Integrity Constraint
 A constraint involving two relations
 Used to specify a relationship among
tuples in two relations:
◦ One relation is called referencing relation
◦ Other relation is called referenced relation
 Eg :attribute Dno of EMPLOYEE gives the
department number for which each employee
works; hence, its value in every EMPLOYEE tuple
must match the Dnumber value of some tuple in
the DEPARTMENT relation.
 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.
 i.e Foreign key is an attribute in a
relation which refers to the primary key
of another relation
 A referential integrity constraint can be
displayed in a relational database schema
as a directed arc from R1.FK to R2.
Example 2

Relational Schema
Database Management System
Module 2

Lect 3 : Converting ER diagram to

Relational Schema
Converting ER diagram to relational
Schema
1) Converting Strong Entity set into
Relational Schema
2) Composite Attributes to Relational schema

Make F_Name,L_Name as individual attribute

3) Representing weak entity set in
relational schema
 Include the primary key of strong entity
set in the weak entity
4) Representing Many- Many relationship in
relational schema
5) Representing Many- One or One- Many
relationship with total participation in
relational schema
6) Representing Many- One or One- Many
relationship in relational schema
7) Representing One- One relationship in
relational schema
8) Representing One- One relationship
with total participation in relational
schema
9) Representing One- One relationship
with total participation at both ends in
relational schema
10) Representing Multivalued attributes in
relational schema
Database Management System
Module 2

Lect 3a : RELATIONAL
ALGEBRA
RELATIONAL ALGEBRA
 Relational Algebra is procedural query
language, which takes Relation as input and
generate relation as output
 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
 Relational algebra is composed of various
operations.
The SELECT Operation
 The SELECT operation is used to choose a tuples
(rows) from a relation that satisfies a selection
condition.
 In general, the SELECT operation is denoted by

 where the symbol σ (sigma) is used to denote the

SELECT operator and the selection condition is a
Boolean expression (condition) specified on the
attributes of relation R
The SELECT Operation

 For example, to select the EMPLOYEE

tuples whose department is 4, or those
whose salary is greater than $30,000
The SELECT Operation
 For example, to 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, we can
specify the following SELECT operation:
The PROJECT Operation
 The PROJECT operation selects certain
columns from the table and discards the
other columns
 The general form of the PROJECT operation
is

(pi) is the symbol used to represent the

PROJECT operation,
<attribute list> is the list of attributes from
the attributes of relation R
 For example, to list each employee’s first
and last name and salary, we can use the
PROJECT operation as follows:
 For example, to list each employee’s first and
last name and salary whose salary is greater
than 30000

Π Firstname, Lastname( salary>3000

(Employee))

For example, to retrieve the first name, last

name, and salary of all employees who work
in department number 5
The UNION, INTERSECTION
and MINUS Operations
 Two relations R(A1, A2, ..., An) and S(B1, B2, ..., Bn) are said to be
union compatible (or type compatible) if two relations have
the same number of attributes and each corresponding pair of
attributes has the same domain.

 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 is 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.
 CARTESIAN PRODUCT (CROSS
PRODUCT) Operation
 The CARTESIAN PRODUCT
operation—also known as CROSS
PRODUCT or CROSS JOIN—which is
denoted by X .
 For R X S , the cartesian product
operation defines a relation that is the
concatenation of every tuple of relation R
with every tuple of relation S
The JOIN Operation
 The JOIN operation, denoted by ,
is used to combine related tuples from
two relations into single “longer” tuples
 A Join combines two tuples from two
relation only if the join condition is
satisfied
 Example : retrieve the name of the
manager of each department
The JOIN Operation
 Equi join & Natural Join
 Left Outer join
 Right Outer Join
Natural Join
 Natural join can only be performed if there
is a common attribute between the relation.
 The name & type of the attribute must be
same
Equi Join
̣ The most common use of JOIN involves join
conditions with equality comparisons only.
Such a JOIN, where the only comparison
operator used is =, is called an EQUIJOIN
 Example : retrieve the name of the
manager of each department
We can also combine the relational algebra
expression as follows
Prev Year University Questions on
Relational Algebra
Database Management System
Module 2

Lect 4 : Introduction to Structured

Query Language
History
Data Definition Languages
Domain Types in SQL
DDL Commands
SQL Create
Attribute Datatype Constraint
Roll No Varchar(5) Primary key
Name Varchar(10) Not Null
Subjectcode Varchar(5) References Subject
Age Int
1. Create the following table with
constraints

Create table student (rollno varchar(5),name varchar(10)

not null,subjectcode varchar(5) age int,
primary key(rollno),
foreign key(subjectcode) references subject);
Rollno Name Subjectcode Age
1 Anil CS203 21
2 Akash CS206 22

2. Insert the following table student

Insert into student values(‘1’, ‘Anil’, ‘CS203’, 21’)

Insert into student values(‘2’, ‘Akash’, ‘CS206’, 22’)

3. Add a new column University varchar(10)

Alter table student add(university varchar(10);

Rollno Name Subjectcode Age University
1 Anil CS203 21 Kerala
2 Akash CS206 22 KTU

4. Update the university column of the table

Update student set university=‘kerala’ where rollno=‘1’;

Update student set university=‘KTU’ where rollno=‘2’;

5. Delete the record of roll no 2

Delete from student where rollno=‘2’;

6. Delete all records from table student

Delete from student;

University Question
2. What is meant by referential integrity?
How is it implemented using foreign key?
Illustrate using a real example ?

Hint : Refer the topic Referential Integrity

 3.
4.
 What is entity integrity constraint? Why
is it important?

Hint : Refer entity Integrity constraint