Wednesday 6 August 2014

Relational Model | Relational calculas


RELATIONAL CALCULUS

Relational algebra is concerned with a procedural language in which the user has to write the steps or procedure to obtain the required result.In this the user is not concerned with the detail of how to obtain information.
         In relational calculus the user tell his requirement and the output is available without knowing the method about its retrieval.
a.) It is a non procedural language.
b.) This concept was first expressed by codd. It is based on the predicate calculus.
c.) It is a formal language used to symbolize local argument in mathematics.Proposition specifying a property consist of an expression that names an individual object and an other expression called the predicate,that stands for the property that an individual object possesses.

In relational calculus we can make the following operations
Example-Let x and y are preposition then we can build other preposition like

"Not x"
"x and y"
 "x or y"m and so on.

Consider the following statement

India is a country.
We can write it.
" is a country  (India)"
we can drop is a then preposition will be
" Country (India)"
Finally if we use symbols for both predicates and objects then we can rewrite the statement X (a).Lower case letter denotes the variables beginning letters {a,b,c......}denotes the constants.
Upper case letter denotes the predicate.
P{X} where X is the argument.

It can be categorized into 2 parts:-
1,) Tuple Oriented Relational calculus
2.) Domain Oriented Relational calculus.

Relational Model | Integrity constraints over relation


INTEGRITY CONSTRAINTS OVER RELATION

INTRODUCTION
Database integrity refers to the validity and consistency of stored data. Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate. Constraints may apply to each attribute or they may apply to relationships between tables.
Integrity constraints ensure that changes (update deletion, insertion) made to the database by authorized users do not result in a loss of data consistency. Thus, integrity constraints guard against accidental damage to the database.

EXAMPLE- A brood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only (can not any other values else).

TYPES OF INTEGRITY CONSTRAINTS

Various types of integrity constraints are-
  1. Domain Integrity
  2. Entity Integrity Constraint
  3. Referential Integrity Constraint
  4. Key Constraints
1. Domain Integrity- Domain integrity means the definition of a valid set of values for an attribute. You define data type, length or size, is null value allowed , is the value unique or not for an attribute ,the default value, the range (values in between) and/or specific values for the attribute. 

2. Entity Integrity Constraint- This rule states that in any database relation value of attribute of a primary key can't be null.

EXAMPLE- Consider a relation "STUDENT" Where "Stu_id" is a primary key and it must not contain any null value whereas other attributes may contain null value e.g  "Branch" in the following relation contains one null value.


Stu_id
Name
Branch
11255234
Aman
CSE
11255369
Kapil
ECE
11255324
Ajay

11255237
Raman
CSE
11255678
Aastha
ECE

3.Referential Integrity Constraint-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
Cource
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.
Rule 4 You can enter a null value in the "stu_1" relation if the records are unrelated.

4.Key Constraints- A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.
There are 4 types of  key constraints-
  1. Candidate key.
  2. Super key
  3. Primary key
  4. Foreign key

Relational Model | ER to Relational model conversion



ER TO RELATIONAL MODEL CONVERSION

INTRODUCTION- In the E-R model, data is represented using entities, and relationships are defined between these entities. However, with the relational model, the entities and their relationships follow strict guidelines. Usually, an E-R model is first developed, and then it is transformed into a relational model.

RULES TO CONVERT  ER TO RELATIONAL MODEL CONVERSION-
  •        Build a table for each entity set
  •         Build a table for each relationship set if necessary (more on this later)

Relational Model | Key constraints | Types of key


KEY CONSTRAINTS

INTRODUCTION-A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.

TYPES OF KEY CONSTRAINTS- Various types of key constraints are-
  1. Candidate key
  2. Super key
  3. Primary key
  4. Foreign key
1. Candidate key- These are those attributes of a relation that contains the properties of uniqueness and irr-reducability. 
  • Uniqueness- At any time no two tuples of a relation should have the same value means no duplicate values. 
  • Irr-reducability-  Let "k" be the set of attributes in any relation "R" i.e.it contains composite key  then no proper subset of "k"  has the uniqueness property.

2. Super Key-  These are those attributes of a relation that contains the properties of uniqueness but not necessary that it satisfies  irr-reducability property.

3. Primary Key- Mixture of uniqueness and not null is known as the primary key.

Properties of Primary key-
1. Stable- It should be stable means value must not change or it should not be null throughout the life of an entity.
2. Minimal- It should be minimal means it should be composite of minimum no of fields that ensures that the occurrence are unique.
3. Definitive- It should be definitive means a value must exist for every record at the creation time.
4. Accessible-It should be accessible means any one who wants to create,read or delete a record mus be able to see the primary key values.

SYNTAX- create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint (constraint name)  primary key);
EG- create  table  mydoc3 ( username varchar (20) ,password  number(10) constraint  rollno_pk  primary key);

4. Foreign key-Primary key of one table is the foreign key of some other table.

