UNIT-2

RELATIONAL MODEL
The relational model uses a collection of tables to represent both data and the relationships
among those data. Each table has multiple columns, and each column has a unique name.
Tables are also known as relations. The relational model is an example of a record-based
model. Record-based models are so named because the database is structured in fixed-format
records of several types. Each table contains records of a particular type.
A relational database consists of a collection of tables, each of which is assigned a unique
name. Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS,
PHONE, and AGE shown in the table.

ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 18

• Attribute: Attributes are the properties that define an entity.

e.g.; ROLL_NO, NAME, ADDRESS

• Relation Schema: A relation schema defines the structure of the relation and
represents the name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema for
STUDENT. If a schema has more than 1 relation, it is called Relational Schema.

• Tuple: Each row in the relation is known as a tuple. The above relation contains
4 tuples, one of which is shown as:

1 RAM DELHI 9455123451 18

• Relation Instance: The set of tuples of a relation at a particular instance of time

is called a relation instance. Table 1 shows the relation instance of STUDENT at
a particular time. It can change whenever there is an insertion, deletion, or update
in the database.
 • Degree: The number of attributes in the relation is known as the degree of the
relation. The STUDENT relation defined above has degree 5.

• Cardinality: The number of tuples in a relation is known as cardinality.

The STUDENT relation defined above has cardinality 4.

• Column: The column represents the set of values for a particular attribute. The
column ROLL_NO is extracted from the relation STUDENT.

Types of Keys

➢ Keys play an important role in the relational database.

➢ It is used to uniquely identify any record or row of data from the table. It is also used
to establish and identify relationships between tables.

1. Primary key

o It is the first key used to identify one and only one instance of an entity uniquely. An
entity can contain multiple keys, as we saw in the PERSON table. The key which is
most suitable from those lists becomes a primary key.
o In the EMPLOYEE table, ID can be the primary key since it is unique for each
employee. In the EMPLOYEE table, we can even select License_Number and
Passport_Number as primary keys since they are also unique.
o For each entity, the primary key selection is based on requirements and developers.

2.Candidate key
 o A candidate key is an attribute or set of attributes that can uniquely identify a tuple.
o Except for the primary key, the remaining attributes are considered a candidate key.
The candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the
attributes, like SSN, Passport_Number, License_Number, etc., are considered a candidate key.

3. Super Key

Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a
candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME),

the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence,
this combination can also be a key.

4. Foreign key

o Foreign keys are the column of the table used to point to the primary key of another
table.
o Every employee works in a specific department in a company, and employee and
department are two different entities. So we can't store the department's information in
the employee table. That's why we link these two tables through the primary key of one
table.
 o We add the primary key of the DEPARTMENT table, Department_Id, as a new
attribute in the EMPLOYEE table.
o In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.

5. Alternate key

There may be one or more attributes or a combination of attributes that uniquely identify each
tuple in a relation. These attributes or combinations of the attributes are called the candidate
keys. One key is chosen as the primary key from these candidate keys, and the remaining
candidate key, if it exists, is termed the alternate key.

For example, employee relation has two attributes, Employee_Id and PAN_No, that act as
candidate keys. In this relation, Employee_Id is chosen as the primary key, so the other
candidate key, PAN_No, acts as the Alternate key.

6. Composite key

Whenever a primary key consists of more than one attribute, it is known as a composite key.
This key is also known as Concatenated Key.

7. Artificial key
The key created using arbitrarily assigned data are known as artificial keys. These keys are
created when a primary key is large and complex and has no relationship with many other
relations. The data values of the artificial keys are usually numbered in a serial order.

Relational Algebra
Relational algebra in dbms is used to manipulate and query data stored in a relational database.
It consists of operators that allow us to retrieve data from one or more tables based on specific
conditions.

Types of Relational Operations in DBMS

There are several types of relational operations in dbms, including:

• Select: This operation is used to select a subset of rows from a table based on a specific
condition.

• Project: This operation is used to select a subset of columns from a table.

• Join: This operation is used to combine rows from two or more tables based on a
common column.

