DBMS and schemas
On This Lesson 11 topics
Lesson Overview
People say "database" for several different things: the software, the stored data, and the structure of the data. This lesson separates them. You will learn three terms precisely: DBMS (database management system), database, and schema.
Using the analogy of this module, the DBMS is the librarian and the rules of the library. The database is the collection of books on the shelves. The schema is the catalogue plan: which shelves exist, what each record must contain, and what is allowed. You will also build a tiny schema check in Java to see a schema doing its job, and preview what a real table definition looks like.
You need the previous lessons: why a database beats a file, the three data shapes, and the relational idea of tables with columns and rows. SQL itself comes in a later module, so any SQL here is marked as a preview to read, not to learn.
Why this matters for a backend: when you read "install PostgreSQL, create a database, define the schema", each noun is a different thing. Mixing them up leads to confused conversations and wrong troubleshooting.
Learning Objectives
By the end of this lesson you will be able to:
- Define DBMS, database, and schema, and tell them apart.
- Name two DBMS products, and say which family each belongs to.
- Explain that a schema describes structure and rules, and that rows are the contents.
- Describe a client-server DBMS: the application asks, the DBMS answers.
- Sketch a schema for contacts: table name, columns, types, and a rule or two.
- Explain how a schema protects data that a plain CSV file cannot protect.
Written Explanation
DBMS: the software
A database management system (DBMS) is the software that stores data and serves it to programs. It reads and writes the files on disk, searches, enforces rules, coordinates many users, and recovers after a crash: all the services from lesson 1. PostgreSQL is a DBMS. So are MySQL and SQLite (relational), and MongoDB (a document DBMS).
Most DBMSs used for backends work in a client-server style. The DBMS runs as a service, much like the backend services from the early modules that run continuously. Your application is a client: it connects to the DBMS, sends a request, and receives an answer. The application does not open the DBMS's data files itself. This is the librarian arrangement: you ask, the librarian fetches. (SQLite is a well-known exception that runs inside the application's own process.) Because only the DBMS touches the data files, it can apply the same rules to every client and keep users from damaging each other's work.
Database: the data it manages
A database is the organised data the DBMS manages. One DBMS installation can manage several databases, each separate: for example, one for a shop and one for a school. PostgreSQL's manual describes it this way: tables are grouped into databases, and the databases managed by one server instance form a database cluster. In everyday speech people also say "the database" to mean the whole system, including the software. That is common and usually fine, but when precision matters, use the three words separately.
Schema: structure and rules
A schema is the description of how the data is structured, together with the rules it must follow: which tables exist, which columns each has, the type of each column, which values are required, which must be unique, and how tables link to each other. If the data is the contents of the library, the schema is the plan that says what kinds of records exist and what each must contain.
In a relational database the schema is explicit and the DBMS checks it on every write. A row that breaks it is refused. This is the "rules" benefit from lesson 1: the CSV file accepted abc as an id, and a schema would not. In a flexible document database, the schema is looser, and the application or optional validation rules carry more of the job (previous lesson).
A note on vocabulary: in some systems, including PostgreSQL, "schema" is also the name of a namespace inside a database, a folder that groups tables. That is a separate, narrower meaning. In this module and in most introductions, "schema" means the structure and rules of the data.
Blueprint versus contents
The most useful way to remember the difference is blueprint versus contents:
| Blueprint | Contents | |
|---|---|---|
| Term | Schema | Rows (the data) |
| Example | contacts has id (whole number, required), name (text, required), email (text) | (1, Sara Ali, sara@example.com) |
| Changes | Rarely, and with care | Constantly |
Changing the blueprint when data already exists is a delicate job, because existing rows must still fit. That is why teams design the schema before building, and why later modules teach controlled schema changes.
A table definition, as a preview
Here is how a relational schema for contacts looks as a SQL statement. This is a preview: you are not expected to learn the syntax now, only to read the shape of it.
CREATE TABLE contacts (
id integer PRIMARY KEY,
name text NOT NULL,
email text UNIQUE
);Read it aloud: a table named contacts; a column id that holds whole numbers and identifies each row; a name that must always have a value; an email where no two rows may share the same value. Compare that with the CSV file, where none of these promises exist. (Exact type names differ between DBMS products, so always check the product's documentation.)
Examples
Example 1: DBMS, database, schema in one sentence (simple)
Read: "We installed PostgreSQL (the DBMS), created a database called shop (the data it manages), and defined a customers table with columns id, name, and email (the schema)."
If someone says "the database is down", ask which they mean. If the PostgreSQL service has stopped, every database on it is unreachable (a DBMS problem). If the shop data is damaged, that is a database problem. If a program fails because the email column is missing, that is a schema problem. Each has a different fix.
Example 2: a schema check in Java (practical)
A DBMS applies schema rules for you. To feel what that means, here is a toy version in Java. The "schema" is two arrays: the column names, and which columns are required. A method checks each row against it. A real DBMS does this far more completely and does not need you to write it.
import java.util.*;
public class L04a {
static final String[] COLUMNS = {"id", "name", "email"};
static final boolean[] REQUIRED = {true, true, false};
static String check(String[] row) {
if (row.length != COLUMNS.length) return "expected " + COLUMNS.length + " values, got " + row.length;
for (int i = 0; i < COLUMNS.length; i++) {
if (REQUIRED[i] && row[i].isBlank()) return COLUMNS[i] + " is required";
}
try { Integer.parseInt(row[0]); } catch (NumberFormatException e) { return "id must be a whole number"; }
return "ok";
}
public static void main(String[] args) {
System.out.println(check(new String[]{"1", "Sara", "sara@example.com"}));
System.out.println(check(new String[]{"x", "Omar", ""}));
System.out.println(check(new String[]{"3", "", ""}));
System.out.println(check(new String[]{"4", "Lina", "a", "b"}));
}
}Output with JDK 21:
ok
id must be a whole number
name is required
expected 3 values, got 4Notice the split: the arrays at the top are the schema (blueprint), and the rows in main are the data (contents). The same check method works for any rows. If you change the schema (say, make email required), no row has to be edited, but old rows might now fail. That is the delicate part of changing a schema.
Example 3: designing a schema for a small library (realistic)
Design, step by step, a schema for a library's books.
- List the facts to keep: title, author, ISBN (the international book number), year, and whether a copy is on loan.
- Pick a table name:
books, a plural noun for a collection of records. - Choose columns and types:
id(whole number, identifies the row),title(text, required),author(text, required),isbn(text, must be unique if present),year(whole number),on_loan(true or false, required). - Add the rules:
idunique;title,author, andon_loanrequired;isbnunique. - Check against the data: one row,
(1, Java Basics, Ana Smith, null, 2020, false)has no ISBN (null means "no value"). It is allowed becauseisbnwas not marked required. A row with no title is refused.
Notice the decision in step 3: the ISBN is text, not a number. ISBNs can contain dashes and leading zeros, and you never add two ISBNs together. A column's type is chosen from how the value is used, not just from how it looks. This kind of decision is the real work of a schema.
Backend Insight
In a backend project, the schema lives in two places that must agree: in the DBMS, and in the code that uses it. A contacts table has name text NOT NULL, and the Java class has a String name that should never be null. When they drift apart (a column is added in the database and the code does not know), failures appear. Professional teams keep the schema in files under version control (BEG-046 taught Git), so changes are reviewed and repeatable instead of being typed into a live database once and forgotten.
A second habit: put the strongest rules in the schema, not only in the application. Application checks can be bypassed by another program, a script, or a bug. A rule in the schema is enforced for everyone who talks to the DBMS.
Common Mistakes
Mistake 1: Using "database" for everything
- Incorrect: "I installed a database" (meaning PostgreSQL), "the database is slow" (meaning a query), "the database has no email" (meaning the schema).
- Correct: name the DBMS, the database, or the schema, whichever you mean.
Mistake 2: Thinking the schema is the data
The schema is the blueprint. You can have a schema with no rows at all, such as a newly created empty table.
Mistake 3: Choosing a type by how the value looks
Phone numbers and ISBNs look like numbers but are identifiers: they can start with zero, contain plus signs or dashes, and are never added together. Text is usually the right type.
Mistake 4: Putting every rule only in the Java code
Another program may write to the same database. Rules the schema enforces protect the data from all of them.
Exercises
Level A: Foundation
A1. In one sentence each, define DBMS, database, and schema.
Show solution A1
A1. DBMS: the software that stores and serves data and enforces rules. Database: the organised data it manages. Schema: the description of the data's structure and rules.
A2. Which are DBMS products: PostgreSQL, a contacts table, MySQL, a shop database, MongoDB?
Show solution A2
A2. PostgreSQL, MySQL, and MongoDB. The contacts table is part of a database, and shop is a database.
A3. True or false: "A schema contains the rows of a table."
Show solution A3
A3. False. The schema is the blueprint (tables, columns, types, rules). The rows are the contents.
Level B: Application
B1. Sketch a schema for a students table: table name, at least four columns with types, and which are required.
Show solution B1
B1. For example students(id integer required, name text required, email text, birth_date date, class_name text required). Type names differ between DBMS products, so any sensible types are fine.
B2. For each statement, say whether it describes the DBMS, the database, or the schema: (a) "The service uses port 5432 and restarted overnight." (b) "Every orders row must have a customer id." (c) "We backed up the shop data last night."
Show solution B2
B2. (a) DBMS. (b) Schema. (c) Database.
B3. Run the toy checker from Example 2 mentally on the row {"5", "Ahmed"} (two values). What does check return, and which part of the code decides that?
Show solution B3
B3. It returns expected 3 values, got 2. The first if in the method compares the row length with the number of schema columns.
Level C: Challenge
C1. Your team has a schema where email is optional. The product owner now wants it required, but 2,000 existing rows have no email. List two ways to handle this and one risk of each.
Show solution C1
C1. Option 1: fill the missing emails first (ask users or use a placeholder), then make the column required. Risk: placeholders become fake data. Option 2: make the rule apply only to new rows and keep old rows as exceptions, or keep the column optional and enforce the rule in the application for new sign-ups. Risk: old and new rows follow different rules, and the schema no longer guarantees the rule. Other well-reasoned answers are acceptable.
C2. Explain to a classmate, with the library analogy, why checking rules in the schema is safer than checking them only in the application code.
Show solution C2
C2. The librarian (DBMS) checks every book that comes in, whoever brings it. If only one visitor promises to check their own books, a different visitor can still put an invalid book on the shelf. Rules in the schema are the librarian's rules, so they apply to everyone.
Mini Challenge
Design a schema for an app of your choice, such as a gym booking system. Write the table name, at least five columns with types, which columns are required, which must be unique, and which column links to another table (name that table too). Then write one valid row and two invalid rows, and say which rule each invalid row breaks.
Check your work: types come from how values are used, and the invalid rows break rules you actually wrote.
Summary & Next Lesson
Key takeaways
- A DBMS is the software (for example PostgreSQL); a database is the data it manages; a schema is the structure and rules.
- The schema is the blueprint and the rows are the contents.
- Most backend DBMSs run as a service, and the application is a client that sends requests.
- In a relational database the DBMS enforces the schema on every write.
- Choose column types by how the value is used, and put key rules in the schema, not only in application code.
Next lesson
The next lesson, "Demo: why the contacts CSV breaks at scale", puts the whole module into practice: you will push the Contacts CSV file to its limits and list the problems a database would solve. It also prepares the module lab.
Self-check
- Is PostgreSQL a DBMS, a database, or a schema?
- What does a schema contain?
- Which changes more often, the schema or the rows?
- Why is an ISBN usually stored as text?
- Name one reason to enforce a rule in the schema.
Show answers
- A DBMS.
- The structure (tables, columns, types) and the rules (required, unique, links).
- The rows.
- It is an identifier, not a quantity: it can contain dashes or leading zeros and is never added up.
- The DBMS enforces it for every program that writes to the data, not only one.
References
- PostgreSQL documentation, Concepts (tutorial). Supports the statements that PostgreSQL is a relational database management system, that tables are grouped into databases, and that databases managed by one server instance form a database cluster.
- The mention that SQLite runs inside the application's process, that "schema" is also a namespace in PostgreSQL, and the exact SQL in the preview were not checked against a fetched page in this lesson. The Java code was compiled and run with JDK 21.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.