DATABASES REVISION

YOUR TOPIC 9 REVISION ROUTE

Begin with what one record represents. Then check its fields, types and identifier before practising queries. These ideas work together: a clear table design makes it easier to ask a precise question of the data.

Use Revise for explanations and worked examples. In Flashcards, answer before flipping, choose a section and mark uncertain cards. Copy your revision list to decide which linked lessons to revisit. The workbook is coming soon.

9.1 | DATABASES, TABLES, FIELDS AND RECORDS

A database is an organised collection of data. A DBMS is the software used to manage it, including storing, retrieving and enforcing configured rules. The data and the software are related but different.

A table groups records with the same fields. A field describes one attribute, such as Title. A record is the collection of field values for one item. In Books, one record means one catalogue title, not one physical copy of a book.

This table has four fields and five records. Copies contains five individual values. The headings name the fields; they are not another book record. B003, A Journey to Mars, Fiction and 4 together form one record.

Choose the meaning of a record before selecting fields. A loan table needs one row per loan; a catalogue table needs one per title. Mixing meanings leads to repeated or missing information.

9.1 | DESIGN FROM THE STORAGE REQUIREMENTS

Translate the information needed into clear field names. Store values separately when they need separate searching or processing. A club table might need MemberID, MemberName, JoinDate and Paid, rather than an unstructured paragraph describing every member.

A data dictionary documents each field’s purpose, type and rules. Rules should follow the scenario. For Books, Copies must be a whole number of available copies and cannot be negative. A chosen upper limit would need a stated library requirement.

Validation checks whether data meets rules, such as a required title or a non-negative count. Verification checks it against the source, such as comparing the entered title with the original document. Passing a range check does not prove the amount is true.

9.2 | MATCH THE TYPE TO THE FIELD’S PURPOSE

The type determines the kind of value stored and the operations that make sense. Choose from the field’s meaning, not only its appearance. An identifier 0072 is usually text so the zeros remain part of its code; a quantity is numeric.

Character does not mean “a short word”: A1 needs more than one character. Boolean does not suit a three-state status such as pending, approved and rejected. Integer can include zero and negative values; a non-negative Copies rule is additional validation.

Date/time storage supports chronological comparisons. A display format is not the stored date’s meaning. A string such as 30/02/2026 may match a pattern but is not a valid calendar date. Do not assume a date-shaped string is a date/time value.

9.2 | JUSTIFY A TYPE WITH A REASON

“Copies is INTEGER because it stores a count of whole copies” connects the choice to the requirement. “PhoneNumber is text because it is an identifier that can contain leading zeros or a plus sign, and is not an amount for arithmetic” explains why its digit appearance is misleading.

REAL allows fractions, but that alone does not validate a measurement’s permitted range. For exam-level price examples, REAL expresses a fractional numeric amount; practical databases may use exact decimal types for currency.

Ask whether the value will be counted, calculated, compared chronologically, or used as a label. Then add required-value and scenario checks separately.

9.3 | ONE KEY VALUE IDENTIFIES ONE RECORD

A primary key is the selected field, or set of fields, whose values uniquely identify each record in its table. Values must be unique and non-NULL. For the single-field examples here, BookID is the primary-key field and B003 is one key value.

Title is descriptive data. Different catalogue entries may share a title, so a unique appearance in a small sample does not guarantee it remains unique in future. Copies and Category already repeat and cannot identify one record.

Another row with BookID B003 would violate the key’s uniqueness. A NULL BookID would violate its required-value constraint. A repeated Category is allowed because Category is not the selected key.

A correct B-plus-three-digits format is separate from uniqueness: B003 fits the pattern but is already used. An unused X006 is distinct but fails that scenario’s format rule.

9.3 | CHOOSE THE KEY FOR WHAT A ROW REPRESENTS

StudentID may identify one student record, but it repeats when a table holds many loans per student. For a one-row-per-loan table, a unique required LoanID is suitable. Do not select the key before identifying the table’s record meaning.

Prefer manageable identifiers that rarely need to change. A name can repeat and change. A stable assigned ID can distinguish records even when names match. Stability is useful design practice; it is not a claim that a key can never be changed.

A key may be text or numeric. It is an identifier, not a password, and it does not by itself grant permission to edit. Sorting rows changes their display position rather than their identity. One selected primary key may combine fields, but that combined value must uniquely identify records.

9.4 | SQL: COLUMNS, SOURCE AND RECORDS

