Unit 4: Introduction to Database Management System

I. Foundations of Database Management

A database is an organized collection of logically related data, while a Database Management System (DBMS) is software that defines, stores, retrieves, updates, secures, and administers that data. Together, they replace isolated files with a controlled environment in which multiple users and applications can share persistent information.

Defining characteristics:

• Structured organization: Data is arranged according to a model, such as relational tables containing rows and columns.
• Persistent storage: Records remain available after an application or computer session ends; for example, a saved STUDENT record survives a system restart.
• Integrated data: Related facts are connected through identifiers such as StudentID, reducing disconnected and duplicate files.
• Controlled access: Authentication and authorization determine who may view or modify particular data.
• Shared use: Multiple applications and users can access one database while the DBMS coordinates their operations.
• Integrity: Constraints keep data valid; a primary key, for instance, prevents duplicate identifiers.
• Abstraction: Users work with tables, forms, or views without needing to know disk-block locations.
• Recovery and consistency: Logging, transactions, and backups protect data against errors and system failures.

II. Databases — Organized and Related Data

A. Conceptual understanding of databases

A database represents facts about a defined domain in a structured form that supports meaningful relationships and efficient operations.

• Data and information: Data consists of stored facts such as StudentID = 104, while information is produced by interpretation, such as identifying the student with the highest grade.
• Database domain: A database models a particular area of reality, called its universe of discourse; a college database may represent students, courses, teachers, and enrolments.
• Schema and instance:
  • Schema: The stable blueprint defining structures, attributes, relationships, and constraints.
  • Instance: The actual records stored at a particular moment.
• Relational structure: In a relational database, a relation is represented as a table, a tuple as a row, and an attribute as a column.

STUDENT(StudentID, Name, Department)
COURSE(CourseID, Title)
ENROLMENT(StudentID, CourseID, Grade)

• Keys: StudentID uniquely identifies a STUDENT; the pair (StudentID, CourseID) may identify an ENROLMENT.
• Relationships: ENROLMENT.StudentID can reference STUDENT.StudentID, connecting an enrolment to an existing student through a foreign key.
• Database operations: Core operations are commonly summarized as CRUD—Create, Read, Update, and Delete.
• Database versus file system: Separate files often cause duplication, inconsistency, weak security, and difficult sharing; a DBMS supplies centralized definitions, constraints, concurrency control, and recovery.
• Data models: Relational, hierarchical, network, object-oriented, and document models organize data differently; the relational model uses tables and declarative queries.

III. DBMS in Practice — Operational Uses

A. DBMS applications

DBMS applications manage large, changing collections of related data where accuracy, controlled sharing, and timely retrieval are important.

• Banking: Databases maintain customers, accounts, transfers, and balances; transferring ₹500 requires debit and credit operations to succeed as one transaction.
• Education: Institutions store admissions, attendance, course registration, grades, and fee records linked through identifiers such as StudentID.
• Healthcare: Systems manage patient records, appointments, laboratory results, and billing while restricting sensitive information to authorized roles.
• Retail and e-commerce: Product catalogues, inventory, orders, payments, and deliveries are coordinated; confirming an order may reduce StockQuantity.
• Transport and reservations: Airline and railway systems track schedules, seats, passengers, and bookings while preventing two confirmed reservations for one seat.
• Government: Databases support taxation, licensing, land records, census data, and public-service administration.
• Telecommunications: Providers record customers, usage events, plans, network resources, and bills at high volume.
• Organizational decision-making: Historical operational data can be consolidated into data warehouses for reporting and trend analysis.
• Suitability and limitation: A DBMS is valuable for shared, persistent, interrelated data, but its software, hardware, administration, and maintenance costs may be excessive for a tiny single-user dataset.

IV. Internal Organization — Functional Building Blocks

A. Components of DBMS

A DBMS combines processing, storage, control, and metadata facilities to translate user requests into safe operations on stored data.

• Query processor: Interprets and executes database commands.
  • Parser: Checks syntax and validates referenced objects.
  • Optimizer: Selects an efficient execution plan, such as using an index rather than scanning every row.
  • Execution engine: Performs the selected operations.
• Storage manager: Connects higher-level queries to records, pages, files, and indexes held on storage devices.
• File and index manager: Organizes physical files and access structures; a B-tree index can accelerate searches for a specific StudentID.
• Buffer manager: Moves frequently needed disk pages into main memory and determines when modified pages are written back.
• Transaction manager: Treats a sequence of operations as one logical unit and supports the ACID properties:
  • Atomicity: All operations occur or none occur.
  • Consistency: Constraints remain satisfied.
  • Isolation: Concurrent transactions do not improperly interfere.
  • Durability: Committed changes survive failures.