• Union: This operation is used to combine the rows of two tables into a single table
while eliminating duplicates.

• Intersection: This operation is used to retrieve only the rows that are common between
two tables.

• Difference: This operation is used to retrieve all rows from one table that are not
present in the other table.

Consider a table named "Students" with columns "Name", "Course", and "Grade", as follows:

Name Course Grade

Alice CS101 A
 Name Course Grade

Bob CS101 B+

Alice CS102 A-

Charlie CS101 C+

Bob CS102 B

Select: To select all students who received an A in CS101, the relational algebra expression
would be:

Input:

σ(Course = "CS101" AND Grade = "A")(Students)

Output:

Name Course Grade

Alice CS101 A

Project: To project only the names of students, the relational algebra expression would be:

Input:

Π(Name)(Students)
 Course Instructor
Output:

CS101 Smith

CS102 Johnson

Name

Alice

Bob

Charlie

Join: Consider another table named "Enrollment" with columns "Course" and "Instructor", as
follows:

Input:

Students ⋈ Enrollment

Output:
To join the "Students" and "Enrollment" tables on the "Course" column, the resulting table
would be:

Name Course Grade Instructor

Alice CS101 A Smith

Bob CS101 B+ Smith

Alice CS102 A- Johnson

Charlie CS101 C+ Smith

Bob CS102 B Johnson

Union: To retrieve a table with all distinct courses from "Students" and "Enrollment", the
resulting table would be:

Input:

Enrollment(Course) ∪ Students(Course)

Output:

Course

CS101
 Course

CS102

Intersection: To retrieve only the courses that are common to both "Students" and
"Enrollment", the resulting table would be:

Input:

Enrollment(Course) ∩ Students(Course)

Output:

Course

CS101

Difference: To retrieve all students who are not enrolled in any courses, the resulting table
would be:

Input:

Students - π(Name, Course, Grade)(Students ⋈ Enrollment)

Output:

Name Course Grade

Charlie NULL NULL

Relational Calculus
Relational calculus is nothing but focusing on what to do rather than focusing on how to do.
Relational calculus is present in two formats:

➢ Domain relational calculus (DRC)

➢ Tuples relational calculus (TRC)

Domian Relational Calculus

• In Domain relational calculus filtering of records is done based on the domain of the
attributes rather than tuple values

• A domain is nothing but the set of allowed values in the column of a table

Syntax:

{a1,a2,………………../P(a1,a2…………….)}

Where a1 , a2 ……..are attributes

P => condition for fetching the data

Example 1:
{ <name, age>/ ∈ student ^ age<21}
Again, the above query will return the names and ages of the students in the table Student
who not greater than 21 years old

Tuple Calculus
Relational Calculus. Relational calculus is a non-procedural query language. In the non-
procedural query language, the user is concerned with the details of how to obtain the end
results. The relational calculus tells what to do but never explains how to do.
Syntax:

TRC queries are expressed in the following general form:

{t ∣ P(t)} \ { t \ mid P(t)\} { t ∣ P(t) }

• t represents a tuple variable.

• P(t)P(t)P(t) is a formula (predicate) that specifies conditions t must satisfy.

Example:

Let's consider a simple relational schema with a table Students having attributes ID, Name, and
Age. To find all students who are older than 20 years using TRC:

{(ID, Name, Age)∣Students(ID, Name, Age)∧Age>20}

Here:

• (ID, Name, Age)(ID, Name, Age)(ID, Name, Age) specifies the tuple structure we are
interested in.
• Students (ID, Name, Age) \ text {Students}(ID, Name, Age)Students(ID, Name, Age)
indicates that (ID, Name, Age)(ID, Name, Age)(ID, Name, Age) are attributes of the
Students table.
• Age>20Age > 20Age>20 is the condition we impose on the tuples.

SQL FUNDAMENTALS

SQL (Structured Query Language) is a standard programming language designed for managing
relational databases and performing various operations on the data stored in them. Here are
some fundamental concepts and operations in SQL:

1) Database Concepts

• Database: A collection of related data stored in tables.

• Table: A structured representation of data consisting of rows and columns.
• Schema: Describes the structure of the database, including tables, columns, data types,
constraints, etc.

2)Basic SQL Commands

SELECT: Retrieves data from one or more tables

SELECT column1, column2 FROM table_name WHERE condition;

INSERT: Adds new rows of data into a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Modifies existing data in a table.

UPDATE table_name SET column1 = value1 WHERE condition;

DELETE: Removes rows from a table.

3) Querying Data

WHERE Clause: Filters rows based on specified criteria.

SELECT * FROM table_name WHERE column1 = value;

ORDER BY Clause: Sorts the result set based on specified columns.

SELECT * FROM table_name ORDER BY column1 ASC;

GROUP BY Clause: Groups rows sharing a common value into summary rows.

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

JOIN: Combines rows from two or more tables based on a related column between
them

SELECT t1.column1, t2.column2 FROM table1 t1 INNER JOIN table2 t2 ON t1.id =

t2.id;

4. Constraints

➢ Primary Key: Unique identifier for each row in a table.

➢ Foreign Key: Establishes a link between two tables, referencing the primary key of
another table.
➢ Unique Constraint: Ensures that all values in a column are unique.
➢ NOT NULL Constraint: Ensures that a column cannot have a NULL value.

Oracle Built-in Data Types

Char data types

MAX Size – MAX Size – MAX Size –

Data Type Description
Oracle 11g Oracle 12c PL/SQL

2000 bytes. 2000 bytes. 32,767 bytes.

Fixed length
Default and Default and Default and
CHAR(size) character with a
minimum is 1 minimum is minimum is 1
length of size.
byte 1 byte byte
 Fixed length 2000 bytes. 2000 bytes. 32,767 bytes.
national character Default and Default and Default and
NCHAR(size)
with a length of minimum is 1 minimum is minimum is 1
size. byte 1 byte byte

Deprecated and
only used for
VARCHAR
backward
compatibity.

Variable length
character string 4000 bytes. 32,767 bytes.
VARCHAR2(size) with a maximum Minimum is 1 32,767 bytes Minimum is 1
length of size byte byte
bytes

Variable length
national character 4000 bytes. 32,767 bytes.
NVARCHAR2(size) string with a Minimum is 1 32,767 bytes Minimum is 1
maximum length byte byte
of size bytes

Variable length
character string.
LONG Larger than 2 GB 2 GB 32,760 bytes.
VARCHAR2.
Deprecated

Numeric Data Types

MAX Size – Oracle MAX Size – Oracle MAX Size

Data Type Description
11g 12c – PL/SQL

Precision p
Numeric
Precision p ranges Precision p ranges ranges from
data, with a
from 1 to 38, and from 1 to 38, and 1 to 38, and
NUMBER(p,s) precision of
scale ranges from -84 scale ranges from -84 scale
p and scale of
to 127 to 127 ranges from
s.
-84 to 127
 32-bit,
single-
BINARY_FLOA From 1.17549E-38F From 1.17549E-38F
precision n/a
T to 3.40282E+38F to 3.40282E+38F
floating point
number

64-bit, From From

double- 2.22507485850720E 2.22507485850720E
BINARY_DOU
precision -308 to -308 to n/a
BLE
floating point 1.79769313486231E 1.79769313486231E
number +308 +308

True, False,
BOOLEAN n/a n/a n/a
or NULL

From -
2,147,483,
Signed
PLS_INTEGER n/a n/a 647 to
integer.
2,147,483,
647

Signed
integer. From -
Older, 2,147,483,
BINARY_INTE
slower n/a n/a 647 to
GER
version of 2,147,483,
PLS_INTEG 647
ER

Translated to
INTEGER NUMBER(3 n/a n/a
8)

Translated to 1 to 126 binary digits, 1 to 126 binary digits,

FLOAT
NUMBER and up to 22 bytes and up to 22 bytes

Translated to
DECIMAL n/a n/a
NUMBER
Date/Time Data Types

