Integrity constraint fundamentals of dbms.pdf

Integrity constraints

1. Integrity constraint:
•Integrity Constraints are pre-defined set of rules that are applied on
the table fields(columns).
•It ensures that data entered into the database must be complete,
accurate , valid and consistent.
Types of Integrity Constraints
1.Domain Integrity Constraint: It contains set of rules to restrict the
kind of attributes(or) values a column can hold in the database
table.

2. Cont:
Ex: Roll No(number) Name(varchar) Age(varchar) class (number)
101 Smith 14 6
102 Amith 16 8
103 Siri 18 A
‘A’ violates the domain integrity constraint because the class attribute
contains only integer values while ‘A’ is a character.

3. Cont:
2) Entity Integrity Constraint:
•It is used to ensure that the primary key cannot be null
•A Primary Key is used to identify individual records in a table and if
the primary key has a null value, then we can’t identify those records.
•There can be null values any where in the table expect the Primary
Key column

4. Cont:
ID NAME SALARY
101 A 1000
102 B 2000
NULL C 3000
‘NULL’ violates the rule

5. 3) Referential Integrity Constraint:
•It ensures that there must always exist a valid relationship between
two relational database tables.
•It confirms that a foreign key exists in a table.
•It should always reference a corresponding value in the table(or) be
null.

6. Cont:
Employee Department table
S.No: Name Sal Dept_Id Dept_IdDept_name
101 A 1000 2 1 Sales
102 B 2000 1 2 HR
103 C 3000 3 3 Technical
104 D 4000
•Row having Dept_id=4 violates the referential integrity constraints.
Since Dept_id=4 is not defined as a primary key column in the
Department table

7. Cont:
4) Key Constraint:
•Keys are the set of entities that are used to identify an entity within
its entity set uniquely.
•A Primary Key can only contain unique and not null values in the
relation database table.(Duplicates are not allowed)

8. Cont:
Roll No: Name Age Class
101 A 2 1
102 B 5 2
103 C 6 1
101 D 7 1
•‘101’ is repeated twice in primary key column. So, it violates the key
integrity Constraint.

9. NULL Values:
•Special Value that is supported by SQL is called as null.
•It is used to represent values of attribute that are unknown.
•If the value is not present then it is represented as null but not as zero.
Empno Sal Job
101 1000 Clerk
102 -----Manager
103 2000 Clerk
104 -----Manager
105 -----Clerk

12. Handling Null Values
1)is null
2)is not null
3)not null constraints
4)NVL()
•IS NULL
Select * from emp
where salis not null;
Emp_no: Sal
101 1000
103 2000

13. Cont:
•IS NOT NULL
Select * from emp
where salis not null;
Empno: Sal
101 1000
102 2000

14. Cont:
•Not NULL Constraint
•It prevents the Column to contain null Values.
•Once not null is applied to a particular column, you cannot enter null
values to that column and restricted to maintain only some proper
value other than null.
•A not-null Constraint cannot be applied at table level.
Create table Student( ID intNOT NULL, Name Varchar(20) NOT NULL,
Age intNOT NULL, Address char(25), Salary decimal(18,2), Primary
key(ID))

15. Cont:
•NVL() NULL function:
•Using NVL function you can substitute a value in the place of NULL values.
•The Substituted Value then temporarily replaces the values in your
calculations (or) expression.
•It does not effect the value stored in the table.
•Syntax:
NVL(exp, replacement_exp)
Exp: Column name of a table(or) an arithmetic expression.
Replacement_exp: value you want to substitute when a NULL value is
encountered.

16. Cont:
•Datatypeof both the parameters must match otherwise the complier will
raise an error.
Ex:
Select NVL(Sal,5000)from emp
Where job=Manager;
•All the null values which satisfy the condition will be replaced with 50000.
•Similarly, we can use NVL null function while performing arithmetic
expression.
•Select NVL(sal, 5000) NVL(sal, 5000)+1000 from empwhere job=Manager;

17. VIEW
•Views are considered as virtual table. It Contains rows &
columns.(Changes will not effect main table.)
•To Create a view, we can select the fields from one or more tables
present in DB.
•A View can contain either all the rows of a table (or) only few rows
based upon certain conditions.

