Source: CS 101 Introduction to Database Systems
Tags: database, DBMS, relational database, SQL, NoSQL, data modeling, normalization, entity-relationship, distributed database, MongoDB, MySQL, database administration, DBA
Difficulty: Beginner | Prerequisites: None. Basic familiarity with how software applications store information is helpful but not required.
Databases sit behind nearly every application you use, from banking apps to social media feeds. This topic introduces what databases are, how they are structured, how you interact with them, and who looks after them. It is foundational material for almost everything else in a computer science programme: web development, data science, systems design, and software engineering all assume you understand these concepts. If you are coming in cold, this is the right starting point.
A database is an organised collection of data managed by software called a DBMS. Relational databases store data in tables linked by keys and are queried with SQL. Non-relational (NoSQL) and distributed databases exist to handle use cases where the traditional relational model falls short, such as massive volumes of unstructured data or global-scale performance requirements.
Database
A collection of organised data that can be easily accessed, managed, and updated. Think of it as a structured filing cabinet that software can search through instantly.
Database Management System (DBMS)
Software that creates, maintains, and controls access to a database. In simple terms, the DBMS is the middleman between you and the raw data, handling storage, retrieval, security, and more.
Relational database
A type of database that stores data in tables (also called relations), where each table represents an entity and each row represents a record. Think of it as a collection of spreadsheets that can reference each other.
Key (database key)
A column or set of columns used to uniquely identify rows and create links between tables. In simple terms, keys are the glue that connects related records across different tables.
Primary key
A column (or combination of columns) whose values uniquely identify every row in a table. No two rows can share the same primary key value.
Foreign key
A column in one table that references the primary key of another table, creating a link between the two. This is how relational databases represent relationships.
SQL (Structured Query Language)
A standard language used to query and manipulate data in a relational database. Think of it as the common tongue that lets you ask questions of, and give instructions to, any relational database.
Data modelling
The process of designing a database structure that accurately represents real-world entities and their relationships. In simple terms, it is the blueprint stage before you build the actual database.
Entity-Relationship (ER) modelling
A data modelling technique that represents entities (things) and the relationships between them, typically drawn as a diagram. Think of it as a map showing what exists in the system and how everything connects.
Data normalisation
The process of organising tables to reduce redundancy and dependency. In simple terms, normalisation stops you storing the same piece of information in multiple places, which prevents inconsistencies.
NoSQL (Not Only SQL)
A category of databases that do not use the traditional relational table model. Think of it as the umbrella term for databases designed to handle data that does not fit neatly into rows and columns.
Distributed database
A database spread across multiple computers or servers, often in different physical locations. In simple terms, the data lives on more than one machine, which can boost speed and resilience but makes keeping everything in sync harder.
Database Administrator (DBA)
The person responsible for managing a database, including performance monitoring, backup, recovery, and security. Think of it as the caretaker who keeps the database healthy and accessible.
Data is stored in tables. Each table represents one type of entity (e.g. "Students", "Courses").
Each row in a table is a single record; each column is an attribute of that entity.
Tables are linked through keys:
A primary key uniquely identifies each row within its own table.
A foreign key in one table points to the primary key of another, creating a relationship.
Popular relational database management systems (RDBMS) include MySQL, Oracle, and Microsoft SQL Server.
Data modelling happens before the database is built. The goal is to capture real-world entities and how they relate to each other.
Two core techniques:
Entity-Relationship (ER) modelling – draws out entities, their attributes, and the relationships between them. Typically presented as an ER diagram.
Data normalisation – restructures tables to eliminate redundancy. Ensures each fact is stored once, reducing the risk of conflicting data.
SQL is the standard language for interacting with relational databases.
Four fundamental operations (often called CRUD):
SELECT – retrieve data from one or more tables.
INSERT – add new records.
UPDATE – modify existing records.
DELETE – remove records.
SQL is declarative: you describe what you want, and the DBMS figures out how to get it.
Database administration covers the ongoing work of keeping a database running well:
Performance monitoring – identifying slow queries, tuning indexes.
Backup and recovery – protecting against data loss.
Security management – controlling who can see or change what.
The DBA role exists because databases do not look after themselves. Someone needs to plan capacity, apply patches, and respond to incidents.
NoSQL databases drop the relational model in favour of structures better suited to certain workloads.
Well suited to large volumes of unstructured or semi-structured data, such as social media posts, sensor readings, or JSON documents.
Popular examples: MongoDB (document store), Cassandra (wide-column store).
Trade-off: NoSQL systems often sacrifice strict consistency guarantees in exchange for scalability and flexibility.
A distributed database is spread across multiple machines or locations.
Benefits:
Improved performance (queries can be served by the nearest or least-busy node).
Better scalability (add more machines rather than upgrading one).
Higher availability (if one node fails, others can take over).
Challenges:
Data consistency – ensuring every node has the same version of the data.
Synchronisation – coordinating writes across nodes without conflicts.
Network partitions – handling situations where nodes cannot communicate.
Relational databases power the transactional systems behind banking, airline reservations, and e-commerce checkouts, anywhere you need strict accuracy and consistency. NoSQL databases are what make social media timelines, recommendation engines, and IoT sensor pipelines feasible at scale. Distributed databases underpin global services such as Google Search and Netflix, where data must be available to users on every continent with minimal delay.
"NoSQL means no SQL at all." The name stands for "Not Only SQL." Many NoSQL systems support SQL-like query languages or can be queried alongside relational databases.
"Relational databases cannot scale." They can, but scaling them horizontally (across machines) is more complex than with purpose-built distributed or NoSQL systems. Vertical scaling (bigger hardware) works up to a point.
"Normalisation always makes a database better." Over-normalisation can lead to excessive joins and slower read performance. In practice, some deliberate redundancy (denormalisation) is introduced for performance reasons.
"A distributed database automatically keeps all copies in perfect sync." Distributed systems face inherent trade-offs between consistency, availability, and partition tolerance (the CAP theorem). Perfect sync at all times is not guaranteed.
⚠️ Be able to define and distinguish a database, a DBMS, and a relational database. These are distinct concepts that exams treat as separate.
⚠️ Know the difference between a primary key and a foreign key, and be able to identify each in a table diagram.
⚠️ Understand the four basic SQL operations (SELECT, INSERT, UPDATE, DELETE) and what each does.
⚠️ Be prepared to compare relational vs. NoSQL databases: when you would choose one over the other, and what trade-offs are involved.
⚠️ Distributed database questions often focus on the tension between consistency and availability. Know that spreading data across nodes introduces synchronisation challenges.
True or false: A DBMS is the same thing as a database.
Fill in the blank: A _______ key uniquely identifies each row in a table, while a _______ key creates a link to a row in another table.
True or false: SQL is a procedural language where you specify step-by-step how to retrieve data.
Fill in the blank: The process of organising tables to reduce redundancy is called data _______.
True or false: NoSQL databases are best suited for highly structured, transactional data.
Answers: 1. False (a DBMS is the software that manages the database). 2. Primary; foreign. 3. False (SQL is declarative). 4. Normalisation. 5. False (NoSQL is better suited for unstructured or semi-structured data).
Q: What is the primary purpose of a database management system (DBMS)?
A: A DBMS is software that manages databases. It handles creating, maintaining, and controlling access to the data, including storage, retrieval, security, and concurrency.
Q: Explain the difference between a primary key and a foreign key.
A: A primary key uniquely identifies each row within its own table. A foreign key is a column in one table that references the primary key of another table, establishing a relationship between the two.
Q: Name the four basic operations SQL can perform on table data.
A: SELECT (retrieve), INSERT (add), UPDATE (modify), and DELETE (remove).
Q: What are two common data modelling techniques, and what does each achieve?
A: Entity-relationship modelling maps out entities and their relationships, typically as a diagram. Data normalisation restructures tables to minimise redundancy and ensure each fact is stored only once.
Q: Give two advantages and two challenges of distributed databases.
A: Advantages include improved performance (queries served by the nearest node) and better scalability (add machines rather than upgrading one). Challenges include maintaining data consistency across nodes and handling synchronisation when network partitions occur.
Q: In what scenario would you choose a NoSQL database over a relational database?
A: When dealing with large volumes of unstructured or semi-structured data (e.g. social media posts, sensor data, JSON documents) where the rigid table structure of a relational database would be a poor fit, and where horizontal scalability matters more than strict transactional consistency.
This material connects directly to software engineering and web development, where choosing the right database is one of the earliest architectural decisions in any project. It also links to data structures and algorithms, since understanding how indexes, B-trees, and hash maps work explains why certain queries are fast or slow. If the course later covers cloud computing or systems design, distributed databases and the CAP theorem will reappear as core concepts.
database, DBMS, database management system, relational database, RDBMS, table, row, record, column, attribute, primary key, foreign key, SQL, Structured Query Language, SELECT, INSERT, UPDATE, DELETE, CRUD, data modelling, data modeling, entity-relationship model, ER diagram, ERD, normalisation, normalization, 1NF, 2NF, 3NF, NoSQL, Not Only SQL, MongoDB, Cassandra, document store, wide-column store, distributed database, CAP theorem, consistency, availability, partition tolerance, database administration, DBA, MySQL, Oracle, SQL Server, backup, recovery, performance tuning, scalability