Database Fundamentals
FoundationFile processing breeds duplicated, inconsistent data, while a database management system stores and controls data centrally. A relational database organises records and fields in tables: primary keys identify records uniquely, foreign keys link tables, normalisation cuts redundancy, and SQL's SELECT and WHERE retrieve and filter records.
- File processing breeds redundancy and inconsistency; a database with a DBMS stores data centrally and controls access
- A relational database organises data in tables: fields are attributes, records are complete entries
- A primary key is unique and non-empty; foreign keys link tables under referential integrity
文體小知識：科技知識說明文——講解數據庫的結構、關聯數據庫的設計概念與基本查詢方法，是選修單元「數據庫」的入門知識。
§Text Passage
1When organisations handle large amounts of data, many started with file processing: storing data in separate files that each program read and wrote on its own. This approach creates problems. The same student data may sit in both the discipline office's file and the academic office's file; when one copy is updated and the other is missed, the data becomes inconsistent. Programs also depend on file structures, so changing one format means touching everything related.
2Databases were created to solve these problems. A database stores an organisation's data centrally and manages it as one unit, while a database management system handles adding, querying, updating and deleting data and controls access rights, so only authorised users can read or write the data. A database administrator sets permissions, performs backups and looks after daily maintenance, keeping the data safe and consistent.
3The most common type, the relational database, organises data in tables. Each table describes one kind of thing; the vertical columns are fields and the horizontal rows are records. Every table should have a primary key, such as the student ID in a student table, so that every record can be uniquely identified. Tables are linked through foreign keys: a borrowing table stores the student ID, pointing to the student table, creating a relationship between tables.
4When designing a database, avoid data redundancy — the same data stored repeatedly in several places. Normalisation distributes data properly across tables: each table describes one kind of thing, non-key data is stored only once, and related data is fetched through keys. This saves space, means an update touches only one place, and, with referential integrity, prevents records from pointing to data that does not exist.
5Users communicate with databases through a database query language, most commonly the Structured Query Language. A basic query states which fields to select, from which table, and filters records with a condition — for example, selecting the titles of all science books from the book table. Grasping this idea of select fields, name the table and filter by condition explains how queries work, from a library system down to the contact list on a phone.
1Summary 重點摘要
File processing breeds duplicated, inconsistent data, while a database management system stores and controls data centrally. A relational database organises records and fields in tables: primary keys identify records uniquely, foreign keys link tables, normalisation cuts redundancy, and SQL's SELECT and WHERE retrieve and filter records.
2Vocabulary cards Vocabulary
A centralised collection of related data stored and managed as one unit; some books also call it a data store.
Software that manages a database: adding, querying, updating and deleting data while controlling access rights.
A database that organises data into tables linked by keys.
A vertical column in a table describing one attribute of each record, such as a book title.
One horizontal row of a table, gathering all field values for a single item.
The field that uniquely identifies each record in a table; it can neither repeat nor be empty.
A field in one table that refers to the primary key of another table, linking the two tables.
The unnecessary duplication of the same data in more than one place, which risks inconsistency.
The design process of organising data into well-structured tables to reduce redundancy.
The standard language for querying relational databases, using statements such as SELECT, FROM and WHERE.
3Step by step Steps
- Step 1: The limits of file processingEarly organisations managed data with file processing: each department built its own files and each program read and wrote them directly. For example, the discipline office and the academic office each kept a copy of the same student data; when a student transferred out, only one copy was updated and the two versions disagreed. Such data redundancy wastes storage and, worse, undermines reliability. Programs and file structures were also tightly coupled — adding one field meant changing every related program. These limitations led to the birth of the database.
- Step 2: Database and DBMSA database stores all of an organisation's data centrally, managed by a database management system; programs reach the data through it without worrying about underlying file formats. For example, a school database serves both the attendance system and the library system, so the student data is maintained only once. The DBMS also controls access rights — a teacher may read marks but not the payroll table — and provides backup and recovery, keeping the data safe and consistent.
- Step 3: Tables, records and fieldsA relational database is built from tables, each describing one kind of thing. Take a book table: the vertical fields describe attributes such as book ID, title and category, while each horizontal record holds the complete data of one book. Choose a suitable data type for each field — text for the ID, date for the borrowing date. The three levels of table, record and field are the basis of database structure and the vocabulary used in both design and queries; answers must use them precisely.
- Step 4: Primary key and entity integrityEvery table needs a primary key that uniquely identifies each record. For example, two students may share a name, but student IDs never repeat, so the ID is a good primary key while the name is not. The primary key's values must be unique and cannot be empty — a requirement called entity integrity. A table may have several suitable candidate keys; the designer picks one as the primary key and the rest become alternate keys. In answers, name the chosen primary key and state the two requirements of uniqueness and non-emptiness.
- Step 5: Foreign keys and links between tablesThe word relational refers exactly to tables linked by foreign keys. In a library, the borrowing table stores the student ID, pointing to the primary key of the student table, so we know who borrowed; it also stores the book ID pointing to the book table, so we know what was borrowed. A foreign key's value must match a real record in the referenced table — a constraint called referential integrity. If someone tries to record a student ID that does not exist, the DBMS should reject it, protecting correctness.
- Step 6: Redundancy and normalisationA central aim of database design is minimising data redundancy. For example, if the book title and author were copied into the borrowing table for every loan, one book's details would repeat dozens of times and a title change would need dozens of edits. Normalisation distributes the data: book details go in the book table, people in the student table, and each borrowing event in the borrowing table, all linked by keys. Each item is stored once, one update touches one place, consistency improves and storage is saved.
- Step 7: Querying with SELECT and WHERESQL is the standard language for talking to relational databases, and the DSE only requires the basic concept. The skeleton of a query: SELECT names the fields to retrieve, FROM names the table, WHERE filters records by a condition. For example, SELECT Title FROM Book WHERE Category = 'Science' means: from the book table, list the titles whose category is science; drop the WHERE clause and every record is listed. Understanding these three actions — select fields, name the table, filter rows — is enough to read a simple query and handle display-style questions.
5Worked example Example
Design task: a school library records loans in spreadsheets, which keeps producing duplicated and inconsistent entries. The librarian decides to move to a relational database with three tables: Book, Student and Loan. Identify the primary key of each table, explain how the tables are linked, and express the query 'list all overdue book IDs' in display-style query terms.
- First separate the three kinds of things: books, students and borrowing events each become one table, instead of cramming everything into one table and creating redundancy
- Choose a primary key for each table: Book ID for the Book table, Student ID for the Student table, and Loan ID (or Book ID plus borrowing date) for the Loan table
- Link the tables with foreign keys: the Loan table stores Book ID and Student ID, pointing to the primary keys of the Book and Student tables, forming one-to-many relationships
- The query concept: select the Book ID column from the Loan table where the return-date field is empty, understood as SELECT BookID FROM Loan WHERE ReturnDate IS NULL
- Answer: The three tables are the Book table (primary key: Book ID), the Student table (primary key: Student ID) and the Loan table (primary key: Loan ID). The Loan table carries two foreign keys, Book ID and Student ID, pointing to the Book and Student tables, recording who borrowed which book. With loans in their own table, titles and names are stored only once, cutting redundancy sharply. To list overdue books, select the Book ID column from the Loan table and use WHERE to filter records that have not been returned (empty return date): SELECT BookID FROM Loan WHERE ReturnDate IS NULL. The marking points are: three tables with clear roles, correct primary keys, foreign keys pointing to the right tables, and a query with all three elements.
6Common mistakes Mistakes
7Quick check Quick check
Q1Compared with file processing, what main problem does a database solve?
Q2What two requirements must a primary key meet?
Q3What is the role of a foreign key?
Q4What do SELECT, FROM and WHERE each do?
Q5Why should borrowing records form their own table?
8Recap Recap
- File processing breeds redundancy and inconsistency; a database with a DBMS stores data centrally and controls access
- A relational database organises data in tables: fields are attributes, records are complete entries
- A primary key is unique and non-empty; foreign keys link tables under referential integrity
- Normalisation splits data by kind of thing, storing each item once — the way to cut redundancy
- A query has three parts: SELECT fields, FROM table, WHERE condition