Features of foreign key:
1. Records cannot be inserted into a detail table if corresponding records in the master table do not exist.
2 .Records of the master table cannot be deleted  or updated if corresponding records in the detail table actually exist.

                        SYNTAX- create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint (constraint name) primary key);
create table <table name> (column name1 datatype (size), column name2 datatype(size) references <tablename which contain primary key < primary key attribute>);
 EG- create  table  doc9    (bname  varchar(20),brollno number(20)    constraint vn_o   primary key);

create  table  doc10   (sname  varchar(20),srollno number(20)    references  doc9(brollno)); 



Relational Query Languages| Basic sql query language


BASIC SQL QUERY LANGUAGE

INTRODUCTION-
  • SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS).
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. 

WHAT CAN SQL DO?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database

SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
SQL can set permissions on tables, procedures, and views

SQL LANGUAGES

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)

1) DATA DEFINITION LANGUAGE- The Data Definition Language (DDL) manages table and index structure.
COMMAND USED- CREATE, ALTER, DROP,RENAME
CREATE- creates an object (a table) in the database.
ALTER modifies the structure of an existing object in various ways, for  example, adding a column to an existing table or a constraint.
DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back.
RENAME rename scheme object

2) DATA MANIPULATION LANGUAGE- The Data Manipulation Language (DML) is the subset of SQL used to add, update,select and delete data.
COMMAND USED-INSERT, UPDATE, DELETE,SELECT
INSERT adds rows (formally tuples) to an existing table.
UPDATE modifies a set of existing table rows.
DELETE removes existing rows from a table.
SELECT Retrieve data from one or more table

3) DATA CONTROL LANGUAGE-The Data Control Language (DCL) authorizes users to access and manipulate data.          
COMMAND USED- GRANT, REVOKE, COMMENT
GRANT authorizes one or more users to perform an operation or a set of operations on an object.
REVOKE eliminates a grant, which may be the default grant.
COMMENT Adding the element into data dictionary

Relational Query Languages | How to create table in sql


CREATING TABLES AND VIEWS
  • CREATING TABLES
CREATE: This command is used when we want to create a new table.
syntax: create table <tname>  (col.1 data type (size) constraint <constraint name> not null , col.2 data type (size));
E.g.: create table mydoc1 (username varchar (20) constraint name_mn not null, password number(10));


  • VIEWS
We can use views to display the particular values from  a table as separate sub table. for example a table emp details has 8 fields and we can build a view with 3 fields from the table.

SYNTAX- 
  • For creating a view -create or replace view <view name> as  <basic query for retrieval> with read only;
  • For dropping a view- Drop view view name;

Relational Query Languages |Data Manipulation Language(DML)


DML (Data Manipulation Language)

The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data and select or retrieval.

COMMAND USED-INSERT, UPDATE, DELETE,SELECT
INSERT adds rows (formally tuples) to an existing table.
UPDATE modifies a set of existing table rows.
DELETE removes existing rows from a table.
SELECT Retrieve data from one or more table

1. INSERT COMMAND- This command is used when we want to insert a data in a new existing table.This syntax is used for inserting a single value in a each column of a table at a time.
SYNTAX: insert into <tname> (col1,col2,col3) values (‘v1’, ‘v2’ , ‘v3’);
E.G.: insert into hotel (ename , salary, place) values (‘Akash’, ‘10000’, ‘chd’);

When we want to insert a multiple values in each column of a existing table then we used a following syntax:
SYNTAX: insert into <tname> values(‘&1’, ‘&2’);
You have to enter the values  and inspite of writing this command again you can use only [(/) enter].

2.UPDATE COMMAND- This command is used when we want to update a data of existing table.
syntax: update <table name> set <condition1> where <condition2>;
E.g.: update emp set <id=525> where name=’raman’ ;


3.DELETE COMMAND-When we want to delete the data from the existing table.
syntax: delete  <table name> where <condition>;
E.g.: delete  emp where name = (‘pahul’);



4. SELECT COMMAND- Select statement is used when we want to retrieve a data from a existing table.
syntax:   select * from <table name>;
E.g. select * from emp;
When we want to retrieve a specific column from a existing table then we use a another syntax.
syntax: select <colname1> ,<colname2> from <tname>;
E.g.: select name , rollno. from emp;
When we want to retrieve a specific row from the existing table then we use following syntax:
syntax: select * from <table name> where <condition>;
E.g.: select * from emp where depno. = 10;

·    Arithmetic operation perform on a table using select command
Ø Add (+): This is used when we want to add some more content in any row of the existing table.
syntax: select <condition> from <table name>;
E.g.: select sal+1000 from emp;
Ø Between : This command is used when we want to retrieve a data in some particular range.
SYNTAX- select * from <table name> where  <condition>;
E.g.: select * from dept where deptno.. between 10 and 30;
Ø In: This command is used when we want to retrieve a particular data.
SYNTAX-  select * from <table name> where <condition>;
E.g.: select * from dept where deptno. IN(10,20,30);
Ø Null: This command is used when we want to select a null value from the existing table.
SYNTAX- select*from where <column name> is null;
E.g.: select * from where dname is null;

