DBMS - Normalization & Normal Forms

Normalization is the process of organizing the attributes of database to reduce or eliminate data redundancy (having same data but at different places).

Problems because of data redundancy
Data redundancy unnecessarily increases size of database as same data is repeated on many places. Inconsistency problems also arise during insert, delete and update operations.

Functional Dependency
Functional Dependency is a constraint between two sets of attributes in a relation from a database. Functional dependency is denoted by arrow (→). If an attributed A functionally determines B, then it is written as A → B.
For example employee_id → name means employee_id functionally determines name of employee. As another example in a time table database, {student_id, time} → {lecture_room}, student ID and time determine the lecture room where student should be.

What does functionally dependent mean?
A function dependency A → B mean for all instances of a particular value of A, there is same value of B.

For example in the below table A → B is true, but B → A is not true as there are different values of A for B = 3.

A   B
------
1   3
2   3
4   0
1   3
4   0

Trivial Functional Dependency
X→Y is trivial only when Y is subset of X.
Examples

ABC --> AB
ABC --> A
ABC --> ABC

Non Trivial Functional Dependencies
X→Y is a non trivial functional dependencies when Y is not a subset of X.

X→Y is called completely non-trivial when X intersect Y is NULL.
Examples:

Id --> Name, 
Name --> DOB

Normal forms
Normal forms are used to eliminate or reduce redundancy in database tables.

First Normal Form
A relation is in first normal form if every attribute in that relation is singled valued attribute.

Example :

 
ID   Name   Courses
------------------
1    A      c1, c2
2    E      c3
3    M      C2, c3

In the above table Course is a multi valued attribute so it is not in 1NF.

Below Table is in 1NF as there is no multi valued attribute
ID   Name   Course
------------------
1    A       c1
1    A       c2
2    E       c3
3    M       c1
3    M       c2

Second Normal Form

A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. 

For example consider following functional dependencies in relation  R (A,  B , C,  D )

AB -> C  [A and B together determine C]
BC -> D  [B and C together determine D]

In the above relation, AB is the only candidate key and there is no partial dependency, i.e., any proper subset of AB doesn’t determine any non-prime attribute.

Third Normal Form
A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency X→Y
a) x is a super key.
b) Y is a prime attribute (each element of Y is part of some candidate key).

For example consider relation R(A, B, C, D, E)
  A -> BC,
  CD -> E, 
  B -> D, 
  E -> A

All possible candidate keys in above relation are {A, E, CD, BC}
All attribute are on right sides of all functional dependencies are prime.

BCNF 
A relation is in BCNF if in every non-trivial functional dependency X→Y, X is a super key.

For example consider relation R(A, B, C) 
  A -> BC, 
  B -> A

A and B both are super keys so above relation is in BCNF.

Key Points

1. BCNF is free from redundancy.
2. If a relation is in BCNF, then 3NF is also also satisfied.
3.  If all attributes of relation are prime attribute, then the relation is always in 3NF.
4. A relation in a Relational Database is always and at least in 1NF form.
5. Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF.
6. If a Relation has only singleton candidate keys( i.e. every candidate key consists of only 1 attribute), then the Relation is always in 2NF( because no Partial functional dependency possible).
7. Sometimes going for BCNF form may not preserve functional dependency. In that case go for BCNF only if the lost FD(s) is not required, else normalize till 3NF only.
8. There are many more Normal forms that exist after BCNF, like 4NF and more. But in real world database systems it’s generally not required to go beyond BCNF.

Steps to find the highest normal form of a relation:

1. Find all possible candidate keys of the relation.
2. Divide all attributes into two categories: prime attributes and non-prime attributes.
3. Check for 1st normal form then 2nd and so on. If it fails to satisfy nth normal form condition, highest normal form will be n-1.

Example 1. Find the highest normal form of a relation R(A,B,C,D,E) with FD set {A→D, B→A, BC→D, AC→BE}

