SESSION INTRODUCTION

• Data is a collection of a distinct small unit of information. It can be

used in a variety of forms like text, numbers, media, bytes, etc.

• Data is raw, unprocessed, unorganized facts that are seemingly

random and do not yet carry any significance or meaning.
Information refers to data that has been organized, interpreted, and
contextualized by a human or machine so that it possess relevance
and purpose.

• Fields and records are two basic components of a database, which

is an organized collection of information, or data. The term "fields"
refers to columns, or vertical categories of data; the term "records"
refers to rows, or horizontal groupings of unique field data.

• A file is group of related records

 SESSION DESCRIPTION

Database
A database is an organized collection of data, so that it
can be easily accessed and managed.

It is also used to organize the data in the form of a table,

schema, views, and reports, etc.

The main purpose of the database is to operate a large

amount of information by storing, retrieving, and
managing data.
 DBMS
• A database management system(DBMS) is collection of interrelated
data and a set of programs to access those data. The collection of
date usually referred to as database, contains information relevant
to an enterprise. The primary goal of a DBMS is to provide a way to
store and retrieve database information that is both convenient
and efficient.

• DBMS provides an interface to perform various operations like

database creation, storing data in it, updating data, creating a table
in the database and a lot more. It provides protection and security
to the database. In the case of multiple users, it also maintains data
consistency.

• Database management system is a software which is used to

manage the database. For example: Oracle,MySql, etc are a very
popular commercial database which is used in different
applications.
 Characteristics of DBMS

• It uses a digital repository established on a server to store

and manage the information.
• It can provide a clear and logical view of the process that
manipulates data.
• DBMS contains automatic backup and recovery procedures.
• It contains ACID properties which maintain data in a
healthy state in case of failure.
• It can reduce the complex relationship between data.
• It is used to support manipulation and processing of data.
• It is used to provide security of data.
• It can view the database from different viewpoints
according to the requirements of the user.
 Advantages of DBMS
• Controls database redundancy: It can control data redundancy
because it stores all the data in one single database file and that
recorded data is placed in the database.
• Data sharing: In DBMS, the authorized users of an organization can
share the data among multiple users.
• Easily Maintenance: It can be easily maintainable due to the
centralized nature of the database system.
• Reduce time: It reduces development time and maintenance need.
• Backup: It provides backup and recovery subsystems which create
automatic backup of data from hardware and software failures and
restores the data if required.
• multiple user interface: It provides different types of user
interfaces like graphical user interfaces, application program
interfaces
 RDBMS
RDBMS stands for Relational Database Management Systems..
• All modern database management systems like SQL, MS SQL Server, IBM
DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.
• It is called Relational Data Base Management System (RDBMS) because it
is based on relational model introduced by E.F. Codd.
• Relational database is most commonly used database. It contains number
of tables and each table has its own primary key.
• Due to a collection of organized set of tables, data can be accessed easily
in RDBMS.
Brief History of RDBMS
• During 1970 to 1972, E.F. Codd published a paper to propose the use of
relational database model.
• RDBMS is originally based on that E.F. Codd's relational model invention.
• For a system to be called an RDBMS, it must use its relational capabilities only
to manage the database.

• All information is represented as values in tables (relations).

• Every piece of data is accessible through a combination of table name,

primary key, and column name.

• Nulls (representing missing or unknown data) must be handled consistently,

independent of data type.

• Metadata (data about the database) must also be stored in tables and
accessible using SQL.

• The system must support at least one language (like SQL) for data definition,
manipulation, integrity, and authorization.
• All theoretically updatable views must be updatable by the system.

• The system must support set-level (not just row-by-row) operations for
insert, update, and delete.

• Changes in storage structures or devices should not affect how users

access data.

• Integrity rules (like primary keys, foreign keys, constraints) must be stored
in the catalog, not in the application.

• Users should not be able to tell whether the database is distributed across
multiple locations.

• Low-level access methods (like record-level operations) must not bypass

security or integrity rules.
 Difference between file system and
DBMS
File System DBMS
• [Link] system is a software that manages and • 1-DBMS is a software for managing the
organizes the files in a storage medium database.
within a computer. • 2-In DBMS there is no redundant data.
• [Link] data can be present in a file • 3-It provides backup and recovery of data
system. even if it is lost.
• [Link] doesn’t provide backup and recovery of • 4-Efficient query processing is there in
data if it is lost. DBMS.
• [Link] is no efficient query processing in file • 5-There is more data consistency because of
system. the process of normalization.
• [Link] is less data consistency in file system. • 6-It has more complexity in handling as
• [Link] is less complex as compared to DBMS. compared to file system.
• [Link] systems provide less security in • 7-DBMS has more security mechanisms as
comparison to DBMS. compared to file system.
• [Link] is less expensive than DBMS. • 8-It has a comparatively higher cost than a
• Example: NTFS(New Technology File System), file system.
EXT(Extended File System).
•
 DBMS Architecture
