Unit 1: Introduction to Fundamentals of DBMS

Database Applications

A Database is an organized collection of structured information or data, typically stored electronically in a computer system. A Database Management System (DBMS) is software designed to store, retrieve, define, and manage data efficiently and securely.

Databases are fundamental to modern enterprise operations across multiple domains. Key applications include:

• Banking and Financial Services: Managing customer account details, balances, deposits, loans, and real-time transaction processing.
• Airlines and Railway Reservation Systems: Tracking flight/train schedules, seat availability, passenger reservations, and ticketing routing.
• Educational Institutions: Maintaining student profiles, course registrations, grade books, faculty details, and attendance records.
• Telecommunication: Keeping records of call details, network usage, customer subscriptions, prepaid balances, and monthly billing statements.
• E-Commerce and Retail: Managing product catalogs, customer orders, shopping carts, payment processing, and supply chain inventory tracking.
• Human Resource Management: Storing employee records, payroll info, tax deductions, performance evaluations, and recruitment data.
• Healthcare: Storing patient medical histories, clinical diagnoses, treatment plans, doctor schedules, and pharmacy inventories.

Purpose of Database Systems

Prior to DBMS, organizations utilized traditional file processing systems supported by standard operating systems. In a file processing system, permanent records are stored in various operational files, and different application programs are written to extract records from and add records to the appropriate files.

Limitations of Traditional File Processing Systems

1. Data Redundancy and Inconsistency: The same information may be duplicated in several places across different files. Redundancy leads to higher storage costs and data inconsistency (where different copies of the same data show different values).
2. Difficulty in Accessing Data: Fetching non-routine reports requires writing new application programs, which is inefficient and time-consuming.
3. Data Isolation: Data is scattered in various files with different formats, making it difficult to write new application programs to retrieve appropriate data.
4. Integrity Problems: Data values stored in the database must satisfy specific consistency constraints. Adding new constraints or enforcing existing ones in application programs is difficult.
5. Atomicity Problems: A computer system is subject to failure. If a failure occurs mid-operation (e.g., fund transfer), the system must restore data to a consistent state. File systems lack built-in mechanisms for atomic operations.
6. Concurrent Access Anomalies: Multiple users reading or updating data simultaneously can lead to inconsistent data states without proper coordination.
7. Security Problems: Enforcing granular access controls (allowing users to access only specific parts of data) is extremely difficult in file-based setups.

Definition: A Database Management System (DBMS) resolves file processing limitations by providing centralized control of data, reducing redundancy, ensuring data consistency, enforcing security, and providing concurrent access with atomicity guarantees.

Components of DBMS

A DBMS operates through the integration of five key components working together:

• Hardware: The physical computer devices (processors, primary RAM, secondary storage like SSDs/HDDs, network devices) used to store and execute database operations.
• Software: The core software engine along with OS software, network software, and application programs that control and access the database system.
• Data: The operational information stored in the system, alongside metadata (data about data, stored in the Data Dictionary or System Catalog).
• Procedures: Instructions, rules, and guidelines that govern the design, installation, operation, backup, and usage of the database system.
• Users / People: Individuals who interact with the database system based on their assigned roles.

Categories of DBMS Users

• Database Administrator (DBA): Responsible for managing the entire system, defining schemas, authorizing access, setting operational rules, and monitoring performance.
• Database Designers: Responsible for identifying data to be stored and selecting appropriate structures to represent and manage data.
• Application Programmers: Developers who write software programs (using languages such as Java, Python, or C++) that interact with the DBMS using queries.
• End Users:
  • Naive / Parametric Users: Unaware of database internal architecture; interact strictly via pre-built user interfaces (e.g., bank tellers, airline booking agents).
  • Casual Users: Access database occasionally by writing unique queries via query processors.
  • Sophisticated Users: Engineers, analysts, and scientists who write complex custom queries to perform analytical tasks.

DBMS Architecture

Database systems can be classified based on physical deployment architecture and logical structural architecture.

Physical Deployment Architecture