Step 1.   As we can see, (AC)+ ={A,C,B,E,D}  but none of its subset can determine all attribute of relation, So AC will be candidate key. A can be derived from B, so we can replace A in AC by B. So BC will also be a candidate key. So there will be two candidate keys {AC, BC}.

Step 2.  Prime attribute are those attribute which are part of candidate key {A,B,C} in this example and others will be non-prime {D,E} in this example.

Step 3.  The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is not in 2nd Normal form because A→D is partial dependency (A which is subset of candidate key AC is determining non-prime attribute D) and 2nd normal form does not allow partial dependency.

So the highest normal form will be 1st Normal Form.

Example 2. Find the highest normal form of a relation R(A,B,C,D,E) with FD set as {BC→D, AC→BE, B→E}

Step 1.   As we can see, (AC)+ ={A,C,B,E,D}  but none of its subset can determine all attribute of relation, So AC will be candidate key. A or C can’t be derived from any other attribute of the relation, so there will be only 1 candidate key {AC}.

Step 2.  Prime attribute are those attribute which are part of candidate key {A,C} in this example and others will be non-prime {B,D,E} in this example.

Step 3.  The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is in 2nd normal form because BC→D is in 2nd normal form (BC is not proper subset of candidate key AC) and AC→BE is in 2nd normal form (AC is candidate key) and B→E is in 2nd normal form (B is not a proper subset of candidate key AC).

The relation is not in 3rd normal form because in BC→D (neither BC is a super key nor D is a prime attribute) and in B→E (neither B is a super key nor E is a prime attribute) but to satisfy 3rd normal for, either LHS of an FD should be super key or RHS should be prime attribute.

So the highest normal form of relation will be 2nd Normal form.

Example 3. Find the highest normal form of a relation R(A,B,C,D,E) with FD set {B→A, A→C, BC→D, AC→BE}

Step 1.   As we can see, (B)+={B,A,C,D,E}, so B will be candidate key. B can be derived from AC using AC→B (Decomposing AC→BE to AC→B and AC→E). So AC will be super key but (C)+={C} and (A)+={A,C,B,E,D}. So A (subset of AC) will be candidate key. So there will be two candidate keys {A,B}.

Step 2.  Prime attribute are those attribute which are part of candidate key {A,B} in this example and others will be non-prime {C,D,E} in this example.

Step 3.  The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is in 2nd normal form because B→A is in 2nd normal form (B is a super key) and A→C is in 2ndnormal form (A is super key) and BC→D is in 2nd normal form (BC is a super key) and AC→BE is in 2nd normal form (AC is a super key).

The relation is in 3rd normal form because LHS of all FD’s are super keys. The relation is in BCNF as all LHS of all FD’s are super keys. So the highest normal form is BCNF.

DBMS - Finding Attribute Closure & Candidate Keys using Functional Dependencies

What is Functional Dependency?

A functional dependency X→Y in a relation holds if two tuples having same value for X also have same value for Y i.e  X uniquely determines Y.

In EMPLOYEE relation given in Table 1,

• FD E-ID → E-NAME holds because for each E-ID, there is a unique value of E-NAME.
• FD E-ID → E-CITY and E-CITY → E-STATE also holds.
• FD E-NAME → E-ID does not hold because E-NAME ‘John’ is not uniquely determining E-ID. There are 2 E-IDs corresponding to John (E001 and E003).

EMPLOYEE

E-ID	E-NAME	E-CITY	E-STATE
E001	John	Delhi	Delhi
E002	Mary	Delhi	Delhi
E003	John	Noida	U.P.

Table 1

The FD set for EMPLOYEE relation given in Table 1 are:

{E-ID->E-NAME, E-ID->E-CITY, E-ID->E-STATE, E-CITY->E-STATE}

Trivial versus Non-Trivial Functional Dependency: A trivial functional dependency is the one which will always hold in a relation.

X->Y will always hold if X ⊇ Y

In the example given above, E-ID, E-NAME → E-ID is a trivial functional dependency and will always hold because {E-ID,E-NAME} ⊃ {E-ID}. You can also see from the table that for each value of {E-ID, E-NAME}, value of E-ID is unique, so {E-ID, E-NAME} functionally determines E-ID.