• The DBMS design depends upon its architecture.
The basic client/server architecture is used to
deal with a large number of PCs, web servers,
database servers and other components that are
connected with networks.
• The client/server architecture consists of many
PCs and a workstation which are connected via
the network.
• DBMS architecture depends upon how users are
connected to the database to get their request
done.
 1-Tier Architecture

• In this architecture, the database is directly available to

the user. It means the user can directly sit on the DBMS
and uses it.

• Any changes done here will directly be done on the

database itself. It doesn't provide a handy tool for end
users.

• The 1-Tier architecture is used for development of the

local application, where programmers can directly
communicate with the database for the quick response.
 2-Tier Architecture

• The 2-Tier architecture is same as basic client-

server. In the two-tier architecture,
applications on the client end can directly
communicate with the database at the server
side.
• The user interfaces and application programs
are run on the client-side.
 3-Tier architecture
• The 3-Tier architecture contains another layer between
the client and server. In this architecture, client can't
directly communicate with the server.
• The application on the client-end interacts with an
application server which further communicates with
the database system.
• End user has no idea about the existence of the
database beyond the application server. The database
also has no idea about any other user beyond the
application.
• The 3-Tier architecture is used in case of large web
application.
 Data Model
• 1) Relational Data Model: This type of model designs the data in
the form of rows and columns within a table. Thus, a relational
model uses tables for representing data and in-between
relationships. Tables are also called relations.
• 2) Entity-Relationship Data Model: An ER model is the logical
representation of data as objects and relationships among them.
These objects are known as entities, and relationship is an
association among these entities.
• 3) Object-based Data Model: An extension of the ER model with
notions of functions, encapsulation, and object identity, as well.
This model supports a rich type system that includes structured and
collection types(Eg object ,attribute ,method and class)

• 4) Semistructured Data Model:This type of data model is different

from the other three data models. The Extensible Markup
Language, also known as XML, is widely used for representing the
semistructured data.
 Data Abstraction(Three level
Architecture)
• The three schema architecture is also used to
separate the user applications and physical
database.
• The three schema architecture contains three-
levels. It breaks the database down into three
different categories.
Internal Level

• The internal level has an internal schema which describes the physical storage
structure of the database.
• It uses the physical data model. It is used to define that how the data will be
stored in a block.
• The physical level is used to describe complex low-level data structures in
detail.
Conceptual level:
• Conceptual level is also known as logical level.
• The conceptual level describes what data are to be stored in the database and
also describes what relationship exists among those data.
• Programmers and database administrators work at this level.
External level:
An external schema is also known as view schema.
• Each view schema describes the database part that a particular user group is
interested and hides the remaining database from that user group.
• The view schema describes the end user interaction with database systems.
 Data model Schema and Instance

• The data which is stored in the database at a

particular moment of time is called an
instance of the database.
• The overall design of a database is called
schema.
• A database schema is the skeleton structure of
the database. It represents the logical view of
the entire database.
 Data Independence

Data independence can be explained using the

three-schema architecture.
1. Logical Data Independence
• Logical data independence refers characteristic of
being able to change the conceptual schema
without having to change the external schema.
• Logical data independence is used to separate the
external level from the conceptual view.
• If we do any changes in the conceptual view of
the data, then the user view of the data would
not be affected.
Physical Data Independence
• Physical data independence can be defined as
the capacity to change the internal schema
without having to change the conceptual
schema.
• If we do any changes in the storage size of the
database system server, then the Conceptual
structure of the database will not be affected.
• Physical data independence is used to
separate conceptual levels from the internal
levels.
 Database Languages
• DDL stands for Data Definition Language. It is used to
define database structure or pattern.
• It is used to create schema, tables, indexes, constraints, etc.
in the database.
• Using the DDL statements, you can create the skeleton of
the database.
• Here are some tasks that come under DDL:
• Create: It is used to create objects in the database.
• Alter: It is used to alter the structure of the database.
• Drop: It is used to delete objects from the database.
• Truncate: It is used to remove all records from a table.
• Rename: It is used to rename an object.
• Comment: It is used to comment on the data dictionary.
 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:
