RDBMS Module 1 — Database System Concepts

Core Concepts

Data, Information, Database & DBMS

The Chain: Raw Data → (processing) → Information → (stored together) → Database → (managed by) → DBMS

Data vs Information

Raw Data	Processed Information
`9884567890`	Mobile number of student Rajesh
`72.5`	Marks scored by Priya in Maths (out of 100)
`2024-03-15`	Date of DBMS lecture
`500001`	PIN code: Hyderabad, Telangana

Exam tip: Data = raw facts with no context. Information = data + context + meaning. Information enables decision-making.

Database — Key Definitions

Database = logically coherent, organized collection of related data to meet an organization's information needs.

Formal (Coronel): "A DATABASE is a shared, integrated computer structure that stores end-user data (raw facts) and metadata (data about the structure of the data)."

Domain	What's Stored
University	Students, courses, faculty, enrollment, marks, fees
Hospital	Patients, doctors, appointments, diagnoses, prescriptions
Bank	Accounts, transactions, loans, customers, branches
E-Commerce	Products, orders, customers, inventory, payments
Railway	Trains, seats, passengers, bookings, schedules

DBMS — What It Does

DBMS = collection of programs that enables users to create, maintain, and control access to a database. Acts as intermediary between DB and users/apps.

Capability	What It Means
Define	Specify data types, structures, constraints (DDL)
Construct	Store data on storage medium
Manipulate	Query, update, generate reports (DML)
Share	Allow multiple users concurrent access
Protect & Maintain	Security, backup, recovery

⚠ MySQL is the DBMS; the student records stored using MySQL form the Database. They are NOT the same thing!

Database System Environment — 4 Components

💻 HARDWARE

Servers, Storage, Network

🛠 SOFTWARE

DBMS, OS, Apps

📦 DATA

Content + Metadata

👥 USERS

End Users, Programmers, DBA

File vs DB

File-Based Problems & 10 DB Characteristics

8 Problems of File-Based Systems

#	Problem	Consequence
1	Data Redundancy	Same data stored in multiple files; wastes storage
2	Data Inconsistency	Different files have contradictory values for same data
3	Difficult Data Access	New program needed for every new query; no standard SQL
4	Data Isolation	Data in different formats across files; integration is hard
5	Integrity Problems	Constraints buried in programs; adding new rules is costly
6	Atomicity Problems	System crash leaves partial updates; no rollback
7	Concurrent Access	Two users book last seat — both succeed (race condition)
8	Security Limitations	Only coarse-grained file-level access control

10 Characteristics of the Database Approach

#	Characteristic	Core Benefit
1	Self-Describing Nature	DB stores its own structure via System Catalog / Data Dictionary (metadata)
2	Program-Data Independence	Storage structure changes don't break application programs
3	Multiple Views of Data	Each user/group sees only the data they need (virtual tables)
4	Multiuser Transaction Processing (ACID)	Safe concurrent access; all-or-nothing operations
5	Control of Data Redundancy	Each item stored once; foreign keys establish relationships
6	Authorization and Security	Fine-grained access at table/column/row/operation level
7	Persistent Storage of Objects	Complex objects (GIS, multimedia) survive program execution
8	Efficient Query Processing	Indexes (B+ Trees), buffer pools, query optimizer
9	Backup and Recovery	WAL logs + checkpoints enable full DB recovery
10	Integrity Constraints	Centrally defined rules enforced for all users automatically

