The Relational Database Model

A customer name, an order date and a price are data: recorded facts. A database is an organized collection of related data. A database management system, or DBMS, is the software that stores and manages that collection. PostgreSQL is a DBMS; a shop's store database is one collection it manages.

Store each fact where it belongs

Suppose a spreadsheet repeats Maria Garcia's phone number on all 14 of her sales. A phone-number change requires 14 edits. One missed cell leaves conflicting records. In a relational database, customers holds Maria's details once, under customer_id 1. Each sale records that identifier. Updating her phone in one customer row gives later lookups one current number.

A relational database organizes facts in tables. “Relational” comes from relation, the mathematical name for a table. E. F. Codd introduced the model in 1970. Its central separation is useful: users describe the data they want through tables and values; the DBMS handles how to find those data on disk.

Rules and shared access

A table has named columns, data types and constraints. A constraint is a rule the DBMS checks, such as “an order must refer to an existing customer.” These checks protect shared data when several applications use the same database. Type enforcement varies: PostgreSQL rejects text such as 'twelve' in a numeric price column, while ordinary SQLite tables have flexible types.

A DBMS also manages simultaneous users, permissions, backups and recovery. Two staff members can work on shared data without exchanging separate copies of a spreadsheet. Permissions can let a reporting user read sales while limiting who may change them. A backup and recovery process helps restore data after a failure.

Keep a group of changes together

A transaction treats several changes as one unit: all are saved or none are. Moving $200 between accounts requires subtracting it from checking and adding it to savings. If a failure interrupts the transfer before completion, the DBMS can undo the unfinished transaction. Saving only the subtraction would leave an incorrect balance.

A spreadsheet remains useful for small calculations and flexible layouts. A database provides a defined structure for shared records and rules. An Excel worksheet has a fixed limit of 1,048,576 rows; databases routinely manage much larger collections, subject to storage, hardware and product limits.

Different data and different jobs

Structured data fit named columns shared by every row. Semi-structured data, such as JSON documents, can have different fields in different records. Unstructured data, such as audio or free-form email, lack that fixed tabular layout. Organizations often combine relational databases with document stores, key-value stores or file storage.

The workload matters too. A transactional system, also called OLTP, records everyday events through many small reads and writes: an order, a payment, an address change. An analytical system, or OLAP, reads many stored records to answer questions such as “How did monthly sales vary by region?” Both can use relational tables and keys.

SQL, short for Structured Query Language, is a language. PostgreSQL, MySQL, SQLite, Microsoft SQL Server and Oracle Database are products that use it. SQL Server is one DBMS, not another name for SQL itself.

How an application reaches the data

In a client-server system, a server program manages the database. A client, such as a website, reporting tool or query editor, sends requests to that server. PostgreSQL, MySQL, SQL Server and Oracle commonly work this way. Tools such as psql, pgAdmin, DBeaver and SQL Server Management Studio let people inspect databases and send requests.

An embedded engine runs inside an application. SQLite uses a library and an ordinary database file, such as store.db, without a separate database server. It is common in phones, browsers and desktop apps. A managed cloud service, such as Azure SQL Database, Amazon RDS or Google Cloud SQL, lets a provider run the database servers. “Managed” describes who operates the service; it does not replace tables and keys with a different model.

One DBMS installation can manage several databases, such as store and payroll. A connection selects the database to use, subject to permissions.

Say what result you want

SQL is declarative: it describes the required result while the DBMS chooses how to obtain it. Read this statement:

SELECT first_name, city
FROM customers
WHERE state = 'TX';

It asks for first names and cities from customer rows whose state is TX. It says nothing about which disk page to read first. Capitalized keywords make the statement easier to scan; select and SELECT mean the same thing. A semicolon normally ends a statement.

Recognize the statement's job

Some books put SELECT in a separate data query language group. The useful distinction is the statement's actual job: adding a column changes structure; adding a customer changes data.

SQL became an ANSI standard in 1986 and is standardized internationally as ISO/IEC 9075. Products add their own dialects. A ten-row limit appears as LIMIT 10 in SQLite, PostgreSQL and MySQL, TOP 10 in SQL Server, or FETCH FIRST 10 ROWS ONLY in standard SQL and Oracle.

