Question

Let's discuss this question: Why are entity integrity and referential integrity rules important in a database? Give example to back up your points.

Answer 1

1 : Why are entity integrity is important in a database ?

Ans : Entity Integrity is used to identify each touple (row )in an Entity uniquely, no two touple can have full same data. The primary key serves as a unique identifier for rows in the table which handles Entity entigrity . Entity Integrity ensures two properties for primary keys:

• The primary key for a row is unique; it does not match the primary key of any other row in the table.
• The primary key is not null, no component of the primary key may be set to null.

Lets take and example, Suppose you have an "Student" Entity (Relational table or Simply Table), which containg Student's ID,name,Branch.

Stu_id	Name	Branch
11255234	Aman	CSE
11255369	Kapil	EcE
11255324	Ajay	ME
11255237	Raman	CSE
11255678	Aastha	ECE

To identify each student, all student should have an unique ID. but ID=11255324 says that it is "Aman" and "Ajay", then cay you say that you can identify each student uniquely ?? No you can't. For this reason We need Entity intigrity, which gonna help us to find each student Uniquely, now f we Put ID=11233324 for Aman, now using ID we can identify each student, So now we can make ID to priary key. i.e Entity entigrity.

2. Why are referential integrity is important in a database ?

ANS:

It states that if a foreign key exists in a relation then either the foreign key value must match a primary key value of some tuple in its home relation or the foreign key value must be null.

The rules are:

1. You can't delete a record from a primary table if matching records exist in a related table.
2. You can't change a primary key value in the primary table if that record has related records.
3. You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
4. However, you can enter a Null value in the foreign key, specifying that the records are unrelated.

   EXAMPLE-

   Consider 2 relations "stu" and "stu_1" Where "Stu_id " is the primary key in the "stu" relation and foreign key in the "stu_1" relation.

   Relation "stu"

   Stu_id	Name	Branch
   11255234	Aman	CSE
   11255369	Kapil	EcE
   11255324	Ajay	ME
   11255237	Raman	CSE
   11255678	Aastha	ECE

   Relation "stu_1"

   Stu_id	Course	Duration
   11255234	B TECH	4 years
   11255369	B TECH	4 years
   11255324	B TECH	4 years
   11255237	B TECH	4 years
   11255678	B TECH	4 years

   Examples

   Rule 1. You can't delete any of the rows in the ”stu­” relation that are visible since all the ”stu” are in use in the “stu_1” relation.

   Rule 2. You can't change any of the ”Stu_id” in the “stu” relation since all the “Stu_id” are in use in the ”stu_1” relation. *Rule 3.* The values that you can enter in the” Stu_id” field in the “stu_1” relation must be in the” Stu_id” field in the “stu” relation.