VCE Spreadsheets, SQL & Visualisation Definitions Worksheet: Manipulating, validating and testing spreadsheet data and Relational database structure (Modules 35–44)
Name:
Date:
Class:
✎
Key terms
Write what each term means, in your own words.
1
pivot table
2
relational database
3
database table
4
data type
5
PRIMARY KEY
6
FOREIGN KEY
7
naming convention
8
student_id
9
table design
10
CREATE TABLE
Answer Key
VCE Spreadsheets, SQL & Visualisation · Modules 35–44 · Student wording will vary for definitions
1pivot table — A tool that summarises a big table by grouping rows under row and column fields, then working out a sum, count or average for each group. Refresh it when the data changes.
2relational database — A database that stores data in separate tables of fields (columns) and records (rows), with tables linked by shared values such as keys.
3database 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.
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.
5PRIMARY 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.
6FOREIGN 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.
7naming convention — A consistent rule for naming a database and its tables, such as a singular noun in lower_case with no spaces or special characters, e.g. student, applied in the same style for every table.
8student_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.
9table 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.
10CREATE 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.