Chapter - 2

2.1 Terminologies used in Relational Model:

1. Tables – The relational model uses the table format to save relationships.
Each table has two properties: rows and columns. Rows represent records,
while columns represent attributes.
2. Tuple – A single row in a table contains the record and is called tuple.
3. Cardinality: Number of rows (tuples) of the table is called cardinality.
4. Attribute – A column in the table is called attribute. These are the
properties define relation.
5. Degree: Number of attributes in the table is called degree.
6. Relation key – Every row has one, two or multiple attributes, which is called
relation key.
7. Attribute domain – Every attribute has some pre-defined value and scope
which is known as attribute domain.

2.2 Relational constraint:

 Relational constraints are the restrictions imposed on the database contents and
operations.
 They ensure the correctness of data in the database.
Types of Constraints in DBMS-

In DBMS, there are following 5 different types of relational constraints-

1. Domain constraint
2. Tuple Uniqueness constraint
3. Key constraint
4. Entity Integrity constraint
5. Referential Integrity constraint
1. Domain Constraint-

 Domain constraint defines the domain or set of values for an attribute.

 It specifies that the value taken by the attribute must be the atomic value
from its domain.

Example-

Consider the following Student table-

STU_ID Name Age

S001 Akshay 20

S002 Abhishek 21

S003 Shashank 20

S004 Rahul A

Here, value ‘A’ is not allowed since only integer values can be taken by the
age attribute.

2. Tuple Uniqueness Constraint-

Tuple Uniqueness constraint specifies that all the tuples must be necessarily
unique in any relation.

Example-01:
Consider the following Student table-

STU_ID Name Age

S001 Akshay 20

S002 Abhishek 21

S003 Shashank 20

S004 Rahul 20
 This relation satisfies the tuple uniqueness constraint since here all the tuples are
unique.

Example-02:
Consider the following Student table-

STU_ID Name Age

S001 Akshay 20

S001 Akshay 20

S003 Shashank 20

S004 Rahul 20

This relation does not satisfy the tuple uniqueness constraint since here all the
tuples are not unique.

3. Key Constraint-

Key constraint specifies that in any relation-

 All the values of primary key must be unique.

 The value of primary key must not be null.

Example-

Consider the following Student table-

STU_ID Name Age

S001 Akshay 20
 S001 Abhishek 21

S003 Shashank 20

S004 Rahul 20

This relation does not satisfy the key constraint as here all the values of primary
key are not unique.

4. Entity Integrity Constraint-

 Entity integrity constraint specifies that no attribute of primary key must contain
a null value in any relation.
 This is because the presence of null value in the primary key violates the
uniqueness property.

Example-
Consider the following Student table-

STU_ID Name Age

S001 Akshay 20

S002 Abhishek 21

S003 Shashank 20

Rahul 20

This relation does not satisfy the entity integrity constraint as here the primary key
contains a NULL value.

5. Referential Integrity Constraint-

 This constraint is enforced when a foreign key references the primary key of a
relation.
 It specifies that all the values taken by the foreign key must either be available in
the relation of the primary key or be null.

Example-

Consider the following two relations- ‗Student‘ and ‗Department‘.

Here, relation ‗Student‘ references the relation ‗Department‘.

Student

STU_ID Name Dept_no

S001 Akshay D10

S002 Abhishek D10

S003 Shashank D11

S004 Rahul D14

Department

Dept_no Dept_name

D10 ASET

D11 ALS

D12 ASFL

D13 ASHS

Here,
 The relation ‗Student‘ does not satisfy the referential integrity constraint.
 This is because in relation ‗Department‘, no value of primary key specifies
department no. 14.
 Thus, referential integrity constraint is violated.

2.3. Relational Integrity:

Integrity means grouping of data. It has completeness, correctness and
consistency of data.

It ensure that data entered in the database must be complete, accurate,

valid and consistent.

It can be achieve by rules or constraints.

The constraints are

 Domain Integrity Constraint

 Entity Integrity Constraint

 Referential Integrity Constraint

2.4. Key Constraints:

 Constraints or nothing but the rules that are to be followed while entering
data into columns of the database table
 Constraints ensure that data entered by the user into columns must be
within the criteria specified by the condition
 For example, if you want to maintain only unique IDs in the employee table
or if you want to enter only age under 18 in the student table etc
 We have 5 types of key constraints in DBMS
