Relational model stores data in the form of tables. This concept purposed
by Dr. E.F. Codd, a researcher of IBM in the year 1960s. The relational model
consists of three major components:
1. The set of relations and set
of domains that defines the way data can be represented (data structure).
2. Integrity rules that define
the procedure to protect the data (data integrity).
3. The operations that can be
performed on data (data manipulation).
A rational model database is
defined as a database that allows to group its data items into one or more
independent tables that can be related to one another by using fields common to
each related table.
Relational
model can represent as a
table with columns and rows. Each row is known as a tuple. Each table of the column
has a name or attribute.
------------------
--------------------------------------------------------------------
Relational Model Concepts
- Attribute: Each
column in a Table. Attributes are the properties which define a relation.
e.g., Student_Rollno, NAME,etc.
- Tables – In
the Relational model the, relations are saved in the table format. It is
stored along with its entities. A table has two properties rows and
columns. Rows represent records and columns represent attributes.
- Tuple – It
is nothing but a single row of a table, which contains a single record.
- Relation
Schema: A
relation schema represents the name of the relation with its attributes.
- Degree: The
total number of attributes which in the relation is called the degree of
the relation.
- Cardinality: Total
number of rows present in the Table.
- Column: The
column represents the set of values for a specific attribute.
- Relation
instance –
Relation instance is a finite set of tuples in the RDBMS system. Relation
instances never have duplicate tuples.
- Relation
key -
Every row has one, two or multiple attributes, which is called relation
key.
- Attribute
domain –
Every attribute has some pre-defined value and scope which is known as
attribute domain
------------------
Domain: It contains a set of atomic values that an attribute
can take.
Tables − In relational data model, relations are saved in
the format of Tables. This format stores the relation among entities. A table
has rows and columns, where rows represents records and columns represent the
attributes.
Tuple − A single row of a table, which contains a single
record for that relation is called a tuple.
Relation instance − A finite set of tuples in the relational database
system represents relation instance. Relation instances do not have duplicate tuples.
Or In the relational database system, the
relational instance is represented by a finite set of tuples. Relation
instances do not have duplicate tuples.
Relation schema − A relation schema describes the relation name
(table name), attributes, and their names. Or A
relational schema contains the name of the relation and name of all columns or
attributes.
Relation key − Each row has one or more attributes, known as
relation key, which can identify the row in the relation (table) uniquely. Or In the relational key, each row has one or more
attributes. It can identify the row in the relation uniquely
Attribute domain − Every attribute has some pre-defined value scope,
known as attribute domain. It contains the name
of a column in a particular table. Each attribute Ai must have a domain,
dom(Ai)
--------------------------------------------------------------------
Properties of
Relations
Ø Name of the relation is distinct from all other
relations.
Ø Each relation cell contains exactly one atomic (single)
value
Ø Each attribute contains a distinct name
Ø Attribute domain has no significance
Ø tuple has no duplicate value
Ø Order of tuple can have a different sequence
----------------------------------------------------------------------------------------
Ø A table has
a name that is distinct from all other tables in the database.
Ø There are no
duplicate rows; each row is distinct.
Ø Entries in
columns are atomic. The table does not contain repeating groups or multivalued
attributes.
Ø Entries from
columns are from the same domain based on their data type including:
o
number (numeric, integer, float, smallint,…)
o
character (string)
o
date
o
logical (true or false)
Ø Operations
combining different data types are disallowed.
Ø Each
attribute has a distinct name.
Ø The sequence
of columns is insignificant.
Ø The sequence
of rows is insignificant.
Relational
Algebra
Relational algebra is a procedural query language. It gives
a step by step process to obtain the result of the query. It uses operators to
perform queries.
We have
learned the 6 fundamental operations of relational algebra:
Rename _ (Rho) ρ
Selection _ σ Sigma
Projection _
uppercase of (pi) ∏
Set union - ∪
Set difference
: -
Cartesian
product _ X
Types of Relational operation
1. Select Operation:
- The select operation selects tuples
that satisfy a given predicate.
- It is denoted by sigma (σ).
Notation: σ p(r)
Where:
σ is used
for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation
BRANCH_NAME
|
LOAN_NO
|
AMOUNT
|
Downtown
|
L-17
|
1000
|
Redwood
|
L-23
|
2000
|
Perryride
|
L-15
|
1500
|
Downtown
|
L-14
|
1500
|
Mianus
|
L-13
|
500
|
Roundhill
|
L-11
|
900
|
Perryride
|
L-16
|
1300
|
Input:
σ BRANCH_NAME="perryride" (LOAN)
Output:
BRANCH_NAME
|
LOAN_NO
|
AMOUNT
|
Perryride
|
L-15
|
1500
|
Perryride
|
L-16
|
1300
|
2.
Project Operation:
- This
operation shows the list of those attributes that we wish to appear in the
result. Rest of the attributes are eliminated from the table.
- It
is denoted by ∏.
1.
Notation: ∏ A1, A2, An (r)
Where
A1, A2, A3 is
used as an attribute name of relation r.
Example: CUSTOMER
RELATION
NAME
|
STREET
|
CITY
|
Jones
|
Main
|
Harrison
|
Smith
|
North
|
Rye
|
Hays
|
Main
|
Harrison
|
Curry
|
North
|
Rye
|
Johnson
|
Alma
|
Brooklyn
|
Brooks
|
Senator
|
Brooklyn
|
Input:
1.
∏ NAME, CITY (CUSTOMER)
Output:
NAME
|
CITY
|
Jones
|
Harrison
|
Smith
|
Rye
|
Hays
|
Harrison
|
Curry
|
Rye
|
Johnson
|
Brooklyn
|
Brooks
|
Brooklyn
|
3.
Union Operation:
- Suppose
there are two tuples R and S. The union operation contains all the tuples
that are either in R or S or both in R & S.
- It
eliminates the duplicate tuples. It is denoted by ∪.
1. Notation: R ∪ S
A union operation must
hold the following condition:
- R
and S must have the attribute of the same number.
- Duplicate
tuples are eliminated automatically.
Example:
DEPOSITOR
RELATION
CUSTOMER_NAME
|
ACCOUNT_NO
|
Johnson
|
A-101
|
Smith
|
A-121
|
Mayes
|
A-321
|
Turner
|
A-176
|
Johnson
|
A-273
|
Jones
|
A-472
|
Lindsay
|
A-284
|
BORROW RELATION
CUSTOMER_NAME
|
LOAN_NO
|
Jones
|
L-17
|
Smith
|
L-23
|
Hayes
|
L-15
|
Jackson
|
L-14
|
Curry
|
L-93
|
Smith
|
L-11
|
Williams
|
L-17
|
Input:
1.
∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
|
Johnson
|
Smith
|
Hayes
|
Turner
|
Jones
|
Lindsay
|
Jackson
|
Curry
|
Williams
|
Mayes
|
4. Set
Intersection:
- Suppose
there are two tuples R and S. The set intersection operation contains all
tuples that are in both R & S.
- It
is denoted by intersection ∩.
1.
Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1.
∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
|
Smith
|
Jones
|
5. Set
Difference:
- Suppose
there are two tuples R and S. The set intersection operation contains all
tuples that are in R but not in S.
- It
is denoted by intersection minus (-).
1.
Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1.
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
|
Jackson
|
Hayes
|
Willians
|
Curry
|
6. Cartesian
product
- The
Cartesian product is used to combine each row in one table with each row
in the other table. It is also known as a cross product.
- It
is denoted by X.
1.
Notation: E X D
Example:
EMPLOYEE
EMP_ID
|
EMP_NAME
|
EMP_DEPT
|
1
|
Smith
|
A
|
2
|
Harry
|
C
|
3
|
John
|
B
|
DEPARTMENT
DEPT_NO
|
DEPT_NAME
|
A
|
Marketing
|
B
|
Sales
|
C
|
Legal
|
Input:
1.
EMPLOYEE X DEPARTMENT
Output:
EMP_ID
|
EMP_NAME
|
EMP_DEPT
|
DEPT_NO
|
DEPT_NAME
|
1
|
Smith
|
A
|
A
|
Marketing
|
1
|
Smith
|
A
|
B
|
Sales
|
1
|
Smith
|
A
|
C
|
Legal
|
2
|
Harry
|
C
|
A
|
Marketing
|
2
|
Harry
|
C
|
B
|
Sales
|
2
|
Harry
|
C
|
C
|
Legal
|
3
|
John
|
B
|
A
|
Marketing
|
3
|
John
|
B
|
B
|
Sales
|
3
|
John
|
B
|
C
|
Legal
|
7.
Rename Operation:
The rename operation is
used to rename the output relation. It is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT
relation to STUDENT1.
1.
ρ(STUDENT1, STUDENT)
Join Operations:
A Join operation combines
related tuples from different relations, if and only if a given join condition
is satisfied. It is denoted by ⋈.
Example:
EMPLOYEE
EMP_CODE
|
EMP_NAME
|
101
|
Stephan
|
102
|
Jack
|
103
|
Harry
|
SALARY
EMP_CODE
|
SALARY
|
101
|
50000
|
102
|
30000
|
103
|
25000
|
1. Operation: (EMPLOYEE ⋈ SALARY)
Result:
EMP_CODE
|
EMP_NAME
|
SALARY
|
101
|
Stephan
|
50000
|
102
|
Jack
|
30000
|
103
|
Harry
|
25000
|
Types
of Join operations:
1.
Natural Join:
- A
natural join is the set of tuples of all combinations in R and S that are
equal on their common attribute names.
- It
is denoted by ⋈.
Example: Let's use the above EMPLOYEE table and SALARY table:
Input:
1. ∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)
Output:
EMP_NAME
|
SALARY
|
Stephan
|
50000
|
Jack
|
30000
|
Harry
|
25000
|
2.
Outer Join:
The outer join operation is an extension of the join
operation. It is used to deal with missing information.
Example:
EMPLOYEE
EMP_NAME
|
STREET
|
CITY
|
Ram
|
Civil
line
|
Mumbai
|
Shyam
|
Park
street
|
Kolkata
|
Ravi
|
M.G.
Street
|
Delhi
|
Hari
|
Nehru
nagar
|
Hyderabad
|
FACT_WORKERS
EMP_NAME
|
BRANCH
|
SALARY
|
Ram
|
Infosys
|
10000
|
Shyam
|
Wipro
|
20000
|
Kuber
|
HCL
|
30000
|
Hari
|
TCS
|
50000
|
Input:
1. (EMPLOYEE ⋈ FACT_WORKERS)
Output:
EMP_NAME
|
STREET
|
CITY
|
BRANCH
|
SALARY
|
Ram
|
Civil
line
|
Mumbai
|
Infosys
|
10000
|
Shyam
|
Park
street
|
Kolkata
|
Wipro
|
20000
|
Hari
|
Nehru
nagar
|
Hyderabad
|
TCS
|
50000
|
An outer join is
basically of three types:
- Left
outer join
- Right
outer join
- Full
outer join
a. Left
outer join:
- Left
outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute names.
- In
the left outer join, tuples in R have no matching tuples in S.
- It
is denoted by ⟕.
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
1. EMPLOYEE ⟕ FACT_WORKERS
EMP_NAME
|
STREET
|
CITY
|
BRANCH
|
SALARY
|
Ram
|
Civil
line
|
Mumbai
|
Infosys
|
10000
|
Shyam
|
Park
street
|
Kolkata
|
Wipro
|
20000
|
Hari
|
Nehru
street
|
Hyderabad
|
TCS
|
50000
|
Ravi
|
M.G.
Street
|
Delhi
|
NULL
|
NULL
|
b. Right outer join:
- Right
outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute names.
- In
right outer join, tuples in S have no matching tuples in R.
- It
is denoted by ⟖.
Example: Using the above EMPLOYEE table and FACT_WORKERS
Relation
Input:
1. EMPLOYEE ⟖ FACT_WORKERS
Output:
EMP_NAME
|
BRANCH
|
SALARY
|
STREET
|
CITY
|
Ram
|
Infosys
|
10000
|
Civil
line
|
Mumbai
|
Shyam
|
Wipro
|
20000
|
Park
street
|
Kolkata
|
Hari
|
TCS
|
50000
|
Nehru
street
|
Hyderabad
|
Kuber
|
HCL
|
30000
|
NULL
|
NULL
|
c. Full
outer join:
- Full
outer join is like a left or right join except that it contains all rows
from both tables.
- In
full outer join, tuples in R that have no matching tuples in S and tuples
in S that have no matching tuples in R in their common attribute name.
- It
is denoted by ⟗.
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
1. EMPLOYEE ⟗ FACT_WORKERS
Output:
EMP_NAME
|
STREET
|
CITY
|
BRANCH
|
SALARY
|
Ram
|
Civil
line
|
Mumbai
|
Infosys
|
10000
|
Shyam
|
Park
street
|
Kolkata
|
Wipro
|
20000
|
Hari
|
Nehru
street
|
Hyderabad
|
TCS
|
50000
|
Ravi
|
M.G.
Street
|
Delhi
|
NULL
|
NULL
|
Kuber
|
NULL
|
NULL
|
HCL
|
30000
|
3. Equi join:
It is also known as an
inner join. It is the most common join. It is based on matched data as per the
equality condition. The equi join uses the comparison operator(=).
Example:
CUSTOMER RELATION
CLASS_ID
|
NAME
|
1
|
John
|
2
|
Harry
|
3
|
Jackson
|
PRODUCT
PRODUCT_ID
|
CITY
|
1
|
Delhi
|
2
|
Mumbai
|
3
|
Noida
|
Input:
1.
CUSTOMER ⋈ PRODUCT
Output:
CLASS_ID
|
NAME
|
PRODUCT_ID
|
CITY
|
1
|
John
|
1
|
Delhi
|
2
|
Harry
|
2
|
Mumbai
|
3
|
Harry
|
3
|
Noida
|
Division Operator (÷):
Division operator A÷B can be applied if and only
if:
·
Attributes of B
is proper subset of Attributes of A.
·
The relation
returned by division operator will have attributes = (All attributes of A – All
Attributes of B)
·
The relation
returned by division operator will return those tuples from relation A which
are associated to every B’s tuple.
Consider the
relation STUDENT_SPORTS and ALL_SPORTS given in Table 1 and Table 2 above.
Table 1
STUDENT_SPORTS
ROLL_NO
|
SPORTS
|
1
|
Badminton
|
2
|
Cricket
|
2
|
Badminton
|
4
|
Badminton
|
Table 2
ALL_SPORTS
SPORTS
|
Badminton
|
Cricket
|
To apply
division operator as
STUDENT_SPORTS÷
ALL_SPORTS
·
The operation
is valid as attributes in ALL_SPORTS is a proper subset of attributes in
STUDENT_SPORTS.
·
The attributes
in resulting relation will have attributes {ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO
·
The tuples in
resulting relation will have those ROLL_NO which are associated with all B’s
tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are associated to Badminton only.
ROLL_NO 2 is associated to all tuples of B. So the resulting relation will be:
ROLL_NO
|
2
|
Assignment
Assignments are often used to increase clarity by cutting a long query into
multiple steps, each of which can be described by a short line.
Denoted by: T ß[expression]
where [expression] is a relational algebra expression, and T is a table
variable.
The assignment stores in T the table output by [expression].
Reference:
I am really feel glad to read out published post on this blog. Apart from this, I am still waiting other post for escalating my knowledge power. Checkout How to Check NECO result
ReplyDeleteExcellent blog, very clear and informative. Thank you for an outstanding content. Check is kwasu post utme de form still on
ReplyDelete