Constraints

Use a constraint to define an integrity constraint—a rule that restricts the values in a
database. Oracle Database lets you create six types of constraints and lets you declare them in
two ways.

The six types of integrity constraint are described briefly here and more fully in Semantics.

A NOT NULL constraint prohibits a database value from being null.

➢ A unique constraint prohibits multiple rows from having the same value in the
same column or combination of columns but allows some values to be null.
➢ A primary key constraint combines a NOT NULL constraint and a unique
constraint in a single declaration. That is, it prohibits multiple rows from having
the same value in the same column or combination of columns and prohibits
values from being null.
➢ A foreign key constraint requires values in one table to match values in
another table.
➢ A check constraint requires a value in the database to comply with a specified
condition.
➢ Column level and table level constraints

Column constraint

Column constraints are constraints attached to a single column. They are used to determine
whether a proposed value for a column is valid or not. Column constraints are evaluated after
the input is validated against basic type requirements (like making sure a value is a whole
number for int columns).
 Column constraints are great for expressing requirements that are limited to a single field. They
attach the constraint condition directly to the column involved. For instance, we could model
the age restriction in a person table by adding a constraint after the column name and data type:

CREATE TABLE person (

...
age INT CHECK (age >= 0),
...
);

This snippet defines a person table with one of the columns being an int called age.
The age must be greater than or equal to zero. Column constraints are easy to understand
because they are added as additional requirements onto the column they affect.

Table constraints

The other type of constraint is called a table constraint. Table constraints can express almost any
restrictions that a column constraint can, but can additionally express restrictions that involve more than
one column. Instead of being attached to a specific column, table constraints are defined as a separate
component of the table and can reference any of the table's columns.

CREATE TABLE person (

...
age INT,
...
CHECK (age >= 0)
);

The same basic syntax is used, but the constraint is listed separately. To take advantage of the
ability for table constraints to introduce compound restrictions, we can use the
logical AND operator to join multiple conditions from different columns.

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy
and reliability of the data in the table. If there is any violation between the constraint and the
data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and
table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

 • NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

Integrity Constraints

You can define integrity constraints to enforce business rules on data in your tables. Business
rules specify conditions and relationships that must always be true, or must always be false.
Because each company defines its own policies about things like salaries, employee numbers,
inventory tracking, and so on, you can specify a different set of rules for each database table.

When an integrity constraint applies to a table, all data in the table must conform to the
corresponding rule. When you issue a SQL statement that modifies data in the table, Oracle
ensures that the new data satisfies the integrity constraint, without the need to do any checking
within your program.

Example of an Integrity Constraint for a Business Rule

To ensure that each employee works for a valid department, first create a rule that all values in
the department table are unique :

ALTER TABLE Dept_tab

ADD PRIMARY KEY (Deptno);

Then, create a rule that every department listed in the employee table must match one of the
values in the department table:

ALTER TABLE Emp_tab

ADD FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno);

When you add a new employee record to the table, Oracle automatically checks that its
department number appears in the department table.

To enforce this rule without integrity constraints, you can use a trigger to query the department
table and test that each new employee's department is valid. But this method is less reliable
than the integrity constrain, because SELECT in Oracle uses "consistent read" and so the query
might miss uncommitted changes from other transactions.

Logical Operators

SQL logical operators are used to test for the truth of the condition. A logical operator like
the Comparison operator returns a boolean value of TRUE, FALSE.
Operator Meaning

AND TRUE if both Boolean expressions are TRUE.

IN TRUE if the operand is equal to one of a list of expressions.

NOT Reverses the value of any other Boolean operator.

OR TRUE if either Boolean expression is TRUE.

LIKE TRUE if the operand matches a pattern.

BETWEEN TRUE if the operand is within a range.

ALL TRUE if all of a set of comparisons are TRUE.

ANY TRUE if any one of a set of comparisons is TRUE.

EXISTS TRUE if a subquery contains any rows.

SOME TRUE if some of a set of comparisons are TRUE.

Oracle function

