The Database Management System (DBMS)
Vocabulary
| English | Chinese | Pinyin |
|---|---|---|
| DBMS | 数据库管理系统 | shù jù kù guǎn lǐ xì tǒng |
| view | 视图 | shì tú |
| backup | 备份 | bèi fèn |
| data management | 数据管理 | shù jù guǎn lǐ |
| data dictionary | 数据字典 | shù jù zì diǎn |
| data modelling | 数据建模 | shù jù jiàn mó |
| logical schema | 逻辑模式 | luó jí mó shì |
| data integrity | 数据完整性 | shù jù wán zhěng xìng |
| data security | 数据安全 | shù jù ān quán |
| transaction | 事务 | shì wù |
| concurrent access | 并发访问 | bìng fā fǎng wèn |
| concurrency control | 并发控制 | bìng fā kòng zhì |
| developer interface | 开发者接口 | kāi fā zhě jiē kǒu |
| query processor | 查询处理器 | chá xún chǔ lǐ qì |
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.
Explore
Database service lab
Watch how a DBMS turns a query into safe shared data access.
Practice
Match each DBMS service to what it does.
A DBMS bundles these services so programs never touch the raw files directly.
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.
Practice
A data dictionary in a DBMS holds:
The data dictionary describes the database structure, so programs query it rather than hard-coding the layout.
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
Practice
The logical schema of a database describes:
The logical design is separate from the physical storage, which is what lets the storage change without the programs noticing.
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.
Practice
Which are data-security features of a DBMS? Select all that apply.
Who may access, and recovery after loss, are security. Normalisation is a design step against redundancy.
Worked example: how the DBMS keeps the data secure
Example
- Describe how a DBMS provides data security. [4]
- Each user has an account and password, so only authorised people log in. The administrator gives each user or group access rights to each table: a clerk may read the prices but not change them; a customer cannot see the staff table at all. A view can present a user with only the fields they need.
- The DBMS runs backup procedures: a regular copy of the database to a separate medium, with a log of transactions since the copy, so a failure or an attack loses no data.
- Security is about who may access; integrity is about the data being correct. Keep them apart when the question names one.
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.
Practice
A database transaction is:
Transactions are all-or-nothing, so the database is never left half-updated.
Practice
Concurrency control (locking) is needed so that:
Locks stop two simultaneous updates from clashing and corrupting shared data.
Practice
A transaction is all-or-nothing: it either fully completes (commit) or fully undoes itself (rollback), so the database is never left half-updated.
This atomicity is why a bank transfer can never debit one account without crediting the other.
Practice
A virtual table (often a saved query) that shows a user only their relevant slice of the data is called a ______.
A view presents a tailored, often restricted, slice of the data without copying it.
Practice
Put the events of two users updating the same record under concurrency control in order.
The lock serialises the two updates so neither overwrites the other's change.
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.
Practice
Match each DBMS component to its job.
Running queries, building applications, describing the structure, and the design itself: four different things the exam names separately.
Worked example: the two tools in use
Example
- A shop's database is used by the manager, who needs sales totals, and by a programmer writing a stock-control application. Explain how the query processor and the developer interface are used.
- The manager writes or builds a query for the sales totals; the query processor parses it, uses the data dictionary to check the tables and fields exist, chooses the fastest way to run it, executes it and returns the results.
- The programmer uses the developer interface: the form and report builders to make the stock screens, and the API so the application's code can send SQL to the DBMS and receive the records back.
- Two marks each: what the tool is, and what this person does with it.
Marks that slip away
Watch out
- The DBMS is the software, not the data. "The DBMS holds the customer records" is wrong; it manages them.
- The data dictionary holds data about the data: structures, types, keys, rights. Not the records themselves.
- The logical schema is the design independent of physical storage. Say that phrase.
- Security is who may access; integrity is the data being correct. Backup belongs to security.
You've got it
Key idea
- a DBMS is the software every program goes through: one copy of each fact, data independence, central rules
- data management with a data dictionary (data about the data) · data modelling producing the logical schema (the design independent of storage) · data integrity (rules enforced) · data security (access rights to users and groups, passwords, encryption, backups)
- concurrency control with locks, transactions that are all-or-nothing, views that show a user their slice
- tools: the developer interface for building applications, the query processor for running SQL