Read text and other database objects

Text literals use single quotes: 'TX'. Standard SQL uses double quotes for identifiers, such as column names. An apostrophe inside text is doubled: 'O''Brien' represents O'Brien.

A view is a saved query used like a table; an ordinary view has no stored rows of its own. An index is a lookup structure that can help find rows quickly. Most systems automatically index primary keys. Stored procedures and functions are saved code that can be run by name.

A table stores facts about one kind of thing. Each row in customers describes one customer; each column holds one property. Their intersection holds a value.

Read across one record

Here are selected customer columns:

Find customer_id 4, then read city: Wei's city is Seattle. To find who lives in Denver, read the name in Denver's row: James Wilson. A neighboring row describes a different customer.

The full table has eight columns: customer_id, first_name, last_name, email, phone, city, state and zip_code. James's phone is NULL: the value is missing, but his row still has the phone column.

Match the vocabulary

A tuple is a whole row, not one cell. An attribute names a property shared by all rows. “Record 4” might mean an identifier or a display position; check which, because positions can change.

Count the shape

Degree counts columns; table cardinality counts rows. The full table has degree 8 and cardinality 4. The display above shows six columns without changing the table's degree.

Adding a customer raises cardinality to 5; degree stays 8. Adding birth_date raises degree to 9 and gives every row a place for that value, even when it is missing. A new customer adds a row, not a column.

Relationship cardinality, such as one-to-many, describes allowed links rather than a table's row count.

Read qualified names

Column names are unique within a table. orders.customer_id and customers.customer_id distinguish identically named columns in different tables: the dot separates table and column names.

snake_case uses lowercase words separated by underscores, as in zip_code. Names with spaces need quoting in SQL. Either singular or plural table names can work; use a consistent convention.

A relational table has a logical structure beyond its rectangular appearance. In the formal model it is a set of rows. Sets contain no duplicate members and have no order. Columns are identified by name, so moving a column in a display does not change the fact it represents.

Identity does not come from position

A DBMS can return rows in a different order after updates, deletions or a change in how it reads storage. “The third row” is not a lasting way to identify a customer. customer_id 3 still identifies Aisha even when her row appears first. A query that needs a particular order asks for it with ORDER BY.

SQL tables differ from formal relations: without a primary key or another suitable uniqueness constraint, they can contain identical rows. Two copies of ('Coffee mug', 12.50) give no distinguishing value in those columns. A primary key gives each row a unique identifier and prevents complete duplicate rows.

One column, one domain

A domain is the set of values allowed in a column. A quantity has a whole-number type and may also need a rule that it must be positive. A state code is text, but not every text value is a valid state code. Data types and constraints describe the allowed values to the DBMS.

All rows share the same columns. One order cannot acquire an extra gift-note column just for itself. The table can have a gift_note column for every order, with NULL where no note was recorded. Optional facts still have a defined place.

Keep individual values separate

An atomic value is one value for the column's purpose. A phone cell containing “512-555-0147, 512-555-0199” holds a list of two numbers. Finding, checking or removing just one number becomes awkward. A customer_phones table can give each phone number its own row, with customer_id showing whose number it is.

Atomic does not mean that every text value must be split into its smallest pieces. A city name can contain several words and remain one city value. The question is whether the cell stores one fact of the kind the column represents, or a collection that should be handled separately.

Read a spreadsheet export critically

A sheet of sales may include a repeated header halfway down, a bottom row labeled “TOTAL,” and a price written as “12.50 (sale).” These are useful layout choices for a person, but they mix different kinds of content in the same columns.

To read it as a relational table:

1. Treat the column names as the header, not as sale records.
2. Keep each data row about one sale; calculate the total from those rows.
3. Store the numeric price separately from a sale note.
4. Give each sale a key so its row can be identified.

A total row mixed with sales could be counted as another sale or added to the total again. A note mixed into a numeric price prevents ordinary numeric operations.

Logical tables and physical storage

A table is the user's logical view. The DBMS may store its data in files, pages and indexes arranged for efficient access. Data independence means that changes to that physical arrangement need not change the table names, column meanings or requests used by applications.

