Introduction to Database System Concepts and Architecture

Data:
• It is a collection of information.
• The facts that can be recorded and which have implicit meaning known as 'data'.
• Example: Customer ----- 1. cname.
2. cno.
3. ccity
Database:
• It is a collection of interrelated data.
• These can be stored in the form of tables.
• A database can be of any size and varying complexity.
• A database may be generated and manipulated manually or it may be computerized.
• Example: Customer database consists the fields as cname, cno, and ccity

Cname Cno Ccity

Database System:
• It is computerized system, whose overall purpose is to maintain the information and to
make that the information is available on demand.

Database Management System (DBMS):

• It is a collection of programs that enables user to create and maintain a database.
• In other words, it is general-purpose software that provides the users with the processes
of defining, constructing and manipulating the database for various applications.

Disadvantages in File Processing

o Data redundancy and inconsistency.
o Difficult in accessing data. Data isolation.
o Data integrity.
o Concurrent access is not possible.
o Security Problems.

Advantages of DBMS:
o Data Independence: DBMSs provide a layer of abstraction between the physical
storage of data and the applications that access the data. This means that changes
to the data structure do not necessarily affect the applications, making maintenance
easier.
o Data Integrity :DBMSs enforce rules and constraints that ensure data accuracy
and consistency across the database. This helps maintain the integrity of data,
reducing errors and inconsistencies.
 o Data security: DBMSs provide robust security features that protect data from
unauthorized access and breaches.
o Data Backup and Recovery: DBMSs have built-in backup and recovery tools,
ensuring that data can be restored in the event of a system failure, accidental
deletion, or corruption.
o Concurrency Control : DBMSs allow multiple users to access and
modify data simultaneously without causing conflicts. They manage
concurrent access through locking mechanisms.

Applications Of Database
o Banking: Manage customer data, process transactions, and detect fraud.
o Education: Handle student records, library management, and online
learning systems. Universities: registration, grades
o Government: Manage citizen services, law enforcement data, and public
infrastructure.
o Manufacturing: Oversee supply chain, quality control, and production
planning.
o Transportation: Optimize fleet management, logistics, and ticketing systems.

People associated with Database system- Database Users

Database users are categorized based up on their interaction with the data
base. These are seven types of data base users in DBMS.

1. Database Administrator (DBA):

• Database Administrator (DBA) is a person/team who defines the schema and also
controls the 3 levels of database.
• The DBA will then create a new account id and password for the user if he/she need
to access the data base.
• DBA is also responsible for providing security to the data base and he allows
only the authorized users to access/modify the data base.
▪ DBA also monitors the recovery and back up and provide technical support.
▪ The DBA has a DBA account in the DBMS which called a system or
super-user account.
▪ DBA repairs damage caused due to hardware and/or software failures.

2. Naive / Parametric End Users:

• Parametric End Users are the unsophisticated who don’t have any DBMS knowledge
but they frequently use the data base applications in their daily life to get the desired
results.
• For examples, Railway’s ticket booking users are naive users. Clerks in any bank is
a naive user because they don’t have any DBMS knowledge but they still use the
database and perform their given task.

3. System Analyst:
• System Analyst is a user who analyses the requirements of parametric end users.
 They check whether all the requirements of end users are satisfied.

4. Sophisticated Users:
• Sophisticated users can be engineers, scientists, business analyst, who are familiar
with the database.
• They can develop their own data base applications according to their requirement.
• They don’t write the program code but they interact the data base by writing SQL
queries directly through the query processor.

5. Data Base Designers:

• Data Base Designers are the users who design the structure of data base which
includes tables, indexes, views, constraints, triggers, stored procedures. He/she
controls what data must be stored and how the data items to be related.
6. Application Programmers:
• Application Program are the back-end programmers who writes the code for the
application programs.
• They are the computer professionals.
• These programs could be written in Programming languages such as Visual Basic,
Developer, C, FORTRAN, COBOL etc.

7. Casual Users / Temporary Users:

• Casual Users are the users who occasionally use/access the data base but each time
when they access the database, they require the new information.
• For example, Middle or higher-level manager.

Characteristics of Database Approach:

The main characteristics of the database approach are the following:

1. Self-describing nature of a database system:

Database system contains not only the database itself but also an entire definition or
description of the database structure and constraints also known as metadata of the
database. This definition is stored within the DBMS catalogue, which contains
information like the structure of every file, the sort and storage format of every data item,
and various constraints/rules on the information.

2. Insulation between programs and data, and data abstraction.

• The characteristic that allows program-data independence and program-operation

independence is known as data abstraction.
• The information model uses logical concepts, like objects, their properties, and their
relationships between them, which will be easier for many users to know than memory
concepts or storage concepts. Hence, the information model hides storage and
implementation details that are not of interest to most database users, so unnecessary
complications are hidden from them.

3. Support of multiple views of the data.

• A database sometimes has many users, each of whom may require a special perspective
or view of the database.
 • A view could also be a subset of the database, or it’s going to contain virtual data that is
derived from the database files but isn’t explicitly stored.

4. Sharing of data and multiuser transaction processing.

• The DBMS must implement concurrency control in the software to make sure that
several users trying to update equivalent data do so in a controlled manner in order
that the results of the updates are correct.
• For instance, when several reservation agents attempt to assign a seat on an airline
flight, the DBMS should make sure that each seat is often accessed by just one user
agent at a single time for an assignment to a passenger.
• A fundamental role of multi-user DBMS software is to make sure that concurrent
transactions operate correctly and efficiently with no inconsistency.

Data Models:
•
It defines the data elements and the relationships between the data elements. Data
Models are used to show how data is stored, connected, accessed and updated in
the database management system.
• Here, we use a set of symbols and text to represent the information so that
members of the organization can communicate and understand it.
Some of the Data Models in DBMS are:
1. Hierarchical Model
2. Network Model
3. Entity-Relationship Model
4. Relational Model
5. Object-Oriented Data Model

1. Hierarchical Model
• Hierarchical Model was the first DBMS
model. This model organizes the data in
the hierarchical tree structure.
• The hierarchy starts from the root which
has root data and then it expands in the
form of a tree adding child node to the
parent node.
• This model easily represents some of the
real-world relationships like food recipes,
sitemap of a website etc.

Features of a Hierarchical Model

• A child node will have only one parent node
• One to many relationship: Hierarchical model is implemented based on one to many
relationship. Based on this any parent node should have more than one child nodes i.e
one parent for many child.

Advantages of Hierarchical Model

• It is very simple and fast to traverse through a tree-like structure.
 • Any change in the parent node is automatically reflected in the child node so, the integrity
of data is maintained.
Disadvantages of Hierarchical Model
• Complex relationships are not supported.
• As it does not support more than one parent of the child node so if we have some complex
relationship where a child node needs to have two parent node then that can't be
represented using this model.
• If a parent node is deleted, then the child node is automatically deleted
2. Network Model
• This model is the same as the hierarchical
model, the only difference is that a record
can have more than one parent. It replaces
the hierarchical tree with a graph.
• The Network Model in DBMS is used to
represent the many-to-many relationship
among the database constraints.
• Example: In the example below we can
see that node student has two parents i.e.
CSE Department and Library. This was
earlier not possible in the
hierarchical model.

