Chapter 11: RDBMS

Chapter 11: RDBMS

      In RDBMS database is a collection of relations each have a unique name. Each relation represented as tables. Eg: A school database consists of ‘Student’, ‘Address’, ‘Score’ tables or relations.

Advantages of RDBMS

w  Relation represents a collection of related data values.

w  Column name represents the values of each row

w  All column value has same data type.

w  Flexible.

w  Less storage is needed

w  Easy to Manipulate.

Terminologies used in RDBMS

§  Relation

§  Domain

§  Tuples

§  Attributes

§  Cardinality

§  Degree

§  Views

§  Keys

Relation

      It is a mathematical term for a table. Each row in a table represents a relationship among a set of values called relation.

Domain

It is a set of values appearing in a column. Eg: - The score is ranges from 0 to 600.  A range of values from which a particular value represents the score of a student is called domain. The domain may be differing in each column.

Tuple

The rows in a relation are called tuple. A row consists of a complete set of values used to represent particular information.

Eg:-

1

Sagir

234

C

Attributes

The columns in a relation are called attributes. Eg: - Slno., Name, Score, Grade

Domain is a set of values taken by the attributes.

Cardinality

The number of rows/tuples in a relation is called cardinality. In the above example cardinality is 5

Degree

The number of columns/attributes determines the degree of the relation. In the above example degree is 4.

Views

A view is a virtual table that doesn't really exist, but it is derived from a set of one or more base tables under given conditions. They are used to secure the data from unwanted persons.

Keys

In a relation all tuples have different values. So we can identify each tuple in a relation. By using the concept of keys in a relation we can identify each tuples. There are five types of key used in a relation.

Candidate key, Primary key, Alternate Key, Super Key, Foreign Key

Candidate Key

It is used to uniquely identify a row. In our example ‘slno’ is the candidate key. In a relation have more than one candidate key, also can use composite key as a candidate key. Eg:- Sl No+Name

Primary Key

It is the unique identifier for a tuple. Here the values do not have duplication in same relation. They may be a candidate key. In our eg:- Sl.No is the primary key because its values are not repeated.

Alternate Key

A candidate key that is not the primary key is called alternate key. Here Name+Score+grade is a alternate key.

Super Key

A combination of primary key with any other attribute is called super key.

Eg:- Sl.No+Score

Foreign Key

A single attribute or a set of attributes which is candidate key in another relation is called foreign key.

Relational Algebra

          Relational algebra consists of a set of operations that takes one or two relations as input and produces a new relation as its result. I.e. they are used to take operations on a relation. They divided in to two according to the number of relation in an operation.

Unary Operations     (Operation on one Table) ·        Select- s ·        Project- p

Binary Operations     (Operation on two Tables) ·        Union- U ·        Inter Section- n ·        Set Difference- ·        Cartesian Product- X

Unary Operations:

1. Select Operation s

It is used to select tuples in a single relation under a condition. The symbol s (sigma) is used for this.

Syntax: s(condition)(table name)

E.g:- Consider the relation student.

Name	Age	Score
Asa	17	32
Manu	16	43
Ninu	18	36

Then the operation s(score>35)(student) produces the result

Name	Age	Score
Manu	16	43
Ninu	18	36

Here we can use the all relational operations like =, <, >, <=, >=, <>.

2. Project Operation p

Used to retrieve columns or attribute wise information from a single table. The symbol p (pi) is used for this.

Syntax: p (column names)(table name)

Name	Score
Asa	32
Manu	43
Ninu	36

 E.g:- Consider the above relation student.

 The operation p name,score)(student) produces

Binary Operations:

1.Union Operation U

 It combines two tables with same attributes and same domain, so they called union compatible, also the resultant relation eliminate the duplicate tuples. The symbol U is used for this.

Syntax: Tablename1 U Tablename2

E.g:- Consider the relations school and hostel.

Name	Class	Age
Vina	IX	14
Sita	IX	14
Zinu	VII	11
Lila	IX	14

Name	Class	Age
Nina	X	15
Sita	IX	14
Manu	VIII	12
Lila	IX	14

The operation school U hostel produces

Name	Class	Age
Nina	X	15
Sita	IX	14
Manu	VIII	12
Lila	IX	14
Vina	IX	14
Zinu	VII	11

2.Intersection Operation n

It produces a new table with same data rows in two tables with same attributes and same domain. The symbol n is used for this.

Syntax: Tablename1 n Tablename2

E.g:- Consider the relations school and hostel.

 The operation school n hostel produces

Name	Class	Age
Sita	IX	14
Lila	IX	14

3. Set Difference Operation –

It produces a relation with tuple that are in one table but are not in another, from two relations. The – symbol is used for this.

Syntax: Tablename1 - Tablename2

E.g:- Consider the relations school and hostel.

The operation school - hostel produces

Name	Class	Age
Nina	X	15
Manu	VIII	12

4. Cartesian Product Operation X

It is used to combine any two relations. Here the relations have different attribute and domain so they called product compatible. It is denoted by X.

Syntax: Tablename1 X Tablename2

 E.g:- Consider the relations student  And score.

Subject	Grade	Result
Chem	B+	EHS
Compu	A	EHS

Name	Class
Nina	X
Manu	VIII
Sita	IX

The operation student X score  produces.

Name	Class	Subject	Grade	Result
Nina	X	Chem	B+	EHS
Nina	X	Compu	A	EHS
Manu	VIII	Chem	B+	EHS
Manu	VIII	Compu	A	EHS
Sita	IX	Chem	B+	EHS
Sita	IX	Compu	A	EHS

The resultant relation has 3x2=6 rows/ cardinality and 2+3=5 columns/ degree