Integrity Constraints

Integrity Constraints

There are 
1.  domain integrity
2. entity integrity,
3. referential integrity 
4. Enterprise  integrity constraints.



Domain Integrity
Domain integrity means the definition of a valid set of values for an attribute. You define 
- data type, 
- lenght or size
- is null value allowed
- is the value unique or not
for an attribute.

You may also define the default value, the range (values in between) and/or specific values for the attribute. Some DBMS allow you to define the output format and/or input mask for the attribute.

These definitions ensure that a specific attribute will have a right and proper value in the database.
Entity Integrity Constraint
The entity integrity constraint states that primary keys can't be null. There must be a proper value in the primary key field.

This is because the primary key value is used to identify individual rows in a table. If there were null values for primary keys, it would mean that we could not indentify those rows.

On the other hand, there can be null values other than primary key fields. Null value means that one doesn't know the value for that field. Null value is different from zero value or space.

The entity integrity constraints assure that a spesific row in a table can be identified. 


Referential Integrity Constraint
The referential integrity constraint is specified between two tables and it is used to maintain the consistency among rows between the two tables.

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.



UPDATE and DELETE CASCADE: referential action for a foreign key to delete data from multiple related tables.

cascade update related fields and cascade delete related rows. These constraints affect the referential integrity constraint.

Cascade Update Related Fields

Any time you change the primary key of a row in the primary table, the foreign key values are updated in the matching rows in the related table. This constraint overrules rule 2 in the referential integrity constraints. 

Cascade Delete Related Rows

Any time you delete a row in the primary table, the matching rows are automatically deleted in the related table. This constraint overrules rule 1 in the referential integrity constraints. 

Refer following link for mysql queries of cascade delete and update
http://www.mysqltutorial.org/mysql-on-delete-cascade/


·         Enterprise Constraints

Enterprise constraints – sometimes referred to as semantic constraints – are additional rules specified by users or database administrators and can be based on multiple tables.
Here are some examples.
·         A class can have a maximum of 30 students.
·         A teacher can teach a maximum of four classes per semester.
·         An employee cannot take part in more than five projects.
·         The salary of an employee cannot exceed the salary of the employee’s manager.




Popular posts from this blog

DDL DML DCL and TCL

DML (Data Manipulation Language) 2.  DDL (Data Definition Language) 3.  DCL (Data Control Language) 4.  TCL (Transaction Control Language)   DML (Data Manipulation Language) DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records. DML statements include the following: SELECT  – select records from a table INSERT  – insert new records UPDATE  – update/Modify existing records DELETE  – delete existing records DDL (Data Definition Language) DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects. CREATE  – create a new Table, database, schema ALTER  – alter existing table, column description DROP  – delete existing objects from database DCL (Data
Read more

Implementation of Calculator using lex and yacc

file name Cal.l  %{ #include<stdio.h> #include "y.tab.h" //extern int yylval; %} %% [0-9]+ { yylval=atoi(yytext); return NUMBER; } [\n] return 0; . return yytext[0]; %% int yywrap() { return 1; } file name  cal.y %{ #include<stdio.h> int flag=0; %} %token NUMBER %left '+' '-' %left '*' '/' '%' %left '(' ')' %% ArithmeticExpression: E{          printf("\nResult=%d\n",$$);          return 0;         }; E:E'+'E {$$=$1+$3;}  |E'-'E {$$=$1-$3;}  |E'*'E {$$=$1*$3;}  |E'/'E {$$=$1/$3;}  |E'%'E {$$=$1%$3;}  |'('E')' {$$=$2;}  | NUMBER {$$=$1;} ; %% int main() {   yyparse();   if(flag==0)    printf("\nEntered arithmetic expression is Valid\n\n");   return 0; } int yyerror() {    printf("\nEntered arithmetic expression is Invalid\n\n");    flag=1;    return 0; }
Read more

A Register Allocation algorithm that translates the given code into one with a fixed number of registers.

//C code for  A Register Allocation algorithm that translates the given code into one with a fixed number of registers. #include<stdlib.h> #include<stdio.h> /* We will implement DAG as Strictly Binary Tree where each node has zero or two children */ struct bin_tree { char data; int label; struct bin_tree *right, *left; }; typedef struct bin_tree node; /* R is stack for storing registers */ int R[10]; int top; /* op will be used for opcode name w.r.t. arithmetic operator e.g. ADD for + */ char *op; /* insertnode() and insert() functions are for adding nodes to tree(DAG) */void insertnode(node **tree,char val) { node *temp = NULL; if(!(*tree)) { temp = (node *)malloc(sizeof(node)); temp->left = temp->right = NULL; temp->data = val; temp->label=-1; *tree = temp; } } void insert(node **tree,char val) { char l,r; int numofchildren; insertnode(tree, val); printf("\nEnter number of children of %c:",val); scanf("%d&quo
Read more