Features of a Network Model

1. Ability to Merge more Relationships: In this model, as there are more relationships so
data is more related. This model has the ability to manage one-to-one relationships as
well as many-to- many relationships.
2. Many paths: As there are more relationships so there can be more than one path to the
same record. This makes data access fast and simple.
3. Circular Linked List: The operations on the network model are done with the help of the
circular linked list. The current position is maintained with the help of a program and
this position navigates through the records according to the relationship.

Advantages of Network Model

• The data can be accessed faster as there can be more than one path to reach a particular
node. So the data can be accessed in many ways.
• As there is a parent-child relationship so data integrity is present. Any change in parent
record is reflected in the child record.

Disadvantages of Network Model

• As more and more relationships need to be handled the system might get complex. So, a
user must be having detailed knowledge of the model to work with the model.
• Any change like update, deletion, insertion is very complex.

3. Entity-Relationship Model
• Entity-Relationship Model or simply ER Model is a high-level data model diagram.
• In this model, we represent the real-world problem in the pictorial form to make it
 easy for the stakeholders to understand.
• It is also very easy for the developers to understand the system by just looking at the ER
diagram. We use the ER diagram as a visual tool to represent an ER Model.
• ER diagram has the following three components:

• Entities: Entity is a r e a l -world thing. It c a n be a person, place, or even

a concept. Example: Teachers, Students, Course, Building, Department, etc are some of the
entities of a School Management System.
• Attributes: An entity contains a real-world property called attribute. This is the
characteristics of that attribute. Example: The entity teacher has the property like teacher
id, salary, age, etc.
• Relationship: Relationship tells how two attributes are related. Example: Teacher works
for a department.
Example:

• In the above diagram, the entities are Teacher and Department. The attributes of Teacher
entity are Teacher_Name, Teacher_id, Age, Salary, Mobile_Number. The
attributes of entity Department entity are Dept_id, Dept_name. The two entities are
connected using the relationship. Here, each teacher works for a department.

Advantages of ER Model
• Simple: Conceptually ER Model is very easy to build. If we know the relationship between
the attributes and the entities we can easily build the ER Diagram for the model.
• Effective Communication Tool: This model is used widely by the database designers for
communicating their ideas.
• Easy Conversion to any Model: This model maps well to the relational model and can
be easily converted relational model by converting the ER model to the table. This model
can also be converted to any other model like network model, hierarchical model etc.

Disadvantages of ER Model
• No industry standard for notation: There is no industry standard for developing an ER
model. So one developer might use notations which are not understood by other
 developers.
• Hidden information: Some information might be lost or hidden in the ER model. As it is
a high- level view so there are chances that some details of information might be hidden.

4. Relational Model
• Relational Model is the most widely used model. In this model, the data is maintained in
the form of a two-dimensional table. All the information is stored in the form of row and
columns.
• The basic structure of a relational model is tables. So, the tables are also called
relations in the relational model.
• Example: In this example, we have an Employee table.

Features of Relational Model

• Tuples: Each row in the table is called tuple. A row contains all the information about
any instance of the object. In the above example, each row has all the information about
any specific individual like the first row has information about John.
• Attribute or field: Attributes are the property which defines the table or relation. The
values of the attribute should be from the same domain. In the above example, we have
different attributes of the employee like Salary, Mobile_no, etc.

Advantages of Relational Model

• Simple: This model is more simple as compared to the network and hierarchical model.
• Scalable: This model can be easily scaled as we can add as many rows and columns we
want.
• Structural Independence: We can make changes in database structure without changing
the way to access the data. When we can make changes to the database structure without
affecting the capability to DBMS to access the data we can say that structural
independence has been achieved.

Disadvantages of Relational Model

• Hardware Overheads: For hiding the complexities and making things easier for the user
this model requires more powerful hardware computers and data storage devices.
• Bad Design: As the relational model is very easy to design and use. So the users don't
need to know how the data is stored in order to access it. This ease of design can lead to
the development of a poor database which would slow down if the database grows.
But all these disadvantages are minor as compared to the advantages of the relational
 model. These problems can be avoided with the help of proper implementation and
organization.

5. Object-Oriented Data Model

• In this model, both the data and relationship are present in a single structure known as
an object.
• We can store audio, video, images, etc in the database which was not possible in the
relational model
• In this model, two are more objects are connected through links.
• We use this link to relate one object to other objects. This can be understood by the
example given below.

• In the above example, we have two objects Employee and Department.

• All the data and relationships of each object are contained as a single unit.
• The attributes like Name, Job title of the employee and the methods which will be
performed by that object are stored as a single object.
• The two objects are connected through a common attribute i.e the Department_id
and the communication between these two will be done with the help of this common id.

Database Languages:
o A DBMS has appropriate languages and interfaces to
express database queries and updates.
o Database languages can be used to read, store and
update the data in the database. Types of Database
Language

1. Data Definition Language

o DDL stands for Data Definition Language. It is used
to define database structure or pattern.
o It is used to create schema, tables, indexes, constraints, etc. in the database.
o Using the DDL statements, you can create the skeleton of the database.
o Data definition language is used to store the information of metadata like the number of
 tables and schemas, their names, indexes, columns in each table, constraints, etc.
Here are some tasks that come under DDL:
o Create: It is used to create objects in the database.
o Alter: It is used to alter the structure of the database.
o Drop: It is used to delete objects from the database.
o Truncate: It is used to remove all records from a table.
o Rename: It is used to rename an object.
o Comment: It is used to comment on the data dictionary.
These commands are used to update the database schema that's why they come under Data
definition language.

2. Data Manipulation Language

• DML stands for Data Manipulation Language. It is used for accessing and manipulating
data in a database. It handles user requests.
Here are some tasks that come under DML:
o Select: It is used to retrieve data from a database.
o Insert: It is used to insert data into a table.
o Update: It is used to update existing data within a table.
o Delete: It is used to delete all records from a table.
o Merge: It performs UPSERT operation, i.e., insert or update operations.
o Call: It is used to call a structured query language or a Java subprogram.
o Lock Table: It controls concurrency.

3. Data Control Language

o Used to control access to data within the database.
o DCL commands manage permissions and access rights to the database.

Here are some tasks that come under DCL:

o Grant: It is used to give user access privileges to a database.
o Revoke: It is used to take back permissions from the user.

4. Transaction Control Language

• TCL is used to run the changes made by the DML statement. TCL can be grouped into a
logical transaction.
Here are some tasks that come under TCL:
o Commit: It is used to save the transaction on the database.
Syntax: Commit
o Rollback: It is used to restore the database to original since the last Commit.
Syntax: Rollback

Database Interfaces:
• A database management system (DBMS) interface is a user interface which allows for the
ability to input queries to a database without using the query language itself.

The most typical kinds of DBMS interfaces are as follows –

1. Command-Line Interface (CLI)
o A text-based interface where users interact with the database using SQL commands.
o This interface is typically used by database administrators (DBAs) and developers for direct,
detailed control over database operations.
Example: MySQL CLI, Oracle SQL*Plus

2. Graphical User Interface (GUI)