A data type tells a DBMS what kind of value a column holds and how to work with it. Numeric addition, text comparison and date ordering are different operations. A familiar type name can also behave differently across database products.

Numbers: whole, exact and approximate

In PostgreSQL, MySQL and SQL Server, INTEGER ranges from −2,147,483,648 to 2,147,483,647. BIGINT reaches 9,223,372,036,854,775,807; SQLite's integer storage can reach that range too.

For decimals, precision is the total number of digits and scale is the number after the decimal point. DECIMAL(8, 2) leaves six digits before the point: its maximum is 999999.99. The price 12.50 fits; 1234567.00 does not. PostgreSQL, MySQL, SQL Server and Oracle provide exact decimal storage. SQLite accepts this declaration but does not enforce its precision or scale, and can store fractions as binary floating point.

Floating point approximates many decimal fractions. In double precision, adding 0.1 and 0.2 gives 0.30000000000000004. SQLite treats these decimal literals as floating point. PostgreSQL treats bare decimal literals as exact numeric, so the same written calculation there can be exact; explicit double-precision values show the approximation.

Text, dates and flags

CHAR(n) is fixed-length text; PostgreSQL and SQL Server pad shorter values with spaces. VARCHAR(n) allows varying lengths up to a limit. SQLite ignores both length settings and does not pad. TEXT holds long text in SQLite, PostgreSQL and MySQL. SQL Server uses VARCHAR(MAX) or NVARCHAR(MAX) for this job; its older TEXT type is deprecated.

A DATE holds a calendar date, a TIME a time of day, and standard SQL TIMESTAMP a date and time. Write dates in year-month-day form, such as '2026-03-02', to avoid confusing March 2 with February 3. Typed dates have an internal storage format; the application chooses their display format. SQLite has no dedicated date type and commonly stores ISO date text. SQL Server uses DATETIME2 for date and time: its TIMESTAMP means ROWVERSION, a binary version counter.

A BOOLEAN represents TRUE or FALSE, or NULL when missing. PostgreSQL has a Boolean type; SQL Server uses BIT, MySQL treats BOOLEAN as a small integer, and SQLite stores the flags as 1 and 0. Binary types such as BLOB or PostgreSQL's BYTEA store bytes, including small images. Larger files are often stored separately with a path or URL in a table.

SQLite's type affinity

In ordinary SQLite tables, a declared type is a preference called type affinity. An INTEGER column converts '42' to the number 42, but can retain 'abc' as text. A SQLite STRICT table rejects text that cannot become a valid integer.

Start with what a value means and how it will be used. A ZIP code contains digits, but nobody adds ZIP codes to get a meaningful total. A quantity also contains digits, and adding quantities is useful. Appearance alone is a poor guide to type.

Ask five questions

1. What are typical values, and what extremes must fit?
2. Will the values be used in arithmetic?
3. Must they be exact, or is approximation acceptable?
4. How must they sort and compare?
5. What size fits realistic future values?

Money and measurements

Money needs exact values. In PostgreSQL, MySQL and SQL Server, a price can use DECIMAL(8, 2). Three items at $12.50 cost exactly $37.50. Binary floating-point storage can leave tiny errors after repeated additions, making exact comparisons unreliable. In SQLite, declaring DECIMAL does not supply exact decimal storage; use integer cents for exact amounts, so $12.50 is stored as 1250.

A measurement such as weight can use REAL when small rounding differences are acceptable. Choose the unit as part of the column's meaning: weight_lb makes pounds visible in its name. A numeric type alone does not record that unit.

Currency also needs a meaning. If a table mixes currencies, add currency_code, such as USD, EUR or JPY. Decimal places vary by currency, and unlike currencies must be converted before their amounts can be added.

Codes and names are text

Use text for ZIP codes, phone numbers, account numbers and stock codes. The Boston ZIP code '02108' loses its leading zero when converted to an integer. VARCHAR(10) can also hold ZIP+4, such as '02108-1234'. Phone numbers may contain a plus sign, spaces and hyphens. These characters are part of an identifier's representation, not arithmetic signs.