o NOT NULL: ensures that the specified column doesn’t contain a
NULL value.
o UNIQUE : provides a unique/distinct values to specified columns.
o DEFAULT: provides a default value to a column if none is
specified.
o CHECK :checks for the predefined conditions before inserting the
data inside the table.
o PRIMARY KEY: it uniquely identifies a row in a table.
o FOREIGN KEY: ensures referential integrity of the relationship

Not Null

 Null represents a record where data may be missing data or data for that
record may be optional
  Once not null is applied to a particular column, you cannot enter null
values to that column and restricted to maintain only some proper value
other than null
 A not-null constraint cannot be applied at table level

Example

CREATE TABLE person (

ID int NOT NULL,
name Varchar(40) NOT NULL,
age int NOT NULL,
address varchar(60),
salary int
);

 In the above example, we have applied not null on three columns ID, name
and age which means whenever a record is entered using insert
statement all three columns should contain a value other than null
 We have two other columns address and salary, where not null is not
applied which means that you can leave the row as empty or use null
value while inserting the record into the table

Unique

 Sometimes we need to maintain only unique data in the column of a

database table, this is possible by using a unique constraint
 Unique constraint ensures that all values in a column are unique

Example

CREATE TABLE Persons (

ID int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
);
In the above example, as we have used unique constraint on ID column we are
not supposed to enter the data that is already present, simply no two ID
values are same

DEFAULT

 Default clause in SQL is used to add default data to the columns

 When a column is specified as default with some value then all the rows will
use the same value i.e each and every time while entering the data we need
not enter that value
  But default column value can be customized i.e it can be
overridden when inserting a data for that row based on the requirement.

Example for DEFAULT clause

The following SQL sets a DEFAULT value for the ―city‖ column when the ―emp‖
table is created:

CREATE TABLE emp (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'addis ababa'
);

 As a result, whenever you insert a new row each time you need not enter a
value for this default column that is entering a column value for a
default column is optional and if you don’t enter the same value is
considered that is used in the default clause

Check

 Suppose in real-time if you want to give access to an application only if the

age entered by the user is greater than 18 this is done at the back-end by
using a check constraint
 Check constraint ensures that the data entered by the user for that column
is within the range of values or possible values specified.

Example for check constraint

CREATE TABLE STUDENT (

ID int ,
Name varchar(255) ,
Age int,
CHECK (Age>=18)
);

 As we have used a check constraint as (Age>=18) which means values

entered by the user for this age column while inserting the data must
be less than or equal to 18 otherwise an error is shown
 Simply, the only possible values that the age column will accept is [0 -17]

Primary Key
A primary key is a constraint in a table that uniquely identifies each row record in
a database table by enabling one or more the columns in the table as the primary
key

Creating a primary key

A particular column is made as a primary key column by using the primary key
keyword followed with the column name

CREATE TABLE EMP (

ID INT
NAME VARCHAR (20)
AGE INT
COURSE VARCHAR(10)
PRIMARY KEY (ID)
);

 Here we have used the primary key on ID column then ID column must
contain unique values i.e one ID cannot be used for another student.
 If you try to enter duplicate value while inserting in the row you are
displayed with an error
 Hence primary key will restrict you to maintain unique values and not
null values in that particular column

Foreign Key

 The foreign key a constraint is a column or list of columns that points to the
primary key column of another table
 The main purpose of the foreign key is only those values are allowed in the
present table that will match the primary key column of another table.

Example to create a foreign key

Reference Table

CREATE TABLE CUSTOMERS1(

ID INT ,
NAME VARCHAR (20) ,
COURSE VARCHAR(10) ,
PRIMARY KEY (ID)
);
 Child Table

CREATE TABLE CUSTOMERS2(

ID INT ,
MARKS INT,
REFERENCES CUSTOMERS1(ID)
);

2.5 Relational Language and View:

Relational Query language is used by the user to communicate with the

database. They are generally on a higher level than any other programming
language.
This is further divided into two types
 Procedural Query Language
 Non-Procedural Language

Procedural Query Language

The user instructs the system to perform a set of operations on the database
to determine the desired results.
Non-Procedural Language
The user outlines the desired information without giving a specific procedure
for attaining the information.

Relational Algebra

The query language ‗Relational Algebra‘ defines a set of operations on

relations.