18. Creating a view
Syntax:
Create view view_nameas
Select column1, column2,….. From table_namewhere condition
Eg: Create view details_viewas
Select name, address from student where Roll.No<3;
Select * from details_view
o/p: Name Address
Rama Hyd
Sita Delhi

19. Creating a view from Multiple tables
Syntax:
Create view marks_viewas
Select student.name, student.address, smarks.marks
From student, smarkswhere student.name=smarks.name;
Select * from marks view;
o/p: Name Address Marks
Rama Hyd 99
Sita Delhi 99
Ammu Hyd 97

20. Deleting a view
Syntax:
•Drop view view_name
Inserting a row in a view:
•Insert into view_name(column1, column2,……) values(value1,
value2,….);
Deleting a row:
Syntax:
Delete from view_namewhere condition;
Update View: To add (or) remove fields create (or) Replace.

21. ALL, ANY,SOME OPERATORS
Sid Sname Class Percentage_in_last_class
1 raj 18 72
2 amith 11 84
3 vivek 9 64
4 Jyothi 10 69
5 vikas 11 92

22. Cont:
•Select Snamefrom std5 where percentage_in_last_class> all (select
percentage_in_last_class=10);
•Select Snamefrom std5 where percentage_in_last_class> any (select
percentage_in_last_class=10);
Sname
amith
vikas
Sname
raj
amith
vikas

23. Cont:
•Select Snamefrom std5 where percentage_in_last_class> some
(select percentage_in_last_class=10);
Sname
raj
amith
vikas

24. Index Definition in SQL
•Index is a Schema object. It is used by the server to speed up the
retrieval of rows.
Syntax:
Create index index_nameon table_namecolumn;
/* for single columns*/
Create index index_nameon table_name(Column1, Column2,….)
/*Multiple Columns, Composite*/

25. Unique Indexes
•Unique indexes are used for the maintenance of the integrity of the data
present in the table as well as for fast performance.
•It will not allow multiple values into the table.
Syntax:
Create Unique index index_nameon table_namecolumn;
How to choose the column When Creating an Index:
•A Column contain wide range of values.
•A Column doesn’t contain large number of null values.
•One (or) more columns are frequently used together for ‘m’ where clause
(or) ‘n’ join.

26. Cont:
•Drop index index_name;
•Alter index indexnameon tablenamerebuild;

27. JOIN
•In SQL, JOIN clause is used to combine the records from two or more tables
in a database.
Types of SQL Joins
•Inner Join
•Left Join
•Right Join
•Full Join
•Self Join
•Natural Join
•EquiJoin
•Theta Join

28. 1. INNER JOIN
•In SQL, INNER JOIN selects records that have matching values in both
tables as long as the condition is satisfied. It returns the combination
of all rows from both the tables where the condition satisfies.
Syntax
•SELECTtable1.column1,table1.column2,table2.column1,....
FROMtable1
INNERJOINtable2
ONtable1.matching_column=table2.matching_column;

29. Cont:
Cust_idCust_nameDesignation
1 abc A
2 pqr B
3 mno c
Ord_id amountCust_id
601 10000 1
602 2000 4
603 1500 2
CustomerOrders

30. Cont:
Query
•selectorders.ord_id,customers.cust_name
fromorders
innerjoincustomers
onorders.cust_id=customers.cust_id;
Output:
ord_id Cust_name
601 abc
603 pqr

31. 2. LEFT JOIN
•The SQL left join returns all the values from left table and the
matching values from the right table. If there is no matching join
value, it will return NULL.
Syntax:
•SELECTtable1.column1,table1.column2,table2.column1,....
FROMtable1
LEFTJOINtable2
ONtable1.matching_column=table2.matching_column;

32. Cont:
Query:
•selectorders.ord_id,customers.cust_name
fromorders
leftjoincustomers
onorders.cust_id=customers.cust_id;
Output:
Ord_id Cust_name
601 abc
602 null
603 pqr

33. 3. RIGHT JOIN
•In SQL, RIGHT JOIN returns all the values from the rows of right table
and the matched values from the left table. If there is no matching in
both tables, it will return NULL.
Syntax:
•SELECTtable1.column1,table1.column2,table2.column1,....
FROMtable1
RIGHTJOINtable2
ONtable1.matching_column=table2.matching_column;

