QCE Digital Solutions Unit 2 Definitions Worksheet: Prototype Data Solutions (Modules 305–336)
Name:
Date:
Class:
✎
Key terms
Write what each term means, in your own words.
1
anomaly
2
1NF
3
2NF
4
3NF
5
normal form check
6
HAVING
7
IN (SELECT studentId FROM results)
8
WHERE mark > (SELECT AVG(mark) FROM results)
9
CREATE VIEW
Answer Key
QCE Digital Solutions Unit 2 · Modules 305–336 · Student wording will vary for definitions
1anomaly — A problem caused by repeated (redundant) data: adding, changing or deleting a record leaves data inconsistent or lost. The three types are insert, update and delete anomalies.
21NF — First normal form: every field holds one atomic value, with no repeating groups or multi-valued fields, and the table has a primary key to identify each row.
32NF — Second normal form: a table is in 1NF and no non-key field depends on only part of a composite primary key. Removing partial dependencies means each field relies on the whole key.
43NF — A table in 2NF where no non-key field depends on another non-key field (no transitive dependency). Every field depends on the key, the whole key and nothing but the key.
5normal form check — Test a table against 1NF, then 2NF, then 3NF, naming each dependency problem found (repeating group, partial or transitive) and backing it up with an example row.
6HAVING — Filters groups after GROUP BY using an aggregate condition, such as HAVING COUNT(*) > 3. WHERE filters rows before grouping.
7IN (SELECT studentId FROM results) — A sub-query is a SELECT inside brackets in a WHERE clause. It returns a list of values, and IN keeps only the rows whose value is in that list. NOT IN keeps the rows that are not in it.
8WHERE mark > (SELECT AVG(mark) FROM results) — A sub-query in brackets returns one value, like the average. The outer WHERE compares each row's field to it. MIN or MAX work too.
9CREATE VIEW — Saves a SELECT query under a name so you can query it like a table. A view stores the query, not a copy of the data, so it always shows current rows. Remove it with DROP VIEW.