• Insert: It is used to insert data into a table.
• Update: It is used to update existing data within a
table.
• Delete: It is used to delete all records from a table.
• Call: It is used to call a structured query language or a
Java subprogram.
• Lock Table: It controls concurrency.
 Data Control Language

Data Control Language

DCL stands for Data Control Language. It is used
to retrieve the stored or saved data.
Here are some tasks that come under DCL:
• Grant: It is used to give user access privileges
to a database.
• Revoke: It is used to take back permissions
from the user.
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:
• Commit: It is used to save the transaction on the
database.
• Rollback: It is used to restore the database to original
since the last Commit.
 ER model
• ER model stands for an Entity-Relationship
model. It is a high-level data model. This
model is used to define the data elements and
relationship for a specified system.
• Every thing in the real world is entity and each
entity has its own attribute.
 Keys
• A key in DBMS is an attribute (or set of attributes) that is used to uniquely
identify a record (tuple) in a table.
• Keys also help establish relationships between tables and ensure data
integrity.

Types of Keys in DBMS:

Super Key
Candidate Key
Primary Key
Alternate Key
Foreign Key
Composite Key
Unique Key
Super Key
A set of one or more attributes that can uniquely identify a tuple
in a relation.
Example: In Student(RollNo, Name, Email):{RollNo}, {RollNo,
Name}, {RollNo, Email} are all super keys.
Candidate Key
The minimal super key (no extra attributes).
Example: In Student(RollNo, Email, Name):
{RollNo}, {Email} are candidate keys (since both uniquely identify
a student).
{RollNo, Email} is not minimal → ❌ not a candidate key.
Primary Key
One chosen candidate key to uniquely identify records in a table.
Example: RollNo can be selected as the primary key of Student.
Alternate Key
Candidate keys not chosen as the primary key.
Example: If RollNo is the primary key, then Email becomes
an alternate key.
Foreign Key
An attribute in one table that refers to the primary key of
another table.
Used to establish relationships between tables.
Example:
Student(RollNo, Name, DeptID)
Department(DeptID, DeptName)
Here, DeptID in Student is a foreign key referencing
DeptID in Department.
Composite Key
A key formed by combining two or more attributes to
uniquely identify a record.
Example: Enrollment(StudentID, CourseID, Semester)
Here, (StudentID, CourseID) together form a composite key.

Unique Key
Similar to Primary Key but:
Allows only one NULL value.
A table can have multiple unique keys but only one primary
key.
Example: In Employee(EmpID, AadharNo, Email):
EmpID = Primary Key
AadharNo and Email = Unique Keys
 Why ER Diagram
• An Entity-Relationship (ER) diagram is created to visually
represent the structure of a database in a clear and organized
way.
• It helps to design and understand how different entities
(tables or objects) within a system are related to each other.
 Here's why it's important:
• 1. Database Design Blueprint
• ER diagrams act as a blueprint for designing a database, helping
developers plan how the database should be structured.

• 2. Simplifies Complex Systems

• Large databases can have numerous entities and relationships, and the
diagram simplifies complex structures by providing a high-level overview.

• 3. Communication Tool
• It helps various stakeholders (e.g., developers, database administrators,
business analysts) communicate their ideas and ensure everyone
understands the design and relationships in the system.

• 4. Ensures Data Integrity

• By showing relationships and constraints between entities, it ensures that
data integrity rules are followed (e.g., foreign key constraints).
• 5. Planning Queries
• The ER diagram helps in planning SQL queries by
clearly showing how data is interconnected, which can
help optimize query performance.
• 6. Visual Representation
• It makes it easier to visualize and make adjustments to
the relationships and entities before actually
implementing the database, avoiding costly changes
later on.
• 7. Error Identification
• Potential design issues, such as redundant or missing
data, can be identified early in the process.
Symbols Used in ER Model
Entity:
• An entity may be any object, class, person or
place.
• Consider an organization as an example-
manager, product, employee, department etc.
can be taken as an entity.
• 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.
• Attribute
The attribute is used to describe the property of
an entity. Eclipse is used to represent an
attribute.
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.
• One-to-One Relationship
• One-to-many relationship
• Many-to-one relationship
• Many-to-many relationship
 Mapping Constraints

• One to one (1:1)

• One to many (1:M)
• Many to one (M:1)
• Many to many (M:M)
Total Participation of Entity Set
ER diagram for Weak entity set
A university registrar’s office maintains data about the following
entities: 1. courses, including number, title, credits, syllabus, and
prerequisites; 2. course offerings, including course number, year,
semester, section number, instructor(s), timings, and classroom;
3. students, including student-id, name, and program; 4. Instructors,
including identi-cation number, name, department, and title.
Further, the enrollment of students in courses and grades awarded to
students in each course they are enrolled for must be appropriately
modeled. Construct an E-R diagram for the registrars office. Document
all assumptions that you make about the mapping constraints.
 Extended ER Model in DBMS