·   Logic condition using select command.
Ø AND
SYNTAX-select * from <table name> where <condition1> and <condition2>;
EG- select * from dept where deptno= 10 and dname= ‘sales’;
Ø OR
SYNTAX- select * from <table name> where <condition1 > or <condition2 >;
EG- select * from dept where deptno.=10 or dname = ‘sales’;
Ø NOT
SYNTAX- select*from <table name> where <condition>;
EG- select * from dept where deptno  not in (10,20,30);

·   Sorting commands for table
Ø Ascending order:
syntax: select * from <tname> order by <col name> ;
E.g: select * from dept order by depno;
Ø Descending order:
syntax: select * from <tname> order by <col name> desc;
E.g.: select * from dept order by depno dese;

Relational Query Languages | Data Definition language(DDL)


DDL(Data Definition Language)

The Data Definition Language (DDL) manages table and index structure.
COMMAND USED- CREATE, ALTER, DROP,RENAME
CREATE creates an object (a table) in the database.
ALTER modifies the structure of an existing object in various ways, for    example, adding a column to an existing table or a constraint.
DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back.
RENAME rename scheme object

1. CREATE COMMAND-This command is used when we want to create a new table.
syntax: create table <tname>  (col.1 data type (size) constraint <constraint name> not null , col.2 data type (size));
E.g.: create table mydoc1 (username varchar (20) constraint name_mn not null, password number(10));


2. ALTER COMMAND- This command is used when we want to delete a particular column from a existing table.
We use three clauses with alter command:
1. ADD
2. MODIFY
3. DROP

·   ADD: This command is used when we want to create a new column.
syntax: alter table tname add (col. name data type (size));
E.g.: alter table student add (Roll no. number(100));

·   MODIFY: This command is used when we want to modify the table.
syntax: alter table tname modify (col. name data type(size));
E.g.: alter table emp modify (Branch  varchar(20));

3. DROP COMMAND- This command is used when we want to drop a table.
syntax: alter table tname drop column (col. name);
E.g.: alter table emp drop column salary; 


4.RENAME COMMAND-Rename scheme object
SYNTAX- alter table <tname> RENAME COLUMN (old column name to new column name);
E.G.- alter table emp RENAME COLUMN (name to empname);

Relational Query Languages | Data control language (DCL)


DCL(Data Control Language)

The Data Control Language (DCL) authorizes users to access and manipulate data.        

COMMAND USED- GRANT, REVOKE, COMMENT
GRANT authorizes one or more users to perform an operation or a set of operations on an object.
REVOKE eliminates a grant, which may be the default grant.

COMMENT Adding the element into data dictionary

1.GRANT COMMAND- It authorizes one or more users to perform an operation or a set of operations on an object.
2.REVOKE COMMAND-It eliminates a grant, which may be the default grant.

EXAMPLE OF GRANT AND REVOKE COMMAND-
  • Suppose that DBA(Data base administer) creates four accounts A1,A2,A3 and A4 and wants only A1 to be able to create base relations, then the DBA must issue the following GRANT command in SQL.               
          GRANT CREATE TABLE TO A1;
  • Suppose that A1 creates two base relations EMPLOYEE AND DEPARTMENT.Hence A1 is then the owner of these two relations. 
EMPLOYEE RELATION  
NAME
SSN
BDATE
ADDRESS
SALARY
DNO
          
DEPARTMENT RELATION
DNUMBER
DNAME
MGR_SSN
  • Next suppose that A1 wants to grant to account A2 ,the privilege to insert and delete tuples in both of these relations.However A1 does not want A2 to be able to propagate these privileges to additional accounts then A1 can issue the following command.
GRANT INSERT ,DELETE ON EMPLOYEE,DEPARTMENT TO A2;

          Here A2 has not given the Grant Option.
  • Now suppose that A1 wants to allow account A3 to retrieve information from either of the two tables. A1 can issue the following command.
GRANT SELECT ON EMPLOYEE ,DEPARTMENT TO A3 WITH GRANT OPTION;

          The clause with GRANT OPTION means that A3 can now propagate the privileges to other              accounts by using GRANT.
          E.G.- A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by issuing the following command. 

GRANT SELECT ON EMPLOYEE TO A4;
  • Now suppose that A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3 .A1 then can issue this command.
REVOKE SELECT ON EMPLOYEE FROM A3;

         The DBMS must now automatically revoke the SELECT privilege on EMPLOYEE from A4 too because A3 granted that privilege to A4 and A3 does not have the privilege any more.


3. COMMENT COMMAND-Adding the element into data dictionary.A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:
  • Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
  • Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.
EXAMPLE-   SELECT last_name, salary /* Select the names of the employee and their salary */
FROM EMPLOYEE /*The EMPLOYEE relation is used from where data is retrieved*/;