If a functional dependency is not trivial, it is called Non-Trivial Functional Dependency. Non-Trivial functional dependency may or may not hold in a relation. e.g; E-ID → E-NAME is a non-trivial functional dependency which holds in the above relation.

Properties of Functional Dependencies:

Let X, Y, and Z are sets of attributes in a relation R. There are several properties of functional dependencies which always hold in R also known as Armstrong Axioms.

1. Reflexivity: If Y is a subset of X, then X → Y. e.g.; Let X represents {E-ID, E-NAME} and Y represents {E-ID}. {E-ID, E-NAME} → {E-ID} is true for the relation.
2. Augmentation: If X → Y, then XZ → YZ. e.g.; Let X represents {E-ID}, Y represents {E-NAME} and Z represents {E-CITY}. As {E-ID} → E-NAME is true for the relation, so { E-ID,E-CITY} → {E-NAME,E-CITY} will also be true.
3. Transitivity: If X → Y and Y → Z, then X → Z. e.g.; Let X represents {E-ID}, Y represents {E-CITY} and Z represents {E-STATE}. As {E-ID} → {E-CITY} and {E-CITY} → {E-STATE}  is true for the relation, so { E-ID } → {E-STATE} will also be true.
4. Attribute Closure: The set of attributes that are functionally dependent on the attribute A is called Attribute Closure of A and it can be represented as A+.

Steps to Find the Attribute Closure of A:

Q. Given FD set of a Relation R, The attribute closure set S be the set of A

1. Add A to S.
2. Recursively add attributes which can be functionally determined from attributes of the set S until done.

From Table 1, FDs are

Given R(E-ID, E-NAME, E-CITY, E-STATE)

FDs = { E-ID→E-NAME, E-ID→E-CITY, E-ID→E-STATE, E-CITY→E-STATE }

The attribute closure of E-ID can be calculated as:

1. Add E-ID to the set {E-ID}
2. Add Attributes which can be derived from any attribute of set. In this case, E-NAME and E-CITY, E-STATE can be derived from E-ID. So these are also a part of closure.
3. As there is one other attribute remaining in relation to be derived from E-ID. So result is:

(E-ID)+ = {E-ID, E-NAME, E-CITY, E-STATE }

Similarly,

(E-NAME)+ = {E-NAME}
(E-CITY)+ = {E-CITY, E_STATE}

---

Question 1. Find the attribute closures of given FDs R(ABCDE) = {AB → C, B → D, C → E, D → A} To find (B)+ ,we will add attribute in set using various FD which has been shown in table below.  

Attributes Added in Closure	FD used
{B}	Triviality
{B,D}	B→D
{B,D,A}	D→A
{B,D,A,C}	AB→C
{B,D,A,C,E}	C→E

• We can find (C, D)+ by adding  C and D into the set (triviality) and then E using(C→E) and then A using (D→A) and set becomes.

    (C,D)+ = {C,D,E,A}

• Similarly, we can find (B,C)+ by adding B and C into the set (triviality) and then D using (B→D) and then E using (C→E) and then A using (D→A) and set becomes

   (B,C)+ ={B,C,D,E,A}

---

Candidate Key:

Candidate Key is minimal set of attributes of a relation which can be used to identify a tuple uniquely. For Example, each tuple of EMPLOYEE relation given in Table 1 can be uniquely identified by E-ID and it is minimal as well. So it will be Candidate key of the relation.

A candidate key may or may not be a primary key.

Super Key:

Super Key is set of attributes of a relation which can be used to identify a tuple uniquely.For Example, each tuple of EMPLOYEE relation given in Table 1 can be uniquely identified by E-ID or (E-ID, E-NAME) or (E-ID, E-CITY) or (E-ID, E-STATE) or (E_ID, E-NAME, E-STATE) etc. So all of these are super keys of EMPLOYEE relation.

Note: A candidate key is always a super key but vice versa is not true.

---