o A visual interface that allows users to interact with the database using graphical elements
like buttons, menus, and forms.
o GUIs are user-friendly and are often preferred for routine database management tasks.
• Example: phpMyAdmin (for MySQL)
pgAdmin (for PostgreSQL)
• Oracle SQL Developer

3. Web-Based Interface
o A browser-based interface that allows users to interact with the database through
a web application. This type of interface is often used for managing databases
remotely.
Examples:
MongoDB, Amazon RDS Management Console

4. Application Programming Interface (API)

o Provides a set of functions, procedures, or methods that an application can use to
interact with a database programmatically.
o APIs allow developers to integrate database operations within their applications
without directly interacting with the database.

Example: JDBC,ODBC

Classification of DBMS
Database Management Systems (DBMS) can be classified based on several criteria,
including the data model, the number of users, the number of sites, and the type of access
path.

1. Based on Data Models

▪ Hierarchical DBMS: Organizes data in a tree-like structure with parent-child

relationships. Each parent can have multiple children, but each child has only
one parent.
▪ Network DBMS: Similar to hierarchical DBMS, but with more complex
relationships, allowing many-to-many relationships.
▪ Relational DBMS (RDBMS): Organizes data into tables (relations) consisting of
rows and columns.
▪ Object-oriented DBMS (OODBMS): Integrates object-oriented programming
principles with database capabilities.
 2. Based on the Number of Users
▪ Single-user DBMS: Supports one user at a time. Typically used on personal
computers. Example: Microsoft Access.
▪ Multi-user DBMS: Supports multiple users simultaneously. Example: Oracle,
MySQL.
3. Based on the Number of Sites
▪ Centralized DBMS: All data is stored and managed on a single site.
▪ Distributed DBMS (DDBMS): Data is distributed across multiple sites, and the
DBMS manages the distribution. Examples: Google Spanner, Amazon DynamoDB.

E-R Model
Introduction
• The Entity-Relationship (E-R) Model is a conceptual framework used to describe the
structure of a database.
• It defines the data elements and the relationships between them, providing a high-level
view of how data is organized and connected.
• The E-R model is widely used in the design and analysis of databases, especially in the
initial stages of database design.

There are three components in ER model

• Entities: Number of tables you need for your database.
• Attributes: Information such as property, facts you need to describe each table.
• Relationships: How tables are linked together.

Component of ER Diagram
1. Entity:
• An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.
• Consider an organization as an example- manager, product, employee, department etc.
can be taken as an entity.
a. Weak Entity
• An entity that depends on another entity called a weak entity. The weak entity doesn't
contain any key attribute of its own. The weak entity is represented by a double rectangle.

b. Strong Entity

• A strong entity is an entity that is not dependent on any other entity. It has a primary key,
or a table includes a primary key.

Example-
Consider the following ER diagram-

In this ER diagram,

Two strong entity sets “Student” and “Course” are related to each other.
• Student ID and Student name are the attributes of entity set “Student”.
• Student ID is the primary key using which any student can be identified uniquely.
• Course ID and Course name are the attributes of entity set “Course”.
• Course ID is the primary key using which any course can be identified uniquely.
• Double line between Student and relationship set signifies total participation.
• It suggests that each student must be enrolled in at least one course.
• Single line between Course and relationship set signifies partial participation.
• It suggests that there might exist some courses for which no enrolments are made.
 2. Attribute
The attribute is used to describe the property of an entity. Eclipse
is used to represent an attribute.

For example, id, age, contact number, name, etc. can be attributes of a
student.

a. Key Attribute
The key attribute is used to represent the main
characteristics of an entity. It represents a primary key. The key
attribute is represented by an ellipse with the text underlined.

b. Composite Attribute
An attribute that composed of many other
attributes is known as a composite attribute. The
composite attribute is represented by an ellipse, and those
ellipses are connected with an ellipse.

c. Multivalued Attribute
An attribute can have more than one value. These attributes are known as
a multivalued attribute. The double oval is used to represent multivalued
attribute.
For example, a student can have more than one phone number.

d. Derived Attribute
An attribute that can be derived from other attribute is known
as a derived attribute. It can be represented by a dashed ellipse.
For example, A person's age changes over time and can be derived
from another attribute like Date of birth.

3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is
used to represent the relationship.
Types of relationship are as follows:
a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known
as one to one relationship.
For example, A female can marry to one male, and a male can marry to one female

b. One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an entity on
the right associates with the relationship then this is known as a one-to-many relationship.
For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.

b. Many-to-one relationship

When more than one instance of the entity on the left, and only one instance of an entity on
the right associates with the relationship then it is known as a many-to-one relationship.
For example, Student enrolls for only one course, but a course can have many students.

c. Many-to-many relationship
When multiple instances of one entity can be associated with multiple instances of another
entity.
For example, Employee can assign by many projects and project can have many employees.

Relationship Set-
A relationship set is a set of relationships of same type.
Example-
Set representation of above ER diagram is-

Degree of a Relationship Set-

The number of entity sets that participate in a relationship set is termed as the degree
of that relationship set. Thus,

Degree of a relationship set = Number of entity sets participating in a relationship set

Types of Relationship Sets-

On the basis of degree of a relationship set, a relationship set can be classified into the
following types-

1. Unary Relationship Set-

 Unary relationship set is a relationship set where only one
entity set participates in a relationship set.

Example- One person is married to only one person

2. Binary Relationship Set-

Binary relationship set is a relationship set where two entity sets participate in a
relationship set.
Example-
Student is enrolled in a Course

3. Ternary Relationship Set-

Ternary relationship set is a relationship set where three entity sets participate in a
relationship set.
Example-

4. N-ary Relationship Set-

N-ary relationship set is a relationship set where ‘n’ entity sets participate in a relationship
set.

Roles, and Structural Constraints

Roles refer to the function that an entity plays in a relationship. When entities participate in a
relationship, they take on roles that define how they interact within that relationship.

Cardinality Ratios of relationships:

• Cardinality Ratio describe the numerical relationship between instances of entities in a

relationship.

• These ratios define how many instances of one entity can or must be associated with
 instances of another entity.

Types of Cardinality
There can be 4 types of cardinality –
1. One-to-one (1:1) –
In this type of cardinality mapping, an entity in E1 is connected to at most one entity in E2.

2. One-to-many (1: N) –
In this type of cardinality mapping, an entity in E1 is associated with any number of entities
in E2.

3. Many-to-one (N:1) –
In this type of cardinality mapping, an entity in A is connected to at most one entity in B.

4. Many-to-many (N: N) –
In this type of cardinality mapping, an entity in A is associated with any number of entities in
B, and an entity in B is associated with any number of entities in A.

Participation Constraints:
Participation Constraints refer to rules that dictate how entities in a relationship can or
must participate. It can be either total or partial.

When each entity in an entity set participates in a relation, it is called Total Participation.
However, when all entities in the given entity set do not participate in a relation, it is called
Partial Participation.
Structural Constraints:
• Cardinality Ratios and Participation Constraints taken together are called Structural
Constraints.
• The name constraints refer to the fact that such limitations must be imposed on the
data, for the DBMS system to be consistent with the requirements.

• The Structural constraints are represented by Min-Max notation.

