ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries Hangman

The 50 key terms

1data table

A grid that organises data. Each column has one heading, and each row holds one record, like one student or one pet. There are no blank rows.

2database table

A database table stores data in columns called fields and rows called records. A single-table database keeps all its data in this one table.

3field and record

A field is a column and a record is one data row. The heading row is not a record. A table with 4 fields and 6 records has 4 columns and 7 rows including the heading.

4data type

The kind of value a database field accepts, such as text, number, date or yes/no. It stops the wrong kind of data being entered, like words in a number field.

5data redundancy

The same details stored again and again in one big table. It wastes space and risks typos or missed updates, like a customer's address copied onto every order.

6relational database

A database that stores data in separate tables of fields (columns) and records (rows), with tables linked by shared values such as keys.

7entity

A type of thing a database stores data about, such as a student, book or order. Each entity becomes one table, and its name is a singular noun.

8attribute

An attribute is one property of an entity, like a student's name or year level. Each attribute becomes one field (column) in the entity's table, and each instance of the entity is one record.

9PRIMARY KEY

A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

11FOREIGN KEY

A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

12one-to-many relationship

One record in a primary table matches many records in a related table. The foreign key sits on the many side, e.g. one class has many students, so class details are not repeated.

13junction table

A table that splits a many-to-many relationship into two one-to-many links by holding two foreign keys, such as Enrolment with studentId and subjectId.

14ERD

An entity relationship diagram draws each entity as a labelled box and each relationship as a line joining two boxes. Boxes list attributes, with PK and FK marked.

15crow's foot

ERD notation where a single bar at a line's end means one and a three-pronged crow's foot means many. The crow's foot end is the foreign key side of the relationship.

16ERD from a scenario

Find the entities, list their attributes, pick primary keys, then work out each relationship and draw it, with the foreign key on the many side.

17referential integrity

Every foreign key value must match an existing primary key in the related table, so no record points to something missing. The database blocks changes that break this.

18table design

Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

19student_id

A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.

20SQL

Structured Query Language, the standard language for asking a database to return data (SELECT) or to add, change or remove it (INSERT, UPDATE, DELETE).

21CREATE TABLE

SQL command that builds a new table by naming its fields and their data types, such as a PRIMARY KEY. INSERT INTO ... VALUES then adds one record to that table.

22VARCHAR(50)

A column's data type and size set in CREATE TABLE. VARCHAR(50) holds text up to 50 characters. Other types: INT, DECIMAL(8,2), DATE, BOOLEAN. They control what a column stores.

23FOREIGN KEY (field) REFERENCES other_table(field)

Used in CREATE TABLE to link a column to another table's key. The database then only accepts values that match an existing key, so bad links are rejected.

24query

A saved question asked of a table. It shows only the fields and records that answer it, as a new view, and leaves the original table unchanged.

25SELECT name, age FROM students;

SELECT lists the fields to show and FROM names the table. This returns the name and age columns for every record in students. SELECT * shows all columns. End with a semicolon.

26SELECT lastName, firstName FROM students;

The output has only the fields named, as columns in the order written after SELECT, with a heading row of field names and one row per record kept (all records if there is no WHERE).

27WHERE

An SQL clause that keeps only the rows meeting a condition, such as WHERE age >= 15 or WHERE town = 'Perth'. Text goes in quotes, and AND or OR can combine conditions.

28WHERE Town = 'Leeds'

The value in a WHERE condition must match the field's data type: text and characters go in quotes, numbers do not, and Boolean fields use TRUE or FALSE, e.g. Member = TRUE.

29AND, OR, NOT

Combine WHERE conditions: AND needs both true, OR needs at least one true, NOT reverses a condition. Brackets set the order and decide which rows are returned.

30WHERE (A OR B) AND C

Brackets make SQL work out the OR first, then the AND. A record must meet C and also A or B. Moving the brackets, as in A OR (B AND C), can return different records.

31WHERE lastName LIKE 'S%'

LIKE matches text by pattern in a WHERE clause. % stands for any run of characters and _ for exactly one, so 'S%' finds values starting with S and '%son' finds values ending in son.

32BETWEEN low AND high

Tests if a value is inside a range, including both limits. Its partner IN (a, b, c) checks a value against a list, replacing repeated OR. NOT IN excludes the list.

33ORDER BY

Sorts the rows a SELECT query returns by a chosen field. It sorts A to Z or lowest to highest by default (ASC), or the reverse if you add DESC after the field name.

34SELECT FROM WHERE ORDER BY

The order of clauses in a full SQL query: SELECT, FROM, WHERE, then ORDER BY. WHERE filters the rows first, then ORDER BY sorts the rows that are left.

35SELECT AVG(score) FROM results;

An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.

36WHERE, ORDER BY, SELECT

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

37JOIN

A join matches each foreign key value in one table to the primary key value in another, so rows from both tables show together. Rows with no match are left out.

38INNER JOIN t2 ON t1.key = t2.key

Combines rows from two tables where a foreign key matches a primary key, so related data shows in one result. Use table.field names to say which table each field belongs to.

39ON orders.customer_id = customers.customer_id

The join condition: it matches the foreign key in one table to the primary key in the other. The = sign compares the two values to find matching rows. It does not assign a value.

40table.field

Names the table a field comes from, like students.name. Needed in a join when both tables share a field name, or SQL gives an ambiguous column error.

41FROM students s

Gives the students table the short name s after FROM, so the rest of the query can write s.firstName instead of students.firstName.

42JOIN ... ON ... WHERE ... ORDER BY

After JOIN ... ON links two tables, WHERE filters the joined rows and ORDER BY sorts them. Clause order: SELECT, FROM, JOIN ON, WHERE, ORDER BY.

43JOIN subjects ON results.subjectId = subjects.subjectId

A second JOIN line chained after the first to reach a third table. It follows a foreign key in the junction table to the primary key of the next table.

44Cartesian product

A join with no ON condition pairs every row of one table with every row of the other, so 3 customers and 4 orders give 12 rows, most of them wrong.

45Query design

A plan for a database query that lists the table, the fields to show, the criteria used to filter records and the sort order, written before the query is built.

46SELECT fields FROM table WHERE condition ORDER BY field DESC;

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

47multi-table query

A query that gets fields from more than one table: find which tables hold them, JOIN them on matching keys, then add WHERE and ORDER BY to match the question.

48Test a query

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

49SQL error

A mistake in an SQL script, such as text without quotes, > instead of >=, AND instead of OR, clauses in the wrong order, a missing table after FROM or a missing semicolon.

50UPDATE table SET field = value WHERE condition;

UPDATE changes field values and DELETE FROM removes records. Always add WHERE so only the chosen records change. Without it, every record in the table is changed or deleted.

51Export query results as CSV

Save the rows a query returns as a CSV file with a header row of field names. Import it into a spreadsheet, then check the columns came across correctly before further analysis.