データベース管理システム（DBMS）

Two people, one seat

• In 1960 American Airlines switched on Sabre, the first computer reservation system. Agents in dozens of cities could sell seats on the same flight at the same moment, and the system's one job that mattered was that no seat was ever sold twice.
• A shared file cannot do that: two programs read "seat 14C free", both write "sold", and two passengers arrive at the gate. Something has to sit between every program and the data and referee.
• That referee is the DBMS 数据库管理系统. Every feature in this lesson is a thing the flat files could not do: describe themselves, keep every user's view consistent, enforce the rules, survive a crash, and let one team build applications while another asks questions.
• This lesson is the features the syllabus names and the two software tools it asks you to describe.

What a DBMS is

• A database management system is the software that manages the database centrally. Every program and every user reaches the data only through it.
• It answers each file-based limitation: one copy of each fact, so no redundancy or inconsistency; data independence, so programs are not tied to the storage; central integrity, security, backup and access for many users at once.
• The syllabus names five features and two tools. Learn each one as a thing the DBMS does.

Data management and the data dictionary

• Data management 数据管理 is the DBMS controlling how the data is stored, organised, retrieved and updated, so that programs never touch the files directly.
• It maintains a data dictionary 数据字典: a description of every table, field, data type, key, relationship and constraint, plus who may access what. It is data about the data.
• Programs query the dictionary instead of hard-coding the structure, so a change to a table does not break them. It is also what the DBMS checks when it validates a new record.

Data modelling and the logical schema

• Data modelling 数据建模 is defining the structure of the data: the entities, their attributes, the keys and the relationships, as in an E-R diagram.
• The result is the logical schema 逻辑模式: the overall logical design of the database, its tables and relationships, described independently of how the bytes are stored on disk.
• Because the logical schema is separate from the physical storage, the storage can be reorganised, indexed or moved and the programs still see the same tables.

The programs see tables; the DBMS sees disks

Data integrity and data security

• Data integrity 数据完整性: the DBMS keeps the data correct and consistent by enforcing the rules centrally: primary keys unique and not null, referential integrity on every foreign key, data types, and validation constraints such as a range or a value from a list.
• Data security 数据安全: the DBMS controls who may see and change what. Access rights are given to individual users or to groups, table by table, as read-only, read and write, or no access; passwords authenticate each user; stored data can be encrypted.
• Security also means backup 备份 procedures: regular copies of the database, with a log of changes since, so the data can be recovered after a failure.

Worked example: how the DBMS keeps the data secure

Many users at once

• Concurrent access 并发访问: many users work on the database at the same time. Concurrency control 并发控制 uses locks so two users cannot update the same record at once and corrupt it: the second waits until the first has finished.
• A transaction 事务 is a group of operations that either all succeed or all fail, so a crash halfway through a transfer never leaves money missing from both accounts.
• A view 视图 is a virtual table, built from a query, that shows each user only their slice of the data.

The software tools

• The developer interface 开发者接口 is the set of tools and programming interfaces for building applications on the database: a form builder for data entry, a report generator, a query builder, an SQL editor, a data-dictionary editor, and APIs that let a program in Python or Java send SQL and receive results.
• The query processor 查询处理器 takes a query written in SQL, checks it against the data dictionary, works out the most efficient way to run it, executes it and returns the result set.
• The user who types a query and the programmer who builds the booking form are both using the DBMS; one through the query processor, the other through the developer interface.

Worked example: the two tools in use

Marks that slip away

You've got it