Database Management Systems (DBMS)

CS304PC

Unit-1
Introduction to Database
Management Systems
P Outline
Looping
• Introduction of DBMS
• Applications of DBMS
• Advantages of DBMS
• Three levels ANSI SPARC database system
• Data Abstraction in DBMS
• Mappings and data independence
• Database users and DBA
• Database system architecture
Section - 1
What is Database Management System (DBMS)?
} Data - Fact that can be recorded or stored
Ê e.g. Person Name, Age, Gender and Weight etc.
} Database - Collection of logically related data
Ê e.g. Books Database in Library, Student Database in University etc.
} Management - Manipulation, Searching and Security of data
Ê e.g. Viewing result in GTU website, Searching exam papers in GTU website etc.
} System - Programs or tools used to manage database
Ê e.g. SQL Server Studio Express, Oracle etc.
} DBMS - A Database Management System is a software for creating and managing databases.
} Database Management System (DBMS) is a software designed to define, manipulate, retrieve
and manage data in a database.
Ê e.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc.

Prof. Venkatesh B (DBMS) w Unit 1 4

Section - 2
Applications of DBMS
} DBMS is a computerized record-keeping system.
} DBMS is required where ever data need to be stored.
Ê E-Commerce (Flikart, Amazon, Shopclues, eBay etc...)
Ê Online Television Streaming (Hotstar, Amazon Prime etc...)
Ê Social Media (WhatsApp, Facebook, Twitter, LinkedIn etc...)
Ê Banking & Insurance
Ê Airline & Railway
Ê Universities and Colleges/Schools
Ê Library Management System
Ê Human Resource Department
Ê Hospitals and Medical Stores
Ê Government Organizations

Exercise Write down any five applications of DBMS other than above.

Prof. Venkatesh B (DBMS) w Unit 1 6

} To understand the need for a DBMS, let us consider a motivating scenario:
Ê A company has a large collection (say, 500 GB) of data on employees, departments, products, sales, etc.,
Ê This data is accessed concurrently by several employees.
Ê Questions about the data must be answered quickly, changes made to the data by different users must be
applied consistently, and
Ê Access to certain parts of the data (e.g., salaries) must be restricted.
} We can try to manage the data by storing it in operating system files. This approach has many
drawbacks, including the following
Ê We probably do not have 500 GB of main memory to hold all the data.
Ê Even if we have 500 GB of main memory, on computer systems with 32-bit addressing, we cannot refer
directly to more than about 4 GB of data.
Ê We have to write special programs to answer each question a user may want to ask about the data.
Ê We must protect the data from inconsistent changes made by different users accessing the data
concurrently.
Ê We must ensure that data is restored to a consistent state if the system crashes while changes are being
made.
Ê Operating systems provide only a password mechanism for security.
Prof. Venkatesh B (DBMS) w Unit 1 8
} A DBMS is a piece of software designed to make the preceding tasks easier.
} By storing data in a DBMS rather than as a collection of operating system files,
} we can use the DBMS's features to manage the data in a robust and efficient manner.
} As the volume of data and the number of users grow hundreds of gigabytes of data and
thousands of users are common in current corporate databases.
} DBMS support becomes indispensable.

Prof. Venkatesh B (DBMS) w Unit 1 9

Reduce data redundancy (duplication)

Computer Civil

Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject

Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS

Database management system

can remove such data Same data is stored at
redundancy by storing data four different places.
centrally.

Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject

Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS

Electrical Mechanical

Prof. Venkatesh B (DBMS) w Unit 1 11

Remove data inconsistency

Computer Civil

Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject

Prof. Ajay Shah Rajkot 1234
6789 PPS Prof. Ajay Shah Rajkot 1234 PPS

Same data having

Database management different state (values)
system can keep data in
consistent state.
Mobile no is changed

Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject

Prof. Ajay Shah Rajkot 1234
6789 PPS Prof. Ajay Shah Rajkot 1234 PPS

Electrical Mechanical

Prof. Venkatesh B (DBMS) w Unit 1 12

Data isolation
} Data are scattered in various files.
File - 1
} Files may be in different formats.
Emp_Name Address Mobile Subject
} Difficult to retrieve the appropriate data. Prof. Ajay Shah Rajkot 1234 PPS

DBMS allow us to access (retrieve) appropriate data easily. File - 2

Emp_Name Post Salary Load
Prof. Ajay Shah Lecturer 50,000 15
Data isolation is a property that determines when and how
changes made by one operation become visible to other
concurrent users and systems. File - 3
This issue occurs in a concurrency situation. Emp_Name Teaching Knowledge Rating
Prof. Ajay Shah Good Excellent 9