• 1-Tier Architecture: The user interface, application code, and database management system reside entirely on a single physical machine (e.g., local database application on a standalone PC).
• 2-Tier Architecture (Client-Server): The application runs on the client machine and communicates directly with the database server via protocols or APIs (e.g., JDBC/ODBC).
• 3-Tier Architecture: The user interface resides on the client machine, application logic resides on an intermediate Application Server, and database management resides on a dedicated Database Server. This enhances security, scalability, and modularity.

Three-Schema Architecture (ANSI/SPARC Architecture)

The goal of the three-schema architecture is to separate user applications from the physical database system. It is structured into three levels:

Important Note: The three-schema architecture provides abstraction. Changes at a lower level should not force updates at a higher level.

Different Data Models

A Data Model is an abstract representation of data, data relationships, data semantics, and data constraints that describe how data is stored and organized in a database system.

Major Classes of Data Models

1. Entity-Relationship (E-R) Data Model: A high-level conceptual data model based on real-world objects called entities and associations among them called relationships. Attributes describe properties of entities.
2. Relational Data Model: A record-based model that represents data and relationships as two-dimensional tables consisting of rows (tuples) and columns (attributes).
3. Hierarchical Data Model: Organizes data in a tree-like structure with parent-child relationships where each child record has exactly one parent record.
4. Network Data Model: Organizes data as a graph/network structure where records are nodes and sets are edges. A child record can have multiple parent records.
5. Object-Based Data Models: Extends relational models by adding object-oriented concepts like encapsulation, classes, inheritance, and object identifiers (e.g., Object-Oriented Data Model, Object-Relational Data Model).
6. Semi-Structured Data Model: Allows data specification where individual data items of the same type may have different sets of attributes (e.g., XML, JSON documents).

Data Independence

Data Independence is defined as the capability to modify a schema definition in one level of a database architecture without altering the schema definition at the next higher level.

Types of Data Independence

1. Logical Data Independence

The capacity to modify the conceptual schema without having to change external schemas or application programs.

• Example: Adding a new column (attribute) like "Middle Name" to a Student table, or adding a new table entirely, without breaking existing application code that selects only "First Name" and "Last Name".
• Why it matters: Protects application programs from structural changes in logical database models.

2. Physical Data Independence

The capacity to modify the internal schema (physical storage details) without having to change the conceptual schema.

• Example: Switching storage hardware from HDD to SSD, changing file storage structures, creating B+ tree indexes, or modifying partitioning schemes without altering the logical structure of tables or SQL queries.
• Why it matters: Performance tuning actions do not force applications to be rewritten.

Various Types of Constraints

Constraints are rules enforced on data columns or tables to prevent invalid data entry and guarantee the accuracy, integrity, and reliability of data inside the database.

1. Domain Constraints

Specify that the value of each attribute must be an atomic value drawn from a specific defined domain. It includes data types, valid ranges, and allowed formats.

• Example: Age must be a positive integer (Age > 0). Salary cannot be negative.

2. Key Constraints

State that an entity set must have a minimal set of attributes that uniquely identifies every entity (tuple) within the relation.

• Super Key: A set of one or more attributes that uniquely identifies a tuple.
• Candidate Key: A minimal super key (no proper subset is a super key).
• Primary Key: The candidate key selected by the database designer to uniquely identify tuples in a table. Must be unique and non-null.
• Alternate Key: Candidate keys that were not chosen as the primary key.

3. Entity Integrity Constraint

Law of Entity Integrity: No primary key value (or any attribute belonging to a primary key) can be NULL.

Reasoning: A primary key is used to uniquely identify individual tuples. A NULL value implies an unknown or missing entry, which prevents unique tuple identification.

4. Referential Integrity Constraint

Maintains consistency between two relations that are connected via attributes. Formulated using Foreign Keys.

Rule: If a tuple in relation R1 references an attribute in relation R2, that referenced value must exist in R2, or be NULL (if NULLs are permitted).

• Foreign Key (FK): An attribute in a table that references the Primary Key (PK) of another table.
• Handling violations: Action policies like ON DELETE CASCADE, ON DELETE SET NULL, or ON DELETE RESTRICT.

5. General / Assertion Constraints

Business rules that span across multiple tables or attributes that cannot be expressed purely using basic domain or referential constraints.

• Example: An employee cannot manage a department if their total experience is less than 5 years.