Find papers

IT 220

Database Management System

TU BIM · Semester 4 · BIM curriculum effective from 2021

Requirement
required
Credits
3
Study Guide
7 units available

Chapter notes

Authoritative study guide across 7 syllabus units

Open a unit to read its study notes.

Unit 1: Database Concepts and Architecture· 4 LHs· ~5 marks

Suggest correction

Pass path: Memorize Three-Schema Architecture layers, logical vs physical data independence, and DBA duties. Top path: Provide detailed comparison of file processing vs DBMS, explain ANSI/SPARC 3-schema mappings with DDL/DML compilation pipelines.

Part 1: Core Concepts (2 Marks)

★ Database Management System (DBMS): A comprehensive software system enabling users to define, create, maintain, and control access to structured persistent collections of related data, enforcing ACID properties and abstracting physical storage. ★ Physical vs Logical Data Independence: Physical data independence is the capacity to alter internal schema storage structures (indexing, file clustering, disk allocation) without requiring alterations to conceptual schemas; Logical data independence is the capacity to modify the conceptual schema (adding entity attributes or relation splits) without altering external views or application programs. ★ Database Administrator (DBA): The primary custodian responsible for authorizing database access, coordinating schema definition, monitoring system performance, managing data integrity/security policies, and executing recovery from hardware failures.

Part 2: Structural Frameworks (10 Marks)

ANSI/SPARC Three-Schema Architecture and Data Independence

  • Concept & Motivation: Proposed to achieve program-data independence and isolate user views from physical hardware reorganization.
  • Three Architectural Levels:
    1. Internal / Physical Level: Describes physical storage structures, access paths, clustering, and file indexes using physical data models.
    2. Conceptual / Logical Level: Describes community-wide database structure, entities, relationships, constraints, and data types without physical hardware concerns.
    3. External / View Level: Defines multiple tailored sub-schemas customized for specific user groups or application interfaces.

Three-Schema Architecture Diagram

Three-Schema Architecture of DBMS
Three-Schema Architecture of DBMS
  • Architectural Mappings:
    • External-to-Conceptual Mapping: Translates requests between specific user queries and global conceptual definitions.
    • Conceptual-to-Internal Mapping: Maps logical entities and relations to actual disk storage blocks and index records.
  • Core Advantages: Decouples evolving physical hardware from enterprise business logic, simplifies application maintenance, and provides robust row/column-level role-based security.

Part 3: Distinction Tables

Criteria Traditional File-Processing System Database Management System (DBMS)
Data Redundancy High; duplicated across distinct department files Controlled; centralized schema minimizes duplicate attributes
Data Consistency Prone to anomalies when duplicate records get updated Enforced through integrity constraints and atomic updates
Data Independence Absent; program logic tightly bound to file structure High; 3-schema architecture provides logical/physical independence
Concurrency Control Limited or operating-system level file locks Granular row/page lock protocols ensuring ACID transactions
Security Control Basic OS permissions on files Granular role-based permissions on views, tables, and columns

Part 4: Worked Problems & Exam Traps

  • Easy: Identify whether adding an index on a student ID column demonstrates logical or physical data independence.
    • Answer: Physical data independence, because an index modifies only the internal storage access path without changing table schemas or user queries.
  • Exam-level: A bank adds a new column pan_number to an existing customer table. Describe the impact on existing counter-clerk views.
    • Answer: Demonstrates logical data independence. The conceptual schema expands to include pan_number, but external schemas and applications ignoring pan_number remain untouched without recompilation.
  • Twist variant: Can 100% complete data independence ever be attained in commercial RDBMS?
    • Answer: No. Significant structural redesigns (such as splitting an existing entity into two normalized relations) typically require corresponding view adjustments or query rewriting.

Students lose marks here because they confuse logical data independence with physical data independence, mistakenly claiming that altering column data types is a physical storage change rather than a conceptual schema modification.