Data Base Management Systems

UNIT II

Module 1: Relational Data Model and Language

Relational Data Model Concepts

Codd proposed the Relational data model in 1970. Before 1970 most of the database systems follows one of the two data models. Those are

• Hierarchical Data Model
• Network Data Model
• The relational model represents the database as a collection of relations.
• A relation is thought of as a table of values, each row in the table represents a

collection of related data values.

• A row represents a fact that typically corresponds to a real-world entity or relationship.
• The table name and column names are used to help to interpret the meaning of the values in each row.

Example: Student relationship.

​

Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple and it has all the properties and capabilities required to process data with storage efficiency.

​

A Relation mainly contains two things

• Relation Schema
• Relation Instance

​

Relation Schema

Relation schema specifies the name of the relation and name of each field and domain of each field. Here domain specifies that the set of values that are associated to the given field.

​

A relation schema R, denoted by R(A1, A2, ...,An), is made up of a relation name R and a list of attributes, A1, A2, ..., An. Each attribute Ai is the name of a role played by some domain D in the relation schema R. D is called the domain of Ai and is denoted by dom(Ai).

​

A relation instance (or relation state) r of the relation schema R(A1, A2, ..., An) can be denoted by r(R), is a set of n-tuples r = {t1, t2, ..., tm}.

• Each n-tuple t is an ordered list of n values t =<v1, v2, ..., vn>, where each value vi, 1 ≤ i ≤ n, is an element of dom (Ai)

​

Definition of a relation can be restated more formally using set theory concepts. A relation (or relation state) r(R) is a mathematical relation of degree n on the domains dom(A1), dom(A2), ..., dom(An), which is a subset of the Cartesian product (denoted by ×) of the domains that define R:

r(R) ⊆ (dom(A1) × dom(A2) × ... × dom(An))

​

The degree (or arity) of a relation is the number of attributes in its relation schema.

Ex: Student (sid: string, name:string, login:string, age:integer, gpa:real)

​

Relation Instance

An instance of a relation is set of tuples also called as records, in which each

tuple has a same number of fields as the relation schema.

​

Domains, Attributes, Tuples, and Relations

A domain D is a set of atomic values. A data type or format is also specified for each domain. It is also useful to specify a name for the domain, to help in interpreting its values.

• Degree or arity of a Relation is Number of fields in a relation.
• Cardinality of a relation instance is number of tuples in it.

​

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.

Relation schema

A relation schema describes the relation name table name, attributes, and their names.

Relation key

Each Relation has one or more attributes, known as relation key, which can identify the row in the relation table uniquely.

Attribute domain

Every attribute has some pre-defined value scope, known as attribute domain.

Integrity Constraints

Set of rules or conditions specified on a database schema and restricts the data that can be stored in an instance of the database. They ensures that the data insertion, updating and other processes have to be performed in such way that

data integrity is not affected.

Types of integrity constraints

• Key constraints
• Domain constraints
• Referential integrity constraints
• Entity Integrity Constraints

Domain constraints

​

Domain constraints can be defined as the definition of a valid set of values for an attribute. The data type of domain includes string, character, integer, time, date, currency, etc.

The value of the attribute must be available in the corresponding domain.

​

Entity integrity constraints

• The entity integrity constraint states that primary key value can't be null.
• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.
• A table can contain a null value other than the primary key field.

​

Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.
• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be available in Table 2 or NULL.

​

​

Key constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• An entity set can have multiple keys, but out of which one key will be the primary key.
• A primary key can contain a unique value in the relational table.

​

Relational Algebra

Relational Algebra is procedural query language, which takes Relation as input and generate relation as output. It uses operators to perform queries. An operator can be either unary or binary.

Relational algebra mainly provides theoretical foundation for relational databases and SQL.

Relational algebra is performed recursively on a relation and intermediate results are also considered relations.

​

We can divide the operations in two categories:

1. Basic Operations

2. Derived Operations

Basic/Fundamental Operations:

1. Select (σ)

2. Project (∏)

3. Union (∪)

4. Set Difference (-)

5. Cartesian product (X)

6. Rename (ρ)

Derived Operations

1. Natural Join (⋈)
2. Left, Right, Full outer join (⟕, ⟖, ⟗)
3. Intersection (∩)

