Introduction to Databases and Transactions

Core Data Concepts and DBMS Functions

• Data: Raw unprocessed facts, figures, observations, or symbols without inherent meaning (e.g., Student Roll Number: $101$, Name: Raghav, Marks: $85$, Age: $20$).

• Database: An organized collection of related data allowing efficient access, retrieval, modification, and deletion (e.g., Library, Hospital, and Bank databases).

• Database Management System (DBMS): Software acting as an interface between the database and users/applications to create, store, manage, retrieve, and manipulate data.

• Key DBMS Functions: Data storage, data retrieval, data manipulation, security management, backup and recovery, concurrency control, and integrity enforcement.

DBMS Advantages, Disadvantages, and File Systems

• Advantages of DBMS: Reduced data redundancy, improved data consistency, multi-user data sharing, enhanced security, data integrity enforcement, robust backup and recovery, and data independence.

• Disadvantages of DBMS: High software, hardware, licensing, and maintenance costs; overall operational complexity; large storage space and memory requirements; and performance overhead for simple applications.

• Disadvantages of File Processing Systems: High data redundancy, data inconsistency across separate files, data isolation, difficult data access requiring custom programs, limited data sharing, weak security, integrity problems, and program-data dependence.

Levels of Data Abstraction and Data Independence

• Data Abstraction Levels:

  • Physical Level (Internal Level): Lowest level; describes physical storage structures, disk blocks, file organization, indexes, and B-trees.

  • Logical Level (Conceptual Level): Middle level; describes what data is stored and its relationships (e.g., $\text{STUDENT}(\text{Student\_ID}, \text{Name}, \text{Course}, \text{Marks})$).

  • View Level (External Level): Highest level; presents specific database subsets customized for individual users to enhance security.

• Data Independence:

  • Physical Data Independence: Ability to change internal schemas (indexing, storage structures) without affecting the conceptual schema.

  • Logical Data Independence: Ability to change conceptual schemas (adding attributes or tables) without affecting external schemas or application programs.

Relational Databases (RDBMS)

• Overview: Proposed by E.F. Codd in $1970$; organizes data into tables (relations) comprising rows (records or tuples) and columns (attributes).

• Primary Concepts:

  • Primary Key: Attribute or set of attributes uniquely identifying each record in a table.

  • Foreign Key: Attribute referencing a primary key in another table to establish relationships.

  • Structured Query Language (SQL): Standard language to create, retrieve, update, and manage relational databases.

  • Normalization: Process of organizing data to reduce redundancy and preserve data integrity.

• Popular RDBMS Platforms: MySQL, Oracle Database, Microsoft SQL Server, and PostgreSQL.

Database Architecture

• User Types:

  • Naive Users: Interact via interface applications without database knowledge (e.g., ATM or railway reservation users).

  • Application Programmers: Write software using programming languages (Java, Python, C#) with embedded SQL.

  • Sophisticated Users: Interact directly via query tools using SQL (e.g., data analysts, researchers).

  • Database Administrator (DBA): Oversees management, configuration, user access, security, backup, recovery, and performance tuning.

• Architecture Components:

  • Query Processor: Contains Compiler & Linker, DML Queries ($\text{SELECT}$, $\text{INSERT}$, $\text{UPDATE}$, $\text{DELETE}$), DDL Interpreter ($\text{CREATE}$, $\text{ALTER}$, $\text{DROP}$), DML Compiler & Organizer, and Query Evaluation Engine.

  • Storage Manager: Features Buffer Manager, File Manager, Authorization & Integrity Manager, and Transaction Manager.

  • Disk Storage: Holds Data, Indices, Data Dictionary (metadata), and Statistical Data.

Transaction Management and ACID Properties

• Transaction Definition: A logical unit of work consisting of one or more operations treated as a single unit (e.g., transferring $\text{₹}1000$ from Account A to Account B).

• ACID Properties:

  • Atomicity: Ensures all operations execute successfully or none are performed (rollback on failure).

  • Consistency: Maintains database validity before and after execution (e.g., balance before transfer $A = \text{₹}5000$, $B = \text{₹}3000$, $\text{Total} = \text{₹}8000$ remains $\text{Total} = \text{₹}8000$ after transfer $A = \text{₹}4000$, $B = \text{₹}4000$).

  • Isolation: Prevents concurrent transactions from interfering with each other.

  • Durability: Guarantees committed updates persist permanently despite power or system failures.