Database concepts
FoundationA database stores masses of data in order: tables split into records (rows) and fields (columns); the primary key identifies each record uniquely (student number, not name). Relational databases link tables; huge, multi-user, error-proof data needs a database, small personal maths a spreadsheet.
- A database stores masses of data in order; the DBMS is the software managing it.
- Tables: rows are records (one individual), columns are fields (one attribute).
- The primary key identifies each record uniquely: unrepeatable, never empty, never changing — the student number, not the name.
文體小知識：原理講解課先講清楚概念和規則，再用實例演示，令抽象的設計方法變得具體易明。
§Text Passage
1A database is a system that stores large amounts of data in an organised way, with a database management system (DBMS) handling access. At its core is the table: shaped like a spreadsheet worksheet, where each row is a record (all the data of one student) and each column is a field (such as name, class, score).
2Every table needs a primary key: a field that never repeats and is never empty, uniquely identifying each record — student numbers like 2023001, 2023002. Could the name be the primary key? No — a class can hold two Marys; only the student number identifies reliably. A relational database goes further: several tables link through shared fields — a scores table stores only the student number and joins back to the students table for the name.
3Compared with spreadsheets: spreadsheets suit individuals handling tens to hundreds of rows with calculations and charts; databases suit tens of thousands to millions of rows, many users reading and writing at once, fast searching and protection against wrong input. A school library catalogue of hundreds of thousands of books, or a citywide student record system, must use a database.
4Databases are everywhere in daily life: every Octopus journey, the hospital authority's booking system, an online shop's orders and stock — all run on databases. The standard language for talking to databases is SQL (Structured Query Language) — the next lessons use it to query data.
1Summary 重點摘要
A database stores masses of data in order: tables split into records (rows) and fields (columns); the primary key identifies each record uniquely (student number, not name). Relational databases link tables; huge, multi-user, error-proof data needs a database, small personal maths a spreadsheet.
2Vocabulary cards Vocabulary
A system storing and managing large amounts of data in an organised way for searching, updating and error prevention.
The two-dimensional structure holding one kind of data, made of rows (records) and columns (fields).
One row of a table — all the data of a single individual, e.g. one student.
One column of a table — a single attribute of the data, e.g. name, class.
The field uniquely identifying each record — never repeated, never empty, e.g. student number.
A database of tables linked by shared fields, e.g. a scores table joining the students table by student number.
The software managing a database — access, permissions and searching — e.g. MySQL, SQLite.
A request made to a database, e.g. 'list all students in 3A', usually written in SQL.
The same data stored in several places, which easily causes inconsistency after updates.
3Step by step Steps
- Meet a students tableThe heart of a database is the table, shaped much like a spreadsheet. Take a students table: it has three fields — student number, name and class; the header row lists the field names, and every row below is one record, e.g. (2023001, Mary, 3A), (2023002, John, 3B), (2023003, Peter, 3A). Remember the rule: the header holds the fields, and each row is one student's complete data. Reading a table's structure is the first step of learning databases.
- Telling records from fieldsRecords and fields are easily confused. In the students table above, the row '(2023002, John, 3B)' is one record — all the data of one student; the vertical column 'class' is one field — the same kind of data for every student. Mnemonic: rows are individuals, columns are attributes; one row per student, one column per data item. Exams often show a table and ask whether a marked part is a record or a field: ask whether it is a 'person' or a 'kind of data', and the answer is clear.
- Choosing a primary keyThe primary key is the field that uniquely identifies each record. Suppose the table holds two students both named Mary, with student numbers 2023001 and 2023004: identifying by name mistakes one for the other; by number is always right. A primary key has three conditions: never repeated, never empty, and ideally never changing. Names repeat and phone numbers change, so neither suits; a student number, assigned once by the school, is ideal. Every table must have a primary key — a guaranteed exam point.
- Linking tables in a relational databaseA relational database splits data across several tables joined by shared fields. For example, the scores table stores only (student_id 2023001, Maths, 85) with no name kept; to show the name, the database uses 2023001 to look up 'Mary' in the students table. The two tables join by student number, so names are never duplicated and redundancy is avoided: a name change is made in one place, and every score record follows automatically. This consistency is the great strength of the relational design.
- Database vs spreadsheetSpreadsheets and databases each have their strengths. Analysing one class of 40 scores, Excel finishes in ten minutes; but 400,000 students citywide, hundreds of teachers entering at once, and the need to block wrong values like '3Z' in the class column call for a database, which excels at concurrent access, fast searching of vast data and enforcing integrity rules. A simple rule: little data, single user — spreadsheet; much data, many users, error-proofing needed — database.
- Data integrity rulesA database can attach rules to every field to keep bad data out: scores must be numbers from 0 to 100; classes must be 3A to 3E; student numbers cannot be left empty. Any input breaking a rule is rejected, stopping junk data at the source. Spreadsheets rely on user discipline, and once a wrong value is in, it hides well. When exams ask 'why does the school system use a database instead of Excel', the answer should cite integrity rules, concurrent access and large volumes.
- Imagine a queryQuerying is the database's strength. A teacher wants '3A students scoring 80 or above in Maths': in a spreadsheet it takes several manual filter steps; in a database one SQL line does it — SELECT name FROM students WHERE class = '3A' AND score >= 80. The sentence means 'pick the names from the students table where the class is 3A and the score is at least 80'. The next lesson teaches SQL word by word; for now, remember that SQL is the standard language for talking to databases.
4Diagrams Diagrams
5Worked example Example
Example: design a 'loans table' for the school library — list suitable fields, name the primary key, and show how it relates to other tables.
- Step one, list the needed fields: loan_id, student_id, book_id, borrow_date, return_date — one field for each item of data to keep.
- Step two, pick the primary key: loan_id is unique per loan, never repeated and never empty; student_id and book_id both repeat — one borrower takes many books, one book is borrowed by many — so neither can be the key.
- Step three, set the relations: student_id joins the students table for the borrower's name; book_id joins the books table for the title; the loans table never stores names or titles itself, avoiding redundancy.
- Step four, add integrity rules: borrow_date cannot be empty; return_date stays empty while the book is out and, once filled, must not be earlier than borrow_date; any input breaking a rule is rejected.
- Answer: The loans table has fields loan_id (primary key), student_id, book_id, borrow_date and return_date; the primary key is loan_id because only it never repeats and never stays empty; the table joins the students table by student_id and the books table by book_id, storing no names or titles itself; integrity rules guard against wrong input, and the design is complete.
6Common mistakes Mistakes
7Quick check Quick check
Q1In a database table, what are a row and a column called?
Q2Which suits a students table's primary key best? A. name B. student number C. class
Q3Why does a scores table store the student number but not the name?
Q4Which tool suits analysing one class of 40 scores, and which suits a citywide system of 400,000 students entered by many users at once?
Q5'The score field accepts only numbers from 0 to 100' — which database mechanism is this?
8Recap Recap
- A database stores masses of data in order; the DBMS is the software managing it.
- Tables: rows are records (one individual), columns are fields (one attribute).
- The primary key identifies each record uniquely: unrepeatable, never empty, never changing — the student number, not the name.
- Relational databases join tables on shared fields, avoiding duplicate storage of the same data.
- Small personal maths: a spreadsheet; huge, multi-user, error-proof data: a database.