SQL means Structured Query Language. A query requests information or a calculation from the database. SELECT specifies returned fields or expressions; FROM names the source table; WHERE filters qualifying records.

SELECT Title, Copies
FROM Books
WHERE Category = 'Fiction';

The text value Fiction is in single quotation marks. The field name Category is not a text literal. Numeric comparisons use numbers without quotation marks in these examples. SELECT * requests all fields.

Without WHERE, all records qualify. Without ORDER BY, SQL does not guarantee row order. Unsorted worked examples here follow source order for checking. These SELECT queries do not change the stored table.

9.4 | COMPARISONS AND THE EXACT BOUNDARY

Copies > 2 keeps 3 and 4 but excludes 2. Copies >= 2 also keeps Ocean Science. That one character changes which record qualifies.

To predict output, test the condition against each stored row. Then display the SELECT columns, not every source field. A valid query can return no matching records.

9.4 | AND AND OR KEEP DIFFERENT RECORDS

AND requires both conditions for the same row. Fiction AND Copies > 0 returns The Coding Club and A Journey to Mars. The Hidden Island is fiction but has zero copies, so it fails the second test.

OR accepts either condition, including both. Fiction OR Copies > 1 returns The Coding Club, Ocean Science, A Journey to Mars and The Hidden Island. Robots at Work fails both.

Check whether the requirement says both restrictions or either route. Replacing AND with OR can include records that fail one of the original restrictions.

9.4 | ORDER THE RETURNED ROWS

Ascending order puts smaller numeric values first; descending puts larger ones first. Standard SQL uses ASC and DESC for the syllabus’s ascending and descending order. Put ORDER BY after WHERE when both are needed.

SELECT Title, Copies
FROM Books
WHERE Category = 'Fiction'
ORDER BY Copies DESC;

Text uses the database’s text ordering; dates sort chronologically when stored appropriately. Equal sort values need a further sort field if a precise order between tied rows is required. Sorting does not change the stored field values.

9.4 | COUNT THE ROWS OR ADD THE AMOUNTS

SUM(Copies) adds numeric values across qualifying records. COUNT(*) counts the records. Five catalogue titles have ten copies, so these are different questions.

SELECT SUM(Copies)
FROM Books
WHERE Category = 'Fiction';

These ungrouped aggregate queries return one summary value, not one row per book. COUNT(Copies) counts non-NULL Copies values rather than adding them. Zero is a present value and is counted.

With no matching records, COUNT(*) gives zero. SUM with no qualifying non-NULL values generally gives NULL rather than automatically zero. Do not confuse NULL, a missing value, with a stored zero.

9.4 | A WORKED QUERY FROM THE REQUIREMENT

Request: show the titles and copy counts of non-fiction books with at least one available copy, largest count first. Identify columns, source, conditions and order separately.

SELECT Title, Copies
FROM Books
WHERE Category = 'Non-fiction' AND Copies >= 1
ORDER BY Copies DESC;

To read a query, identify FROM, test WHERE against each row, identify selected columns or aggregate, and apply the required order. This is a reliable prediction method, not a description of the DBMS’s physical execution plan.

To complete a script, use the exact given table and field names. Keep field-list commas, quotation marks, comparison boundaries and clause order consistent with the request.

COMMON MISTAKES | CHECK YOUR THINKING

• Counting headings as a record, or confusing a field with its individual value.
• Choosing a numeric type just because an identifier contains digits.
• Assuming validation proves the value is accurate.
• Choosing a key from a small sample without a uniqueness guarantee.
• Assuming a correctly formatted key cannot be duplicated.
• Using visible row position as record identity.
• Confusing SELECT columns with WHERE records.
• Using OR when both restrictions must hold.
• Excluding equality when the request says “at least”.
• Using COUNT when the request asks for the total amount.
• Assuming zero is NULL or that SUM counts titles.
• Assuming an unsorted query guarantees source order.

PRACTISE EXPLAINING AND CONNECTING

Explain with reasons: “BookID is suitable because its values are required and guaranteed unique, therefore it identifies one catalogue record even when titles repeat.” Compare with both roles: “SUM adds Copies values, whereas COUNT(*) counts catalogue records.”

Try these without looking: identify the record and field counts; justify each field type; reject Category as a key; write a query for available fiction; calculate the fiction-copy total; and return all titles in ascending Copies order.

Select any uncertain flashcards and revisit their linked lessons before the end-of-topic review.