Prof. Venkatesh B (DBMS) w Unit 1 13

Guaranteed atomicity
} Atomicity: Either transaction execute 0% or 100%.

Sum of both account

before transfer is 3000

Person A Person B
Account A Transfer 500 Account B
Bal : 2000 Bal : 1000
Step 1 : Debit 500 from Account A
Step 2 : Credit 500 into Account B
Transaction Sum of both
is failed Sum of both account account is 2500
after transfer is 3000 so inconsistent

Prof. Venkatesh B (DBMS) w Unit 1 14

Allow to implement integrity constraints

Emp_Name Address Mobile_No Subject

Prof. Ajay Shah Rajkot 9876543210 PPS

Should contain exact 10 digits

Student_Name Branch Backlog SPI

Nirav Patel Rajkot 0 8.5

Should be between 0 to 10

DBMS allows us to implement such business rules in our database..

Prof. Venkatesh B (DBMS) w Unit 1 15

Sharing of data among multiple users

Want to access
Computer Civil

Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject

Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS

Want to access Database management system

allows more than one user to
access same data simultaneously.

Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject

Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS

Electrical Mechanical

Prof. Venkatesh B (DBMS) w Unit 1 16

Restricting unauthorized access to data

File - 1
Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234 PPS
Wants to
access Faculty
File - 2 of other
Emp_Name Post Salary Load college
Prof. Ajay Shah Lecturer 50,000 15
Wants to
access
BVRIT
File - 3 Faculty
Emp_Name Teaching Knowledge Rating
Prof. Ajay Shah Good Excellent 9

DBMS prevents unauthorized user to access data.

Prof. Venkatesh B (DBMS) w Unit 1 17

Providing backup and recovery services

Provides facilities to backup and restore the database in case of failure.

Prof. Venkatesh B (DBMS) w Unit 1 18

Advantages of DBMS (Summary)
} Reduce data redundancy (duplication)
Ê Avoids unnecessary duplication of data by storing data centrally.
} Remove data inconsistency
Ê By eliminating redundancy, data inconsistency can be removed.
} Data isolation
Ê A user can easily retrieve proper data as per his/her requirement.
} Guaranteed atomicity
Ê Either transaction executes 0% or 100%.

Prof. Venkatesh B (DBMS) w Unit 1 19

Advantages of DBMS (Summary)
} Allow implementing integrity constraints
Ê Business rules can be implemented such as do not allow to store amount less than Rs. 0 in balance.
} Sharing of data among multiple users
Ê More than one users can access same data at the same time.
} Restricting unauthorized access to data
Ê A user can only access data which is authorized to him/her.
} Providing backup and recovery services
Ê Can take a regular auto or manual backup and use it to restore the database if it corrupts.

Prof. Venkatesh B (DBMS) w Unit 1 20

What is a Database Models?
} A database model is a type of data model that defines the logical structure of a database.
} It determine how data can be stored, accessed and updated in a database management
system.
} The most popular example of a database model is the relational model, which uses a table-
based format.

Prof. Venkatesh B (DBMS) w Unit 1 22

Type of Database Models

Hierarchical Model

Network Model

Entity-relationship Model

Relational Model

Object-oriented database Model

Prof. Venkatesh B (DBMS) w Unit 1 23
Hierarchical Model
} The hierarchical model organizes data into a tree-like structure, where each record has a
single parent or root.
Department

Student Professor
} The hierarchy starts from the Root data, and expands like a tree, adding child nodes to the
parent nodes.
} In hierarchical model, data is organized into tree-like structure with one-to-many relationship
between two different types of data, for example, one department can have many professors
and many students.

Prof. Venkatesh B (DBMS) w Unit 1 24

Network Model
} This is an extension of the hierarchical model, allowing many-to-many relationships in a tree-
like structure that allows multiple parents.

B C

D E F

Prof. Venkatesh B (DBMS) w Unit 1 25

Entity-relationship Model
} In this database model, relationships are created by dividing object of interest into entity and
its characteristics into attributes.

Attributes
RollNo Name BookNo Name
Relationship

Student Issue Book

Branch Sem Entities Author Price

Prof. Venkatesh B (DBMS) w Unit 1 26

Relational Model
} In this model, data is organized in two-dimensional tables and the relationship is maintained
by storing a common attribute.

Rno Student_Name Age SubID Subject_Name Teacher

101 Raj Patel 20 1 DBMS Doshi
102 Meet Shah 21 2 DS Vyash
Foreign Key Foreign Key

