Hi Folks,

Continuing with DBMS, we'll take up relational model today. Previous article on ER model can be found here ER Model.

### Relational Model

Some important points to note here about attributes in a relation are as follows:

1. Each attribute of a relation has a name.

2. The set of allowed values for each attribute is called the

**domain**of the attribute
3. Attribute values are required to be

**atomic**i.e indivisible.
4. Domain is said to be atomic if all its members are atomic.

5. The special value

**NULL**is a member of every domain.
6. A relation is a set of n tuples (a1, a2, a3...) where each attribute belongs to corresponding domain.

## Relational Algebra

The basic operators used in relational algebra are as follows:

#### 1. SELECT

It is used to obtain a subset of the tuples of a relation that

**satisfy**a select condition. The SELECT operator is denoted by symbol σ (sigma). The syntax for SELECT statement would be as follows:**σ**( R would represent the relation or table from which selection is made )

_{<Selection condition>}(R)

**Example:**

*SELECT dob '01/jan/1978' (employee)*

#### 2. PROJECT

It is used to select a subset of attributes of a relation by

**specifying the names**of required attributes. The symbol used for PROJECT operator is ∏ (pie). The syntax is as follows:**∏**

_{<attribute list>}(R)

**Example:**

*PROJECT surname, empno (employee)*

#### 3. UNION

UNION of two relations r and s designated as r È s is a relation that includes all the tuples that are either in r or in s or in both r and s. Duplicate tuples are eliminated.

#### 4. INTERSECTION

INTERSECTION of two relations r and s denoted by r Ç s, includes all tuples that are in

**both**r and s

#### 5. DIFFERENCE

DIFFERENCE of two relation r and s denoted by r - s, includes all tuples that are in r but not in s

#### 6. CARTESIAN PRODUCT

It is also referred to as cross product or cross join denoted by r x s, creates a relation that has all attributes of r and s i.e allowing

**all combinations**of tuples from r and s#### 7. JOIN

It is used to combine related tuples from two relations. It can be further studies in three forms:

**a) Inner Join**

It joins two tables according to matching of a certain criteria using a

**comparison operator**. SQL representation is as follows:
SELECT * from tab1

JOIN tab2

ON tab1.col_name = tab2.col_name

**b) Equi Join**

It joins two tables based on matching of certain criteria using

**equal sign**(=) as the comparison operator in WHERE clause.
SELECT col_name

FROM tab1, tab2

WHERE tab1.col_name = tab2.col_name.

**c) Outer Join**

It joins all rows from both tables which satisfy the join condition and which do not satisfy the join condition thereby avoiding loss of information. It is of 3 types further:

- LEFT OUTER JOIN - keep data from
**left**hand table - RIGHT OUTER JOIN - keep data from
**right**hand table - FULL OUTER JOIN - keep data from
**both**tables

Lets look at the example figures below to solidify the understanding of outer joins.

*The figures have been taken from the DBMS book by Korth, for a better understanding.*

### Quote of the day

If you don't burn out at the end of each day, you're a bum. - George L

*Deepak A*

*Smart Prep Kit for Banking Exams by Ramandeep Singh - Download here*
## 0 comments:

## Post a Comment

Thanks for commenting. It's very difficult to answer every query here, it's better to post your query on IBPSToday.com