34. Cont:
Query:
•selectorders.ord_id,customers.cust_name
fromorders
right joincustomers
onorders.cust_id=customers.cust_id;
Output:
Ord_id Cust_name
601 abc
602 pqr
null mno

35. 4. FULL JOIN
•In SQL, FULL JOIN is the result of a combination of both left and right
outer join. Join tables have all the records from both tables. It puts
NULL on the place of matches not found.
Syntax:
•SELECTtable1.column1,table1.column2,table2.column1,....
FROMtable1
FULLJOINtable2
ONtable1.matching_column=table2.matching_column;

36. Cont:
Query:
•selectorders.ord_id,customers.cust_name
fromorders
Full joincustomers
onorders.cust_id=customers.cust_id;
Output:
ord_id Cust_name
601 abc
602 null
603 pqr
null mno

37. 5.SELF JOIN
•The SQLSELF JOINis used to join a table to itself were joining
condition is based on columns of same table.
Syntax:
•SELECTa.column_name,b.column_name,
FROMtable1 a, table1 b
WHERE a.column_field = b.column_field;

38. Cont:
•Write SQL Query list the names of the employees and their manager
names from emp table.
Emp E1 Emp E2
E_id E_name M_id
10 A 40
20 B 30
30 C 20
40 D 10
E_id E_name M_id
10 A 40
20 B 30
30 C 20
40 D 10

39. Cont:
•Select E1.Ename as E_name, E2.Ename as M_name from Emp E1,
Emp E2, where E1.Eid=E2.Mid;
E_name M_name
A D
B C
C B
D A

40. Cont:
•selectdistinct E_name
fromemployee E
self joinemployee M
on E.emp_id=M.Manager_id;
•Every employee cannot be a manager but every manager can be an
employee.

41. 6) NATURAL JOIN
•Natural Join joins two tables based on same attribute name and data
types. The resulting table will contain all the attributes of both the
table but keep only one copy of each common column.
•Select *
From student s
Natural Join Marks m;

42. Cont:
id name
101 abc
102 pqr
103 xyz
id marks
102 100
103 70
104 80
id name marks
102 pqr
100
103 xyz
70
Student Marks

43. 7)EQUI JOIN(= CONDITION)
•The resultant relation of an equijoin operation always has one (or) more
pairs of attributes that have identical values in every tuple.
Syntax:
•Select column_list
From table1, table2
Where table1.column_name = table2.column_name (or)
•Select column_list
From table1
Join table2
[ON (join_condition)]

44. Cont:
BOOK Publisher
p_id p_name
1 A
2 B
3 C
4 P
B_name p_id price
XYZ 1 200
ABC 1 500
PQR 2 700
STU 2 1000
B_name Price Book.p_idPublisher.p_idp_name
XYZ 200 1 1 A
ABC 500 1 1 A
PQR 700 2 2 B
STU 1000 2 2 B

45. 8)Theta Join (or) Conditional Join
•Theta Joinallows you to merge two tables based on the condition
represented by theta. Theta joins work for all comparison operators.
It is denoted by symbolθ. The general case of JOIN operation is called
a Theta join.( Comparision Condition {=,<,>,<=,>=})
Model Price
Nokia 10K
Samsung 20K
Phone 50K
Model Price
Dell 30K
Acer 20K
Lenovo 10K
Mobile Laptop

46. Cont:
•Purchase both mobile, Laptop but mobile price should be less than
laptop price.

47. Difference Between Natural Join And Inner Join
SR.NO. NATURAL JOIN INNER JOIN
1.
Natural Join joins two tables based on same
attribute name and datatypes.
Inner Join joins two table on the basis of the
column which is explicitly specified in the ON
clause.
2.
In Natural Join, The resulting table will contain all
the attributes of both the tables but keep only one
copy of each common column
In Inner Join, The resulting table will contain all the
attribute of both the tables including duplicate
columns also
3.
In Natural Join, If there is no condition specifies
then it returns the rows based on the common
column
In Inner Join, only those records will return which
exists in both the tables
4.
SYNTAX:
SELECT *
FROM table1 NATURAL JOIN table2;
SYNTAX:
SELECT *
FROM table1 INNER JOIN table2 ON
table1.Column_Name = table2.Column_Name;