Question

Using a simple relation of your own choice and example, explain Entity Integrity and Referential Integrity.

Answer 1

Entity Integrity:
Entity integrity ensures each row in atable is a uniquely indentfiable entity. you can apply entity integrity to a table by specifing a PRIMARY KEY and UNIUQE KEY constraint.Mainly Entity integrity referred as primary key.
PRIMARY KEY: This is used avoid duplicates and nulls.this will works as combination of unique and not null.
primary key always attached to the parent table.
A table should contain only one primary key.
it can apply on any datatype column like integer,character,decimal datatypes...etc
UNIQUE KEY: this is used to avoid duplicates but it allow nulls.

Example: 1.We create a table

create table Emp(EID int Primary key,Ename varchar(40),Salary money)

2.insert values into table

insert into Emp values(101,'Raju',15000)

insert into Emp values(102,'suresh',25000)

insert into Emp values(101,'jon',35000)

Referntial Integrity:
Referntial integrity ensures the relationships between tables remain preserved as data is inserted,deleted and modified.You can apply referntial integrity using a FOREIGN KEY constraint.

Foreign key:One of the most importent concepts in database is creating relation ships between database tables.these relationships provide a mechanism for linking data stored in multiple tables and retriving it in an efficient manner.
In order to create a link between two tables we must specify aforeign key in one table that referces a column in another table.
Foreign key constraint is used for relating or binding two tables with each other and then verifies the existence of one table data in the other.
to impose a foreign key constraint we require the following things.
we require two tables for binding with each other and those two tables must have a comman column for linking the tables.
Example:
To create department table:
create table Department(Deptno int primary key,Dname varchar(40),Location varchar(max))
Insert records into the Department table:

insert into Department values(10,'Sales','Hyderabad')
insert into Department values(20,'Production','Chennai')
insert into Department values(30,'Finance','Mumbai')

To create Employee table (Child table):

create table Employee(EID int,Ename varchar(40),Salary money, Deptno int foreign key refernces Department(Deptno))

Insert records into Employee table:

insert into Employee values (101,'sai',35000,10)
insert into Employee values (102,'pavan',45000,20)
insert into Employee values (103,'kamal',74000,30)

Foregin Key rules:

1.Can not insert a value into the foregin key column provided that value is not existing under the refernce key column of the parent table.
2.Can nit update the refernce key value of aparent table provided that value has corresponding child record in the child table with out addressing what to do with the child record.
3.We can not delete a record from the parent table provided that records refernce key value has child record in the child table with out addressing what to do with the child record.
If we wnat to delete or update a record in the parent table when they have corresponding child records in the child table we are provide with a set of rules to perform delete and update operations known as cascade rules.

On delete cascde: It is used to delete a key value in the parent table which reference by the foreign key in other table all rows that contains those foreign keys in child table are also deleted.

on Update cascade: It is used to update a key value in the parent table which is referenced by foreign key in other table all rows that contanins those foreign keys in child table also updated.

If we apply this while creating the child table like below

create table Emp(EID int ,Ename varchar(40),salary money,Deotno int foreign key refernces Department(Deptno) on delete cascade on update cascade)