Database Fundamentals

Why it matters

The screen shows what the application chose to display; the database holds what was actually stored. Many defects live only there: a phone number that lost its leading zero, an Arabic name saved as question marks, a description cut at 50 characters, a timestamp shifted by three hours. They surface weeks later in an invoice, a report or another system.

Knowing how a table is built turns guessing into test design. Each column's type and length are limits the requirement rarely mentions, and each optional column raises the question of what "empty" means.

Tables, rows and columns

The customers table below shows three rows and five of its columns: one row per customer, one column per attribute, and a single value where a row meets a column.

Two properties surprise beginners. A column means the same thing in every row: phone cannot hold a phone number in one row and an email address in another. And rows have no built-in order: without ORDER BY the database may return them in any order, so "the last row" is no way to find the record you just created. Look it up by its identifier — here customer_id, a key, the subject of the lesson on keys and relationships.

• Other names: a table is a relation in relational theory, a row is a record or tuple, and a column is a field or attribute.
• Schema: the definition of all tables, columns, types and rules (in PostgreSQL and SQL Server, also a namespace that groups tables).

Table: customers

customer_id | full_name   | phone         | birth_date | wallet_balance
------------+-------------+---------------+------------+---------------
        317 | Sara Ahmed  | 0550000001    | 1994-03-18 |         250.00
        318 | Ravi Kumar  | +919800000002 | NULL       |           0.00
        319 | Lina Haddad | 0790000003    | 2001-11-02 |        1250.50

Data types: what a column may hold

Every column declares a data type, which decides what values it accepts, how they sort and compare, and how large they can be. Names and strictness differ between engines: SQL Server uses BIT instead of BOOLEAN; PostgreSQL rejects text that is too long or a number too large for its column but rounds extra decimal places without an error, MySQL's behaviour depends on its SQL mode, and SQLite by default stores almost any value in any column.

• Whole numbers — INTEGER, BIGINT: counts, quantities, identifiers. Each has a fixed range; a 4-byte INTEGER stops at 2147483647.
• Exact decimals — NUMERIC(p,s) or DECIMAL(p,s): money. NUMERIC(10,2) has 10 digits in total, 2 of them after the point: it holds at most 99999999.99, and PostgreSQL stores 0.125 as 0.13.
• Approximate numbers — REAL, DOUBLE PRECISION: measurements. Never money: in binary floating point, 0.1 + 0.2 is not exactly 0.3.
• Text — VARCHAR(n), TEXT: n is a maximum length. Most engines count characters, but some types count bytes, and in UTF-8 an Arabic letter takes 2 bytes and a Devanagari letter 3.
• Date and time — DATE, TIMESTAMP: a day or a moment. Check whether a timestamp keeps its time zone: 01:30 in Riyadh (UTC+3) is still the previous day in UTC.
• Boolean — BOOLEAN: true or false; MySQL stores it as a small integer, so you see 1 and 0.
• NULL — not a type but a marker for "no value" in any column. It differs from 0, from empty text '' and from the word "NULL" typed by a user. A NOT NULL column is required.

Try it: CSV to SQL Try it: Boundary Number Dataset Generator Try it: String Length Counter

Worked example: test ideas from a table definition

A new profile page lets customers enter a name, a mobile number and an optional birth date, and shows their wallet balance. Before any screen exists, read the developer's table definition below as part of the test basis (everything you derive tests from) and turn its columns into test conditions (the aspects worth testing) or questions:

• full_name VARCHAR(100): save names of exactly 100 and 101 characters (the limit and one past it) in English, Arabic and Hindi. Expect 100 stored unchanged and 101 rejected with a clear message, not silently cut.
• phone VARCHAR(20): text is right; a number type would drop the leading 0 of 0550000001 and the + of +919800000002. Compare the stored value character by character.
• birth_date DATE NULL: leave the field empty. Expect NULL in the row, not a placeholder such as 1900-01-01.
• wallet_balance NUMERIC(10,2): top up 0.10 three times and expect exactly 0.30. Ask what happens when a top-up would take the balance above 99999999.99, the column's maximum.
• created_at TIMESTAMP: in PostgreSQL this type keeps no time zone. Sign up at 01:30 Riyadh time and ask whether UTC or local time was stored; every daily report depends on it.

CREATE TABLE customers (
  customer_id     INTEGER        NOT NULL,
  full_name       VARCHAR(100)   NOT NULL,
  phone           VARCHAR(20)    NOT NULL,
  birth_date      DATE           NULL,
  wallet_balance  NUMERIC(10,2)  NOT NULL DEFAULT 0,
  is_active       BOOLEAN        NOT NULL DEFAULT TRUE,
  created_at      TIMESTAMP      NOT NULL,
  CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

Try it: SQL Dataset Generator Try it: Unicode Dataset Generator

Relational and other kinds of databases

Relational databases, queried with SQL, are widely used for business records and are the focus of this module. SQL is standardised as ISO/IEC 9075, but each engine adds its own dialect. Non-relational ("NoSQL") databases organise data differently and often leave checks to the application:

• Document databases, such as MongoDB, store JSON-like documents whose fields can differ within one collection unless a validation rule forbids it; test for missing or misspelt fields and numbers stored as text.
• Key-value stores, such as Redis, keep a value under a key, often as a cache that can fall out of date with the database behind it.
• Wide-column stores, such as Apache Cassandra, spread very large tables across many servers.
• Graph databases, such as Neo4j, store nodes and the relationships between them, such as who follows whom.

Try it: JSON to SQL

Frequently asked questions

References

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupLexical structure of SQL in PostgreSQL: identifiers, quoting, string constants and escaping.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.