• This is a pair of numbers (m, n) that appear on the connecting line between the entities
and their relationships.
• The minimum number of times an entity can appear in a relation is represented by
m whereas, the maximum time it is available is denoted by n.
• If m is 0 it signifies that the entity is participating in the relation partially, whereas, if m
is either greater than or equal to 1, it denotes total participation of the entity.

A simple ER Diagram:

• In the following diagram we have two entities Student and College and their relationship.
• The relationship between Student and College is many to one as a college can have many
students however a student cannot study in multiple colleges at the same time. Student
entity has attributes such as Stu_Id, Stu_Name & Stu_Addr and College entity has
attributes such as Col_ID & Col_Name.
• Here are the geometric shapes and their meaning in an E-R Diagram.
E R Diagram for Hospital management:
ER Diagram for College Database
References:

1. [Link]
2. [Link]
3. [Link]
types
4. [Link]
5. [Link]
6. [Link]
7. [Link]
8. [Link]
9. [Link]
info/215/erd/#:~:text=Data%20modeling%20is%20a%20technique,system%20
using%20diagrams%20and%20symbols.&text=The%20highest%20level%20of%
20abstraction,data%20requirements %20for%20a%20database.
10. [Link]
11. [Link]
12. [Link]
model/
13. [Link]
14. [Link]
types/
15. [Link]
16. [Link]
17. [Link]
18. [Link]
19. [Link]
Conventions,-and-Design- Issues_11434/
20. [Link]
conventions-and-design-issues
 Relational Data Model & Relational Algebra
2.1 Relational model in DBMS

• In relational model, the data and relationships are represented by collection of inter-
related tables.
• Each table is a group of columns and rows, where column represents attribute of
an entity and rows represents records.
• The table name and column names are helpful to interpret the meaning of values
in each row.
• In the formal relational model terminology, a row is called a tuple, a column header
is called an attribute, and the table is called a relation.
• The data type describing the types of values that can appear in each column is
represented by a domain of possible values.

2.1.1 Structure of Relational model

The structure of the relational model is based on a few fundamental concepts, which
include:
1. Relation (Table)
• A relation is a table with rows and columns. In the context of a relational
database, it represents a set of entities or relationships.
• Each row in the table is called a tuple, representing a single entity or record.
• Each column in the table is called an attribute, representing a data field or
property of the entity.
2. Attributes (Columns)
• Attributes are the properties or characteristics of a relation. They define the type
of data that can be stored in a table.
• Each attribute has a specific domain, which is the set of permissible values that
the attribute can have.
3. Domain
• A domain is the set of permissible values for an attribute. For example, the
domain for an attribute "age" might be all integers between 0 and 120.
• Each attribute in a relational model is associated with a domain that defines the
possible values it can hold.
4. Tuple (Row)
• A tuple is a single row in a table, representing a single record or instance of the
relation.
• A tuple is a finite ordered list of elements, where each element is a value
corresponding to one of the attributes.
5. Degree
• The degree of a relation is the number of attributes (columns) in a table. It
indicates the number of properties the relation captures.
6. Cardinality
• Cardinality refers to the number of tuples (rows) in a relation. It indicates the
number of records stored in the table.
Relation schema- A relational schema is the design for the table.
It includes none of the actual data, but is like a blueprint or design for the table, so
describes what columns are on the table and the data types. It may show basic table
constraints ( e.g. if a column can be null) but not how it relates to other tables.

The schema includes:

• Relation Name (R): The name of the relation or table.
• Attributes (A1, A2, ..., An): A list of attributes (or columns) that define the type of
data that will be stored in the relation.

(A1,A2,...,An), it means that the relation RRR has attributes A1,A2,...,AnA1, A2, ...,
AnA1,A2,...,An

Null value: A field with a NULL value is a field with no value. Primary key can’t be a null
value.

2.2 Characteristics of Relations

Relations in a relational database have several key characteristics, which are

fundamental to their structure and behaviour. Here are the primary characteristics of
relations:

1. Relation Name

• Each relation (table) has a unique name within the database schema.

2. Tuples

• A relation consists of a set of tuples (rows). Each tuple represents a single entry in
the relation and contains a set of attribute values.

3. Attributes

• Each relation is defined by a fixed set of attributes (columns), where each attribute
has a name and a domain (the set of allowable values).

4. Attribute Domain

• Each attribute in a relation has a domain, which is the set of permissible values
for that attribute. For example, an attribute "Age" might have a domain of
integers.
5. Atomicity

• Each attribute value in a tuple is atomic, meaning it cannot be subdivided. There

are no multi-valued or composite attributes in a relational database.

6. NULL Values

• Attributes can contain NULL values, which indicate missing or unknown

information. However, attributes that are part of a primary key cannot be NULL.

7. Integrity Constraints

• Relations enforce integrity constraints, such as primary keys, foreign keys, and
unique constraints, to maintain the consistency and accuracy of the data.

2.3 Relational Model Constraints

• In a relational model, constraints are rules that enforce the integrity and validity of the data
in the database.
• These constraints ensure that the database remains accurate, consistent, and adheres to
the business rules.
• Here are the key types of constraints in the relational model:

Domain constraints
• Each table has certain set of columns and each column allows a same type of data, based
on its data type. The column does not accept values of any other data type.
• They ensure that the data entered into a database is valid and adheres to the predefined
rules or domains.

Example:
Integer Example:

• Attribute: Age
• Domain: Integer values between 0 and 120.
• Constraint: The Age attribute must hold an integer value
within this range.

String Example:
• Attribute: CountryCode
• Domain: A set of predefined country codes such as "US", "CA", "GB".
• Constraint: The CountryCode attribute must hold one of the specified country codes.

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

Key constraints

o Key Constraints ensure that each tuple (row) within a relation (table) can be uniquely
identified, and they play a crucial role in maintaining data integrity.
Here’s an overview of the key types of key constraints:

1. Primary Key Constraint

• A primary key is a unique identifier for each tuple in a relation. The primary key constraint
ensures that no two rows can have the same value for this key and that it cannot contain
NULL values.
• Characteristics:
o Uniqueness: Each value of the primary key must be unique across all rows in the table.
o Non-nullability: The primary key cannot have NULL values; every row must have a
valid primary key value.
• Example: In a students table, StudentID might be the primary key. No two students can share
the same StudentID, and every student must have a StudentID.
• Notation: Often denoted by underlining the attribute(s) in the schema, e.g.,
Students(StudentID, Name, Birthdate) where StudentID is underlined.

2. Unique Key Constraint

• A unique key constraint ensures that all values in a particular attribute or set of attributes
are unique across the relation, similar to a primary key, but it can accept NULL values.
• Characteristics:
o Uniqueness: The combination of values in the unique key columns must be unique
across all rows.
o Allowing NULLs: Unlike the primary key, a unique key can contain NULL values, but
only one NULL is allowed if the unique key is a single attribute.
• Example: In an Employees table, Email might be a unique key. No two employees can share
the same email address, but it's possible for the Email to be NULL if an employee doesn't
have one.
• Notation: Often denoted with the keyword UNIQUE in the schema definition.

3. Entity Integrity Constraints