ResID Rno SubID Marks

1 101 1 80
2 101 2 85
3 102 1 75
4 102 2 80

Prof. Venkatesh B (DBMS) w Unit 1 27

Object-oriented database Model
} This data model is another method of representing real world objects.
} It considers each object in the world as objects and isolates it from each other.
} It groups its related functionalities together and allows inheriting its functionality to other
related sub-groups.

Prof. Venkatesh B (DBMS) w Unit 1 28

Data Model
} Introduction to Data Models
Ê Definition: A data model is a conceptual framework for organizing and defining the structure of data, the
relationships among data, and the constraints on the data in a database.
Ê Purpose: Data models provide a way to describe the data, data relationships, data semantics, and data
constraints. They serve as a blueprint for constructing databases.
} Types of data models
Ê Hierarchical Data Model
Ê Network Data Model
Ê Relational Data Model
Ê Object-Oriented Data Model
Ê Entity-Relationship Model
Ê Document Data Model
Ê Key-Value Data Model
Ê Column-Family Data Model
Ê Graph Data Model

Prof. Venkatesh B (DBMS) w Unit 1 30

Types of data model (cont..)
} Hierarchical Data Model
Ê Structure: Organizes data in a tree-like structure. Each record has a single parent and can have multiple
children.
Ê Key Concepts: Parent-child relationships, tree structure.
Ê Advantages: Simple to understand, fast access to hierarchical data.
Ê Disadvantages: Limited flexibility, difficulty in managing complex relationships.
Ê Use Cases: Organizational charts, file systems.
} Network Data Model
Ê Structure: Similar to the hierarchical model but allows more complex relationships with multiple parent
records.
Ê Key Concepts: Sets, records, and links.
Ê Advantages: More flexible than the hierarchical model, supports many-to-many relationships.
Ê Disadvantages: Complex to design and maintain.
Ê Use Cases: Telecommunications, transportation networks.

Prof. Venkatesh B (DBMS) w Unit 1 31

Types of data model
} Relational Data Model
Ê Structure: Data is organized in tables (relations) consisting of rows (tuples) and columns (attributes).
Ê Key Concepts: Tables, primary keys, foreign keys, normalization.
Ê Advantages: Flexible, easy to use, supports SQL for data manipulation.
Ê Disadvantages: Performance can be an issue with very large datasets.
Ê Use Cases: Business applications, financial systems.
} Object-Oriented Data Model
Ê Structure: Combines object-oriented programming principles with database capabilities.
Ê Key Concepts: Objects, classes, inheritance, encapsulation.
Ê Advantages: Better alignment with application code, supports complex data types.
Ê Disadvantages: Complexity, less mature than relational models.
Ê Use Cases: Multimedia applications, CAD systems.

Prof. Venkatesh B (DBMS) w Unit 1 32

Types of data model
} Entity-Relationship Model
Ê Structure: Conceptual model that represents data as entities, attributes, and relationships.
Ê Key Concepts: Entities, attributes, relationships, ER diagrams.
Ê Advantages: Intuitive and easy to design, visually representing data relationships.
Ê Disadvantages: Conceptual only, must be translated into another model for implementation.
Ê Use Cases: Database design and planning.
} Document Data Model
Ê Structure: Stores data in documents, typically JSON or XML.
Ê Key Concepts: Documents, collections.
Ê Advantages: Flexible schema, suitable for hierarchical data.
Ê Disadvantages: Potential for redundant data.
Ê Use Cases: Content management systems, blogging platforms.

Prof. Venkatesh B (DBMS) w Unit 1 33

Types of data model
} Key-Value Data Model
Ê Structure: Simple model that stores data as key-value pairs.
Ê Key Concepts: Keys, values.
Ê Advantages: High performance, scalability.
Ê Disadvantages: Limited querying capabilities.
Ê Use Cases: Caching, session management.
} Column-Family Data Model
Ê Structure: Data is stored in columns rather than rows.
Ê Key Concepts: Column families, columns, rows.
Ê Advantages: Efficient for read and write operations, good for large datasets.
Ê Disadvantages: Complex schema design.
Ê Use Cases: Real-time analytics, large-scale data processing.

Prof. Venkatesh B (DBMS) w Unit 1 34

Types of data model
} Graph Data Model
Ê Structure: Uses nodes, edges, and properties to represent and store data.
Ê Key Concepts: Nodes, edges, properties.
Ê Advantages: Excellent for representing complex relationships.
Ê Disadvantages: Can be less performant for simple queries.
Ê Use Cases: Social networks, recommendation systems.

