Find papers

IT 232

Database Management System

TU BBA · Semester 2 · BBA 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

Difficulty: ★★★ | High-yield focus: Learn the 4 roles of a Database Administrator (DBA), DDL vs DML, and physical vs logical data independence. Key exam mastery: Master the ANSI/SPARC Three-Schema Architecture and evaluate schema mappings for enterprise data independence.

Part 1: Core Concepts (2 Marks)

  • Database Management System (DBMS) (★): A generalized software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and application programs.
  • Database Administrator (DBA) (★): The chief technical custodian responsible for authorizing database access, monitoring resource utilization, tuning performance, acquiring software/hardware resources, and managing backup and recovery protocols.
  • Data Independence: The capacity to alter the database schema at one level of a database system without having to alter the schema at the next higher level.

Part 2: Structural Frameworks (10 Marks)

The ANSI/SPARC Three-Schema DBMS Architecture

Three-Schema DBMS Architecture Diagram

ANSI/SPARC Three-Schema DBMS Architecture
ANSI/SPARC Three-Schema DBMS Architecture
  1. External Level (Individual User Views):
    • Describes the customized part of the database that a specific user group is interested in, hiding the rest of the database.
    • Example: A teller sees account balances; a loan manager sees credit scores.
  2. Conceptual Level (Community Logical View):
    • Describes the structure of the entire database for a community of users, hiding physical storage details.
    • Describes all entities, data types, relationships, user operations, and integrity constraints.
  3. Internal Level (Physical Storage View):
    • Describes the physical storage structure of the database using a physical data model.
    • Details record formats, file organizations, B+ tree indexes, hashing schemes, and storage access paths.
  4. Mappings & Data Independence:
    • Logical Data Independence: Modifying conceptual schema (adding entity/attribute) without altering external schemas or application programs.
    • Physical Data Independence: Modifying internal schema (reorganizing files, creating new indexes) without altering the conceptual schema.

Part 3: Distinction Tables

Dimension Traditional File-Processing System Database Management System (DBMS)
Data Redundancy High; same data duplicated across separate application files. Controlled and minimized through centralized relational design.
Data Isolation Data scattered across diverse file formats; difficult to access. Unified logical access via standardized query languages (SQL).
Data Independence Zero; application programs tightly coupled to file structures. High; clear separation of external, conceptual, and internal levels.
Concurrency Control Absent; concurrent updates cause overwrites and corrupted data. Robust; ACID transactions, locking protocols, and rollback logs.

Part 4: Worked Problems & Exam Traps

Solved Archetype: Classifying DBMS Languages & DBA Functions (5 Marks) (★)

Problem (TU Past Board): Classify each of the following SQL statements into DDL, DML, or DCL, and explain the core responsibilities of a DBA in each operational area:

  1. CREATE TABLE Student (Roll_No INT PRIMARY KEY, Name VARCHAR(50));
  2. INSERT INTO Student VALUES (101, 'Sunita Sharma');
  3. GRANT SELECT ON Student TO Accounts_Officer;
  4. ALTER TABLE Student ADD COLUMN GPA DECIMAL(3,2);

Solution:

  1. CREATE TABLE: Data Definition Language (DDL) — used to define and modify the conceptual database schema and table structures.
  2. INSERT INTO: Data Manipulation Language (DML) — used to retrieve, insert, update, and delete instance tuples in database tables.
  3. GRANT: Data Control Language (DCL) — used by the DBA to establish access security privileges and permissions.
  4. ALTER TABLE: Data Definition Language (DDL) — schema modification command. DBA Role: Manages schema evolution under DDL, audits high-frequency queries under DML, and enforces role-based access control (RBAC) under DCL.

Students lose marks here because they classify ALTER TABLE as DML because it alters table contents, failing to understand that altering structural metadata (columns, constraints) is strictly DDL.