Relational Algebra
RELATIONAL ALGEBRA is a widely used procedural query language. It collects
instances of relations as input and gives occurrences of relations as output. It uses
various operations to perform this action. SQL Relational algebra query operations
are performed recursively on a relation. The output of these operations is a new
relation, which might be formed from one or more input relations.
Unary Relational Operations

 SELECT (symbol: σ)
 PROJECT (symbol: π)
 RENAME (symbol: ρ)

Relational Algebra Operations From Set Theory

 UNION (υ)
 INTERSECTION ( ),
  DIFFERENCE (-)
 CARTESIAN PRODUCT ( x )

Binary Relational Operations

 JOIN
 DIVISION

Let‘s study them in detail with solutions:

SELECT (σ)
The SELECT operation is used for selecting a subset of the tuples according to a
given selection condition. Sigma(σ)Symbol denotes it. It is used as an expression to
choose tuples which meet the selection condition. Select operator selects tuples
that satisfy a given predicate.

σp(r)

σ is the predicate

r stands for relation which is the name of the table

p is prepositional logic

Example 1

σ topic = "Database" (Tutorials)

Output – Selects tuples from Tutorials where topic = ‗Database‘.

Example 2

σ topic = "Database" and author = "guru99"( Tutorials)

Output – Selects tuples from Tutorials where the topic is ‗Database‘ and ‗author‘ is
guru99.

Example 3

σ sales > 50000 (Customers)

Output – Selects tuples from Customers where sales is greater than 50000

Projection(π)
The projection eliminates all attributes of the input relation but those mentioned in
the projection list. The projection method defines a relation that contains a vertical
subset of Relation.
This helps to extract the values of specified attributes to eliminates duplicate
values. (pi) symbol is used to choose attributes from a relation. This operator helps
you to keep specific columns from a relation and discards the other columns.

Example of Projection:

Consider the following table

CustomerID CustomerName Status

1 Google Active
2 Amazon Active
3 Apple Inactive
4 Alibaba Active

Here, the projection of CustomerName and status will give

Π CustomerName, Status (Customers)

CustomerName Status
Google Active
Amazon Active
Apple Inactive
Alibaba Active

Rename (ρ)
Rename is a unary operation used for renaming attributes of a relation.

ρ (a/b)R will rename the attribute ‗b‘ of relation by ‗a‘.

Union operation (υ)

UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B.
It also eliminates duplicate tuples. So, set A UNION set B would be expressed as:

The result <- A ∪ B

For a union operation to be valid, the following conditions must hold –

 R and S must be the same number of attributes.

 Attribute domains need to be compatible.
 Duplicate tuples should be automatically removed.

Example

Consider the following tables.

Table A Table B
column 1 column 2 column 1 column 2
1 1 1 1
1 2 1 3
A ∪ B gives

Table A ∪ B
column 1 column 2
1 1
1 2
1 3

Set Difference (-)

– Symbol denotes it. The result of A – B, is a relation which includes all tuples that
are in A but not in B.

 The attribute name of A has to match with the attribute name in B.

 The two-operand relations A and B should be either compatible or Union
compatible.
 It should be defined relation consisting of the tuples that are in relation A,
but not in B.

Example

A-B
Table A – B
column 1 column 2
1 2

Intersection
An intersection is defined by the symbol ∩

A∩B

Defines a relation consisting of a set of all tuple that are in both A and B. However,
A and B must be union-compatible.
 Visual
Definition of Intersection
Example:

A∩B
Table A ∩ B
column 1 column 2
1 1

Join Operations
Join operation is essentially a cartesian product followed by a selection criterion.

Join operation denoted by ⋈.

JOIN operation also allows joining variously related tuples from different relations.

Types of JOIN:

Various forms of join operation are:

Inner Joins:

Outer join:

 Left Outer Join

 Right Outer Join
 Full Outer Join

Inner Join:
In an inner join, only those tuples that satisfy the matching criteria are included,
while the rest are excluded. Let‘s study various types of Inner Joins:

INNER JOIN (⋈)

Natural join can only be performed if there is a common attribute (column) between
the relations. The name and type of the attribute must be same.

Example

Consider the following two tables

C
Num Square
2 4
3 9
D
Num Cube
2 8
3 27
C⋈D
C⋈D
Num Square Cube
2 4 8
3 9 27

OUTER JOIN
In an outer join, along with tuples that satisfy the matching criteria, we also
include some or all tuples that do not match the criteria.

Left Outer Join(A B)

In the left outer join, operation allows keeping all tuple in the left relation. However,
if there is no matching tuple is found in right relation, then the attributes of right
relation in the join result are filled with null values.