Prof. Venkatesh B (DBMS) w Unit 1 35

Section - 4
Basic terms
} Data
Ê Data is raw, unorganized facts that need to be processed.
Ê Example: Marks of students
Ê Student_1 = 50/100, Student_2 = 25/100.
} Information
Ê When data is processed, organized, structured or presented in a given context so as to make it useful, it is
called information.
Ê Example: Result of students (Pass or Fail)
Ê Student_1 = Pass, Student_2 = Fail.

Prof. Venkatesh B (DBMS) w Unit 1 37

Basic terms (cont…)
} Metadata
Ê Metadata is data about data.
Ê Data such as table name, column name, data type, authorized user and user access privileges for any table is
called metadata for that table.

Faculty
Emp_Name Address Mobile_No Subject
Prof. Ajay Shah Rajkot 9876543210 PPS

Ê Metadata of above table is:

§ Table name such as Faculty
§ Column name such as Emp_Name, Address, Mobile_No, Subject
§ Datatype such as Varchar, Decimal
§ Access privileges such as Read, Write (Update)

Prof. Venkatesh B (DBMS) w Unit 1 38

Basic terms (cont…)
} Data dictionary
Ê A data dictionary is an information repository which contains metadata.
• Table Name – Faculty
• Column Name – EmpName, Address, Mob, Subject, Salary
• Datatype – Varchar, Decimal
• Access Privileges – Read, Write (Update)

} Data warehouse
Ê A data warehouse is an information repository which stores data.

Faculty
Emp_Name Address Mobile_No Subject
Prof. Ajay Shah Rajkot 9876543210 PPS
Prof. Ajay Patel Surat 0123456789 DBMS

Exercise Why data dictionary and data warehouse are stored in the different places?

Prof. Venkatesh B (DBMS) w Unit 1 39

Basic terms (cont…)
} Field
Ê A field is a character or group of characters that have a specific meaning.
Ê E.g, the value of Emp_Name, Address, Mobile_No etc are all fields of Faculty table.
Faculty
Emp_Name Address Mobile_No Subject Fields
Prof. Ajay Shah Rajkot 9876543210 PPS
Prof. Ajay Shah Rajkot 9876543210
Prof. Ajay Patel Surat 0123456789 DBMS

} Record / Tuple
Ê A record is a collection of logically related fields.
Ê E.g, the collection of fields (Emp_Name, Address, Mobile_No, Subject) forms a record for the Faculty.

Prof. Ajay Shah Rajkot 9876543210 PPS

Record / Tuple
Prof. Ajay Patel Surat 0123456789 DBMS

Prof. Venkatesh B (DBMS) w Unit 1 40

Section - 5
3 Levels ANSI SPARC Database System

User 1 User 2 User 3

How data are viewed View
View 1 View 2 View 3
by each users? Level

What data are stored and Conceptual Logical

What relationships exist? Level Level

How the data are actually Internal Physical

stored on storage devices? Level Level

Database

Prof. Venkatesh B (DBMS) w Unit 1 42

3 Levels ANSI SPARC Database System
} Internal level (Physical level)
Ê It describes how a data is stored on the storage device.
Ê Deals with physical storage of data.
§ Structure of records on disk - files, pages, blocks and indexes and ordering of records
Ê Internal view is described by the internal schema.
} Conceptual level (Logical level)
Ê What data are stored and what relationships exist among those data?
Ê It hides low level complexities of physical storage.
Ê For Example, STUDENT database may contain STUDENT and COURSE tables which will be visible to users but
users are unaware about their storage.
Ê Database administrator works at this level to determine what data to keep in the database.
} External level (View level)
Ê It describes only part of the entire database that an end user concern or how data are viewed by each user.
Ê Different user needs different views of the database, so there can be many views in a view level abstraction
of the database. Used by end users and application programmers.
Ê End users need to access only part of the database rather than the entire database.
Prof. Venkatesh B (DBMS) w Unit 1 43
3 Levels ANSI SPARC Database System: Example

We are storing student information in a student table.

User 1 User 2 User 3
User just interact with system with the help of GUI. View
View 1 View 2 View 3
Users are not aware of how and what the data is stored. Level

Records can be described as fields and attributes along

with their data types, their relationship among each other Conceptual
can be logically implemented. Logical
Level Level
Programmers generally work at this level.

Records can be described as blocks of storage (bytes,

Internal Physical
gigabytes, terabytes etc.) in memory.
Level Level
These details are often hidden from the programmers.

Database

Prof. Venkatesh B (DBMS) w Unit 1 44

