.

INTEGRITY RULES IN DBMS

Integrity  Rules:

Database Management System  provides  high level of data integrity, by using the  Integrity Rules:

1. Entity Integrity: Entity integrity says that  a prime attribute in a relation cannot accept null values or duplicate value.

2. Referential Integrity: The referential integrity rule is related along with the foreign key concept. Let R1 and R2 are two relations where R1 is having an attribute(s) with primary key. Let R2 be having a foreign key, that refers to relation R1 via the same set of attributes. Then the value of the foreign key in a tuple in R2 relation must either be equal to primary key of a tuple in a relation R1 relation or be entirely NULL.

R1

Department_ID	Department_Name	Department_Head	Department_Addres
D1	HR	Jacob	London
D2	IT	Rohan	Newyork
D3	FINANCE	Michel	Delhi

R2

EMP_ID	EMP_Name	EMP_City	Department_ID
E1	Rohan		D2
E2	Mukesh		D1
E3	Joseph		D1
E4	Ron		D1
E5	Michel		D3
E6	Jacob		D1

In above example  , in relation R1  Department_ID  is primary key  where as in R2 Department_ID is foreign key.

Value of the foreign key in R2 is same as Value exist in R1 of primary key.

Relational algebra

Relational algebra is a procedural language which specifies the operations to be performed on existing relations to get the desired relations. Relational algebra constitutes a basic set of operations for manipulating relational data. These operations enable the user to perform some basic retrieval operations. The result of the retrieval operation on a relation is another relation. The relational operations can be divided into two categories:

1)    Set oriented operations which include mathematical set operations like UNION, INTERSECTION, SET DIFFERENCE and CARTESIAN PRODUCT first these operations require union compatible operand relations. Two relations are union compatible when they have same arty and one to one corresponding attributes defined over the same domain.

2)    These operations are developed and specifically for relational database which include SELECT, PRODUCT, RENAME, JOIN and DIVION.

a.     Selection (σ): The select operation is used to select a subset of the tuples from a relation that satisfies a selection condition or search criteria. The resulting relation and the main (original) relation have the same attributes. It discards the words that do not match the selection condition. For example: to select that employee from EMPLOYEE relation who is having empno 1007, we will write

σ(empno =1007) (EMP)

b.     Projection (σ): whereas the select operations is used to choose a sunset a tuples of relation the PROJECT operation is used to choose only a subset of columns discarding others. More simply, project operations on relations are defined as the projection of all tuples over same set of attributes. The output of this operation is a new relation which will have the attributes only listed in attribute list from relation R. For example, if we have to take only the employee names and their salaries from EMPLOYEE relation then this can be represented as

σ(ename,sal)(EMP)

c.      Natural join: This operation takes only two source relations as inputs and produces a relation whose tuples are formed by concatenating tuples from each input source. It is basically a Cartesian product of the extension of each input source. However, not all possible combinations of tuples necessarily end up in the result. This is because it implicitly selects from among all possible tuple combinations only those that have identical values in attributes shared by both relations. If the degree of the input sources were m and n, and the number of shared attributes was s, then the degree of the resultant relation is m+n-s.

d.     Division: It involves dividing one relation by another. Division is in principle a partitioning operation. Thus, 6/2 can be paraphrased as partitioning a single group of 6 into a number of groups of 2, which in this case, 3 groups of 2. Relational division too can leave remainders but we ignore remainders and focus only on constructing whole groups of the divisor.

The manner in which a relational dividend is partitioned is a little more complex. A division of a relation R is performed over some attribute of R. The divisor is a subset of values from that attribute domain and the result is a relation comprising the remaining attributes of R. In relational algebra expressions, the divisor is in fact specified by another relation D. For this to be meaningful at all, D must have at least one attribute in common with the R. The division is over the common attribute and the set of values used as the actual divisor are the values found in D.  

e.     Set operations: relations are basically sets. A set operation on two or more sets is meaningful if the sets comprise values of the same type. This is so that comparison of values from different sets is meaningful. As a relation is a set of tuples, the values we are interested in are the tuples themselves. The structure of the tuples must be identical, the tuples must be of equal length and their corresponding elements must be of the same type. Only then can two tuples be equal. Thus, meaningful set operations on relations require that the source relations have identical schemas.

The following are the operations:

1.     Union: Union operator gives the information available in both the relations used union. For example, give the complete list of employees who are either working as full time or part time. Consider full_time and part_time two seperates tables which store the employees separately.

Ʌ(ename) (Full_time) U  Ʌ(ename)(Part_time)

2.     Intersection: it returns the common information from both the relations used in the intersection. The intersection operation takes the form

For example: display the name of the employees who are working as full time as well as part time.

Ʌ(ename) (Full_time)  Ʌ(ename) (part_time)

3.     Difference: it returns the information, which is available in first relation but not in second. The difference operation takes the form

Relation1-Relation2

Example: to display only those employee names who are working as full time, not as part time.

Ʌ(ename) (full_time)- Ʌ(ename)(part_time) 

Few Basic Key of DBMS

a. Primary key – It is a set of one or more fields/columns of a table that uniquely identify a record in database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.

b. Candidate key – It is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Eg: In below diagram ID, Roll No. and Enroll No. are Candidate Keys since all these three fields can be work as Primary Key.

c. Alternate key – It is a key that can be work as a primary key. Basically, it is a candidate key that currently is not primary key.

Eg: In below diagram Roll No. and Enroll No. becomes Alternate Keys when we define ID as Primary Key.

 

ID	Roll No.	Name	Enroll No.	Address	Dept ID
1	6068741	Harsh	ADM1	Delhi	1
2	6068742	Neha	ADM2	Noida	1
3	6068743	Aditi	ADM3	Gurgaon	2

Primary Key

                             Alternate Keys

Possible Candidate Keys