Topic lessonTopic: SQL Server and database foundations
Database basics: tables, keys, reads and writes
A database holds data; a database management system (DBMS) manages that data. SQL Server is one DBMS. Use five documents and three owners to distinguish tables, rows, columns, a document’s primary key and its foreign-key reference to a person.
Distinguish a document identifier from a person reference, explain table structure and read/write roles, and avoid treating NULL as zero or an absent row.
No database knowledge is assumed. Reason about a fictional internal document list without running SQL or operating a server.
The database, DBMS and application have different jobs
The DocView application displays a document list. The CorpDocs database holds document and person data; SQL Server processes requests as the DBMS. SSMS is a management tool, not the stored table.
| Role | Example | Job |
|---|---|---|
| Application | DocView | Handles user requests and screens |
| DBMS | SQL Server | Manages data, queries and access |
| Database | CorpDocs | Groups data and database objects |
| Management tool | SSMS | Connects to the engine to inspect and query |
Read a five-row table by rows and columns
| Document ID | Title | Owner ID | Status | Reviewed on |
|---|---|---|---|---|
| 101 | Handbook | 1 | open | NULL |
| 102 | Diagram | 1 | open | 2026-10-22 |
| 103 | Budget | 2 | draft | NULL |
| 104 | Archive | 3 | open | 2026-10-23 |
| 105 | Unassigned | NULL | open | NULL |
Each row is one document; columns describe its ID, title, owner, status and review date. Document 101 is Handbook, owned by person 1, open and without a specified review date. These are committed fictional rows at T0, held unchanged during the examples.
| person_id | Name |
|---|---|
| 1 | Aki |
| 2 | Mei |
| 3 | Ren |
Primary keys identify; foreign keys reference
document_id is unique per document; owner_id references an owner.
person_id identifies a person; multiple documents can reference the same person.
Arrows identify foreign-key references, not a data or network execution path.
A fictional teaching example. Containment, communication and permission relationships have different meanings.
A primary key is a column or column combination that uniquely identifies a table row. Here document_id identifies documents 101 and 102 separately. SQL Server primary keys do not allow duplicates or NULL. A primary key does not by itself guarantee SELECT result order.
A foreign key constrains a reference to a key. Non-NULL owner_id values reference People.person_id. Documents 101 and 102 both belong to Aki, so owner_id=1 occurs twice. A person reference does not uniquely identify a document.
Separate reading from changing data
Reading retrieves columns and matching rows; updating changes values in matching rows. Using a DBMS does not decide which identity may read or modify a table or which documents the application should display.
| Principal | Stipulated access | Not implied |
|---|---|---|
| reader_demo | SELECT on two required CorpDocs tables | Updates or another database |
| editor_demo | SELECT and UPDATE(status) in a separate example | Administration of all databases |
Next, separate SQL Server, T-SQL and SSMS and choose a study path. Understanding the table lets you connect product and connection concepts to the same data.
Check tables and keys
Choose an option to see the result and explanation.
Check, then explain in your own words
- Identify documents 101 and 102 by their primary keys.
- Explain the repeated owner_id=1 using the person table.
- Explain that document 105 exists while its owner is unspecified.
- Describe the app, DBMS, database and management tool in one sentence each.
Check your understanding: Does owner_id=1 appearing twice duplicate the document primary key?
No. The document primary keys, 101 and 102, are distinct.
An owner reference can repeat across documents. Document identity and owner reference have different roles.
Sources and conditions checked
Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.
- What is SQL Server?Checked:
Scope and conditionsSQL Server is an RDBMS and uses T-SQL. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonThe database, DBMS and application have different jobs Check tables and keys
Scope and conditionsSSMS is a management/query client, not the database engine. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonThe database, DBMS and application have different jobs
Scope and conditionsA primary key identifies a row and a foreign key enforces a reference; this sample explicitly allows a NULL owner. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonPrimary keys identify; foreign keys reference Check tables and keys
Scope and conditionsRow order is not guaranteed without ORDER BY. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonPrimary keys identify; foreign keys reference
- NULL and UNKNOWN (Transact-SQL)Checked:
Scope and conditionsNULL comparisons yield UNKNOWN; use IS NULL. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonPrimary keys identify; foreign keys reference Check tables and keys
- SELECT (Transact-SQL)Checked:
Scope and conditionsSELECT returns rows, and WHERE can filter them. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonSeparate reading from changing data
- UPDATE (Transact-SQL)Checked:
Scope and conditionsT-SQL UPDATE supports a WHERE condition to narrow targeted rows. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonSeparate reading from changing data
- Principals (Database Engine)Checked:
Scope and conditionsServer-level and database-level principals have different scopes. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonSeparate reading from changing data