Skip to content

Artificial Intelligence, Data Analytics and Cyber Security - Laws and Practice · Computer Hardware and Software

Database Management Systems and Data Storage Explained

Updated 11 October 2026 · Fact-checked

A database is an organised collection of data. A DBMS is the software that creates, stores, retrieves and protects it. Types include relational (tables, SQL) and NoSQL. A data warehouse stores cleaned historical data for analysis. A data lake holds raw data of any format. Answer by defining, comparing and applying to a business case.

Understand Database Management Systems and Data Storage

Start with data. A company holds customer details, invoices, share transfers and board records. If this sits in scattered files, you get duplicates, errors and no control over who sees what. A database solves this by keeping related data together in an organised way.

A Database Management System (DBMS) is the software that sits between users and the database. It lets you store, update, search and delete data. It also controls access, keeps data consistent and supports backup and recovery. Examples are MySQL, Oracle, PostgreSQL and MongoDB.

DBMS types you should know:

  • Relational (RDBMS): data in tables of rows and columns. Tables link through keys. You query it with SQL. Strong on consistency. Good for accounts, payroll and registers.
  • NoSQL: flexible, non-tabular models. Main kinds are document, key-value, column-family and graph. Built for large, varied, fast-changing data and easy scaling out.
  • Hierarchical and network: older models. Data is arranged as a tree or a graph of links.
  • Object-oriented: stores data as objects.

SQL (Structured Query Language) is the standard language of relational databases. Its commands fall into groups. DDL defines structure (CREATE, ALTER, DROP). DML handles data (INSERT, UPDATE, DELETE). Retrieval is done with SELECT. DCL controls permissions (GRANT, REVOKE). TCL controls transactions (COMMIT, ROLLBACK).

For analytics, storage matters. A data warehouse is a central store of structured, cleaned, historical data from many sources, organised for reporting and analysis. An operational database runs day-to-day transactions. A data lake stores raw data in its original form, structured or not, and applies structure only when the data is read. A data mart is a smaller warehouse for one department. Big data is usually kept in distributed storage so it can grow across many machines.

Key rules to remember

Database vs DBMS
DBMS = software; Database = the data it manages
Do not use the two terms as if they mean the same.
SQL retrieval syntax
SELECT column FROM table WHERE condition
Basic query pattern. Add ORDER BY or GROUP BY if needed.
SQL command groups
DDL: CREATE, ALTER, DROP | DML: INSERT, UPDATE, DELETE | DCL: GRANT, REVOKE | TCL: COMMIT, ROLLBACK
SELECT is often listed under DML or as DQL. State which grouping you follow.
Warehouse vs lake
Warehouse = schema-on-write (structured, processed); Lake = schema-on-read (raw, any format)
The most tested contrast in storage questions.
Keys
Primary key = unique row identifier; Foreign key = column referring to another table's primary key
Keys link tables in a relational database.

How to solve Database Management Systems and Data Storage questions

Use this method for definition, comparison and short case questions on databases and storage.

  1. 1Read the verb. Define, differentiate, explain or apply each need a different answer shape.
  2. 2Define the core term in one clear line, such as database, DBMS, warehouse or lake.
  3. 3State the key features or types in short points. Name an example for each.
  4. 4For a comparison, pick four to five points: purpose, data type, structure, users, and processing.
  5. 5If SQL is asked, write the command in correct order and say what it does in one line.
  6. 6Link to the case facts. Say which storage or DBMS suits the company and why.
  7. 7Close with a one-line conclusion and, where relevant, a security or data protection point.

Quickest way: Define, compare, recommend

When to use it: When time is short and the question asks for differences or a suitable choice.

  1. Write a one-line definition of each item.
  2. List three or four contrasts in short points.
  3. Give one business example for each item.
  4. Finish with a recommendation tied to the facts given.