Question 2. Finding Candidate Keys and Super Keys of a Relation using FD set The set of attributes whose attribute closure is set of all attributes of relation is called super key of relation. For Example, the EMPLOYEE relation shown in Table 1 has following FD set. {E-ID → E-NAME, E-ID → E-CITY, E-ID → E-STATE, E-CITY → E-STATE}

Let us calculate attribute closure of the different set of attributes:

(E-ID)+ = {E-ID, E-NAME,E-CITY,E-STATE}
(E-ID,E-NAME)+ = {E-ID, E-NAME,E-CITY,E-STATE}
(E-ID,E-CITY)+ = {E-ID, E-NAME,E-CITY,E-STATE}
(E-ID,E-STATE)+ = {E-ID, E-NAME,E-CITY,E-STATE}
(E-ID,E-CITY,E-STATE)+ = {E-ID, E-NAME,E-CITY,E-STATE}
(E-NAME)+ = {E-NAME}
(E-CITY)+ = {E-CITY,E-STATE}

As (E-ID)+, (E-ID, E-NAME)+, (E-ID, E-CITY)+, (E-ID, E-STATE)+, (E-ID, E-CITY, E-STATE)+ give set of all attributes of relation EMPLOYEE. So all of these are super keys of relation.

The minimal set of attributes whose attribute closure is set of all attributes of relation is called candidate key of relation. As shown above, (E-ID)+ is set of all attributes of relation and it is minimal. So E-ID will be candidate key. On the other hand (E-ID, E-NAME)+ also is set of all attributes but it is not minimal because its subset (E-ID)+ is equal to set of all attributes. So (E-ID, E-NAME) is not a candidate key.

DBMS - Operators in Relational Algebra

Relational Algebra is a procedural query language which takes relations as an input and returns relation as an output. Basic and Extended operators are discussed here.

Basic Operators which can be applied on relations to produce required results are discussed below. STUDENT_SPORTS, EMPLOYEE and STUDENT relations as given in Table 1, Table 2 and Table 3 respectively is used to understand these operators.

Table 1 : STUDENT_SPORTS

ROLL_NO	SPORTS
1	Badminton
2	Cricket
2	Badminton
4	Badminton

Table 2 : EMPLOYEE

EMP_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
5	NARESH	HISAR	9782918192	22
6	SWETA	RANCHI	9852617621	21
4	SURESH	DELHI	9156768971	18

 Table 3 : STUDENT

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
2	RAMESH	GURGAON	9652431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	DELHI	9156768971	18

Selection operator (σ): Selection operator is used to select tuples from a relation based on some condition. Syntax:

σ (Cond)(Relation Name)

Extract students whose age is greater than 18 from STUDENT relation given in Table 1

σ (AGE>18)(STUDENT)

RESULT:

ROLL_NO	NAME	ADDRESS	PHONE	AGE
3	SUJIT	ROHTAK	9156253131	20

Projection Operator (∏): Projection operator is used to project particular columns from a relation. Syntax:

∏(Column 1,Column 2….Column n)(Relation Name)

Extract ROLL_NO and NAME from STUDENT relation given in Table 1

∏(ROLL_NO,NAME)(STUDENT)

RESULT:

ROLL_NO	NAME
1	RAM
2	RAMESH
3	SUJIT
4	SURESH

Note: If resultant relation after projection has duplicate rows, it will be removed. For Example:  ∏(ADDRESS)(STUDENT) will remove one duplicate row with value DELHI and return three rows.

Cross Product (X): Cross product is used to join two relations. For every row of Relation1, each row of Relation2 is concatenated. If Relation1 has m tuples and Relation2 has n tuples, cross  product of Relation1 and Relation2 will have m X n tuples. Syntax:

Relation1 X Relation2

To apply Cross Product on STUDENT relation given in Table 1 and STUDENT_SPORTS relation given in Table 2,

STUDENT X STUDENT_SPORTS

RESULT:

ROLL_NO	NAME	ADDRESS	PHONE	AGE	ROLL_NO	SPORTS
1	RAM	DELHI	9455123451	18	1	Badminton
1	RAM	DELHI	9455123451	18	2	Cricket
1	RAM	DELHI	9455123451	18	2	Badminton
1	RAM	DELHI	9455123451	18	4	Badminton
2	RAMESH	GURGAON	9652431543	18	1	Badminton
2	RAMESH	GURGAON	9652431543	18	2	Cricket
2	RAMESH	GURGAON	9652431543	18	2	Badminton
2	RAMESH	GURGAON	9652431543	18	4	Badminton
3	SUJIT	ROHTAK	9156253131	20	1	Badminton
3	SUJIT	ROHTAK	9156253131	20	2	Cricket
3	SUJIT	ROHTAK	9156253131	20	2	Badminton
3	SUJIT	ROHTAK	9156253131	20	4	Badminton
4	SURESH	DELHI	9156768971	18	1	Badminton
4	SURESH	DELHI	9156768971	18	2	Cricket
4	SURESH	DELHI	9156768971	18	2	Badminton
4	SURESH	DELHI	9156768971	18	4	Badminton

Union (U): Union on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relations should have same number of attributes and corresponding attributes in two relations have same domain) . Union operator, when applied on two relations R1 and R2 will give a relation with tuples which are either in R1 or in R2. The tuples which are in both R1 and R2 will appear only once in result relation. Syntax:

 Relation1 U Relation2

Find persons who are either student or employee, we can use Union operator like:

STUDENT U EMPLOYEE

RESULT:

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
2	RAMESH	GURGAON	9652431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	DELHI	9156768971	18
5	NARESH	HISAR	9782918192	22
6	SWETA	RANCHI	9852617621	21

Minus (-): Minus on two relations R1 and R2 can only be computed if R1 and R2 are union compatible. Minus operator when applied on two relations as R1-R2 will give a relation with tuples which are in R1 but not in R2. Syntax:

 Relation1 - Relation2

Find person who are student but not employee, we can use minus operator like:

STUDENT - EMPLOYEE

RESULT:

ROLL_NO	NAME	ADDRESS	PHONE	AGE
2	RAMESH	GURGAON	9652431543	18
3	SUJIT	ROHTAK	9156253131	20

Rename (ρ): Rename operator is used to give another name to a relation. Syntax:

ρ(Relation2, Relation1)

To rename STUDENT relation to STUDENT1, we can use rename operator like:

ρ(STUDENT1, STUDENT)

 If you want to create a relation STUDENT_NAMES with ROLL_NO and NAME from STUDENT, it can be done using rename operator as:

ρ(STUDENT_NAMES, ∏(ROLL_NO, NAME)(STUDENT))

Extended Operators are those operators which can be derived from basic operators.There are mainly three types of extended operators in Relational Algebra:

• Join
• Intersection
• Divide 

The relations used to understand extended operators are STUDENT, STUDENT_SPORTS, ALL_SPORTS and EMPLOYEE which are shown in Table 1, Table 2, Table 3 and Table 4 respectively.

STUDENT

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
2	RAMESH	GURGAON	9652431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	DELHI	9156768971	18

Table 1

  STUDENT_SPORTS        

ROLL_NO	SPORTS
1	Badminton
2	Cricket
2	Badminton
4	Badminton

Table 2

 ALL_SPORTS

SPORTS

Badminton

Cricket

Table 3

EMPLOYEE                                                               

EMP_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
5	NARESH	HISAR	9782918192	22
6	SWETA	RANCHI	9852617621	21
4	SURESH	DELHI	9156768971	18

 Table 4

Intersection (∩): Intersection on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain). Intersection operator when applied on two relations as R1∩R2 will give a relation with tuples which are in R1 as well as R2. Syntax:

 Relation1 ∩ Relation2

Example: Find a person who is student as well as employee-  STUDENT ∩ EMPLOYEE  

In terms of basic operators (union and minus) :

STUDENT ∩ EMPLOYEE = STUDENT + EMPLOYEE - (STUDENT U EMPLOYEE) 