Allow room for real names and addresses. “Christopher Montgomery” has 22 characters, so VARCHAR(10) is too short. PostgreSQL and SQL Server reject it; MySQL rejects it in strict mode and may truncate otherwise. SQLite stores it because declared text lengths are ignored. A generous varying-length field avoids rejecting ordinary names without reserving its maximum length for every row.

Sorting reveals a wrong choice

Text sorting compares characters. Numbers stored as text can appear in the order '1', '10', '2', '25', '3'. A numeric column sorts those values numerically. Choosing text for every field shifts checking and conversion work onto later calculations.

Dates have a similar trap. US-style date text puts '12/01/2025' after '03/02/2026' in text order, although December 2025 is earlier. Use a date type, or consistently formatted ISO text in SQLite, so calendar order is preserved.

Read a product's types together

A product can use product_id INTEGER, sku VARCHAR(20), name VARCHAR(100), price DECIMAL(8, 2), weight_lb REAL, in_stock BOOLEAN and added_on DATE. Each type follows a job: identification, code storage, a name, exact money, measurement, a flag or a date. For SQLite, store the price as integer cents and the date as ISO text.

NULL marks a missing value. It is different from zero, empty text '' and the text 'NULL'. A known discount of 0 means no discount; a NULL discount means the amount is not recorded. A nullable column allows missing data, while NOT NULL requires a value.

Missing has more than one meaning

James Wilson has no recorded phone number. Order 102 has no shipping date because it has not shipped yet. A company customer may have no applicable last name. All can be represented by NULL, but NULL does not record which reason applies. A data dictionary should state each column's meaning.

Empty text is a present text value in PostgreSQL and SQLite. Oracle currently treats empty text as NULL. Query tools may display both as an empty-looking cell, so appearance alone does not tell them apart.

Comparisons can be unknown

SQL comparisons have three possible results: TRUE, FALSE and UNKNOWN. A comparison involving NULL, including NULL = NULL, is UNKNOWN. Two missing facts are not known to have the same value.

A WHERE clause keeps only TRUE results. Read these conditions:

• phone IS NULL finds missing phone numbers.
• phone IS NOT NULL finds recorded phone numbers.
• phone = NULL yields UNKNOWN and finds no rows.

Negating a comparison does not recover a missing value. Add Daniel Brown, whose state is NULL, to the four store customers. The condition state <> 'TX' keeps CO, MA and WA: three rows. Daniel is excluded because his state comparison is UNKNOWN. To include missing states too, the condition is state <> 'TX' OR state IS NULL.

Combine conditions carefully

Think of UNKNOWN as “could be TRUE or FALSE.” A result is settled only if both possibilities give the same outcome.

Calculations and summaries

Ordinary arithmetic with NULL gives NULL: 5 + NULL is NULL. A line price multiplied by a missing quantity is missing too, not zero. Joining text with || and NULL gives NULL in PostgreSQL and SQLite; Oracle's concatenation treats NULL as empty text.

Summary functions usually skip NULL inputs. With five customers, including James and Daniel without phone numbers, COUNT(*) counts all five rows. COUNT(phone) counts the three recorded phones. The average of 4, NULL and 2 is 3, because the two known numbers are divided by two; treating NULL as zero would incorrectly give 2.

Use NOT NULL for facts that must be present, such as an order date. It does not force a positive number or nonempty text: those are separate rules. Primary keys require non-NULL identity; ordinary SQLite tables have a historical exception for some primary key declarations, so explicit NOT NULL matters there.

A key identifies a row through its values. Names can repeat; customer identifiers distinguish records so they can be read, changed or linked.

Test uniqueness and minimality

A superkey is a column or combination that uniquely identifies a row. A candidate key is a minimal superkey: removing any column loses that guarantee. Choose one candidate as the primary key; the others are alternate keys.

Suppose employee_id, ssn and email are each required and unique. Each is a candidate key. (employee_id, department) is a superkey but not a candidate: employee_id alone identifies the employee. A repeated last name fails the uniqueness test.

