Integrity and Manipulation Components in Relational Model

What is the Integrity and Manipulation in Relational Model?

Integrity: -- It forces the business rules and uniqueness of data.

1. Candidate Key - It is an attribute, which helps to identify an individual tuple in a relation.

2. Primary Key - It is an attribute (one or more attributes), that help to identify an individual tuple in a relation. We can also call this attribute as candidate key. But this attribute will not allow duplicate data values and NULL (unknown data). This is the entity integrity rule.

3. Foreign Key - This is the key (child relation) which stores data that should be available in one of the attribute (primary key) in another relation (parent relation). This is the referential integrity rule.

Manipulation: -- The manipulation defines reading (SELECT) and Writing (INSERT, UPDATE & DELETE) the data on a relation. Two parts in the manipulation.

1. Relational Algebra: It is a collection of operators which can be applied among the relations.

2. Relational Assignment Operator: It will take the output of "Relational Algebra" and assign it to another relation.

Relational Algebra: -- Restrict data; Project data, Product, Union; Intersect & Difference --> These operators will produce a relation as output.

Restrict: - Fetch the specific tuples from a relation. It is nothing but applying WHERE condition on a relation. It returns a relation with tuples which satisfy the particular condition.

Project: -- Defines what attributes that we have to display. It returns a relation with all the tuples with specified attributes.

Product: -- It returns a relation that contains all possible tuples with the combinations from two or more relations. This operation is known as Cartesian product (Cross Product, Cross Join and Cartesian Join).

Union: -- It returns a relation with all tuples that appear in either or both relations. It avoids the duplicate tuples.

Intersect: -- It returns a relation with all tuples that appear in both relations.

Difference: -- It returns a relation with all the tuples in first relation which are not available in second relation

Relational Assignment Operator: The below example shows who to assign a relation to another relation.

SELECT * INTO #RelationProducedFromHelloRelation from Hello JOIN Hello1 ON Hello.id = Hello1.id