Chapter No 2 :

Relational Data Model and Security and Integrity

Specification
Marks = 22

2.1----------------------------------- (10 Marks)

Data Model
Network Model
Hierarchical Model
Relational Model
Relational Model: - Basic Concepts Attributes and Domains. Key
Concepts:- Candidate key, Primary key, Foreign key and Super key.
E-R model, Components of ER Model, Types of attributes, role indicator,
weak & strong entity set.
Enhanced ER Model: Introduction, Specialization & Generalization.

2.2 ----------------------------------- (12 Marks)

Relational Algebra and Relational Calculus.

Database Design: Relational database Design, Functional
dependencies, Normalization based on functional dependencies,
Normal forms: 1NF, 2NF, 3NF, BCNF. Normalization based on
multivalued dependencies, Normalization based on Join
dependencies.
Integrity Constraints: Domain Integrity Constraints, Entity integrity
Constraints, Referential Integrity Constraints & on delete cascade.
Database Security: introduction, Data security requirements.

DATA MODEL
It is a collection of tools used for describing data, relationships among
data, data semantics and consistency constraints.
This is the way which describes the design of the database at physical,
logical and view level.
Different categories of Data models are

Relational Model
Network Model
Hierarchical Model
ER Model

Hierarchical Data Model

o Developed in 1960.
o It represent database as upside down tree.
o Parent- Child relationship is present.
o It support only one to many relationship
o Starting point is called as Root , subpart is called as node and the last node called as leaf node.

Network Data Model

o
o
o
o

Developed in 1964 and standardized in 1971.

It represent database as a arbitrary graph.
There is no parent-child relationship between nodes.
It supports many to many relationship.

Cust_id

CUSTOMER

Cust_name

Address

ACCOUNT

Ac_no

Balance

ATM_NO

ATM_CARD

ATM_N
O

PIN

CVV

.
C101

Sameer

A101

200000

2000310012

Pune

A101

2000310012

2278

134

Ac_no

Root Node

C102

Ajay

A102

300000

2000310015

Fig. Hierarchical Data Model

Bangalore

A102

2000310015

4587

475

Leaf Node

Cust_id

CUSTOMER

Ac_no

ACCOUNT

C101

C102

Sameer

Ajay

Pune

Bangalore

Cust_name

Balance

Address

Ac_no

ATM_NO

A101,A103

A102,A103

Fig. Network Data Model

A101

200000

2000310012

A103

300000

2000310015

A102

300000

2000310015

ER Model
o ER model is a Entity Relationship Model.
o It is a graphical representation of database including relationships,
constraints, roles and cardinalities in database.
o Components of ER model
o Entity Set
o Relationship Set
o Attribute

Entity
An entity is a thing or object in the real world that is distinguishable from
all other objects.
For example, each person in an enterprise is an entity. An entity has a set of properties,
and the values for some set of properties may uniquely identify an entity.

Attribute
Attributes are descriptive properties possessed by each member of an entity set. An
entity is represented by a set of attributes.
For example Possible attributes of the customer entity set are customer_id, customername, customer_street and customer_city.

Relationship
A relationship is an association among several entities.
For example, we can define a relationship that associates customer Hayes with loan L15. This relationship specifies that Hayes is a customer with loan number L-15.

Symbols For ER Diagrams

Rectangles,
which represent entity sets.
Ellipses,
which represent attributes.
Diamonds,
which represent relationship sets.
Lines,
which link attributes to entity sets
and entity sets to relationship sets.
Double ellipses, which represent multivalued attributes.
Dashed ellipses, which denote derived attributes.
Double rectangles,
which represent weak entity sets

Types of Attributes :
Simple Attribute

Primary Key Attribute

Multivalued Attribute

Derived Attribute

Composite Attribute

Role of Entity
The function that an entity plays in a relationship is called that entitys role.
We indicate roles in E-R diagrams by labeling the lines that connect diamonds
to rectangles. Figure 2.12 shows the role indicators manager and worker between
the employee entity set and the works-for relationship set.

Strong Entity Set

An entity set that has a primary key is termed a strong entity set.

Weak Entity Set

An entity set may not have sufficient attributes to form a primary key. Such an
entity set is termed a weak entity set.
For a weak entity set to be meaningful, it must be associated with another entity
set, called the identifying or owner entity set.

Types of Relationships / Mapping Cardinalities

PERSON

PAN CARD

Query Language
A query language is a language in which a user requests information from the database.
These languages are usually on a level higher than that of a standard database
programming language.
Query languages can be categorized as either procedural or nonprocedural.
In a procedural language, the user instructs the system to perform a sequence of operations on
the database to compute the desired result.
For example Relational Algebra
In a nonprocedural language, the user describes the desired information without giving a
specific procedure for obtaining that information.
For Example Relational Calculus