Four customers with different last names do not establish a key: a fifth may share a name. Uniqueness must hold for all allowed future data. A repeat disproves a proposed key; a sample without repeats still needs a supporting business rule.

Read the declaration

A table has at most one primary key. Its values are unique and its columns must be non-NULL. Stable values avoid repeated changes to references. Look for PRIMARY KEY: neither a column's name nor its position declares it.

If customer 1 exists, another customer with key 1 is rejected. An omitted key needs a default or generated value. SQLite's INTEGER PRIMARY KEY generates an integer when omitted or given NULL. Some other primary key declarations in ordinary SQLite tables historically allow NULL; explicit NOT NULL, a STRICT table or the integer-primary-key form closes that gap.

Read a composite key

In enrollments(student_id, course_id, term, grade), a student can take several courses and retake one in another term. (student_id, course_id, term) identifies one enrollment: one composite primary key, not three separate primary keys.

Individual components may repeat; the complete combination cannot. Most DBMSs create a primary-key index for lookup, but the key does not guarantee query output order.

A natural key comes from real-world data, such as a vehicle's VIN, a book edition's ISBN or a state code. A surrogate key exists only to identify a row, often as a generated integer or UUID.

Choose an identifier that lasts

Prefer unique, present, stable, compact values that are safe to share. Names repeat; phones and emails change or are shared. Social Security numbers are sensitive and unavailable for some customers; using them in referring tables spreads that information.

A generated customer_id lets Maria keep identifier 1 when her contact details change. A natural state_code, such as TX, can work well because it is compact, standardized and useful in address records.

Protect business identifiers separately

The same person can register twice and receive two surrogate IDs. The IDs distinguish rows; they do not detect duplicate people.

If email must identify every account, use NOT NULL UNIQUE. UNIQUE alone permits several NULLs in PostgreSQL, SQLite and MySQL. SQL Server normally permits one NULL in a single-column unique constraint. Presence and uniqueness are separate rules.

The required, unique email is an alternate key. Changing it leaves the surrogate customer number in existing orders unchanged. Changing a referenced primary key requires its child references to remain consistent too.

Recognize generated-key syntax

PostgreSQL also has the older SERIAL form. Deletions, failed inserts and canceled work can leave gaps. IDs 1, 2 and 4 label three rows, not four. Renumbering to close gaps can break references.

Recognize UUIDs

A UUID is a 128-bit identifier, commonly written as 36 characters with hyphens: 7c9e6679-7425-40de-944b-e07fc1f90ae7. Computers can generate them independently with very low collision risk, useful when records from several places will be combined.

UUIDs take more space and are harder to read than small integers. Choose according to how records are created and referenced. Either kind of key is a label: its maximum value is not a row count, and its order need not show event times.

A foreign key is a column or group of columns that refers to a primary key or another suitable unique key. The table carrying the reference is the child; the table it points to is the parent. A non-NULL foreign key must match an existing parent key when the constraint is enforced.

Follow a value across tables

orders.customer_id refers to customers.customer_id. Order 102 points to customer 2, James Wilson. Orders 101 and 103 both point to customer 1, Maria Garcia. Repetition is allowed in the foreign key; the referenced parent key is unique.

This rule is referential integrity. An order for customer 99 is rejected if customer 99 does not exist. Otherwise it would be an orphan: a child with no matching parent. A name alone creates no constraint. A foreign key named placed_by can still refer to customer_id; the declared reference creates the link, with matching types for the key columns.

Optional or required

A nullable coupon_id can be NULL when an order has no coupon. A customer_id declared NOT NULL requires every order to have a customer reference, and the foreign key requires that customer to exist. These two constraints work together: one requires a value and the other checks the reference.

Foreign keys need not cross table boundaries. In employees(employee_id, name, manager_id), manager_id can refer to employee_id in the same table. The chief executive can have a NULL manager reference.

Parent changes need a rule

With the default action, customer 1 cannot be deleted while orders 101 and 103 still refer to her. Cascading from an order to its lines can make sense because those lines belong to that order. ON UPDATE specifies the corresponding behavior when a parent key changes. Cascading does not mean that deleting a child deletes its parent.

Composite references and enforcement