The Extended Entity–Relationship (EER) Model is an
enhancement of the basic ER model used in Database
Management Systems (DBMS).

It provides additional concepts to capture more complex data and

relationships that cannot be represented easily with a simple ER
model.
 Main Extensions
(a) Specialization
•Divides an entity into subclasses based on distinct features.
•Example: Employee → Engineer, Manager, Clerk.

(b) Generalization
•Combines similar entities into a superclass.
•Example: Car, Bike, Bus → generalized as Vehicle.

(c) Category (Union Type)

•A subclass that can represent entities from different super classes.
•Example: Owner could be a Person or a Company.

(d) Aggregation
•Treats a relationship as an entity so that it can participate in other relationships.
•Example: Relationship Works_On(Employee, Project) can be treated as an entity related to
Department.

(e) Inheritance
•Subclasses inherit attributes and relationships of their superclass.
•Example: Faculty inherits Name, DOB from Person.
Generalization
• Generalization is the process of extracting common
properties from a set of entities and creating a
generalized entity from it. It is a bottom-up approach in
which two or more entities can be generalized to a
higher-level entity if they have some attributes in
common.

• Specialization

In specialization, an entity is divided into sub-entities

based on its characteristics. It is a top-down approach
where the higher-level entity is specialized into two or
more lower-level entities.
Aggregation
An ER diagram is not capable of representing the
relationship between an entity and a
relationship which may be required in some
scenarios. In those cases, a relationship with its
corresponding entities is aggregated into a
higher-level entity. Aggregation is an abstraction
through which we can represent relationships as
higher-level entity sets.
• Construct an ER diagram for car insurance
company that has a set of customers each of
whom owns one or more cars. Each car has
associated with it zero to any number of
recorded accidents.
• Design an E-R diagram for keeping track of the
exploits of your favorite sports team. You
should store the matches played, the scores in
each match, the players in each match and
individual player statistics for each match.
Summary statistics should be modeled as
derived attributes.
Assignment:
Construct an ER diagram for a hospital with a
set of patients and a set of doctors. Associate
with each patient a log of the various tests and
examinations conducted.
Overall Database Structure
 Components of a Database System

1. Query Processor:
• It interprets the requests (queries) received from end user via an
application program into instructions. It also executes the user
request which is received from the DML compiler.
Query Processor contains the following components –
• DML Compiler: It processes the DML statements into low level
instruction (machine language), so that they can be executed.
• DDL Interpreter: It processes the DDL statements into a set of table
containing meta data (data about data).
• Embedded DML Pre-compiler: It processes DML statements
embedded in an application program into procedural calls.
• Query Optimizer: It executes the instruction generated by DML
Compiler.
2. Storage Manager:
• Storage Manager is a program that provides an interface between the data stored
in the database and the queries received. It is also known as Database Control
System. It maintains the consistency and integrity of the database by applying the
constraints and executing the DCL statements. It is responsible for updating,
storing, deleting, and retrieving data in the database.
It contains the following components –
• Authorization Manager: It ensures role-based access control, i.e,. checks whether
the particular person is privileged to perform the requested operation or not.

• Integrity Manager: It checks the integrity constraints when the database is

modified.

• Transaction Manager: It controls concurrent access by performing the operations

in a scheduled way that it receives the transaction. Thus, it ensures that the
database remains in the consistent state before and after the execution of a
transaction.

• File Manager: It manages the file space and the data structure used to represent
information in the database.

• Buffer Manager: It is responsible for cache memory and the transfer of data
between the secondary storage and main memory.
• 3. Disk Storage:
• It contains the following components:
• Data Files: It stores the data.
• Data Dictionary: It contains the information
about the structure of any database object. It
is the repository of information that governs
the metadata.
• Indices: It provides faster retrieval of data
item.
 References
The Content of the PPT is taken from different online/offline sources.

Reference Books:
• Korth, Silbertz, Sudarshan,” Database Concepts”, McGraw Hill
• Date C J, “An Introduction to Database Systems”, Addision Wesley
• Elmasri, Navathe, “ Fundamentals of Database Systems”, Addision Wesle
Sites and Web links:
1. [Link]
2. Database Management System - Course ([Link])
3- [Link]
4- etc.