Unit 1: Database Concepts and Architecture [4 Hours]

Databases are the backbone of every modern information system, from hospital records to banking systems to e-commerce platforms. This unit introduces the core concepts of a database environment: what a database is, how a Database Management System (DBMS) manages it, and how data is organized through models, schemas, and instances. It then examines the three-schema architecture that allows data independence, the languages and interfaces that users rely on, the overall database system environment, and finally the different architectures and classification schemes used to organize DBMSs. Together, these topics form the conceptual map of how enterprise-grade data systems are built and used.

Tested Topics

1. Database languages and DDL — 3x (2024, 2023, Model Q)
2. Database users and DBA roles — 3x (2024, 2024, 2023)
3. Three-schema architecture and levels of abstraction — 2x (2023, Model Q)
4. Data independence — 1x (Model Q)
5. Data models — 1x (2023)

1.1 Database

Definition

A database is an organized, shared collection of logically related data and a description of that data, designed to meet the information needs of an organization.

A database is like a well-organized digital filing cabinet for an entire organization. Instead of each department keeping its own scattered files, everything — student records, courses, grades, payments — is stored in one structured system, ready to be searched, updated, and shared in seconds. Every piece of data lives in exactly one place, and every part of the organization reads from that same place.

Detailed Explanation

Before databases existed, organizations stored information in separate paper files or independent computer files. Each department maintained its own copy of customer or student data. When data had to be updated, it had to be changed in every file manually. This created two serious problems: data redundancy (the same data stored in many places unnecessarily) and data inconsistency (different files disagreeing because updates were missed).

A database solves these problems by storing each fact exactly once in a centralized, structured format. In a relational database — the dominant type — data is organized into tables. Each table represents one entity type:

Tables relate to each other through shared keys. A Courses table and an Enrollments table can be joined to answer: "Which students are enrolled in DBMS this semester?" without duplicating student information inside the Courses table.

Key Characteristics of a Database

• Self-describing nature: the database stores not only the raw data but also a description of that data (called metadata) in a system catalog.
• Insulation between programs and data: application programs do not need to know how data is physically stored; the DBMS handles that layer.
• Support for multiple views: different users see only the portion of the database relevant to their role.
• Sharing of data and multiuser transaction processing: many users can read and modify the database simultaneously without corrupting each other's work.
• Controlled redundancy: data is stored in one place, preventing duplication and inconsistency.

1.2 Database Management System (DBMS)

Definition

A Database Management System (DBMS) is a collection of programs that enables users to create, maintain, and use a database by acting as an intermediary between users and the stored data.

If the database is a warehouse stocked with goods, the DBMS is the warehouse management software — it decides where items go, who can access them, how to find them quickly, and how to recover from accidents. Users never touch the raw storage directly; every request goes through the DBMS.

Detailed Explanation

The DBMS sits between the user (or application program) and the physical database. It accepts requests expressed in a high-level query language, translates them into low-level file operations, executes them, and returns the result. This separation means the user does not need to know anything about disk layout or file structures.

Functions of a DBMS

• Data definition: allows users to define the structure of the database — what tables exist, what columns they have, and what constraints apply.
• Data manipulation: allows Create, Read, Update, and Delete (CRUD) operations on stored data.
• Data security and authorization: enforces access rules so different users see only what they are permitted to see.
• Concurrency control: manages simultaneous access by many users so that transactions do not interfere with each other.
• Backup and recovery: maintains logs and backup copies to restore the database to a consistent state after a failure.
• Integrity enforcement: applies constraints automatically — for example, a student's GPA cannot be negative, and every enrollment record must refer to a valid student.

How a Request Flows Through a DBMS

User writes query → DBMS parses and validates syntax → DBMS checks user permissions → DBMS generates and optimizes an execution plan → DBMS retrieves or modifies data on disk → DBMS returns the result to the user

A typical query using Structured Query Language (SQL):

SELECT Name, Semester FROM Students WHERE Faculty = 'BBA';