RESULT:

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
4	SURESH	DELHI	9156768971	18

Conditional Join (⋈c): Conditional Join is used when you want to join two or more relation based on some conditions. Example: Select students whose ROLL_NO is greater than EMP_NO of employees

STUDENT⋈c STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product and selection) :

σ (STUDENT.ROLL_NO>EMPLOYEE.EMP_NO)(STUDENT×EMPLOYEE)

RESULT:

Equijoin: Equijoin is a special case of conditional join where only equality condition holds between a pair of attributes. As values of two attributes will be equal in result of equijoin, only one attribute will appear in the result.

Example:Select students whose ROLL_NO is equal to EMP_NO of employees

STUDENT⋈STUDENT.ROLL_NO=EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product, selection and projection) :

∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE EMPLOYEE.NAME, EMPLOYEE.ADDRESS, EMPLOYEE.PHONE, EMPLOYEE>AGE)(σ (STUDENT.ROLL_NO=EMPLOYEE.EMP_NO) (STUDENT×EMPLOYEE))

RESULT:

Natural Join (⋈): It is a special case of equijoin in which equality condition hold on all attributes which have same name in relations R and S (relations on which join operation is applied). While applying natural join on two relations, there is no need to write equality condition explicitly. Natural Join will also return the similar attributes only once as their value will be same in resulting relation.

Example: Select students whose ROLL_NO is equal to ROLL_NO of STUDENT_SPORTS as:

STUDENT ⋈ STUDENT_SPORTS

In terms of basic operators (cross product, selection and projection) :

∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE STUDENT_SPORTS.SPORTS)(σ (STUDENT.ROLL_NO=STUDENT_SPORTS.ROLL_NO) (STUDENT×STUDENT_SPORTS))

RESULT:

ROLL_NO	NAME	ADDRESS	PHONE	AGE	SPORTS
1	RAM	DELHI	9455123451	18	Badminton
2	RAMESH	GURGAON	9652431543	18	Cricket
2	RAMESH	GURGAON	9652431543	18	Badminton
4	SURESH	DELHI	9156768971	18	Badminton

Natural Join is by default inner join because the tuples which does not satisfy the conditions of join does not appear in result set. e.g.; The tuple having ROLL_NO 3 in STUDENT does not match with any tuple in STUDENT_SPORTS, so it has not been a part of result set.

Left Outer Join (⟕): When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Left Outer Joins gives all tuples of R in the result set. The tuples of R which do not satisfy join condition will have values as NULL for attributes of S.

Example:Select students whose ROLL_NO is greater than EMP_NO of employees and details of other students as well

STUDENT⟕STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT:

Right Outer Join (⟖): When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Right Outer Joins gives all tuples of S in the result set. The tuples of S which do not satisfy join condition will have values as NULL for attributes of R.

Example: Select students whose ROLL_NO is greater than EMP_NO of employees and details of other Employees as well

STUDENT⟖STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT:

Full Outer Join (⟗): When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Full Outer Joins gives all tuples of S and all tuples of R in the result set. The tuples of S which do not satisfy join condition will have values as NULL for attributes of R and vice versa.

Example:Select students whose ROLL_NO is greater than EMP_NO of employees and details of other Employees as well and other Students as well

STUDENT⟗STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT:

Division Operator (÷): Division operator A÷B can be applied if and only if:

• Attributes of B is proper subset of Attributes of A.
• The relation returned by division operator will have attributes = (All attributes of A – All Attributes of B)
• The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.

Consider the relation STUDENT_SPORTS and ALL_SPORTS given in Table 2 and Table 3 above.

To apply division operator as

  STUDENT_SPORTS ÷ ALL_SPORTS

• The operation is valid as attributes in ALL_SPORTS is a proper subset of attributes in STUDENT_SPORTS.
• The attributes in resulting relation will have attributes {ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO
• The tuples in resulting relation will have those ROLL_NO which are associated with all B’s tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are associated to Badminton only. ROLL_NO 2 is associated to all tuples of B. So the resulting relation will be:

ROLL_NO

2