Consider the following 2 Tables

A
Num Square
2 4
3 9
4 16
B
Num Cube
2 8
3 18
5 75
A B
A⋈B
Num Square Cube
2 4 8
3 9 18
4 16 –

Right Outer Join: ( A B)

In the right outer join, operation allows keeping all tuple in the right relation.
However, if there is no matching tuple is found in the left relation, then the
attributes of the left relation in the join result are filled with null values.

A B
A⋈B
Num Cube Square
2 8 4
3 18 9
5 75 –

Full Outer Join: ( A B)

In a full outer join, all tuples from both relations are included in the result,
irrespective of the matching condition.

A B
A⋈B
Num Cube Square
2 4 8
3 9 18
4 16 –
5 – 75
View:
It is the virtual table based on the result set of SQL statement.
It contains rows and columns just like a real table.
Syntax:
Create view viewname as select col1,col2,.. coln from tablename where <condition>
Eg:
Custid Custname Contactno Country
101 YZ 0988947457 Ethiopia
102 ABC 0945421434 China
103 ZZZ 0954545475 Ethiopia
104 DEF 0945475542 India

Retrieve Customer name and Contact number for Ethiopia customer.

Ordinary Query:

Select custname,contactno from customer where country=‖Ethiopia‖;

Non-Procedural view:
Create view EthiopiaCustomer as Select custname,contactno from customer
where country=‖Ethiopia‖;

Procedural View:
Create view EthiopiaCustomer1 as (π custname,contactno(σ
country=‖Ethiopia‖ (customer));

2.6 Relational Database Management System:

RDBMS stands for relational database management system—a software system

that enables you to define, create, maintain, and control access to relational
databases.

In relational databases, data is arranged and stored in tables consisting of columns

and rows.

Like we mentioned above, RDBMS stores data in tables. Each row in a table is a
record with a unique ID, which is called primary key. The columns of a table
contain attributes of the data. Since each record typically has a value for each

Basic notions associated with relational databases:

Primary key is a unique ID that identifies each row of a table.

Foreign key is a field (or multiple fields) in one table that refers to a primary key in
another table. It helps establish relations between tables.

View is a virtual table that does not store data; instead, it presents a certain data
output that is computed from underlying tables.

Index is a data structure that contains a copy of a column (or multiple columns)
from a database table that is ordered to accelerate database retrieval operations on
the original column.
 Structured Query Language (SQL) is a domain-specific language used for storing,
retrieving, and manipulating data—a standard for relational databases.

SQL query is a request for information from an RDBMS. Users write and execute
queries to retrieve, add, modify, and delete data from relational databases.

Types of relationships
There are three main types of relationships between tables that ensure the absolute
flexibility of the relational database model.

 One-to-one relationships
 One-to-many relationships
 Many-to-many relationships

One-to-one relationships
One-to-one relationship is when a single record in table A is related to one (and
only one) record in table B.

One-to-many relationships
One-to-many relationship is a bit more complex: a single record in table A is related
to multiple records in table B.

Many-to-many relationships
Finally, the most sophisticated example is many-to-many relationship, when
multiple records in table A are related to multiple records in table B.

Key features of RDBMS

Structured and interrelated data

Data in relational databases is stored in a well-structured and easily
understandable table format. Meanwhile, versatile relations between tables and the
use of SQL to write queries of different complexity contribute to the flexibility of
data management.

ACID support
ACID (which comprises Atomicity, Consistency, Isolation, Durability) is a well-
known set of properties of database transactions meant to guarantee data validity
despite errors, failures, and other possible mishaps. Relational databases deliver
full ACID support.

Multi-user access
Relational databases provide multi-user access along with privileges that allow
database administrators to have complete control over activities in databases and
grant different levels of access to other users.

Ease of use
 Besides the overall convenience of storing, accessing, and managing data in tables,
we should mention a multitude of database tools that offer intuitive GUIs and make
daily development and administration of databases rather easy even for
newcomers.

Advantages and disadvantages of RDBMS:

Advantage:

 Flexible data management

 Integrity and security of sensitive data
 Reliable data storage, easy backup and recovery
 Compliance with data protection standards and regulations (such as GDPR)
 Fast integration with commercial software and streamlined development cycle

Disadvantage:

 Hardware and software costs

 Scalability can be pricey
 Regular updates of your RDBMS may require regular database maintenance
 Effective work with RDBMS requires professional expertise of database developers
and administrators