The DBMS processes this and returns only BBA students' names and semesters without exposing the raw data file.

Advantages of Using a DBMS

1.3 Database Users

Definition

Database users are the people or systems that interact with a database, each with different levels of access, technical expertise, and purposes.

Think of a hospital database. Doctors look up patient records; receptionists book appointments; the IT team builds the database; managers run summary reports; and an automated billing system generates invoices overnight. All of these are "users" of the same database, but each one interacts with it differently and with different permissions.

Detailed Explanation

A database system must serve very different kinds of people. The DBMS provides separate interfaces and permission levels for each category of user.

Types of Database Users

1. Database Administrator (DBA)

The Database Administrator (DBA) is the person responsible for the overall management of the database system. The DBA has the highest level of authority and access.

Roles and responsibilities of a DBA:

• Defining and enforcing the database schema and integrity constraints
• Granting access privileges to other users
• Monitoring performance and tuning the system
• Planning and executing backup and recovery procedures
• Managing storage allocation and database security
• Upgrading and maintaining the DBMS software
• Liaising between end users, application developers, and management

2. Database Designers

Database designers are responsible for identifying the data to be stored, the relationships between data, and the constraints. They design the logical and physical structure of the database before it is populated. They work closely with users to understand requirements and translate them into schemas.

3. End Users

End users are the people who query and update the database in day-to-day operations. They are further divided into:

• Casual end users: access the database occasionally and use a menu-driven or forms-based interface. Example: a manager who runs a quarterly report.
• Naive (parametric) end users: perform a fixed set of queries and updates repeatedly without knowing SQL. Example: a bank teller processing withdrawals using a pre-built interface.
• Sophisticated end users: familiar with database concepts; write their own SQL queries. Example: a business analyst generating custom financial reports.
• Stand-alone users: maintain personal databases using ready-made packages. Example: a freelancer tracking invoices in a desktop database application.

4. System Analysts and Application Programmers (Software Engineers)

System analysts determine user requirements. Application programmers implement those requirements by writing software that interacts with the DBMS. They design forms, reports, and transaction programs that end users ultimately use.

1.4 Benefits of Databases

Definition

The benefits of databases refer to the advantages a well-designed database system offers over traditional file-based or manual data management approaches.

Before databases, organizations stored data in separate paper files or individual computer files per department. The database approach solves the problems that arise from that fragmented model.

Detailed Explanation

1.5 Data Models, Schemas, and Instances

Definition

A data model is a collection of concepts used to describe the structure of a database, including what data types exist, how they relate, and what constraints apply to the data.

A data model is the blueprint of a database. Just as an architect draws floor plans before a building is constructed, a database designer uses a data model to plan what tables will exist, what columns they will have, and how they connect — before a single row of real data is stored.

Detailed Explanation

Types of Data Models

1. Hierarchical Model

Organizes data in a tree structure where each record has one parent and can have multiple children. Navigation is top-down. Relationships are one-to-many only. Used in early IBM systems (IMS). Limitation: cannot represent many-to-many relationships directly.

2. Network Model

Extends the hierarchical model by allowing a record to have multiple parents. Data is organized as a graph rather than a tree. More flexible but complex to navigate. Example: CODASYL databases.

3. Relational Model

The dominant model today. Data is organized into tables (relations). Rows are records; columns are attributes. Tables are linked through keys. The query language is SQL. Simple, powerful, and well understood.

4. Entity-Relationship (ER) Model

A high-level conceptual model used at the design stage (before choosing a DBMS). It represents data as entities (objects, such as Student or Course), attributes (properties of entities), and relationships (associations between entities). Typically drawn as an ER diagram and then translated into a relational schema.

5. Object-Oriented Model

Represents data as objects, as in object-oriented programming. Supports complex data types, methods, and inheritance. Used when the application domain involves complex objects (e.g., multimedia, CAD systems).

6. Object-Relational Model

Extends the relational model with object-oriented features such as user-defined data types and inheritance. Modern systems like PostgreSQL implement this hybrid.

Schema

