What a DBMS Is and Why It Exists
A DBMS is the software layer between users and stored data. It exists to give many users controlled, concurrent, recoverable and consistent access to shared data without every application solving those problems itself.
-
DBMS Fundamentals
- Data, Information and Databases
- What a DBMS Is and Why It Exists
- File System versus DBMS
- Advantages and Limitations of a DBMS
- Database Users and the Role of the DBA
- Three Level Architecture and Data Abstraction
- Logical and Physical Data Independence
- Schema, Instance and Metadata
- Database Applications and the Database System Environment
- Database Architecture
- Data Models
-
ER Model
- Entities, Entity Sets and Entity Types
- Types of Attributes in the ER Model
- Keys in the ER Model
- Relationships, Relationship Sets and Degree
- Cardinality and Participation Constraints
- Strong and Weak Entities
- Drawing and Reading ER Diagrams
- Extended ER: Generalisation, Specialisation and Aggregation
- Converting an ER Diagram into Relational Tables
- ER Design Projects
- Relational Model
- Relational Algebra
- Functional Dependencies
-
Normalisation
- Why Normalisation Exists: Anomalies and Redundancy
- First Normal Form
- Second Normal Form and Partial Dependency
- Third Normal Form and Transitive Dependency
- BCNF and BCNF Decomposition
- 4NF, 5NF, Multivalued and Join Dependencies
- Lossless Decomposition and Dependency Preservation
- Complete Worked Normalisation: Unnormalised to BCNF
- Denormalisation and When to Use It
Concept
A Database Management System is software that sits between the people or programs asking questions and the data stored on disk. Every request goes through it, and it is responsible for answering correctly, safely and at the same time as everyone else.
Its job can be stated in one sentence: a DBMS turns a pile of bytes on a disk into a shared, queryable, protected and recoverable resource.
What a DBMS actually does
| Function | What it means |
|---|---|
| Define | Create and change the structure — tables, columns, types, constraints — and store that structure as metadata. |
| Construct | Load data into the storage the DBMS controls. |
| Manipulate | Query, insert, update and delete, and return results in a defined form. |
| Share | Let many users and programs work at the same time without corrupting each other work. |
| Protect | Enforce who may see and change what, and survive hardware and software failure. |
| Maintain | Let the system evolve — new columns, new indexes, new users — without rewriting applications. |
Architecture
users applications admin tools
| | |
+--------------------+--------------------+
|
+-----v------+
| DBMS |
+------------+
| query proc | parse, optimise, execute
| txn manager| atomicity, isolation
| storage mgr| buffers, files, indexes
| recovery | logs, checkpoints
| security | authentication, privileges
+-----+------+
|
+--------v---------+
| database + the |
| data dictionary |
+------------------+Notice that the data dictionary sits inside the managed store. The DBMS reads its own catalog to know what your tables look like.
Why it exists: the five problems it removes
- Redundancy. The same fact stored in several places drifts out of step. A DBMS lets one fact live in one place and be referenced from everywhere else.
- Inconsistency. When redundancy exists, two copies eventually disagree. Constraints let the DBMS refuse the update that would cause it.
- Concurrent access. Two people editing the same row at the same time can silently destroy one edit. Transactions and locking make the outcome defined.
- Failure. A crash halfway through a money transfer must not leave money missing. Logging and recovery guarantee all or nothing.
- Uncontrolled access. Not everyone should read salaries. Privileges and roles make access a rule rather than an honour system.
Example
The classic illustration, because it exercises four of the five problems at once.
Transfer 5,000 from account A to account B
step 1 read balance of A
step 2 subtract 5,000 from A
step 3 read balance of B
step 4 add 5,000 to B
Crash after step 2, without a DBMS:
5,000 has vanished. Nothing in the file system knows
that steps 2 and 4 belonged together.
Crash after step 2, with a DBMS:
the transaction never committed, so recovery undoes
step 2 from the log. The money is back in A.
This is ATOMICITY, and it is the single clearest
reason a DBMS exists.Important terminology
| Term | Meaning |
|---|---|
| DBMS | The software managing the database. |
| RDBMS | A DBMS built on the relational model, where all data is held in tables. |
| Database system | Database, DBMS and the applications together. |
| Query processor | Parses, optimises and executes requests. |
| Storage manager | Moves data between disk and memory and manages files and indexes. |
| Transaction manager | Keeps concurrent work correct and atomic. |
| System catalog | Where the DBMS keeps its own metadata. |
DBMS and RDBMS compared
| Point | DBMS | RDBMS |
|---|---|---|
| Data organisation | Any structure — files, hierarchies, networks | Tables of rows and columns |
| Relationships | Often by pointers or physical links | By values, through keys |
| Normalisation | Not necessarily supported | Central to the design method |
| Constraints | Limited or application enforced | Declared and enforced by the system |
| Multi user | Sometimes single user | Designed for many users |
| Examples | Early file based and hierarchical systems | MySQL, MariaDB, PostgreSQL, Oracle, SQL Server |
Every RDBMS is a DBMS. The reverse is not true. State it that way in an interview and the follow up question is usually about how relationships are represented, so mention by values through keys.
Common mistakes
- Saying a DBMS is just a place to store data. Storage is the least interesting part. Concurrency, recovery and integrity are the reasons it exists.
- Claiming an RDBMS removes all redundancy. It reduces uncontrolled redundancy. Foreign keys are a deliberate, controlled repetition of a value.
- Confusing the DBMS with the query language. SQL is a language; the DBMS is the software that implements it.
Exam and interview questions
- Define a DBMS and list its six main functions.
- Explain, using the bank transfer, why atomicity cannot be provided by an application alone.
- Differentiate DBMS and RDBMS with at least four points.
- Name the major components of a DBMS and state what each does.
- Which DBMS function does a system catalog support?
Practice
- Take an attendance register kept as a spreadsheet. List four problems that appear when twenty teachers edit it at once, and name the DBMS feature that solves each.
- Write the bank transfer as four steps and mark the point where a crash is most damaging.
- Explain in two sentences why the data dictionary is stored inside the database rather than in the application.
Conclusion
A DBMS exists so that redundancy, inconsistency, concurrency, failure and uncontrolled access are solved once, by the system, instead of badly and repeatedly by every application that touches the data.