What is DBMS? Introduction, Architecture and Types

A database is an organized collection of related data: a college's students, courses and marks, or a bank's customers, accounts and transfers. A database management system (DBMS) is the software that stores that data and lets many people and programs use it at the same time without corrupting it. It decides how the bytes sit on disk, answers questions written in a query language such as SQL, enforces the rules the data must follow, and brings the data back to a correct state after a crash. Almost every application you use keeps its state in one.

What a DBMS does

The definition to say in an interview: a DBMS is a collection of programs that lets users define, create, query, update and administer a database. Behind that sentence sit the services every DBMS provides:

• Data definition: a data definition language (DDL) to describe tables, columns, types and constraints.
• Data manipulation: a data manipulation language (DML) to insert, update, delete and read rows.
• Query processing: an optimizer that turns a declarative query ("what I want") into an efficient plan ("which index, which join order").
• Transaction management: a group of operations happens completely or not at all, even across a crash.
• Concurrency control: many users read and write at once, and each sees a consistent picture.
• Recovery: logs and backups restore the database after a power failure or a lost disk.
• Security and integrity: users get only the access they are granted, and constraints reject invalid data.

Keep three terms apart. The database is the data. The DBMS is the software. The database system is both together with the applications that use them. The DBMS also keeps metadata, data about the data (table definitions, column types, constraints, users and privileges), in a system catalog or data dictionary; in MySQL you can read it through INFORMATION_SCHEMA.

DBMS vs file system

Before databases, every program kept its records in files of its own format. That arrangement fails in predictable ways, and each failure became a feature of the DBMS.

1. Each office keeps its own file of the same students. Every name and city is stored in both files, 4 values held twice, and nothing links a row in one file to its twin in the other.
2. Asha moves to Mumbai and the accounts office updates its file. The exam cell's copy still says Pune, so the two files now disagree and nothing in either can say which one is right.
3. With a DBMS each student is stored once, and the change of city is one UPDATE. Both offices read the same row through their own views, so they cannot disagree, and a new question is a query rather than a new program.

A file system is still the right tool for some jobs: a small single-user tool, logs that are only appended and read in order, or large media files (a database usually stores the video's path, not the video).

Data models

A data model is the set of concepts used to describe a database's structure: what kinds of things exist, how they relate, and which rules they obey.

Models are also grouped by how close they sit to the user. Conceptual models (the ER model) describe data the way people think about it. Representational or logical models (the relational model) describe what the DBMS implements. Physical models describe files, pages and record layouts. The relational model, proposed by E. F. Codd in 1970, became the standard for business data because tables are simple, its query languages are declarative, and its theory of keys, dependencies and normal forms is exact.

The three-schema architecture

A schema is the description of a database, its design. An instance (or state) is the data stored in it at one moment. The schema changes rarely; the instance changes with every insert. The ANSI/SPARC three-schema architecture describes one database at three levels:

The DBMS keeps two mappings: external/conceptual (how a view is computed from the tables) and conceptual/internal (how each table is stored). There can be many external schemas, but there is exactly one conceptual schema and one internal schema.

Data independence

Data independence is the payoff of the three levels: the schema at one level can change without changing the schema at the level above, because only the mapping between them changes.

• Physical data independence: change the internal schema without changing the conceptual schema. Adding an index, moving a table to a faster disk, compressing pages or changing the file organization leaves every table and every query untouched.
• Logical data independence: change the conceptual schema without changing the external schemas or the programs written against them. Adding a column or splitting a table leaves applications that read through views working.

1. One database, described three times: the views each user sees, the tables of the whole database, and the pages and indexes that store them. The DBMS keeps a mapping between each pair of neighbouring levels and translates every query through both.
2. Physical data independence: adding an index on email changes the internal level and its mapping only. The student table and both views are untouched, so no query or program changes.
3. Logical data independence: fee moves to a table of its own. The accounts view is redefined as a join of student and fees, so it shows the same columns as before, and the programs that read through it keep working.

Logical data independence is harder to achieve. Applications depend on the logical structure (table and column names, which table holds what), while almost nothing depends on how pages are laid out. Every relational DBMS gives you physical independence; logical independence holds only as far as views can hide a change.

Database users and the DBA

The DBA's duties are a standard interview list: defining the schema and storage structures, changing them as needs evolve, granting and revoking authorization, planning backups and recovery, monitoring performance and disk space, tuning indexes and queries, and applying upgrades and security patches.

Types of databases and architectures

The labels overlap: PostgreSQL is relational, centralized by default and mostly used for OLTP, while a warehouse is relational but tuned for OLAP.

Applications reach a database in a one-tier, two-tier (client-server) or three-tier arrangement. Web applications are three-tier, which keeps database credentials and business rules off the user's device.

Common mistakes

• Calling MySQL "a database": it is a DBMS that manages many databases.
• Describing the three levels as three databases: they are three descriptions of one database.
• Swapping the two independences: adding an index is physical, adding a column is logical.
• Using "schema" and "instance" as synonyms: the schema is the design, the instance is today's data.
• Claiming a DBMS is always faster than files: it is safer and more general, but a plain file can be faster for one fixed access pattern.

Interview questions

What is a DBMS? Name its main functions. A DBMS is software for defining, storing, querying and administering a database. Its main functions are data definition, data manipulation, query optimization, transaction management, concurrency control, recovery, and security with integrity enforcement.

What problems of file-based systems does a DBMS solve? Redundancy and inconsistency, difficulty of answering new questions, data isolated in different formats, integrity rules hidden in code, partial updates after a crash, concurrent programs overwriting each other, and coarse security. Each maps to a DBMS feature: one shared schema, SQL, constraints, transactions, concurrency control and access control.

Explain the three-schema architecture. It describes one database at the internal (physical storage), conceptual (logical structure of the whole database) and external (per-user views) levels. The DBMS maps between adjacent levels, so a change at one level is absorbed by a mapping instead of reaching the users.

What is the difference between physical and logical data independence? Which is harder? Physical independence means changing storage without changing tables; logical independence means changing tables without changing applications or views. Logical independence is harder, because programs depend on table and column names but not on page layouts.

What is the difference between a schema and an instance? The schema is the structure: table names, columns, types and constraints. The instance is the data present at a given moment. The schema is sometimes called the intension and the instance the extension.

What is a data dictionary? It is the system catalog: metadata describing every table, column, type, index, constraint, user and privilege. The DBMS reads it to check and plan every query; in MySQL it is exposed through INFORMATION_SCHEMA.

What are DDL and DML? DDL (CREATE, ALTER, DROP) defines the structure, and DML (SELECT, INSERT, UPDATE, DELETE) works on the rows. The SQL basics note covers them with DCL and TCL.

What is the difference between two-tier and three-tier architecture? In two-tier, the client connects to the database directly. In three-tier, an application server sits between them, holding business logic and the database connection, which improves security and lets each tier scale on its own.

Next, learn to design a database before building it: ER Model in DBMS. To check the basics, try the SQL (Basic) skill test.