A schema is the description or blueprint of the entire database structure. It specifies what tables exist, what columns each table has, the data types of those columns, and the constraints that apply. The schema is defined once using the Data Definition Language (DDL) and rarely changes. Think of the schema as the design template — the database's constitution.

Example schema (written informally):

Students(StudentID: INT, Name: VARCHAR, Faculty: VARCHAR, Semester: INT)
Courses(CourseID: INT, CourseName: VARCHAR, Credits: INT)
Enrollments(StudentID: INT, CourseID: INT, Grade: CHAR)

Instance

An instance (also called the database state or snapshot) is the actual data stored in the database at a particular moment in time. While the schema stays fixed, the instance changes constantly as data is inserted, updated, and deleted. The schema is the template; the instance is today's content filling that template.

1.6 Three-Schema Architecture and Data Independence

Definition

The three-schema architecture is a framework that separates the database system into three distinct levels — external, conceptual, and internal — to isolate users from physical storage and provide data independence.

Imagine a theatre: the audience (external level) sees only the performance. The stage manager (conceptual level) coordinates the full production. The backstage crew (internal level) operates the lighting rigs and machinery. Each group has a completely different view of the same show, and changes to the backstage machinery do not disrupt what the audience sees. Three-schema architecture applies this same separation to databases.

Detailed Explanation

Proposed by the American National Standards Institute (ANSI) and Standards Planning and Requirements Committee (SPARC), the three-schema architecture (also called the ANSI/SPARC architecture) defines how data is organized and accessed at three separate levels.

The Three Levels

1. External Level (View Level)

The external level is the highest level. It describes how different users see the data. Each user group or application has its own external schema (also called a view), which presents only the portion of the database relevant to that user.

Example: A student views only their own grades and courses. The finance department views only fees and payments. Both are views of the same database but neither sees the other's data.

Multiple external schemas can exist simultaneously on top of a single conceptual schema.

2. Conceptual Level (Logical Level)

The conceptual level is the middle level. It describes the structure of the entire database for the organization, hiding the details of physical storage. The conceptual schema defines what tables exist, what data types each column has, relationships between tables, integrity constraints, and security policies.

This is the level that database designers work at. It represents the full logical view of the entire database, independent of any specific user and independent of how data is physically stored.

3. Internal Level (Physical Level)

The internal level is the lowest level. It describes how data is physically stored on disk: the storage format, file organization, indexing, compression methods, and access paths. The internal schema deals with physical implementation details.

This is the level that the DBMS and storage engineers interact with. End users never see or interact with the internal level.

Mappings Between Levels

The DBMS provides mappings that translate between levels:

• External/Conceptual mapping: translates between a specific external view and the full conceptual schema. Allows each user to see their own subset of the database.
• Conceptual/Internal mapping: translates between the conceptual schema and the physical storage layout. Allows the physical implementation to change without altering the logical schema.

Data Independence

Data independence is the ability to change the schema at one level of the three-schema architecture without changing the schema at the next higher level. It is the primary benefit of the three-schema architecture.

There are two types:

1. Physical Data Independence

The ability to change the internal schema (how data is physically stored) without having to change the conceptual schema or any external schemas. Example: switching from unindexed flat files to B-tree indexed files to speed up queries — the logical structure (tables, columns, relationships) remains unchanged, and no application program needs to be rewritten.

2. Logical Data Independence

The ability to change the conceptual schema (the logical structure of the database) without having to change external schemas or application programs. Example: adding a new column to the Students table, or adding a new table. Existing views and applications that do not use the new column continue to work unchanged.

Logical data independence is harder to achieve than physical data independence because changes to the logical structure are more likely to affect user-facing views.

1.7 Database Languages and Interfaces

Definition

Database languages are the formal languages provided by a DBMS that allow users and application programs to define, manipulate, and control data in a database. Database interfaces are the interaction mechanisms (tools and environments) that allow different types of users to communicate with the DBMS.

