Revision notes

Database Basics: Practice

The key points, rules to remember, tables and diagrams from all 9 lessons, on one page for quick revision. To keep a copy, use your browser's Print and choose "Save as PDF".

Lesson 1

Tables, records, fields and DBMS

Terms in a student table
TermMeaningExample
Table (relation)Data arranged in rows and columnsStudent table
Field (attribute)One column: one kind of factName, Class, City
Record (tuple)One row: all facts about one itemOne student's full details
Primary keyA field whose value is unique for every recordRoll number

Row = record. Column = field. Primary key = unique for every record.

— Remember this

Key takeaways: rows are records, columns are fields, and the primary key must be unique.

Read the full lesson

Lesson 2

Keys and relationships between tables

Types of keys
KeyMeaning
Primary keyChosen field that uniquely identifies each record; cannot be empty or repeated
Candidate keyAny field that could serve as a primary key
Foreign keyA field in one table that refers to the primary key of another table
Composite keyA primary key made of two or more fields together

A foreign key points to another table's primary key. A primary key is unique and never empty.

— Rule to remember

Key takeaways: link tables with foreign keys, and identify the relationship by counting matches on each side.

Read the full lesson

Lesson 3

SQL basics: SELECT, WHERE and common commands

Common SQL
StatementPurpose
SELECTRetrieve data
INSERTAdd a new record
UPDATEChange existing data
DELETERemove records
CREATE TABLEMake a new table
WHEREFilter rows by a condition
ORDER BYSort the results

SELECT chooses columns, FROM names the table, WHERE filters rows, ORDER BY sorts.

— Rule to remember

Key takeaways: read a query from SELECT to WHERE, and check boundaries carefully: greater than excludes equal values.

Read the full lesson

Lesson 4

Data integrity, redundancy and normalisation

Terms
TermMeaning
RedundancyUnnecessary repetition of the same data
Data integrityData is accurate, consistent and follows rules
NormalisationOrganising tables to reduce redundancy and avoid anomalies
AnomalyA problem that arises from poor design when inserting, updating or deleting
Data dictionaryA description of the database's tables, fields and types
BackupA copy of the data to restore after loss

Normalisation reduces redundancy. Integrity keeps data correct and consistent.

— Remember this

Key takeaways: avoid repeating data, link tables with keys and enforce rules to keep data correct.

Read the full lesson

Lesson 5

Database mixed practice

Cue and answer
CueAnswer
RowRecord
ColumnField
Unique identifierPrimary key
Points to another tableForeign key
Retrieve dataSELECT
Reduce repeated dataNormalisation

Work out the table first, then apply the query or term.

— Exam method

Key takeaways: use the cue table and double-check boundary conditions in queries.

Read the full lesson

Lesson 6

SQL output drills: predict the rows

Filter first, sort second, show columns last. BETWEEN includes both ends. SELECT never changes the table.

— Remember
  • Trace row by row; do not guess.
  • IN (...) is a short way to write several OR conditions.
  • Check boundary values when the condition has < or > and when it has BETWEEN.
Read the full lesson

Lesson 7

Totals and groups: aggregate and GROUP BY practice

Group first in your head: list each group's rows, then do the arithmetic once per group.

— Remember
  • Row calculations come before the aggregate.
  • WHERE then GROUP BY then HAVING is the logical order.
  • Check boundaries: 'more than 60' excludes 60.
Read the full lesson

Lesson 8

Key identification drills: candidate, primary, alternate and foreign

Key terms
TermMeaning
Candidate keyA column or set of columns that could uniquely identify each row (minimal)
Primary keyThe candidate key chosen to identify rows; unique and never NULL
Alternate keyA candidate key that was not chosen as the primary key
Composite keyA key made of two or more columns together
Foreign keyA column that refers to the primary key of another table
SuperkeyAny set of columns that uniquely identifies rows; it may contain extra columns
DegreeNumber of columns (attributes)
CardinalityNumber of rows (tuples)

A primary key is unique and never NULL. Foreign keys may repeat and may be NULL when the rule allows it. Degree counts columns; cardinality counts rows.

— Remember
  • Candidate keys compete for the primary key; the rest are alternate keys.
  • A composite key uses several columns together.
  • A foreign key links to the primary key of another table.
Read the full lesson

Lesson 9

Commands, data types and database objects: match-up practice

Command groups
GroupFull nameCommands
DDLData Definition LanguageCREATE, ALTER, DROP
DMLData Manipulation LanguageSELECT, INSERT, UPDATE, DELETE
DCLData Control LanguageGRANT, REVOKE
TCLTransaction Control LanguageCOMMIT, ROLLBACK
Objects
ObjectJob
TableStores the data in rows and columns
QueryAsks a question and returns matching data
FormA screen for entering or viewing one record at a time
ReportA formatted, printable summary of data
ViewA stored query that acts like a virtual table
IndexA lookup aid that speeds up searching

DDL changes structure, DML changes data, DCL changes permissions, TCL changes the fate of a transaction.

— Remember
  • Learn the four command groups with an example of each.
  • Match object to job: table stores, query asks, form enters, report prints.
  • Identifiers like phone and PIN code are text.
Read the full lesson

Privacy · Terms · Refunds · Delivery