• Concurrency-control manager: Coordinates simultaneous operations through mechanisms such as locking or multiversion concurrency control.
• Recovery manager: Uses logs, checkpoints, backups, undo, and redo operations to restore a consistent state after failure.
• Authorization and integrity manager: Enforces privileges and constraints such as NOT NULL, UNIQUE, and foreign keys.
• System catalogue: Stores metadata—table names, column types, constraints, users, privileges, and statistics used by the optimizer.
• Database interface: SQL, APIs, forms, and administrative utilities allow people and programs to communicate with the DBMS.

V. Human Roles — Interaction and Responsibility

A. Users and actors of DBMS

DBMS actors have different responsibilities ranging from database design and administration to application use.

• Database administrator (DBA): Controls accounts, permissions, storage allocation, performance tuning, backup, recovery, and availability.
• Database designer or data architect: Identifies entities, attributes, relationships, and constraints, then maps the conceptual design into a database schema.
• System analyst: Studies organizational requirements and specifies how data and business processes should interact.
• Application programmer: Builds programs that submit queries and transactions through SQL, drivers, or APIs.
• End users:
  • Naive or parametric users: Perform predefined operations, such as a cashier entering a sale through a form.
  • Casual users: Access the database occasionally and issue ad hoc searches or reports.
  • Sophisticated users: Use advanced query or analytical tools directly.
  • Standalone users: Maintain personal databases through packaged software.
• DBMS designers and implementers: Develop the database engine, including query optimization, transaction processing, and storage mechanisms.
• Tool developers: Produce modelling, reporting, monitoring, migration, and administration utilities.
• Operators and maintenance staff: Run servers, schedule jobs, monitor equipment, and maintain the computing environment.
• Separation of duties: Dividing administration, development, and auditing responsibilities reduces accidental misuse and unauthorized control.

VI. DBMS Architecture — Levels and Deployment

A. DBMS architecture

DBMS architecture separates user views, logical design, and physical storage while also defining how application and database services are deployed.

• External level: Contains user-specific views; a lecturer may see student names and grades but not financial details.
• Conceptual level: Describes the complete logical database, including entities, relationships, attributes, and constraints.
• Internal level: Specifies physical representation through files, pages, indexes, partitions, compression, and record placement.
• Mappings between levels: External–conceptual and conceptual–internal mappings translate operations while hiding lower-level changes.
• One-tier architecture: The interface, application, DBMS, and data operate on one system; this arrangement is common for local development.
• Two-tier architecture: A client application communicates directly with a database server, often through JDBC or ODBC.
• Three-tier architecture:
  • Presentation tier: Displays the user interface.
  • Application tier: Applies business rules and communicates with the DBMS.
  • Data tier: Stores and manages the database.
• Three-tier benefit: Clients do not require direct database access, improving scalability, maintainability, and security.
• Centralized and distributed forms: A centralized database is managed at one principal site, whereas a distributed database stores coordinated data across multiple sites.

VII. Separation of Concerns — Protection from Structural Change

A. Data independence

Data independence is the ability to modify a schema at one architectural level without requiring changes at the next higher level.

• Purpose: It isolates applications from changes in data representation, reducing maintenance and extending system life.
• Architectural basis: Independence results from the separation among external, conceptual, and internal schemas.
• Program–data independence: Data definitions are maintained in the catalogue rather than being permanently embedded in every application.
• Practical effect: An administrator can tune storage while a query such as the following remains unchanged:

SELECT Name
FROM STUDENT
WHERE StudentID = 104;

• Limit: Independence is not absolute; an application must change if it depends on an altered field that is removed or given incompatible meaning.
• Importance: Stable interfaces permit databases to evolve as storage technology, workload, and organizational requirements change.

VIII. Forms of Independence — Physical and Logical

A. Types of data independence

The two principal types of data independence differ according to the schema level being changed and the higher level being protected.

1. Physical data independence

   • Meaning: The internal schema can change without altering the conceptual or external schemas.
   • Concrete changes: Creating an index, changing file organization, partitioning a table, compressing records, or moving data to new storage should not alter application queries.
   • Example: Adding an index on STUDENT(StudentID) may improve retrieval speed, but the table definition and SELECT statements remain the same.
   • Relative difficulty: It is generally easier to achieve because physical details are strongly hidden by the storage manager.

2. Logical data independence

   • Meaning: The conceptual schema can change without forcing changes to external views or application programs.
   • Concrete changes: Adding an attribute, introducing a new entity, or splitting one relation into related relations may be hidden through views and mappings.
   • Example: Adding Email to STUDENT need not affect an application that retrieves only StudentID and Name.
   • Relative difficulty: It is harder to achieve because applications often depend directly on logical structures and meanings.

• Explicit contrast: Physical independence protects logical definitions from storage changes; logical independence protects user views and programs from conceptual changes.
• Role of views: A view can preserve an expected external structure after logical redesign, provided the required data and meaning remain available.