• Entity integrity constraints state that primary key can never contain null value because
primary key is used to determine individual rows in a relation uniquely, if primary key
contains null value then we cannot identify those rows.
• This is achieved through the use of Primary Keys.
• Without entity integrity, it would be possible for multiple rows to represent the same entity,
leading to inconsistencies and data anomalies.

4. Referential integrity constraints

Referential Integrity Constraints are rules in a relational database that ensure the consistency and
accuracy of relationships between tables.

Foreign Key Constraint

• A foreign key is an attribute (or a set of attributes) in one relation that refers to the primary
key in another relation. The foreign key constraint enforces referential integrity between two
tables.
• Characteristics:
o Referential Integrity: The foreign key values must either match a primary key value
in the referenced table or be NULL.
o Cascade Actions: On update or delete of the referenced row, certain actions can be
specified (like CASCADE, SET NULL, etc.) to maintain integrity.
• Example: In an Orders table, CustomerID might be a foreign key referencing CustomerID in
the Customers table. This ensures that each order is linked to a valid customer.
• Notation: The foreign key is often denoted by specifying the referenced table and primary
key, e.g., FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID).
 Relational Algebra

Introduction
• Relational Algebra is a procedural query language.
• Relational algebra mainly provides a theoretical foundation for relational
databases and SQL.
• The main purpose of using Relational Algebra is to define operators that transform
one or more input relations into an output relation.
• These operators accept relations as input and produce relations as output

Basic Relational Algebra Operations:

Relational Algebra divided in various groups

Unary Relational Operations

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

Relational Algebra Operations From Set Theory

● UNION (υ)
● INTERSECTION ( ),
● DIFFERENCE (-)

Binary Relational Operations

● JOIN
● DIVISION
● CARTESIAN PRODUCT ( x )

SELECT (σ): The SELECT operation is used for selecting a subset of the tuples
according to a given selection condition. Sigma(σ) Symbol denotes it.

σ condition (Relation)

Example:

σage > 17 (Student)

This will fetch the tuples(rows) from table Student, for which age will be greater than 17.

σage > 17 and gender = 'Male' (Student)

This will return tuples(rows) from table Student with information of male students, of age
more than 17.

BRANCH_NAME LOAN_NO AMOUNT

Sahil L-17 1000
Samantha L-23 2000
Ananya L-15 1500
David L-14 1500
Zafar L-13 500
Sachin L-11 900
Deepika L-16 1300

Input:
σ BRANCH_NAME="Sachin" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Sachin L-11 900

Project Operation (Π):

• Project operation is used to project only a certain set of attributes of a relation. In

simple words, If you want to see only the names all of the students in the Student
table, then you can use Project Operation.

• It will only project or show the columns or attributes asked for, and will also
remove duplicate data from the columns.

Syntax of Project Operator (Π)

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

Example:
ΠName, Age(Student)

Above statement will show us only the Name and Age columns for all the rows of data in
Student table.

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:
Π NAME, CITY (CUSTOMER)

Output:
NAME CITY
Jones Harrison
Smith Rye
Hays Harrison
Curry Rye
Johnson Brooklyn
Brooks Brooklyn

Rename Operation (ρ):

This operation is used to rename the output relation for any query operation which returns result
like Select, Project etc. Or to simply rename a relation(table)

Syntax: ρ (RelationNew, RelationOld)

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.

ρ(STUDENT1, STUDENT)

Cartesian Product (X):

This is used to combine data from two different relations(tables) into one and fetch data from the
combined relation.

Syntax: A X B

For example, if we want to find the information for Regular Class and Extra Class which are
conducted during morning, then, we can use the following operation:

σtime = 'morning' (RegularClass X ExtraClass)

For the above query to work, both RegularClass and ExtraClass should have the attribute time.
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:
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
Join in DBMS:
• A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.
 • Join in DBMS is a binary operation which allows you to combine join product and
selection in one single statement.
• The goal of creating a join condition is that it helps you to combine the data from
two or more DBMS tables.
• The tables in DBMS are associated using the primary key and foreign keys.

Types of SQL JOIN

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN

Table name: EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

PROJECT

PROJECT_NO EMP_ID DEPARTMENT

101 1 Testing
102 2 Development
103 3 Designing
104 4 Development

1. INNER JOIN
In SQL, INNER JOIN selects records that have matching values
in both tables as long as the condition is satisfied.
It returns the combination of all rows from both the tables
where the condition satisfies.

Syntax
SELECT table1.column1, table1.column2
FROM table1 INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Query
SELECT EMPLOYEE.EMP_NAME, [Link]
FROM EMPLOYEE INNER JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

2. LEFT JOIN
The SQL left join returns all the values from left table and the
matching values from the right table. If there is no matching join
value, it will return NULL.

Syntax
SELECT table1.column1, table1.column2
FROM table1 LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Query
SELECT EMPLOYEE.EMP_NAME, [Link]
FROM EMPLOYEE LEFT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL

3. RIGHT JOIN
In SQL, RIGHT JOIN returns all the values from the values
from the rows of right table and the matched values from the
left table. If there is no matching in both tables, it will return
NULL.

Syntax
SELECT table1.column1, table1.column2
FROM table1 RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Query
SELECT EMPLOYEE.EMP_NAME, [Link]
FROM EMPLOYEE RIGHT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

4. FULL JOIN
In SQL, FULL JOIN is the result of a combination of both left and right
outer join. Join tables have all the records from both tables. It puts NULL
on the place of matches not found.

Syntax
SELECT table1.column1, table1.column2
FROM table1 FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Query
SELECT EMPLOYEE.EMP_NAME, [Link]
FROM EMPLOYEE
FULL JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL

Division operation ( ÷ ): The Division operator results in columns values in one table for which
there are other matching column values corresponding to every row in another table.
Relational Algebra Operations From Set Theory

Union operation (υ): UNION is symbolized by ∪ symbol. It includes all tuples that are
in tables A or in B. It also eliminates duplicate tuples. 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.

Retrieve students name either participant in arts or sports:

Π NAME (ARTS) ∪ Π NAME (SPORTS)

● INTERSECTION ( ∩):

A ∩ B of two sets A and B is the set that contains all elements of A that also belong
to B (or equivalently, all elements of B that also belong to A ), but no other elements.
Retrieve students name those who participant in both arts and sports:

Π NAME (ARTS) ∩ Π NAME (SPORTS)

● SET DIFFERENCE (-) OR MINUS

The result of set difference operation is tuples, which are present in one relation
but are not in the second relation.

Retrieve students name those who participant only in arts and not in sports:

Π NAME (ARTS) - Π NAME (SPORTS)

ARTS - SPORTS

Aggregate functions in SQL

SQL aggregation function is used to perform the calculations on multiple rows of a single
column of a table. It returns a single value.

o It is also used to summarize the data.

Aggregate Functions
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()

1. COUNT FUNCTION
• COUNT function is used to Count the number of rows in a database table. It can
work on both numeric and non-numeric data types.

• COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table. COUNT(*) considers duplicate and Null.

Example: COUNT()
SELECT COUNT(*) FROM PRODUCT_MAST;

Example: COUNT with WHERE