Relational Algebra
o
o
o

The relational algebra is a procedural query language.

It consists of a set of operations that take one or two relations as input and produce a new relation as
their result.
The fundamental operations in the relational algebra are select, project, union, set difference,
Cartesian product, and rename.
Unary operations
Binary operations

Unary Operations
o Select
o Project
o Rename
Binary Operations
o Union
o Intersection
o Set Difference
o Cartesian Product

Select Operation
o Select records from relation that satisfy the selection condition or search condition.
o Lower case Greek letter sigma ( ) is used to denote selection.
o Selection criteria or selection condition appears as subscript to sigma.
o Consider schema BOOK (B_ID,TITLE,AUTHOR,YEAR,PRICE).
o Syntax

(Selection condition)

(Relation)

o Example
Extract record of books having price less than 450/-

(price < 450 )

(BOOK)

Project Operation
o Project operation display certain columns or attributes from table/relation.
o Lower case Greek letter pi ( ) is used to denote project operation.
o Column names appears as subscript to pi.
o Consider schema BOOK (B_ID,TITLE,AUTHOR,YEAR,PRICE).
o Syntax

(Column Name1, Column Name2, .., Column Name n)

(Relation)

o Example
Extract title and author of books from BOOK relation

(title , author)

(BOOK)

Rename Operation

Rename operation use to rename relation/table name as well as attribute/column names.

Lower case Greek letter rho ( ) is used to denote rename operation.

1.

Rename only relation/table name.

2.

Rename only attribute/column names.

3.

Rename both relation/table name and attribute/column names at a time.

1.

Rename only relation/table name.

Syntax :

s (R)
new name of relation

Example :

old name of relation

arkp_stud (student)

2.

Rename only attribute/column names.

3.

Rename both relation/table name and attribute/column names at a

time.

Rename attribute/column names

Syntax :

(new names for attributes) (R)

name of relation

Example :

(s_rn, s_name, s_class) (student)

Rename relation/table name as well as attribute names

Syntax :

s (new names for attributes) (R)

new name of relation

Example :

old name of relation

arkp_stud(s_rn, s_name, s_class) (student)

Union Operation
o Union operation is a binary operation.
o It returns all data values that appear in either or both of the two relations.
o Denoted by symbol .
Syntax :
Relation 1 Relation 2
Example :

Cust_name (borrower )

BORROWER

Cust_name (depositor)

DEPOSITOR

BORROWER
union
DEPOSITOR

Cust_name

Loan_no

Cust_name

Acount_no

Adams

L_16

Adams

A_102

John

L_12

Johnson

A_105

John

Smith

L_13

Sam

A_209

Smith

Cust_name
Adams

Johnson
Sam

Intersection Operation
o Intersection operation is also a binary operation.
o It returns all data values which are common in two or more relations.
o Denoted by symbol

Relation 1 Relation 2

Syntax :
Example :

Cust_name (borrower )
BORROWER

Cust_name (depositor)

DEPOSITOR

Cust_name

Loan_no

Cust_name

Acount_no

Adams

L_16

Adams

A_102

John

L_12

Johnson

A_105

Smith

L_13

Sam

A_209

BORROWER
intersection
DEPOSITOR
Cust_name
Adams

Set Difference Operation

o Set Difference operation is also a binary operation.
o Set Difference operation allows us to find tuples which are present in first relation but not in
other relation.
o Denoted by symbol
.
Syntax :
Relation 1
Relation 2
Example :

Cust_name (borrower )
BORROWER

Cust_name (depositor)

DEPOSITOR

Cust_name

Loan_no

Cust_name

Acount_no

Adams

L_16

Adams

A_102

John

L_12

Johnson

A_105

Smith

L_13

Sam

A_209

BORROWER
set difference
DEPOSITOR
Cust_name
John
Smith

Cartesian product Operation

o Cartesian product operation is also a binary operation.
o Cartesian product operation combines two or more relations in such a way that
each row of one relation combine with each row of other relation.
o Denoted by symbol .
Syntax :

Example :

Relation 1

borrower

Relation 2

depositor

BRROWER DEPOSITOR

BORROWER
Cust_name

Loan_no

Cust_name

Loan_no

Cust_name

Acount_no

Adams

L_16

Adams

L_16

Adams

A_102

John

L_12

Adams

L_16

Johnson

A_105

Smith

L_13

Adams

L_16

Sam

A_209

John

L_12

Adams

A_102

John

L_12

Johnson

A_105

DEPOSITOR
Cust_name

Acount_no

John

L_12

Sam

A_209

Adams

A_102

Smith

L_13

Adams

A_102

Johnson

A_105

Smith

