INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

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.

Prerequisite

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.

Do not collapse these roles into the word database
RoleExampleJob
ApplicationDocViewHandles user requests and screens
DBMSSQL ServerManages data, queries and access
DatabaseCorpDocsGroups data and database objects
Management toolSSMSConnects to the engine to inspect and query

Read a five-row table by rows and columns

Documents: five committed rows in CorpDocs.app.Documents
Document IDTitleOwner IDStatusReviewed on
101Handbook1openNULL
102Diagram1open2026-10-22
103Budget2draftNULL
104Archive3open2026-10-23
105UnassignedNULLopenNULL

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.

A separate owner table: app.People
person_idName
1Aki
2Mei
3Ren

Primary keys identify; foreign keys reference

Separate document identity from its owner reference
Documents

document_id is unique per document; owner_id references an owner.

People

person_id identifies a person; multiple documents can reference the same person.

Document 101: Handbookowner_id = 1 1: Aki
Document 102: Diagramowner_id = 1 1: Aki
Document 103: Budgetowner_id = 2 2: Mei
Document 104: Archiveowner_id = 3 3: Ren
Document 105: Unassignedowner_id = NULL Owner unspecified

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.

Two principals used in later lessons
PrincipalStipulated accessNot implied
reader_demoSELECT on two required CorpDocs tablesUpdates or another database
editor_demoSELECT and UPDATE(status) in a separate exampleAdministration 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.

Documents 101 and 102 both have owner_id=1. Which column identifies each document?
Read the answer and reason

Answer: document_id · Its primary-key values distinguish the two documents.

Which best describes a DBMS in this document app?
Read the answer and reason

Answer: Stores data and processes authorized queries · A DBMS stores data and evaluates queries subject to permissions.

What does owner_id=NULL mean for document 105?
Read the answer and reason

Answer: The owner is not specified in this example · NULL does not designate person 0.

Check, then explain in your own words

  1. Identify documents 101 and 102 by their primary keys.
  2. Explain the repeated owner_id=1 using the person table.
  3. Explain that document 105 exists while its owner is unspecified.
  4. 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.