ACID Properties (Characteristic #4) — Deep Dive

Property	Meaning	Bank Transfer Example
Atomicity	All-or-nothing execution	Debit + Credit both happen, or neither does
Consistency	DB moves from one valid state to another	Total money before = total money after
Isolation	Concurrent transactions don't see each other's intermediate states	No one sees A debited but B not yet credited
Durability	Committed changes survive crashes	Transfer saved even if power fails right after commit

Integrity Constraint Types (Characteristic #10)

Constraint	Description	Example
Domain	Values must be in defined domain	Age: INTEGER, Marks: 0–100
Entity Integrity	Primary Key ≠ NULL	Student_ID cannot be null
Referential Integrity	FK must match existing PK or be NULL	Dept_ID must exist in DEPARTMENT
Key	Candidate key uniqueness enforced	No two students with same roll number
Semantic	Business rules via CHECK/triggers	Salary increase ≤ 30% per update

CREATE TABLE STUDENT (
  Student_ID  INT         PRIMARY KEY,
  Name        VARCHAR(50) NOT NULL,
  Age         INT         CHECK (Age BETWEEN 15 AND 35),
  Dept_ID     INT         REFERENCES DEPARTMENT(Dept_ID)
);

Backup & Recovery Mechanisms (Characteristic #9)

Failure Type	Recovery Mechanism
Transaction Failure	Transaction Rollback (undo log)
System Crash	Crash Recovery (redo committed, undo uncommitted)
Media Failure	Media Recovery (restore from backup + logs)
Human Error	Point-in-Time Recovery (restore to timestamp)

WAL (Write-Ahead Log) = log the change BEFORE writing to DB. Checkpoints = periodic snapshots to reduce recovery time.

File System vs Database — Master Comparison

Aspect	File System	Database (DBMS)
Data Storage	Separate, independent files	Centralized, integrated
Data Redundancy	High — duplicated everywhere	Controlled — stored once
Consistency	Low — files can contradict	High — DBMS enforces
Data Access	Custom program per query	Standard SQL
Security	File-level only	Table/column/row/op level
Backup & Recovery	Manual — programmer's job	Automatic — DBMS manages
Concurrency	Race conditions possible	Full ACID control
Data Independence	None — tied to programs	Physical & logical independence
Cost	Low initial cost	High initial, lower long-term

Multiple Views Example (Characteristic #3)

User Role	View Access
Student	Own marks, attendance, fee status only
Faculty	Marks of students in their course (no financial data)
Accounts Clerk	Fee records, dues, scholarships (no academic records)
Registrar	All student data, enrollment, degrees

Views provide security, simplicity, customization, and data abstraction — all from one underlying database.

Actors

Actors on the Scene & Workers Behind the Scene

Actors on the Scene (Direct Users — interact with DB)

Database Administrator (DBA) — Manages the ENTIRE database environment. Most critical human resource.

DBA Responsibilities
Install & configure DBMS	Define schema (tables, columns, constraints)
Grant/revoke access privileges	Monitor performance & tune queries
Manage backup & recovery	Apply security patches & upgrades
Enforce data standards	Handle concurrency control & deadlocks

Database Designers — Design structure BEFORE data is loaded; understand user requirements.

Identify data to be stored and relationships
Create ER diagrams → convert to relational tables
Define primary keys, foreign keys, constraints
Apply normalization rules; create indexes

End Users Classification

Type	Description	Examples
Naive (Casual)	Use pre-written apps, unaware of DB internals	ATM users, e-commerce shoppers, hospital registration
Sophisticated	Direct SQL interaction; understand DB concepts	Business analysts, scientists, engineers
Standalone	Maintain personal databases using desktop tools	Personal library in MS Access

Role	Responsibility
System Analysts	Determine user requirements; design specifications
Application Programmers	Implement specs; write SQL-embedded programs (Java, Python, PHP)

Workers Behind the Scene (Invisible — build/maintain the DBMS itself)

Role	Function
DBMS System Designers	Design/build DBMS components: query processor, storage engine, transaction manager
Tool Developers	Create design tools, GUI tools (SQL Workbench, pgAdmin, TOAD), performance monitors
Operators & Maintenance	Run/maintain hardware/software environment; backups; monitoring

Key distinction: Actors on the scene use the DB. Workers behind the scene build/maintain the DBMS software itself.

Advantages

13 Advantages of the DBMS Approach

#	Advantage	How
1	Controlling Data Redundancy	Each item stored once; FK establishes relationships
2	Restricting Unauthorized Access	Authentication, roles, audit logging
3	Persistent Storage	Data persists across program executions and restarts
4	Efficient Query Processing	Indexes, buffer pools, query optimizer
5	Backup and Recovery	Automatic management with WAL, checkpoints
6	Multiple User Interfaces	SQL for experts, GUI for naive users, APIs for programmers
7	Complex Relationships	1:1, 1:N, M:N via keys and joins
8	Integrity Constraints	Centrally defined and enforced for all users
9	Inferencing and Rules	Triggers and stored procedures for automatic actions
10	Reduced Development Time	Developers focus on business logic, not data management
11	Flexibility	Schema modifications with minimal program impact
12	Current Information	All users see most current data after commit
13	Economies of Scale	Centralized data reduces overall cost

When NOT to Use a DBMS

Situation	Reason to Avoid DBMS
High initial cost	Software, hardware, training are expensive
High overhead	DBMS consumes significant CPU, memory, disk
Simple applications	Single-user, single-purpose with small fixed dataset
Real-time systems	Strict timing constraints may find DBMS overhead unacceptable
Embedded systems	Very limited resources may not support full DBMS

For exam: DBMS always has overhead — justifiable only when data is large, shared, or complex. A simple to-do list app doesn't need Oracle.

Data Models

Data Models, Schemas & Instances

Data Model = a collection of concepts used to describe database structure — data types, relationships, and constraints. The "blueprint" for a database.

3 Categories of Data Models

Level	Also Called	Examples	User
High-Level	Conceptual Model	ER Model, Object-Oriented Model	Database Designer
Representational	Implementation / Logical Model	Relational, Network, Hierarchical	Programmer / DBA
Low-Level	Physical Model	Record formats, Indexes, B-Trees	DBMS (internal)

Schema vs Instance

Schema (Structure)

Description of database — types & constraints
Defined at design time
Changes infrequently (like a class definition)
Also called intension

STUDENT(Student_ID: INT, Name: VARCHAR(50), Age: INT, Dept_ID: INT)

Instance (State / Snapshot)

Actual data at a particular moment
Changes with every INSERT/UPDATE/DELETE
Also called extension
Multiple valid instances for one schema

(101, 'Arun', 20, 'CS')
(102, 'Priya', 21, 'IT')

📌 Analogy: Schema = blueprint/class. Instance = actual building/object. You have one schema but many instances over time.

Major Data Models — Visual + Simple Properties

Note: In exams, "rational model" usually means Relational model. Here, Float model is treated as a distinct 2D-array numeric model.

ER Model

[STUDENT] --- ENROLLS --- [COURSE]
   | PK: Student_ID            | PK: Course_ID
   | Name, Dept                | Title, Credits

Conceptual model for database design.
Uses entities, attributes, relationships.
Easy for users to understand before table creation.

Relational Model

STUDENT(Student_ID, Name, Dept_ID)
COURSE(Course_ID, Title)
ENROLL(Student_ID, Course_ID)

Join by keys: STUDENT.Student_ID = ENROLL.Student_ID

Data stored as tables (relations).
Primary keys and foreign keys define links.
Manipulated using SQL; most widely used model.

Hierarchical Model

COMPANY
  |
  +-- DEPARTMENT
        |
        +-- EMPLOYEE

Tree structure with parent-child records.
Supports 1:N naturally.
Fast for fixed paths, less flexible for complex queries.

Network Model

[SUPPLIER] ---- supplies ---- [PART]
     \                          /
      \---- works_with --------/

Graph-like structure (records + set links).
Supports M:N relationships directly.
More flexible than hierarchical, but complex to manage.

Object-Oriented Model

class Student {
  id, name
  enroll(course)
}

object s1 : Student

Stores data as objects with state and behavior.
Supports classes, inheritance, encapsulation.
Good for complex data like CAD, multimedia, GIS.

Float Model

2D Array (no duplicates)

[ 1.2500, 2.5000, 3.7500 ]
[ 4.1250, 5.2500, 6.5000 ]
[ 7.8750, 8.0000, 9.6250 ]

Structure: Organized as a two-dimensional array.
No Duplicates: Duplicate elements are not allowed.
Precision: Useful for high-precision decimal numeric values.
Use Case: Best for small, specialized numeric datasets.
Limitations: Inefficient for large-scale storage and lacks complex relational mapping.

3-Schema

Three-Schema Architecture & Data Independence

Purpose (ANSI/SPARC Architecture): Achieve data independence — separate user applications from physical database. Proposed to standardize DBMS structure.

Three-Schema Architecture — Visual

Level	Also Called	What It Describes	Who Uses It
External	View Level / User Schema	What individual users/groups see — customized subsets	End Users, App Programs
Conceptual	Logical Level	Complete logical structure — all tables, relationships, constraints	DBA, Designers
Internal	Physical Level	How data is physically stored — files, indexes, storage structures	DBMS (internal)

Data Independence — The Main Goal

Data Independence = ability to change schema at one level WITHOUT changing schema at the next higher level.

Physical Data Independence

What changes: Internal schema (storage, indexes, file organization)

What stays same: Conceptual schema + external views

Examples: Add/remove indexes, move to SSD, change file organization

Logical Data Independence

What changes: Conceptual schema (add tables/columns)

What stays same: External views + application programs

Examples: Add new table, add columns, split tables

⚠ Harder to achieve than physical independence

Languages

Database Languages & Interfaces

SQL Sub-Languages Overview

Language	Full Name	Purpose	Commands
DDL	Data Definition Language	Define/modify DB structure (schema)	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Retrieve, insert, update, delete data	SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language	Control access/permissions	GRANT, REVOKE
TCL	Transaction Control Language	Manage transactions	COMMIT, ROLLBACK, SAVEPOINT
VDL	View Definition Language	Define user views (external schema)	CREATE VIEW

DDL Examples

-- Create table with constraints
CREATE TABLE STUDENT (
  Student_ID INT         PRIMARY KEY,
  Name       VARCHAR(50) NOT NULL,
  Age        INT
);
-- Add a column
ALTER TABLE STUDENT ADD COLUMN Email VARCHAR(100);
-- Remove table (irreversible!)
DROP TABLE STUDENT;

DML Examples + Procedural vs Non-Procedural

Type	User specifies	Example
Procedural	WHAT + HOW to get it	Network/hierarchical languages
Non-Procedural (Declarative)	WHAT only — DBMS decides HOW	SQL (most common)

SELECT Name, Age FROM STUDENT WHERE Dept_ID = 'CS';
INSERT INTO STUDENT VALUES (103, 'Kavya', 20, 'EC');
UPDATE STUDENT SET Age = 21 WHERE Student_ID = 101;
DELETE FROM STUDENT WHERE Student_ID = 102;

DCL & TCL Examples

-- DCL: Permissions
GRANT SELECT, INSERT ON STUDENT TO faculty_user;
REVOKE DELETE ON MARKS FROM exam_staff;

-- TCL: Transactions
COMMIT;                        -- permanently save changes
ROLLBACK;                      -- undo since last commit
SAVEPOINT before_update;      -- partial rollback point

-- VDL: Views
CREATE VIEW Student_Marks AS
  SELECT s.Name, m.Subject, m.Marks
  FROM STUDENT s JOIN MARKS m ON s.Student_ID = m.Student_ID;

Database Interfaces

Interface Type	Description	Use Case
Menu-Based	Lists of options; no SQL knowledge needed	Web clients, e-commerce portals
Forms-Based	Screen fields for data entry	Banking forms, employee records
GUI	Visual tools, drag-and-drop	MS Access, pgAdmin, SQL Workbench
Natural Language	English queries converted to SQL	"Show students with marks > 80"
Keyword-Based	Search engine style queries	Document databases
Speech I/O	Voice input and synthesized output	Telephone inquiry systems
Parametric	Predefined transactions via function keys	Bank tellers, booking agents
DBA Interface	Admin tools: schema, security, performance	Oracle Enterprise Manager

DBMS Internal Components

Component	Role
Query Processor	Parses and compiles SQL into execution plans
Query Optimizer	Selects lowest-cost execution strategy
Transaction Manager	Enforces ACID properties
Concurrency Control Manager	Implements locking and timestamp protocols
Recovery Manager	Manages WAL, checkpoints, crash recovery
Buffer Manager	Manages memory buffer pool (caching)
Storage Manager	Manages physical storage and indexes
Data Dictionary/Catalog	Stores all metadata (the self-describing part)

Data Types

Structured, Semi-Structured & Unstructured Data

Visual Overview

Master Comparison Table

Aspect	Structured	Semi-Structured	Unstructured
Schema	Fixed, predefined	Flexible, self-describing	None
Format	Tables (rows/cols)	XML, JSON, HTML	Text, images, video, audio
Query Language	SQL	XQuery, MongoDB Query	Full-text search, AI/ML
DBMS	MySQL, Oracle, PostgreSQL	MongoDB, CouchDB, eXist-db	Amazon S3, Elasticsearch
Consistency	High	Medium	Low
Scalability	Moderate	High	Very High
% of World Data	~20%	~<5%	~80–90%

Polyglot Persistence — Using All Three Together

Polyglot Persistence = using different database types within a single application architecture for different data types.

Data Type	Storage	Use Case (Hospital System)
Structured	RDBMS (MySQL)	Patient records, billing, appointments
Semi-Structured	MongoDB	JSON messages from medical devices
Unstructured	Object Storage (S3)	MRI scan images, X-rays

Flashcards

Tap to Flip

Click any card to reveal the answer

∑

Quick Ref

Master Cheatsheet

Everything in One Table

Topic	Key Fact
Data vs Information	Data = raw facts; Information = data + context + meaning
Database (Coronel def)	Shared, integrated structure storing end-user data + metadata
DBMS vs Database	MySQL = DBMS. Student records in MySQL = Database.
DB Environment components	Hardware, Software, Data, Users (4 things)
File system problems count	8 problems (redundancy, inconsistency, difficult access, isolation, integrity, atomicity, concurrency, security)
DB Characteristics count	10 characteristics
Characteristic #1	Self-Describing — DB stores its own metadata in System Catalog
Characteristic #2	Program-Data Independence — structure changes don't break programs
Characteristic #4	Multiuser Transaction Processing — ACID properties
ACID stands for	Atomicity, Consistency, Isolation, Durability
Atomicity	All or nothing — no partial transactions
Isolation	Concurrent txns don't see each other's intermediate states
WAL	Write-Ahead Log — log BEFORE writing to DB
Schema	Structure/blueprint — changes infrequently (intension)
Instance	Actual data at a moment — changes constantly (extension)
3-Schema levels	External (View) → Conceptual (Logical) → Internal (Physical)
Physical Data Independence	Change internal schema; conceptual schema unchanged
Logical Data Independence	Change conceptual schema; external views unchanged (harder)
DBA role	Manages entire DB environment: schema, security, backup, performance
Naive end users	Use pre-written apps; unaware of DB internals (ATM users)
Workers behind the scene	Build/maintain DBMS itself (not the data)
Advantages count	13 advantages of DBMS approach
High-Level data model	ER Model, Object-Oriented — close to user perception
Representational data model	Relational, Network, Hierarchical
DDL commands	CREATE, ALTER, DROP, TRUNCATE
DML commands	SELECT, INSERT, UPDATE, DELETE
DCL commands	GRANT, REVOKE
TCL commands	COMMIT, ROLLBACK, SAVEPOINT
SQL type	Non-procedural/Declarative — user says WHAT, not HOW
Structured data	Fixed schema, SQL, RDBMS (MySQL, Oracle)
Semi-structured data	Flexible schema, XML/JSON, MongoDB/CouchDB
Unstructured data	No schema, ~80–90% of all data, needs AI/ML
Polyglot persistence	Using different DB types for different data in same app
Query optimizer does	Selects lowest-cost execution plan for SQL queries
Data Dictionary	Stores metadata — enables self-describing characteristic
Index performance	B+ Tree: O(log n) lookups vs O(n) full scan