Hi Folks,
Deepak A
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:
σ<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 È 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
