Special relational operations | Relational Model

SPECIAL RELATIONAL OPERATIONS

These includes operations like
 

1. Selection
2. Projection
3. Join
4. Division

1. Selection-To identify a set of tuples which is a part of a relation and to extract only these tuples out. The select operation selects tuples that satisfy a given predicate or condition. It is also known as restriction operation. 

Read More

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.

Read More

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

Read More

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)

Read More

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)); 

Read More

Relational Algebra in Database management system | Relational Model

RELATIONAL ALGEBRA

INTRODUCTION-

It is the collection of operations used to manipulate a relations or tables.These operations enables the user to specify the retrieval request on one or more relations that result in a new relation without changing the original relation.It is a procedural language.The user has to specify what is required and what are the sequence of steps to get the desired output.

Relational Closure- When operations are performed on existing relations to produce a new relation then the original relation remains unaffected. Therefore the output from one operation can become the input of some other operation.This property is called relational closure.

TYPES OF RELATIONAL OPERATIONS- There are two types of relational operations

1.) Traditional Set operations or Basic Set oriented operations

2.) Special relational Operations

1.) Traditional Set operations or Basic Set oriented operations- 

These include Union,integration ,difference and Cartesian product.These are the binary operations, it means that these can be applied to a pair of relations.

2.) Special relational Operations-

These includes operations like

• Selection
• Projection
• Join
• Division  

where selection and projection are unary operations and Join and division are binary operations.

Read More

Relational model | Data models

RELATIONAL MODEL

INTRODUCTION-

Relational model stores the data in the form of table. It consists of three major components.

1.) The set of relations and set of domains that defines the way data can be represented.

2.) Integrity rules that defines the procedure to protect the data.

3.) The operations that can be performed on the table.

CHARACTERISTICS-

Characteristics of relational database are:-

1.) All data is conceptually represented as an orderly arrangement of data into rows and column called a relation or table.

2.) All the values are scaler i.e. at any given row or column position there is one and only one value.

3.) All operations are performed on the entire relation and the result is an entire relation.

BASIC TERMINOLOGY OF RELATIONAL DATABASE-  

1.) TUPLE OF A RELATION-Each row in a table is known as tuple.

2.) CARDINALITY OF A RELATION-  It is the no of tuples in the relation.

3.) DEGREE OF A RELATION-No of columns is known as degree of a relation.

4.) DOMAIN OF A RELATION-It defines the kind of data represented by the attribute.It is the set of all possible values that an attribute may contain.

5.) BODY OF THE RELATION-It consists of an unordered set of 0 or more tuples.

EXAMPLE:- Consider a table

1.) TUPLE OF A RELATION-  8

2.) CARDINALITY OF A RELATION-  8

3.) DEGREE OF A RELATION- 5

4.) DOMAIN OF A RELATION- All the data entered in the table is the domain of a relation.

KEYS OF A RELATION-

1.) PRIMARY KEY- It is the key that uniquely identifies a record. It doesn't have null values.

2.) FOREIGN KEY- It refers to the primary key of some other table.It permits only those values which appear in the primary key of the table to which it refers.

Read More

Data models and their types | database managenent system

DATA MODELS

INTRODUCTION:-
It can be  defined as an integrated collection of concepts for describing and manipulating data,relationship between data and the constrains of the data in any organization.

It consists of three components:-

1.)Structured part:- It consist of set of rules according to which database can be connected.

2.) Manipulative part:- It defines the types of operations that are allowed on data.

3.) Set of integrity rules:-It ensures that the data is accurate.

PURPOSE OF DATA MODEL:- Purpose of data model is to represent the data and to make it understandable.

TYPES OF DATA MODEL:-

There are 3 types of data models:-

1.) Object based data models

2.) Physical data models

3.) Record based

1.) OBJECT BASED DATA MODELS:-It uses the concepts such as entities,attributes and relationship.It is of further 4 types:-

1.) ER(entity relationship)model

2.) Object oriented ER model

3.) Semantic model

4.) Functional model

2.) PHYSICAL DATA MODEL:-It describes how the data is stored in the computer representing the information such as record structure ,record modeling and access paths.

3.) RECORD BASED DATA MODEL:- These are used in describing the data at logical and view levels.These are used to specify the overall logical structure of the database.Record database models are of 3 types which are:-

1.) Hierarchical model

2.) Network model

3.) Relational model

Read More