Data Abstraction in DBMS
} Database systems are made-up of complex data structures.
} To ease the user interaction with database, the developers hide internal irrelevant details from
users.
} This process of hiding irrelevant details from user is called data abstraction.

Prof. Venkatesh B (DBMS) w Unit 1 45

Mapping and Data Independence
Want to access some data

User 1 User 2 User 3

View
View 1 View 2 View 3
Level

Request
Process of transforming requests and results between the
three levels is called mapping. Conceptual Logical
Level Level

Ability to modify a schema definition in one level without

Internal Physical
affecting a schema definition in the next higher level.
Level Level

Result
Database

Prof. Venkatesh B (DBMS) w Unit 1 46

Types of Data Independence
} Physical Data Independence
Ê Physical Data Independence is the ability to modify the physical schema without requiring any change in
logical (conceptual) schema and application programs.
Ê Modifications at the internal levels are occasionally necessary to improve performance.
Ê Possible modifications at internal levels are changes in file structures, compression techniques, hashing
algorithms, storage devices, etc.
} Logical Data Independence
Ê Logical data independence is the ability to modify the conceptual schema without requiring any change in
application programs.
Ê Modification at the logical levels is necessary whenever the logical structure of the database is changed.
Ê Application programs are heavily dependent on logical structures of the data they access. So any change in
logical structure also requires programs to change.

Prof. Venkatesh B (DBMS) w Unit 1 47

Section - 6
Types of Database Users
} Naive Users (End Users)
Ê Unsophisticated users who have zero knowledge of database system
Ê End user interacts to database via sophisticated software or tools
Ê e.g. Clerk in bank
} Application Programmers
Ê Programmers who write software using tools such as Java, .Net, PHP etc…
Ê e.g. Software developers
} Sophisticated Users
Ê Interact with database system without using an application program
Ê Use query tools like SQL
Ê e.g. Analyst
} Specialized Users (DBA)
Ê User write specialized database applications program
Ê Use administration tools
Ê e.g. Database Administrator

Prof. Venkatesh B (DBMS) w Unit 1 49

Section - 7
Role of DBA
} Schema Definition
Ê DBA defines the logical schema of the database.
} Storage Structure and Access Method Definition
Ê DBA decides how the data is to be represented in the database & how to access it.
} Defining Security and Integrity Constraints
Ê DBA decides on various security and integrity constraints.
} Granting of Authorization for Data Access
Ê DBA determines which user needs access to which part of the database.
} Liaison with Users
Ê DBA provide necessary data to the user.

Prof. Venkatesh B (DBMS) w Unit 1 51

Role of DBA
} Assisting Application Programmer
Ê DBA provides assistance to application programmers to develop application programs.
} Monitoring Performance
Ê DBA ensures that better performance is maintained by making a change in the physical or logical schema if
required.
} Backup and Recovery
Ê DBA backing up the database on some storage devices such as DVD, CD or magnetic tape or remote servers
and recover the system in case of failures, such as flood or virus attack from this backup.

Prof. Venkatesh B (DBMS) w Unit 1 52

Section - 8
Database System Architecture
Naive Application Sophisticated Database
user programmer user administrator
uses write uses uses

Application Application Query Administration

interfaces program tool tool

Translates
Interprets DML
DDL
Compiler DML DDL statementsintointo
and linker queries interpreter statements
Deals withlevela
Executes
low low
level
Application set of tables
execution of DDL
instructions
instructions that
program DML compiler containing
and DML
generated
the queryDML
by
object code and organizer metadata
statements
compiler.
Query evaluation evaluation engine
engine Query processor understands

Buffer File Authorization and Transaction

manager manager integrity manager manager

Manages allocation
Storage manager
Fetches data from
of space
disk on disk
storage to Preserves atomicity
Provides interface
memorystorage
for being Checks the authority
and controls
between low-level
used Indices Data dictionary of users to access
To provide faster Disk storage concurrency
data stored and
data and integrity
access to data items Data Statistical data application program
To To store
store statistical
metadata constraints
To store user data or queries
information about the data

Prof. Venkatesh B (DBMS) w Unit 1 54

Questions asked in Exams
1. List and explain the advantages of DBMS over file based system. OR Explain disadvantages of
files based system.
2. Draw and explain 3 level architecture of DBMS.
3. List and explain different categories/types of database users.
4. List and explain different tasks/roles/functions/duties of DBA (Database Administrator).
5. Explain DBMS architecture with block diagram. OR Explain Database System architecture with
block diagram.

Prof. Venkatesh B (DBMS) w Unit 1 55