Relational Model - DBMS

Hi Folks, 
Continuing with DBMS, we'll take up relational model today. Previous article on ER model can be found here ER Model.
rdbms
Any query- shoot it in comments section!

==>> This article is a part of PK Series IT Officer

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:

σ<Selection condition>(R)  ( R would represent the relation or table from which selection is made )

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 È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. 
Relational Model

4. INTERSECTION

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

5. DIFFERENCE

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

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

rdbms

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.

rdbms

rdbms

rdbms

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

Join 40,000+ readers and get free notes in your email

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