If an order line is identified by (order_id, product_id), a shipment referring to that line needs both columns. The combination must match an existing line. Matching an order and a product separately is insufficient: that product may never have appeared in that order. A required composite reference declares all its columns NOT NULL.

PostgreSQL, SQL Server, Oracle and MySQL's usual InnoDB engine enforce declared foreign keys. In SQLite, enable and check foreign-key enforcement for each connection with PRAGMA foreign_keys = ON; before relying on it. A declared but disabled constraint cannot protect the rows. Foreign keys also do not combine query results by themselves; a query must explicitly follow the link, usually through a join.

To identify a relationship, ask two questions: “For one row here, how many rows there can be linked?” and then the reverse. Include the minimum as well as the maximum. Today's sample shows what has happened; the constraints describe what is allowed.

One customer, many orders

A customer can place zero or more orders. Each store order belongs to exactly one customer because orders.customer_id is a non-NULL foreign key. This is one-to-many. The foreign key belongs in orders, the many side: each order stores one customer reference.

Maria has orders 101 and 103; James has order 102. Aisha and Wei have none. Their absence from orders is valid. A foreign key requires a parent for each child, but does not require every parent to have a child.

One-to-one needs uniqueness too

In user_profiles(user_id, bio, photo_url), let user_id be both the primary key and a foreign key to users. Every profile refers to one user, and a user can have at most one profile because the profile key cannot repeat. A user may have no profile yet.

A foreign key without uniqueness would allow several profiles for the same user, giving one-to-many. The uniqueness constraint changes the maximum. Such separate profile tables can keep optional or sensitive details apart from the main user record.

Many-to-many uses a table of pairs

An order can contain several products, and a product can appear in several orders. A junction table, also called a bridge, linking or associative table, records each pairing:

order_id refers to orders; product_id refers to products. The composite primary key (order_id, product_id) allows the same product in different orders but prevents the same pair appearing twice. Two one-to-many links implement the many-to-many relationship.

quantity and unit_price belong to the pairing. They describe how much of that product was sold in that order and the price charged then. A current product price may change later; it must not rewrite the historical charge.

For order 101, the two line amounts are two mugs at $12.50, giving $25.00, and one charger at $8.99. Together they cost $33.99. The quantity says how many mugs were bought; storing two duplicate mug lines would conflict with this table's chosen key.

Avoid lists and fixed slots

A product_ids cell containing “11,12” hides several references inside one text value. Columns such as product1, product2 and product3 impose a fixed limit and leave unused slots. A junction table adds a row for each additional pairing and lets the DBMS check each reference separately.

A self-referencing relationship uses the same pattern inside one table: many employees can refer to the same manager through manager_id. The manager is also an employee row. Read each link as a role, so employee identity and manager reference stay distinct.

A schema describes tables, columns, data types and constraints. An instance is the data stored at a particular moment. Adding an order changes the instance; adding a new column changes the schema. The schema tells what can be stored, while the rows tell what is stored now.

Read a short description

customers(customer_id, first_name, last_name, email, phone, city, state, zip_code) lists a table and its columns. Notes may underline primary keys or mark them PK, and mark references FK. For example, orders(order_id PK, customer_id FK → customers, order_date, shipped_date) identifies the main key and the customer link.

Read a full table definition

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  order_date DATE NOT NULL,
  shipped_date DATE
);

Read one column at a time. order_id is the integer primary key. customer_id is required and must refer to a customer. order_date is required. shipped_date may be NULL. This definition gives the allowed structure, not a list of existing orders.

Constraints answer different questions:

A default is not a replacement for a supplied invalid value. If status has DEFAULT 'pending', omitting status supplies 'pending'; a supplied value still has to meet the column's other rules.