L_13

Johnson

A_105

Sam

A_209

Smith

L_13

Sam

A_209

Fig. Cartesian Product

Relational Calculus
It is a non procedural query language while relational algebra is procedural.
There are two variant of relational calculus
Tuple Relational Calculus (TRC)
Domain Relational Calculus (DRC)
Tuple Relational Calculus

o In TRC , All operations are depend on tuples.

o A tuple variable is a variable that takes tuples of a particular relation. Tuple variable has same number of
columns as in relation.
o A tuple relational calculus query has the form
{ T | p(T) }
where T is tuple variable
p(T) is formula that operates/describe tuple variable (T)
o Example
query = find all sailors with a rating above 7
the tuple relational calculus query will be

{ S | S sailors S.rating > 7 S.AGE<30}

Domain Relational Calculus

o In DRC , All operations are depend on attribute domains (columns).

o A domain variable is a variable that contains values from some attribute domains.
o A domain relational calculus query has the form
{ (X1,X2,.,Xn) | p(X1,X2,.,Xn)
where each Xi is either a domain variable or constant
p(X1,X2,.,Xn) is formula that operates/describe tuple variable (T)
o Example
query = find all sailors with a rating above 7 AND AGE IS BELOW 30
the tuple relational calculus query will be

{ (I,N,T,A) | (I,N,T,A) sailors T > 7 A<30 }

SAILORS
sid

sname

rating

age

22

Dustin

45

29

Brutus

29

31

Lubber

39

32

Andy

42

58

Rusty

10

35

Table : SAILORS

FUNCTIONAL DEPENDENCY
A

a1

b1

c1

d1

a1

b1

c1

d2

a1

b2

c2

d1

a2
b1
Fig. A relation that satisfy FD

c3
AB C

d1

If AB C then following condition must be present in relation R

T1.(AB) =(a1b1)
T2.(AB)=(a1b1)

&

Now consider
T1(C) = C1
T2(C) = C1
Therefore we can say that
AB C

NORMALIZATION
Normalization can be defined as a process of
o Minimizing Redundancy
o Minimizing Inconsistency
o Minimizing the insertion, deletion and update anomalies from the database.
There are different type of normal forms having there own condition OR criteria for normalization.
Different normal forms are

First Normal form ( 1NF )

Second Normal form (2 NF )
Third Normal form (3 NF )
Boyce/Codd Normal form (BCNF )

 This example illustrates the concept of functional dependency. The situation

modelled is that of college students visiting one or more lectures in each of which
they are assigned a teaching assistant (TA). Let's further assume that every
student is in some semester and is identified by a unique integer ID.
StudentID Semester Lecture
TA
1234 6
Numerical Methods
Aisaule
1221 4
Numerical Methods
Dimash
1234 6
Visual Computing
Ahmed
1201 2
Numerical Methods
Peter
1201 2
Physics II
Simone
We notice that whenever two rows in this table feature the same StudentID, they
also necessarily have the same Semester values. This basic fact can be expressed
by a functional dependency:

StudentID Semester.

Note that if a row was added where the student had a different value of semester
that the FD would no longer exist. This means that the FD is implied by the data
as it is possible to have values that would invalidate the FD.

First Normal form ( 1NF )

A relation is in 1 NF if and only if every attribute of a relation is single OR atomic
DNAME

DNUMBER

DMGR_NO

DLOCATIONS

Research

05

3320

{Delhi, Bombay, Calcutta}

Administration

04

2924

{Ahmedabad, Nagpur}

Headquarter

01

2221
{Chennai}
Fig. DEPARTMENT (not in 1NF )
DNUMBER DLOCATIONS

DNAME

DNUMBER

DMGR_NO

Research

05

3320

Administration

04

2924

Headquarter

01

2221

Fig. DEPARTMENT (In 1NF )

05

Delhi

05

Bombay

05

Calcutta

04

Ahmedabad

04

Nagpur

01

Chennai

Fig. DLOCATIONS (In 1NF )

Second Normal Form ( 2NF )

A relation is in second normal if and only if it is in 1NF every nonprime attribute is
fully functional depend on primary key of a relation.

Property_ID City_Name

Sector_No

Area

Price

Table : PROPERTY
Property_ID City_Name

Sector_No

Table : PROPERTY_1
City_Name

Tax_Rate

Table : PROPERTY_2

Area

Price

Tax_Rate

Third Normal Form ( 3NF )

A relation is in third normal form if and only if, it is in 2NF and there is no
transitive dependency in a relation.

Property_ID City_Name

Sector_No

Area

Table : PROPERTY_1
Property_ID City_Name

Sector_No

Table : PROPERTY_11
Sector_No

Area

Table : PROPERTY_12

Price

Price