​

Select Operator (σ)

Select Operator is denoted by sigma (σ) and it is used to find the tuples (or rows) in a relation (or table) which satisfy the given condition.

​

If you understand little bit of SQL then you can think of it as a where clause in SQL, which is used for the same purpose.

Syntax

σ Condition/Predicate (Relation/Table name)

Example

σ Customer_City="Agra" (CUSTOMER)

​

​

​

​

Project Operator (∏)

Project operator is denoted by ∏ symbol and it is used to select desired columns (or attributes) from a table (or relation).

​

Project operator in relational algebra is similar to the Select statement in SQL.

​

Syntax

​

∏ column_name1, column_name2, ...., column_nameN (table_name)

In this example, we have a table CUSTOMER with three columns, we want to fetch only two columns of the table, which we can do with the help of Project Operator ∏.

​

∏ Customer_Id, Customer_Name (CUSTOMER)

​

Union Operator (∪)

Union operator is denoted by ∪ symbol and it is used to select all the rows (tuples) from two tables (relations).

​

Lets discuss union operator a bit more. Lets say we have two relations R1 and R2 both have same columns and we want to select all the tuples(rows) from these relations then we can apply the union operator on these relations.

​

Note: The rows (tuples) that are present in both the tables will only appear once in the union set. In short you can say that there are no duplicates present after the union operation.

r ∪ s = { t | t ∈ r or t ∈ s}

​

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

• r, and s must have the same number of attributes.
• Attribute domains must be compatible.
• Duplicate tuples are automatically eliminated.

Syntax

table_name1 ∪ table_name2

∏ Student_Name (COURSE) ∪ ∏ Student_Name (STUDENT)

​

​

​

​

​

​

​

​

As you can see there are no duplicate names present in the output even though we had few common names in both the tables, also in the COURSE table we had the duplicate name itself.

​

Set Difference (-)

Set Difference is denoted by – symbol. Lets say we have two relations R1 and R2 and we want to select all those tuples(rows) that are present in Relation R1 but not present in Relation R2, this can be done using Set difference R1 – R2.

Syntax

table_name1 - table_name2

Example

∏ Student_Name (STUDENT) - ∏ Student_Name (COURSE)

​

A query to select those student names that are present in STUDENT table but not present in COURSE table

Cartesian product (X)

Cartesian Product is denoted by X symbol.

Lets say we have two relations R1 and R2 then the cartesian product of these two relations (R1 X R2) would combine each tuple of first relation R1 with the each tuple of second relation R2.

Syntax

R1 X R2

R1

​

​

​

R2

​

​

R1 X R2

Rename (ρ)

Rename (ρ) operation can be used to rename a relation or an attribute of a relation.�

Syntax�ρ(new_relation_name, old_relation_name)

​

Example

​

​

​

​

​

ρ(CUST_NAMES, ∏Customer_Name (CUSTOMER))

​

Derived Operators

Intersection Operator (∩)

Intersection operator is denoted by ∩ symbol and it is used to select common rows (tuples) from two tables (relations).

​

Lets say we have two relations R1 and R2 both have same columns and we want to select all those tuples(rows) that are present in both the relations, then in that case we can apply intersection operation on these two relations R1 ∩ R2.

​

Syntax

table_name1 ∩ table_name2

Example

∏ Student_Name (COURSE) ∩ ∏ Student_Name (STUDENT)

JOIN

An SQL Join is used to combine data from two or more tables, based on a common field between them.

Example

Orders table

​

​

​

​

​

​

Customers table

​

The "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

�SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

​

​

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

​

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

​

Left Outer Join

Returns all records from the left table, and the matched records from the right table

 The result of a left outer join (or simply left join) for tables Employee and Location always contains all records of the "left" table (Employee), even if the join-condition does not find any matching record in the "right" table (Location).

​

select * from employee left outer join on employee.empID = location.empID;

Right outer Join

Returns all records from the right table, and the matched records from the left table.

​

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (Location) will appear in the joined table at least once. If no matching row from the "left" table (Employee) exists, NULL will appear in columns from Employee for those records that have no match in Location.

select * from employee right outer join on employee.empID = location.empID;

​

​

​

Full Outer Join

​

Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.