DBMS Fundamentals: Architecture, Data Models, and SQL Languages

Posted on Oct 14, 2026 in Computers

1. DBMS – Introduction

Data

Data means raw facts and figures.

Example: 101, Vikas, CSE, 1st Year

Database

A database is an organized collection of related data that can be easily accessed, managed, and updated.

DBMS

DBMS (Database Management System) is software used to create, store, retrieve, update, and manage data in a database.

Examples: MySQL, Oracle, PostgreSQL, SQL Server.

Functions of DBMS

• Data storage
• Data retrieval
• Data insertion and updating
• Data security
• Backup and recovery
• Concurrency control
• Integrity enforcement

2. DBMS Architecture

DBMS architecture describes how users interact with stored data.

Three-Level Architecture

          USER
           ↓
    ┌──────────────┐
    │ External     │
    │ Level        │
    └──────┬───────┘
           ↓
    ┌──────────────┐
    │ Conceptual   │
    │ Level        │
    └──────┬───────┘
           ↓
    ┌──────────────┐
    │ Internal     │
    │ Level        │
    └──────┬───────┘
           ↓
      DATABASE

1. External Level

• Highest level.
• Shows only the data required by a particular user.
• Different users can have different views.

Example: Student sees marks; accounts department sees fees.

2. Conceptual Level

• Describes the complete logical structure of the database.
• Contains tables, relationships, constraints, etc.

3. Internal Level

• Lowest level.
• Describes how data is physically stored.
• Deals with files, indexes, storage blocks, etc.

3. File Processing System

A file processing system is a traditional system where data is stored in separate files and individual application programs manage those files.

Example

Student File
     ↓
Marks File
     ↓
Fee File
     ↓
Attendance File

Each application may maintain its own data files.

4. Limitations of File Processing System

1. Data Redundancy

Same data may be stored in multiple files.

Example: Student name is stored in both marks and fee files.

2. Data Inconsistency

If duplicated data is changed in one file but not another, different values may exist.

3. Difficulty in Data Access

Obtaining specific information may require writing a new program.

4. Data Isolation

Data is scattered among different files and formats.

5. Integrity Problems

It is difficult to enforce rules on data.

Example: Marks should be between 0 and 100.

6. Security Problems

Providing different access permissions to different users is difficult.

7. Concurrent Access Problems

Multiple users accessing the same data simultaneously can create inconsistencies.

8. Atomicity Problems

If a transaction fails halfway, restoring the correct state is difficult.

9. Backup and Recovery Problems

Recovering data after a system failure is difficult.

5. Advantages of Database System

A DBMS solves many problems of the traditional file system.

Advantages

1. Reduced data redundancy
2. Reduced data inconsistency
3. Data sharing
4. Improved security
5. Data integrity
6. Data independence
7. Backup and recovery
8. Concurrency control
9. Easy data access
10. Centralized data management

One-line answer

A database system provides efficient, secure, consistent, and reliable storage and retrieval of data.

6. Schema

A schema is the overall logical structure or design of a database.

Example:

STUDENT(Roll_No, Name, Branch, Semester)

Here, the structure of the Student table is the schema.

Important point

Schema changes rarely.

7. Instance

An instance is the actual data stored in a database at a particular point in time.

Example:

This actual data is the database instance.

Schema vs Instance

⭐ Easy trick

Schema = Structure
Instance = Information

8. Data Independence

Data independence is the ability to change the database at one level without affecting the next higher level.

There are two types:

A. Physical Data Independence

Changes at the internal/physical level do not affect the conceptual level.

Example

Changing:

• File organization
• Storage method
• Indexing technique

does not require changing the logical database structure.

B. Logical Data Independence

Changes at the conceptual level do not affect external views or applications.

Example

Adding a new attribute to a table without changing existing user views.

Difference

9. Data Dictionary

A data dictionary is a collection of information about the data stored in a database.

It is also called metadata.

Metadata

Metadata = Data about data

For example:

Data dictionary contains

• Table names
• Attribute names
• Data types
• Constraints
• Relationships
• Indexes
• User information

10. Functions of DBA

DBA = Database Administrator

A DBA is responsible for the overall management of a database system.

Functions

1. Database design
2. User authorization
3. Security management
4. Backup and recovery
5. Performance monitoring
6. Storage management
7. Integrity management
8. Concurrency control
9. Database maintenance
10. Managing access permissions

Exam definition

DBA is responsible for managing, securing, maintaining, and ensuring the proper functioning of a database system.

11. Database Languages

Database languages are used to define, manipulate, control, and manage data.

1. DDL – Data Definition Language

Used to define the database structure.

Commands:

• CREATE
• ALTER
• DROP
• TRUNCATE

Example:

CREATE TABLE Student (
    Roll_No INT,
    Name VARCHAR(50)
);

2. DML – Data Manipulation Language

Used to manipulate data.

Commands:

• INSERT
• UPDATE
• DELETE
• SELECT

Example:

INSERT INTO Student VALUES (101, 'Vikas');

3. DCL – Data Control Language

Used to control access and permissions.

Commands:

• GRANT
• REVOKE

4. TCL – Transaction Control Language

Used to manage transactions.

Commands:

• COMMIT
• ROLLBACK
• SAVEPOINT

Quick Revision Table

12. Data Models

A data model is a collection of concepts used to describe the structure of data and relationships among data.

Your syllabus includes:

1. Hierarchical Data Model
2. Network Data Model
3. Relational Data Model
4. E-R Model

13. Hierarchical Data Model

The Hierarchical Data Model organizes data in the form of a tree.

             COLLEGE
                |
       ┌────────┴────────┐
      CSE               ECE
       |
    STUDENTS
    /      \
 Vikas     Rahul

Characteristics

• Tree structure
• Parent-child relationship
• One parent can have many children
• Mainly supports 1:N relationships

Advantages

• Simple structure
• Easy navigation
• Fast access

Disadvantages

• Difficult to represent M:N relationships
• Can cause data redundancy
• Less flexible

14. Network Data Model

The Network Data Model represents data as a graph/network of records.

A record can have multiple parent records.

        Student
        /     \
   Course 1  Course 2
        \     /
         Teacher

Characteristics

• Graph-like structure
• Supports 1:1
• Supports 1:N
• Supports M:N relationships

Advantages

• Flexible
• Handles complex relationships
• Efficient navigation

Disadvantages

• Complex to design
• Difficult to maintain
• Less simple than relational model

15. Relational Data Model

The Relational Data Model represents data using tables.

A table contains:

• Rows → Tuples
• Columns → Attributes

Example:

Advantages

• Simple
• Easy to understand
• Flexible
• Uses SQL
• Easy data manipulation
• Reduces redundancy through normalization
• Widely used

Examples

• MySQL
• Oracle
• PostgreSQL
• SQL Server

16. E-R Model

E-R = Entity-Relationship Model

It is a conceptual model used to represent entities, attributes, and relationships.

Main Components

1. Entity

An entity is a real-world object.

Examples:

• Student
• Teacher
• Course
• Employee

2. Attribute

An attribute is a property of an entity.

For Student:

Student
 ├── Roll_No
 ├── Name
 ├── Branch
 └── Semester

3. Relationship

A relationship shows an association between entities.

Student ─── ENROLLS ─── Course

Common ER Symbols

Simple ER diagram

 [STUDENT] ─── ◇ ENROLLS ◇ ─── [COURSE]

17. Comparison Between Models