A database language is to a DBMS what a spoken language is to a conversation: the agreed-upon set of commands that both sides understand. A bank teller cannot speak raw binary to the server; a query language like SQL translates human intent into database operations the DBMS executes.

Database Languages

1. Data Definition Language (DDL)

The Data Definition Language (DDL) is used to define, modify, and delete the structure (schema) of database objects such as tables, views, indexes, and constraints. DDL commands operate on the schema, not on the data within it.

Core DDL commands:

• CREATE: creates a new database object (table, view, index).
• ALTER: modifies the structure of an existing object (adds or drops a column).
• DROP: permanently deletes a database object and all its data.
• TRUNCATE: removes all rows from a table without deleting the table structure.

-- DDL example: create a Students table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name      VARCHAR(100),
    Faculty   VARCHAR(50),
    Semester  INT
);

2. Data Manipulation Language (DML)

The Data Manipulation Language (DML) is used to insert, retrieve, update, and delete data within database objects that already exist. DML operates on the data (instances), not the structure.

Core DML commands:

• SELECT: retrieves data from one or more tables.
• INSERT: adds new rows to a table.
• UPDATE: modifies existing rows.
• DELETE: removes rows from a table.

-- DML example: retrieve all BBA students
SELECT Name, Semester FROM Students WHERE Faculty = 'BBA';

DML can be procedural (specifying both what data is needed and how to navigate to it) or non-procedural/declarative (specifying only what is needed; the DBMS figures out how). SQL's SELECT is declarative — the user states the desired result; the DBMS chooses the access path.

3. Data Control Language (DCL)

The Data Control Language (DCL) manages access rights and permissions on database objects. DCL commands control who can do what.

• GRANT: gives a user permission to perform specific operations (SELECT, INSERT, UPDATE, DELETE) on specific objects.
• REVOKE: removes previously granted permissions.

-- DCL example: allow user 'anita' to query the Students table
GRANT SELECT ON Students TO anita;

4. Transaction Control Language (TCL)

The Transaction Control Language (TCL) manages database transactions to ensure data consistency and integrity.

• COMMIT: permanently saves all changes made in the current transaction.
• ROLLBACK: undoes all changes made in the current transaction, reverting the database to its state at the last COMMIT.
• SAVEPOINT: sets a point within a transaction to which a partial ROLLBACK can be performed.

Database Interfaces

Different users need different ways to interact with the DBMS. The major interface types are:

• Menu-based interfaces: present lists of options; the user selects commands rather than typing them. Used by casual end users in web portals and desktop applications.
• Forms-based interfaces: display a form with fields. The user fills in values and submits. Common in banking and airline reservation systems. Naive users can operate these without knowing SQL.
• Graphical User Interface (GUI): provides point-and-click tools to build queries visually without writing SQL. Example: query-by-example grids in Microsoft Access.
• Natural language interfaces: accept queries in everyday English or another natural language and translate them into SQL. Still limited and error-prone for complex queries.
• SQL command interface / query tool: a text window where users type SQL directly. Used by sophisticated users, developers, and DBAs. Examples: MySQL Workbench, pgAdmin, SQL Server Management Studio.
• Application programming interface (API): allows programs written in other languages (Python, Java, Node.js) to send SQL queries to the DBMS and receive results. Examples: JDBC for Java, psycopg2 for Python.
• Mobile interfaces: lightweight interfaces for smartphones and tablets, typically connecting to the database through a web server and API layer.

1.8 The Database System Environment

Definition

The database system environment refers to the complete set of hardware, software, data, users, and procedures that together form an operational database system. It describes the components of the system and how they interact.

A database system is not just one program. It is an entire ecosystem: the physical hardware the data is stored on, the DBMS software managing it, the application programs users interact with, the people using those programs, and the data itself — all working in concert.

Components of the Database System Environment

1. Hardware

The physical devices on which the database system operates. Includes servers, storage devices (hard disks, SSDs), network infrastructure, and client machines. The capacity and speed of hardware directly affects database performance.

2. Software

The software layer sits between the hardware and users. It includes:

• The DBMS software itself (MySQL, PostgreSQL, Oracle).
• The operating system that runs the DBMS.
• Application programs that interact with the DBMS on behalf of end users.
• Network software enabling remote database access.

3. Data

The most important asset in the environment. Includes:

• Operational data: the actual data used in day-to-day operations.
• Metadata: data about the structure of the database (stored in the system catalog or data dictionary). Describes table names, column types, indexes, and constraints.

4. Procedures

The instructions and rules that govern the design and use of the database system. Includes backup procedures, security procedures, user access guidelines, and disaster recovery plans.

5. People

All the human roles in the database environment: Database Administrator, Database Designers, Application Programmers, End Users (as covered in section 1.3).

The System Catalog (Data Dictionary)

A critical component of the DBMS environment is the system catalog (also called the data dictionary). It is a repository of metadata — information about the database itself. The system catalog stores:

• Names and structures of all tables, views, and indexes
• Column names, data types, and constraints for every table
• User accounts and access privileges
• Statistics used by the query optimizer to choose efficient access paths

The DBMS uses the system catalog internally whenever it processes a query. DBAs and developers can query the catalog to inspect the database structure without reading the actual data.

1.9 Centralized and Client/Server Architectures for DBMSs

Definition

The architecture of a DBMS system describes how the database processing is distributed across hardware components — specifically, where the DBMS software and data reside, and where query processing takes place.

Architecture answers the question: who does the work, and where is the data? In some systems, one powerful machine does everything. In others, work is split between many client machines and one or more servers. The answer has major implications for performance, cost, scalability, and management.

Centralized Architecture

In a centralized architecture, the entire database system — the DBMS software, the database, and the processing logic — runs on a single computer (a mainframe or dedicated server). Users access the system through dumb terminals: devices with a keyboard and display but no processing power of their own. All computation happens on the central machine.

Characteristics:

• Single point of control: easier to maintain, back up, and secure.
• Single point of failure: if the central machine goes down, no user can access the database.
• Limited scalability: adding more users stresses a single machine.
• Simple management: one machine to manage, patch, and monitor.

Centralized architecture was common in the mainframe era and is still used in some high-security environments where strict control is essential.

Client/Server Architecture

In a client/server architecture, database processing is split between client machines and a server machine. The server (back-end) runs the DBMS and stores the database. The client (front-end) runs application programs that send requests to the server over a network.

This architecture introduced the concept of a query server (also called a transaction server or SQL server): the client sends an SQL query; the server processes it and returns only the result, not the raw data files. This is far more efficient than transferring entire files to the client (as happened in older file-server architectures).

Two main variants:

Two-Tier Architecture

Client communicates directly with the database server. The application logic can reside on the client (thick client) or partially on both sides. Simple to set up for small numbers of users; becomes difficult to manage at scale.

Client (application + UI) → Server (DBMS + database)

Three-Tier Architecture

An application server layer is added between the client and the database server. The client presents a user interface. The application server contains the business logic. The database server manages data. Web applications use this model almost universally.

Client (browser/UI) → Application server (business logic) → Database server (DBMS + data)

1.10 Classification of Database Management Systems

Definition

The classification of DBMSs refers to categorizing database management systems according to different criteria such as the data model they use, the number of users they support, the number of sites the database is distributed across, and the cost or access model.

Just as cars can be classified by fuel type (petrol, diesel, electric), by number of seats, or by use (city car, truck, sports car), DBMSs can be classified in multiple ways — and a single DBMS can fall into several categories simultaneously.

Classification by Data Model

This is the most common basis for classification. The data model determines how data is organized and queried:

Classification by Number of Users

• Single-user DBMS: supports only one user at a time. Used on personal computers for individual applications. Example: Microsoft Access (in single-user mode), personal SQLite databases.
• Multi-user DBMS: supports many concurrent users simultaneously. Requires concurrency control mechanisms. Used in enterprise environments. Example: Oracle, MySQL, PostgreSQL.

Classification by Number of Sites

