Topic 1 of 5
Relational data concepts
The relational model is the most widespread way to store transactional data: inventories, e-commerce, customer data. The exam asks about its features, what normalization is, and what keys are for. These concepts also show up in SQL and in Azure services.
Contents
Why the relational model exists
In early systems, every application stored data in its own structure. Using that data meant knowing the structure in depth, and those structures were inefficient, hard to maintain, and hard to optimize. The relational model solved that with a standard way to represent and query data that any application can use.
Its big advantage is tables: an intuitive, efficient, and flexible way to store structured information. It fits any information with related elements that must be organized in a consistent, rules-based structure.
Tables, rows, and columns
In a relational database, real-world entities are modeled as tables. An entity is anything you want to record information about, typically important objects and events.
Table
A collection of entities: Customer, Product, Order, LineItem.
Row
One instance of the entity: a specific customer, a specific order.
Column
An attribute: email, price, order date.
Relational tables are a format for structured data: every row in a table has the same columns.
Data types and NULL
Each column stores data of a specific type. The available types depend on the database system, though ANSI (the American National Standards Institute) defines standard types that most systems support.
| Column | Suitable data type |
|---|---|
| Email (Customer) | Text, fixed or variable length |
| Price (Product) | Decimal number |
| Quantity (Order) | Integer |
| OrderDate (Order) | Date/time |
NULL
Not every column needs a value. A MiddleName column can be empty (NULL) for customers with no middle name or whose middle name is unknown. The column still exists in every row.
Normalization
A schema design process that minimizes data duplication and enforces data integrity. There are complex formal rules (the "normal forms"), but in practice it comes down to:
Separate each entity into its own table.
Separate each discrete attribute into its own column.
Uniquely identify each entity instance (row) with a primary key.
Use foreign key columns to link related entities.
Example: from spreadsheet to tables
A company tracks its sales in a spreadsheet:
| OrderNo | CustomerName | CustomerAddress | ProductName | UnitPrice | Qty |
|---|---|---|---|---|---|
| 1 | Jane Smith | 42 Oak St Seattle | Widget A | 9.99 | 2 |
| 1 | Jane Smith | 42 Oak St Seattle | Widget B | 4.49 | 1 |
| 2 | Bob Jones | 18 Pine Ave Portland | Widget A | 9.99 | 5 |
| 3 | Jane Smith | 42 Oak St Seattle | Gadget X | 24.99 | 3 |
Problems
Customer and product details are repeated for every item sold, and the address mixes street and city in a single cell.
After normalizing
Customer, Product, Order, and LineItem tables, each attribute in its own column. Changing Jane's address only requires modifying one row.
- Splitting attributes into columns forces each value to have the right type: decimal prices, integer quantities.
- It also gives more granularity for querying: easily filtering customers who live in a specific city.
Primary and foreign keys
Primary key
An ID or other value that uniquely identifies each instance of the entity. Example: the customer ID.
Foreign key
When one entity references another, it stores the related entity's primary key. Example: the order stores the customer ID.
That way, the customer's address is stored only once and each order looks it up through the Customer table.
Referential integrity and composite keys
Referential integrity
A relational database management system (RDBMS) can ensure that a foreign key value has a matching primary key in the related table. That prevents, for example, orders for nonexistent customers.
Composite key
A key (primary or foreign) can be based on a unique combination of several columns. The LineItem table uses OrderNo + ItemNo to identify each line of an order.
Exam scenarios
| Situation | Concept | Why |
|---|---|---|
| Change a customer's address in a single place | Normalization | Each entity in its own table, no duplicates. |
| Uniquely identify each product | Primary key | A unique value per row. |
| Store in each order which customer it belongs to | Foreign key | A reference to the Customer primary key. |
| Prevent orders for customers that don't exist | Referential integrity | The foreign key must exist in the related table. |
| Identify an order line by order number and line number | Composite key | A unique combination of several columns. |
| Filter customers by city without searching inside text | Attributes in separate columns | More granularity for querying. |
| A customer with no middle name | NULL value | The column exists but has no value. |
Common exam traps
❓ Does normalization aim to duplicate data so queries run faster?
No. It aims to minimize duplication and enforce integrity. Denormalizing is what's done for analytics.
❓ Does a foreign key uniquely identify the rows in its own table?
No. That's the primary key; the foreign key references another table's primary key.
❓ Can a key be made of several columns?
Yes: that's a composite key, like OrderNo + ItemNo.
❓ If a column allows NULL, does the table stop having a fixed schema?
No. The column still exists; it can just be left without a value.
❓ What stops you from recording an order for a customer that doesn't exist?
The referential integrity the RDBMS enforces on the foreign key.
❓ Are data types identical across all database systems?
No. They depend on the system, though ANSI defines standard types most support.
Did you get this topic?
Test what you just learned
In a sales database, the Order table has a CustomerID column that points to the ID in the Customer table. What is CustomerID in the Order table?
Ready to test "Relational data concepts"?
Original questions, each with a full explanation.
Start a mock exam