Common mistakes in Database Management Systems and Data Storage

  • Treating database and DBMS as the same thing

    Both words are used loosely in daily talk.

    Fix: Say the database is the data and the DBMS is the software that manages it.

  • Saying NoSQL means 'no SQL at all' and is always better

    The name is read literally.

    Fix: Say it is a non-relational approach, often read as 'not only SQL'. It suits flexible, large-scale data. Relational systems remain better where strict consistency is needed.

  • Confusing data warehouse with data lake

    Both store large volumes for analytics.

    Fix: Warehouse holds processed, structured data. Lake holds raw data in any format and applies structure on reading.

  • Mixing up SQL command groups, such as placing DELETE under DDL

    Students memorise names without the logic.

    Fix: DDL changes structure. DML changes data. DELETE removes rows, so it is DML.

  • Writing SQL clauses in the wrong order

    Syntax is learned by sight, not practice.

    Fix: Remember the order SELECT, FROM, WHERE, GROUP BY, ORDER BY.

  • Giving a theory answer with no link to the case

    Students recall notes instead of reading the facts.

    Fix: Name the company's data need and tie your choice of system to it.

Worked examples

Example 1

Differentiate between a data warehouse and an operational database. Suggest which a listed company should use to analyse five years of sales trends.

Show the solution
  1. Define: an operational database supports day-to-day transactions such as recording a sale. A data warehouse stores integrated historical data for analysis.
  2. Purpose: the operational database serves routine processing. The warehouse serves reporting and decision support.
  3. Data: the operational database holds current, detailed data and changes often. The warehouse holds cleaned, historical data from many sources, mostly read-only.
  4. Users: clerks and applications use the operational database. Analysts and management use the warehouse.
  5. Application: a five-year sales trend needs history across sources, which the operational system is not designed for.

Answer: The company should use a data warehouse for five-year sales trend analysis. The operational database should continue to handle daily transactions.

Example 2

A table EMPLOYEE has columns EmpID, Name, Dept and Salary. Write SQL to (a) list the names of employees in the 'Legal' department, and (b) raise the salary of EmpID 101 to 60000. Name the command group of each.

Show the solution
  1. For (a) we need to retrieve data, so use SELECT with a WHERE condition.
  2. Query: SELECT Name FROM EMPLOYEE WHERE Dept = 'Legal';
  3. SELECT retrieves data. It is often called DQL or treated as part of DML.
  4. For (b) we change existing data, so use UPDATE.
  5. Query: UPDATE EMPLOYEE SET Salary = 60000 WHERE EmpID = 101;
  6. UPDATE modifies data, so it is a DML command. The WHERE clause limits the change to one employee.

Answer: (a) SELECT Name FROM EMPLOYEE WHERE Dept = 'Legal'; (b) UPDATE EMPLOYEE SET Salary = 60000 WHERE EmpID = 101; Both are data-handling commands, with UPDATE being DML.

Exam tips

  • Expect comparison questions. Prepare short tables-in-words: database vs DBMS, relational vs NoSQL, warehouse vs lake.
  • Learn SQL command groups with one example command each. Practise two or three simple queries.
  • Always tie your answer to a company scenario. Case-based marking rewards application.
  • Add a line on data security and personal data protection where the case involves customer data.
  • Keep definitions to one line and use points. Examiners prefer clear structure over long text.

Practice questions from Computer Hardware and Software

Database Management Systems and Data Storage: frequently asked questions

What is the main difference between a data warehouse and a database?

A database usually supports day-to-day transactions. A data warehouse stores integrated, historical data from many sources for analysis and reporting. The warehouse is optimised for reading and analysis, not for routine updates.

What is the difference between a data lake and a data warehouse?

A data warehouse stores structured, processed data with the structure defined before loading. A data lake stores raw data of any format and applies structure only when it is read. Lakes suit exploratory analytics on varied big data.

What are the types of DBMS?

Common types are hierarchical, network, relational, object-oriented and NoSQL. Relational and NoSQL are the most important for analytics. Relational uses tables and SQL. NoSQL uses flexible models such as document, key-value, column-family and graph.

What SQL commands should I know for CS Professional?

Know the groups: DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE) and TCL (COMMIT, ROLLBACK), plus SELECT for retrieval. Be able to write a simple query with a WHERE condition.