• Centralized DBMS: the entire database and DBMS reside on a single computer at a single location.
• Distributed DBMS (DDBMS): the database is distributed across multiple physical locations (sites) connected by a network. Users interact with it as if it were a single database. A homogeneous DDBMS uses the same DBMS at all sites; a heterogeneous DDBMS uses different DBMSs at different sites.

Classification by Cost / Access Model

• Commercial DBMS: proprietary, sold under a license. Examples: Oracle Database, Microsoft SQL Server, IBM Db2.
• Open-source DBMS: freely available, community-maintained. Examples: MySQL, PostgreSQL, MariaDB, SQLite.
• Cloud-based DBMS: delivered as a managed service over the internet. Users do not manage servers. Examples: Amazon RDS, Google Cloud SQL, Azure SQL Database.

Classification by Purpose

• General-purpose DBMS: designed to serve a wide variety of applications (most relational systems).
• Special-purpose DBMS: optimized for a specific use case, such as real-time processing, scientific data, time-series data, or spatial data.

Extra: Topics Asked in Exams But Not in Syllabus

These topics have appeared in past papers. They are not listed in the syllabus but you should know them.

Data Redundancy and Data Inconsistency (2023 exam)

Data redundancy means the same piece of data is stored in more than one place unnecessarily. For example, a student's address stored separately in the admissions system, the accounts department, and the library system. When the address changes, every copy must be updated. If even one update is missed, the records disagree — this is data inconsistency. Redundancy is the cause; inconsistency is the consequence. The database approach eliminates redundancy by storing each fact in exactly one place (controlled through normalization), so any update propagates automatically.

Exam Questions

1. Define Database Language. (November, 2024)
2. Who is DBA? List the roles of DBA. (November, 2024)
3. Explain any four types of Database user. (November, 2024)
4. Define Data Model. (March - April, 2023)
5. Why level of abstraction is maintained in database? (March - April, 2023)
6. What are the types of database user? Explain each of them in brief. (March - April, 2023)
7. Identify and explain different types of database languages with example. (March - April, 2023)
8. Define data independence. (Model Q)
9. What is DDL? (Model Q)
10. Explain three-schema architecture of database in detail. (Model Q)

Whole Chapter Summary

Database & DBMS: A database is an organized, shared collection of related data. A DBMS is the software intermediary that manages storage, retrieval, security, and integrity — decoupling users and applications from raw data files.

Database Users: Users range from the DBA (highest authority: schema, security, backup, performance) to database designers, sophisticated users writing SQL, and naive end users operating pre-built forms and interfaces. Application programmers build those interfaces.

Data Models, Schemas, Instances: A data model describes how data is organized (relational model is dominant). The schema is the fixed blueprint of the database structure. The instance is the actual data at a given moment — it changes constantly while the schema stays stable.

Three-Schema Architecture & Data Independence: The three levels (external user views, conceptual logical structure, internal physical storage) are kept separate so changes at one level do not cascade. Physical independence protects the logical schema from storage changes; logical independence protects user views from logical schema changes.

Database Languages: DDL defines structure (CREATE, ALTER, DROP). DML manipulates data (SELECT, INSERT, UPDATE, DELETE). DCL controls permissions (GRANT, REVOKE). TCL manages transactions (COMMIT, ROLLBACK). SQL is the standard language for all four in relational systems.

Database System Environment: A complete database system is hardware + DBMS software + operating system + application programs + data + metadata (system catalog) + procedures + people. The system catalog stores all metadata the DBMS needs to process queries.

Centralized vs. Client/Server Architectures: Centralized: single machine, all processing in one place, simple but not scalable. Client/server: server runs DBMS; clients send queries; results travel the network, not raw files. Three-tier (client → app server → DB server) is the standard for web applications.

DBMS Classification: Classified by data model (relational, hierarchical, NoSQL, etc.), number of users (single vs. multi-user), number of sites (centralized vs. distributed), and cost model (commercial, open-source, cloud).

Last-Minute Revision Sheet