Combine rules for an order line

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(8, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The primary key at the bottom uses two columns. A second line for the same order and product fails. Quantity 0 fails the check. A missing quantity fails NOT NULL: the check alone would allow NULL because NULL > 0 is UNKNOWN, not FALSE. Explicit NOT NULL on both key parts also avoids SQLite's historical nullable-composite-key exception. In SQLite, use integer cents for an exact unit_price; the decimal declaration alone does not provide exact storage.

Find the schema and its meanings

In the SQLite shell, .schema orders displays the definition; in PostgreSQL's psql, \d orders describes it. INFORMATION_SCHEMA views list structural metadata in PostgreSQL, MySQL and SQL Server. SQLite lists objects in sqlite_schema, and graphical tools show tables and columns in a tree.

“Schema” also names a namespace inside systems such as PostgreSQL and SQL Server. sales.orders means the orders table in the sales schema. A data dictionary supplies meanings the structure cannot express, such as the price currency or whether a missing shipping date means “not shipped.” To see rows, a query such as SELECT * FROM orders; reads the current instance.

A schema diagram draws each table as a box. The title names the table; the contents list columns and often types. PK marks primary keys, and FK marks foreign keys. Relationship lines show which foreign key refers to which parent key.

First identify the kind of line

Some diagrams use an arrow from a foreign key to its referenced key. An arrow from orders.customer_id to customers.customer_id describes the reference's direction. Other tools use end symbols to describe how many rows can be linked. Check the diagram's legend: an arrow direction and a relationship's size are different information.

Read crow's foot symbols

In crow's foot notation, a bar means one, a circle means zero, and a three-pronged foot means many. Each line end states a minimum and maximum. The symbol nearest the table gives the maximum; the other gives the minimum.

When reading from one table, look at the symbols beside the other table. Those symbols describe how many rows there can match your one starting row.

Work through customers and orders

The store line has exactly-one symbols beside customers and zero-or-more symbols beside orders.

1. Start with one customer. Look at the orders end: the customer can have zero or more orders.
2. Start with one order. Look at the customers end: the order belongs to exactly one customer.
3. Check the definition: the non-NULL foreign key is in orders, so it is the many side, and each order needs a valid customer.

The crow's foot is next to the table holding the repeating foreign key. Reading the near end while starting from that same table reverses the meaning. A nullable customer reference would change the customers end to zero-or-one; it would not limit each customer to one order.

Recognize a junction table

The store has one-to-many links from orders to order_items and from products to order_items. Both many ends meet at order_items. Its two foreign keys form the primary key. This structure lets an order contain several products and a product appear in several orders.

The diagram describes allowed connections, not today's counts. It permits Maria to have several orders and Aisha to have none. To learn how many each actually has, read the rows. A parent-to-child minimum of one may express an additional business rule; a foreign key by itself does not require a parent to have children.

Recognize related diagram styles

An entity-relationship diagram made during design may show business entities before tables exist. Chen-style diagrams use ovals for attributes and diamonds for relationships. A schema diagram shows the actual tables and their declared keys. Both help communicate structure, but their symbols need to be read according to their own legend.

Locate each requested fact and follow the keys connecting it to the starting record. Names can repeat or change; match key values instead.

Find an order's customer

“Where does the customer who placed order 102 live?” follows this path:

1. Find order 102 by orders.order_id.
2. Read its customer_id: 2.
3. Find customer 2 by customers.customer_id.
4. Read customers.city: Denver.

Order number 102 is not the customer identifier. SQL joins automate these key matches; display positions do not connect the records.

Find products and a historical total

Order 101 has these order_items rows. Follow each product_id to products for its name:

Use the recorded line price for the amount charged:

• Two mugs at $12.50 cost $25.00.
• One charger at $8.99 costs $8.99.
• Total: $33.99.

Today's product price could misstate an old order's cost. Order 102 has one lamp at $34.00; order 103 has four mugs at $12.50, totaling $50.00.

Interpret missing values and rules

Order 102's shipping date is NULL, documented here as “not shipped.” Test it with IS NULL, not equality to NULL.

Read every relevant constraint when judging a change:

• A line for order 104 fails if that order does not exist.
• Another line with key (101, 11) fails because the pair exists.
• Quantity 0 fails the positive-quantity check.
• Default foreign-key behavior blocks deleting product 11 while lines reference it.

An unused key does not make a row valid when its reference is missing.

Identify the new rows

A returning customer buying two different existing products adds one orders row and two order_items rows. The customer and products already exist. Offering a new product adds one products row, without requiring an order.