SELECT COUNT(*) FROM PRODUCT_MAST; WHERE RATE>=20;

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST;

2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on numeric
fields only.

Syntax
SUM()
or
SUM( [ALL|DISTINCT] expression )

Example: SUM()
SELECT SUM(COST) FROM PRODUCT_MAST;

Example: SUM() with WHERE :

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;

3. AVG function
The AVG function is used to calculate the average value of the numeric type. AVG function returns
the average of all non-Null values.

Syntax: AVG()
Ex: SELECT Avg(RATE) FROM PRODUCT_MAST

4. MAX Function
MAX function is used to find the maximum value of a certain column. This function determines the
largest value of all selected values of a column.

Syntax: MAX()
Example: SELECT MAX(RATE) FROM PRODUCT_MAST;

5. MIN Function
MIN function is used to find the minimum value of a certain column. This function
determines the smallest value of all selected values of a column.

Syntax: MIN() )

Example: SELECT MIN(RATE) FROM PRODUCT_MAST;

GROUP BY Statement
The GROUP BY Statement in SQL is used to arrange identical data into groups with the
help of some functions. i.e. if a particular column has the same values in different rows
then it will arrange these rows in a group.

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN,
SUM, AVG) to group the
result-set by one or more
columns.

GROUP BY Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s)

Group By single column: Group By single column means, to place all the rows with
same value of only that particular column in one group.
HAVING Clause:
We can use HAVING clause to place conditions to decide which group will be the part of
final result-set. Also we cannot use the aggregate functions like SUM(), COUNT() etc. with
WHERE clause. So we have to use HAVING clause if we want to use any of these
functions in the conditions.

Syntax:
SELECT column1, function_name(column2) FROM table_name
WHERE condition
GROUP BY column1, column2 HAVING condition
ORDER BY column1, column2;

function_name: Name of the function used for example, SUM() , AVG().

table_name: Name of the table.
condition: Condition used.

Example:

SELECT NAME, SUM(sal) FROM Emp

GROUP BY name
HAVING SUM(sal)>50000;

Nested Queries
 In SQL, a nested query involves a query that is placed within another
query.
 Output of the inner query is used by the outer query.
 A nested query has two SELECT statements: one for the inner query and
another for the outer query.
Types of Nested Queries in SQL

Subqueries can be either correlated or non-correlated

Non-correlated (or Independent) Nested Queries

Non-correlated (or Independent) subqueries are executed independently of the outer

query. Their results are passed to the outer query. Operators Used in Independent
Nested Queries are IN,NOT IN, ALL,ANY.

Correlated Nested Queries

Correlated subqueries are executed once for each row of the outer query. They use values
from the outer query to return results. Operators Used in Co-related Nested Queries are
EXISTS ,NOT EXISTS,ANY,ALL.

Syntax
SELECT column1, column2, ...
FROM table1
WHERE column1 IN ( SELECT column1
FROM table2
WHERE condition );

Consider the following sample table to execute nested queries on these.

Table: employees table

emp_id emp_name dept_id

1 John 1

2 Mary 2

3 Bob 1

4 Alice 3

5 Tom 1

Table: departments table

 dept_id dept_name

1 Sales

2 Marketing

3 Finance

Table: sales table

sale_id emp_id sale_amt

1 1 1000

2 2 2000

3 3 3000

4 1 4000

5 5 5000

6 3 6000

7 2 7000

Example: Find the names of all employees in the Sales department.

SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id
FROM departments
WHERE dept_name = 'Sales');

OUTPUT:
emp_name

John

Bob

Tom

Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and columns.

oTo create the view, we can select the fields from one or more tables present in the
database.
o A view can either have specific rows based on certain condition or all the rows of a
table.

Sample table: Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18

1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a
single table or multiple tables.

Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

2. Creating View from a single table

Query:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.
SELECT * FROM DetailsView;

Output:
NAME ADDRESS
Stephan Delhi
Kathrin Noida
David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the
SELECT statement.

In the given example, a view is created named MarksView from two tables Student_Detail
and Student_Marks.

Query:
CREATE VIEW MarksView AS
SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView: SELECT * FROM MarksView;

NAME ADDRESS MARKS

Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90

4. Deleting View
A view can be deleted using the Drop View statement.

Syntax
1. DROP VIEW view_name;

Example:
If we want to delete the View MarksView, we can do this as:
1. DROP VIEW MarksView;

Uses of a View :

A good database should contain views due to the given reasons:

1. Restricting data access –
Views provide an additional level of table security by restricting access to a
predetermined set of rows and columns of a table.
2. Hiding data complexity –
A view can hide the complexity that exists in a multiple table join.
3. Simplify commands for the user –
Views allows the user to select information from multiple tables without requiring the
users to actually know how to perform a join.
4. Store complex queries –
Views can be used to store complex queries.
5. Rename Columns –
Views can also be used to rename the columns without affecting the base tables provided
the number of columns in view must match the number of columns specified in select
statement. Thus, renaming helps to hide the names of the columns of the base tables.
6. Multiple view facility –
Different views can be created on the same table for different users.
Data Normalization & Query Processing Transaction Management
3.1 Introduction

• Data normalization is the process of reorganizing data within a database so that

users can utilize it for further queries and analysis.
• It is the process of developing clean data. This includes eliminating redundant and
unstructured data and making the data appear similar across all records and fields.

3.1.1 Anomalies in relational database design

Anomalies in the relational model refer to inconsistencies or errors that can arise when
working with relational databases, specifically in the context of data insertion, deletion,
and modification.

These anomalies can be categorized into three types:

• Insertion Anomalies
• Deletion Anomalies
• Update Anomalies.

Example 1:

Worker_id Worker_name Worker_dept Worker_address

65 Ramesh ECT001 Jaipur

65 Ramesh ECT002 Jaipur

73 Amit ECT002 Delhi

76 Vikas ECT501 Pune

76 Vikas ECT502 Pune

79 Rajesh ECT669 Mumbai

In the above table, we have four columns which describe the details about the workers like
their name, address, department and their id. The above table is not normalized, and there
is definitely a chance of anomalies present in the table.

Insertion Anomaly
 • Occurs when new data cannot be added to the database because other required
data is missing.
• For example, if in the above table, we create a new row of a worker, and if it is not
allocated to any department then we cannot insert it in the table so, it will create
an insertion anomaly.

Deletion Anomaly

• If we delete some rows from the table and if any other information or data which is
required is also deleted from the database, this is called the deletion anomaly in the
database.
• For example, in the above table, if we want to delete the department number
ECT669 then the details of Rajesh will also be deleted since Rajesh's details are
dependent on the row of ECT669. So, there will be deletion anomalies in the table.

Update Anomaly

• When we update some rows in the table, and if it leads to the inconsistency of the
table then this anomaly occurs. This type of anomaly is known as an updation
anomaly.
• In the above table, if we want to update the address of Ramesh then we will have to
update all the rows where Ramesh is present. If during the update we miss any
single row, then there will be two addresses of Ramesh, which will lead to
inconsistent and wrong databases.

3.2 Decomposition

• Decomposition refers to the division of tables into multiple tables to produce