A function is a subprogram that is used to return a single value. You must declare and define a
function before invoking it. It can be declared and defined at a same time or can be declared
first and defined later in the same block.

CREATE function in oracle

Syntax

1. CREATE [OR REPLACE] FUNCTION function_name

2. [ (parameter [,parameter]) ]
3. RETURN return_datatype
4. IS | AS
5. [declaration_section]
6. BEGIN
7. executable_section
8. [EXCEPTION
 9. exception_section]
10. END [function_name];

Example
1. create or replace function adder(n1 in number, n2 in number)
2. return number
3. is
4. n3 number(8);
5. begin
6. n3 :=n1+n2;
7. return n3;
8. end;
9. /

Now write another program to call the function.

1. DECLARE
2. n3 number(2);
3. BEGIN
4. n3 := adder(11,22);
5. dbms_output.put_line('Addition is: ' || n3);
6. END;
7. /

Output:

Addition is: 33
Statement processed.
0.05 seconds

Range Searching

Between operator

Range operators are those operators which selects data according to particular range. There
are two type of range operator and these are following:-
1)BETWEEN
2)NOT BETWEEN
The SQL BETWEEN condition allows you to easily test if an expression is within a range of
values (inclusive). The values can be text, date, or numbers. It can be used in
a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition
will return the records where the expression is within the range of value1 and value2.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Query:

CREATE TABLE Emp(

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

Country VARCHAR(50),

Age int(2),

Salary int(10)

);

-- Insert some sample data into the Customers table

INSERT INTO Emp (EmpID, Name,Country, Age, Salary)

VALUES (1, 'Shubham', 'India','23','30000'),

(2, 'Aman ', 'Australia','21','45000'),

(3, 'Naveen', 'Sri lanka','24','40000'),

(4, 'Aditya', 'Austria','21','35000'),

(5, 'Nishant', 'Spain','22','25000');

Select * from Emp;

 Output:

Using BETWEEN with numeric values

List all the Employee’s Names who is having salary between 30000 and 45000.
Query

SELECT Name

FROM Emp

WHERE Salary

BETWEEN 30000 AND 45000;

Output

Using NOT operator with BETWEEN

SELECT Name

FROM Emp

WHERE Salary

NOT BETWEEN 30000 AND 45000;

Output:

Pattern Matching

o LIKE clause is used to perform the pattern matching task in SQL.

o A WHERE clause is generally preceded by a LIKE clause in an SQL query.
o LIKE clause searches for a match between the patterns in a query with the pattern in
the values present in an SQL table. If the match is successful, then that particular value
will be retrieved from the SQL table.
o LIKE clause can work with strings and numbers.

The LIKE clause uses the following symbols known as wildcard operators in SQL to perform
this pattern-matching task in SQL.

1. To represent zero, one or more than one character, % (percentage) is used.

2. To represent a single character _ (underscore) is used.

Let us start with the syntax of a LIKE clause:

1. SELECT ColumnName1, ColumnName2 FROM TableName WHERE ColumnNa

me LIKE [Expression];

Expression refers to the pattern which we want to search for in the values of a table. This
expression will include the wildcard operators such as '%' and '_'.

ID Name City Salary

1 Priyanka Bagul Nasik 26000

2 Riya Sharma Mumbai 72000

3 Neha Verma Varanasi 37000

4 Neeta Desai Nasik 39500

5 Priya Wagh Udaipur 60000

Using LIKE clause with % (percentage)
Example 1: Write a query to display employee details in which name starts with 'Pr'.

mysql> SELECT * FROM employee_details WHERE Name LIKE 'Pr%';

We have used the SELECT query with the WHERE clause applied on the Name column
followed by the LIKE clause. We have specified the expression value as 'Pr' in the LIKE
clause, followed by the wildcard operator percent (%). So, according to the query, all
the records that have names starting with the string 'Pr' followed by any other character
will be considered a part of the output.

Output:

ID Name City Salary

1 Priyanka Bagul Nasik 26000

5 Priya Wagh Udaipur 60000

Faculty HOD