DBMS Basics & Architecture

1. What is a Database Management System (DBMS) and how does it differ from a Relational Database Management System (RDBMS)?

A DBMS is software that lets you store, manage, and retrieve data in an organized way. It gives you a layer of tools between the raw data and the people or programs using it.

An RDBMS is a specific kind of DBMS. The difference comes down to structure:

• A general DBMS stores data in files and may not enforce any relationships between them.
• An RDBMS organizes data into tables, where rows are records and columns are fields. Tables are connected through keys, and it enforces rules like referential integrity.

A simple way to think of it: every RDBMS is a DBMS, but not every DBMS is relational. RDBMS is the version that adds tables, keys, and relationships.

Example: If you store customer records and their orders in separate files with a plain DBMS, nothing stops you from deleting a customer whose orders still exist. In an RDBMS, a foreign key would refuse that delete unless the related orders are handled first.

2. What are the three levels of Data Abstraction?

Data abstraction hides implementation details behind layers. The three levels are:

• Physical level (internal): how data is actually stored on disk — files, indexes, blocks. Lowest level, hidden from users.
• Logical level (conceptual): what data exists and how it relates — tables, columns, constraints. This is what database designers work with.
• View level (external): what individual users see — custom views exposing only the parts relevant to them.

A cashier sees only the orders view. A manager sees a sales summary view. Neither knows (or cares) about how the data is physically stored. Abstraction lets each user see only what they need.

3. What is a Database Schema vs. Instance?

• Schema: the blueprint — the structure of the database. Tables, columns, constraints, relationships. It’s designed once and changes rarely.
• Instance: the actual data in the database at a particular moment. It changes constantly as rows are added, updated, and deleted.

Think of a building: the schema is the architect’s floor plan, the instance is the current set of furniture inside. Same plan, different contents at different times.

4. What is Data Integrity?

Data integrity means the data in the database is accurate, consistent, and reliable over its whole lifetime.

The main types:

• Entity integrity — every row is uniquely identifiable (primary keys, no NULL in key columns).
• Referential integrity — foreign keys correctly reference existing rows (no orphan records).
• Domain integrity — values fall within allowed ranges/types (e.g., age can’t be negative).
• User-defined integrity — business rules enforced via constraints (e.g., “salary ≥ minimum wage”).

These are enforced with primary/foreign keys, NOT NULL, CHECK, and UNIQUE constraints — the database refusing to store bad data, rather than hoping the application never sends it.

5. What is Data Independence in a DBMS?

Data independence means you can change how data is organized at one level without disturbing the levels above it.

There are two kinds:

• Physical data independence — changing how data is stored (new indexes, different file layout) without affecting the logical schema or the applications using it.
• Logical data independence — changing the logical schema (adding a column, restructuring a table) without breaking the views the applications see.

The whole point is insulation. An app that reads a users view shouldn’t care whether the database added a new index or reorganized the underlying storage.

6. What is a Synonym in a database?

A synonym is a permanent logical alias for a database object — a table, view, sequence, or procedure.

Example:

CREATE SYNONYM emp FOR hr.employees;
SELECT * FROM emp;

emp is now an alternate name for hr.employees. Any query using emp hits the employees table.

Synonyms are useful for hiding the real owner/schema of an object and simplifying references — applications can point at a synonym while the underlying table is renamed or moved, without touching the application code.

7. What is the purpose of the Data Dictionary (or System Catalog) in a DBMS?

The data dictionary is a special set of tables that stores metadata — data about the data. It knows the structure of everything in the database.

It holds:

• Table and column definitions
• Constraints (primary keys, foreign keys, unique)
• Indexes
• User accounts and privileges

When you run SHOW TABLES, DESCRIBE, or EXPLAIN, the database reads the data dictionary to answer. When you CREATE TABLE, the dictionary grows.

You don’t normally interact with it directly — the DBMS manages and consults it behind the scenes, but it’s the source of truth for the entire schema.

8. What is the difference between a Physical View and a Logical View of data?

The physical and logical views describe the same data at different levels of detail.

• Physical view — how data actually lives on disk: files, pages, blocks, indexes, byte layouts.
• Logical view — how users and applications perceive it: tables, rows, columns, and the relationships between them.

A user sees a neat employees table with clean columns. On disk, those rows are scattered across pages, possibly interleaved with index structures. The user never needs to know the physical reality — the DBMS translates between the two views.

9. What is the purpose of a Three-Schema Architecture in a DBMS?

The three-schema architecture separates the database into three levels so that each layer can change without breaking the others:

• View (external) schema — what individual users see: custom views for specific roles.
• Logical (conceptual) schema — the whole logical structure: tables, columns, relationships.
• Physical (internal) schema — how data is actually stored on disk.

The payoff is data independence:

• Change the physical layout → the logical and view schemas are untouched (physical independence).
• Change the logical schema → views are updated to match, users don’t see the difference (logical independence).

Users at the view level never know what happens at the physical level.