consistency in the data.
• It is performed in a database management system when we need to ensure
consistency and remove anomalies and duplicate data present in the database.
• When we perform decomposition in DBMS, we must try to ensure that no
information or data is lost.

Types of Decomposition
There are two types of Decomposition:
• Lossless Decomposition
• Lossy Decomposition

Lossless Decomposition

• It ensures that when a relation (table) is decomposed into two or more smaller
relations, the original relation can be perfectly reconstructed by joining these
smaller relations without any loss of information.
• It is used to remove the redundant data from the database while retaining the
useful information. The lossless decomposition tries to ensure following things:

• While regaining the original relation, no information should be lost.

• If we perform join operation on the sub-divided relations, we must get the
original relation.
Example:
• There is a relation called R(A, B, C)

A B C

55 16 27

48 52 89

Now we decompose this relation into two sub relations R1 and R2

R1(A, B) R2(B, C)

A B B C

55 16 16 27

48 52 52 89

After performing the Join operation, we get the same original relation
A B C

55 16 27

48 52 89

Lossy Decomposition
As the name suggests, lossy decomposition means when we perform join operation on
the sub-relations it doesn’t result to the same relation which was decomposed. After the
join operation, we always found some extraneous tuples. These extra tuples generate
difficulty for the user to identify the original tuples.

Example: We have a relation R(A, B, C)

Now , we decompose it into sub-relations R1 and R2

This relation after natural join operation produces results with data inconsistencies.

Functional dependencies

Functional dependency is a concept that specifies the relationship between two sets of
attributes where one attribute determines the value of another attribute.

It is denoted as X → Y, where the attribute set on the left side of the arrow, X is
called Determinant, and Y is called the Dependent

Example:
Consider a table of student records:

StudentID Name Major

101 Alice CS

102 Bob Math

103 Carol CS

In this table:

• StudentID → Name is a functional dependency because the StudentID uniquely

determines the Name. No two students can have the same StudentID.
 • StudentID → Major is also a functional dependency because a unique StudentID
determines the Major.

However, Name → Major is not necessarily a functional dependency because two students
with the same name could be enrolled in different majors.

Armstrong’s axioms/properties of functional dependencies:

1. Reflexivity: If Y is a subset of X, then X→Y holds by reflexivity rule

Example, {roll_no, name} → name is valid.

2. Augmentation: If X → Y is a valid dependency, then XZ → YZ is also valid by the

augmentation rule.
Example, {roll_no, name} → dept_building is valid, hence {roll_no, name,
dept_name} → {dept_building, dept_name} is also valid.

3. Transitivity: If X → Y and Y → Z are both valid dependencies, then X→Z is also

valid by the Transitivity rule.
Example, roll_no → dept_name & dept_name → dept_building, then roll_no →
dept_building is also valid.

TYPES OF FUNCTIONAL DEPENDENCY:

The different types of functional dependency are described below:
1. TRIVIAL FUNCTIONAL DEPENDENCY:

In Trivial Functional Dependency, a dependent is always a subset of the determinant.

i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency

Example: roll_no name age

Here, {roll_no, name} → name is a trivial functional

dependency, since the dependent name is a subset of 42 abc 17
determinant set {roll_no, name}.

2. NON-TRIVIAL FUNCTIONAL DEPENDENCY:

43 pqr 18
In Non-trivial functional dependency, the dependent is
strictly not a subset of the determinant. i.e. If X → Y and Y is
not a subset of X, then it is called Non-trivial functional 44 xyz 18
dependency.

EX: Here, roll_no → name is a non-trivial functional dependency, since the

dependent name is not a subset of determinant roll_no.

3. TRANSITIVE FUNCTIONAL DEPENDENCY:

In transitive functional dependency, dependent is indirectly dependent on determinant.

i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive
functional dependency.
Ex: Here roll_no->name and name->age, then roll_no->age is a valid functional
dependency.

4. Full Functional Dependency

A full functional dependency means that an attribute (or set of attributes) depends entirely
on the whole primary key.
Ex: {StudentID,CourseID}→Grade (the grade depends on both the student and the
course).

Normalization
➢ Database Normalization is a technique of organizing the data in the database.
➢ Normalization is a systematic approach of decomposing tables to eliminate data
redundancy(repetition) and undesirable characteristics like Insertion, Update and
Deletion Anomalies.
➢ It is a multi-step process that puts data into tabular form, removing duplicated data
from the relation tables.
➢ It divides larger tables to smaller tables and links them using relationships.
➢ Normalization is used for mainly two purposes,
1. Eliminating redundant(useless) data.
2. Ensuring data dependencies make sense i.e data is logically stored.

Normalization rules are divided into the following normal forms:

1. First Normal Form

2. Second Normal Form
3. Third Normal Form
4. Boyce Codd Normal Form

1. First Normal Form (1NF)

 Ensures that all data is stored in atomic (indivisible) values.
 There are no repeating groups or arrays.
 Example: If a table stores multiple phone numbers for a person in one column, you
would split this into multiple rows or create a separate table for phone numbers.
 The first normal form helps to eliminate duplicate data and simplify queries.

2. Second Normal Form (2NF)

Requires the table to be in 1NF.
Eliminates partial dependencies, meaning non-key attributes should depend on
the whole primary key, not just part of it.
This applies when the table has a composite primary key (a key with more than
one column).

Ex: Table Before 2NF (In 1NF but Not in 2NF):

 Composite Primary Key: In this case, StudentID and CourseID together make up
the primary key, because each student can take multiple courses.

 Partial Dependency:StudentName depends only on StudentID, not on the full

primary key (StudentID, CourseID).

 CourseName depends only on CourseID, not on the full primary key.

 Table After 2NF:

To resolve the partial dependencies, we split the table into two smaller tables:

[Link] Normal Form (3NF):

 3NF is a level of database normalization designed to ensure that data is
stored efficiently and with minimal redundancy.
 A table is in 3NF if it meets the following criteria:
1. It must be in 1NF & 2 NF
2. It has no transitive dependencies- A transitive dependency occurs when a
non-key attribute depends on another non-key attribute rather than directly
on the primary key.
3. Each attribute is only dependent on the primary key of its respective table.
Ex:

 In this table:

• EmployeeID is the primary key.

• DepartmentID is a foreign key.

• DepartmentName is dependent on DepartmentID and not on EmployeeID,

creating a transitive dependency.

4. Boyce-Codd Normal Form (BCNF)

BCNF is an advanced version of the Third Normal Form (3NF) that deals with certain types
of anomalies not addressed by 3NF.

It is stricter than 3NF in its handling of functional dependencies.

A table is in BCNF if it meets the following criteria:

1. It is in 3NF, meaning:
 o The table is already in 2NF (i.e., no partial dependencies).

o There are no transitive dependencies (i.e., non-prime attributes depend only

on the primary key).

2. Every determinant is a candidate key:

o A determinant is an attribute or a set of attributes on which some other

attribute is fully functionally dependent.

Example of a BCNF Violation:

Consider the following table for a college course scheduling system:

Dependencies:

1. Course → TimeSlot: Each course is scheduled at a specific time.

2. Instructor → Course: Each instructor teaches a specific course.

Problem:

Neither Instructor nor Course alone is a candidate key. The combination of Instructor
and Course is the primary key, but we have a functional dependency (Instructor → Course)
that violates BCNF. In other words, knowing the instructor tells us the course they are
teaching, which leads to redundancy.

How Does BCNF Fix This?

To make the table follow BCNF, we need to break it into two tables to eliminate the problem:
 Query Processing and Transaction Management

Introduction Transaction Processing

 It is the execution of a sequence of operations on a database to perform a

specific task, typically as part of an application.
 These tasks often involve reading or writing data. In database systems, a
transaction is a unit of work that must be completed in its entirety or not
at all.
 Transaction processing is crucial for ensuring data integrity, especially in
systems where multiple users or processes interact with the database
simultaneously.
 Examples include banking systems, order processing in e-commerce, and
airline reservation systems.

Single & Multi user systems

Single User Systems:

 Single-user and multi-user systems refer to how many users or

applications can interact with the database simultaneously.
 A single-user DBMS supports only one user at a time, meaning only one
application or user can access and manipulate the database.
 Simpler in terms of design, locking mechanisms, and resource
management.
 Ex: Microsoft Access or SQLite in a local environment.
Multi user systems

 A multi-user DBMS allows multiple users or applications to access the database

concurrently, often through a network or centralized server.
 Utilizes locking, transaction management, and scheduling to prevent conflicts
between users.
 Requires advanced concurrency control mechanisms to ensure data consistency
and integrity
 Ex: Oracle, MySQL, PostgreSQL, or Microsoft SQL Server, used by businesses,
financial institutions

Transactions: read & write operations.

 In a DBMS, transactions consist of a sequence of operations that read and/or write

data to the database.
 These operations are essential for ensuring data consistency and integrity,
 especially when multiple users or processes interact with the database
concurrently.

1. Read Operation:

 A read operation in a transaction retrieves data from the database without

modifying it.
 It ensures that the requested data is available to the transaction but does not alter
the data in any way.
 Typically involves SQL queries like SELECT.
 Ex: SELECT name, age FROM students WHERE student_id = 1;

2. Write Operation:

 A write operation modifies data in the database, changing its current state.
 Writes are more critical because they can affect the integrity of the data.
 Typically involves SQL commands like INSERT, UPDATE, or DELETE.
 INSERT INTO students (name, age) VALUES ('John Doe', 20);
 UPDATE students SET age = 21 WHERE student_id = 1;
 DELETE FROM students WHERE student_id = 1;

Need of concurrency control:

 Concurrency control is crucial in a Database Management System (DBMS) to

manage simultaneous operations from multiple users or processes, ensuring that
data remains consistent and accurate.
 Key reasons why concurrency control is needed in a DBMS.

1. Prevent Data Inconsistency

2. Maintain Data Integrity
 3. Ensure Isolation of Transactions
4. Avoid Anomalies in Multi-User Environments

1. The Lost Update Problem

 The Lost Update problem occurs when two or more transactions read the same
data and then try to update it.

 Since each transaction bases its update on the old data, the final value only reflects
one of the transactions' updates, causing the other transaction’s changes to be
"lost."

Ex Scenario: Two employees, A and B, access a shared system to update the stock
quantity of a product in a database. The current stock is 100.

1. Transaction 1 (T1 - Employee A):

1. Reads the stock value as 100.
2. Plans to update the stock to 90 after a sale of 10 units.
2. Transaction 2 (T2 - Employee B):
1. Reads the same stock value (100).
2. Plans to update the stock to 95 after a sale of 5 units.
3. If both transactions proceed without isolation, they might both read the old stock
value (100) and update it independently:
1. T1 writes 90 (100 - 10).
2. T2 writes 95 (100 - 5).
4. The final value of the stock will be 95, losing the update from T1. In reality, the
stock should be 85 after both transactions have been considered.

Concurrency Control Solution:

• Locking Mechanisms: To avoid this issue, DBMS can use write locks to ensure
that once T1 starts updating the stock, T2 must wait until T1 completes its
transaction.

• Optimistic Concurrency Control: Versions of data can be used to compare old

and new values before committing any changes.

2. The Dirty Read Problem

 A Dirty Read problem occurs when a transaction reads data that has been modified
by another transaction but not yet committed.

 If the modifying transaction is rolled back, the reading transaction ends up using
incorrect or inconsistent data.

Scenario: Two transactions, T1 and T2, are interacting with the same database record.
Assume T1 modifies a record but hasn’t committed the change yet, and T2 reads the
uncommitted data.

1. Transaction 1 (T1):
 1. Updates a customer's balance from 10,000 to 5,000 due to a withdrawal.
2. Has not yet committed the transaction (the change is still tentative).
2. Transaction 2 (T2):
1. Reads the customer’s balance, which shows 5,000 (the uncommitted change
from T1).
• If T1 encounters an error and rolls back the transaction, restoring the balance to
10,000, T2 has already processed the incorrect balance of 5,000.
 This leads to inconsistencies because T2 made decisions based on invalid data.

Concurrency Control Solution:

• Isolation Levels: To prevent dirty reads, DBMS uses isolation levels. For instance,
at the Read Committed isolation level, T2 will not be able to read uncommitted
changes from T1.

• Strict Two-Phase Locking (2PL): This ensures that no transaction can read data
that is in the process of being updated by another transaction until the update is
committed.

Types of failures

 In a Database Management System (DBMS), transactions can fail for various

reasons, leading to different types of failures. Here are the main types:

• Transaction Failure: Caused by logical errors or system errors like

deadlocks.

• System Failure: Results from crashes due to hardware or software issues,

potentially leaving transactions incomplete.

• Media Failure: Occurs when storage media is damaged, leading to data loss
or corruption.

• Concurrency Control Failure: Arises from interference between

simultaneous transactions, causing inconsistent data.

• Network Failure: Happens in distributed systems due to communication

issues like message loss or timeouts.

• Human Error: Involves mistakes made by users, such as accidental data

deletion.

• Application Failure: Results from bugs in the application code or

unexpected runtime conditions.

Desirable properties (ACID properties) of Transactions.

ACID is an acronym that stands for atomicity, consistency, isolation, and durability (ACID).
Together.

ACID properties ensure that a set of database operations (grouped together in a

transaction) leave the database in a valid state even in the event of unexpected errors.
Atomicity

Atomicity guarantees that all of the commands that make up a transaction are treated as
a single unit and either succeed or fail together.

This is important in the event of a system failure or power outage, in that if a transaction
wasn't completely processed, it will be discarded and the database maintains its data
integrity.

Consistency

Consistency guarantees that changes made within a transaction are populated across
the database system (e.g., nodes) and in alignment with DBMS constraints.

Isolation

Each transaction is isolated from the other transactions to prevent data conflicts. This
also helps database operations in relation to managing multiple entries and multi-level
transactions.

For example, if two users are trying to modify the same data (or even the same
transaction), the DBMS uses a mechanism called a lock manager to suspend other users
until the changes being made by the first user are complete.

Durability

Durability guarantees that once the transaction completes and changes are written to
the database, they are persisted. This ensures that data within the system will persist
even in the case of system failures like crashes or power outages. The concept of
durability is a key element in data reliability.

References:

[Link]