Databases: notes

A database is an organised store of data that is easy to search, sort and update.

File structure

• Field: one item of data, such as Surname. Record: all fields about one thing, such as one student. Table (file): a set of records about the same kind of thing.
• Primary key: a field that is unique for every record, such as StudentID.
• Foreign key: a field in one table that is the primary key of another, used to link the two.
• Data types: text, number, date/time, currency, Boolean (yes/no).

Database objects

• Table: stores the data.
• Query: finds records that meet conditions, such as all students in Form 4 with a mark above 70.
• Form: a screen for entering and viewing data one record at a time.
• Report: a formatted printout or summary of data.
• A view shows chosen fields and records without changing the table.

Working with data

• Data manipulation: add, edit, delete, sort and search records.
• Validation checks data is sensible as it is entered: range check, type check, presence check, length check, format check.
• Verification checks data was copied correctly: double entry or proofreading.

Data analysis and advanced queries

• Use criteria with AND, OR, and comparison operators.
• Totals, counts and averages summarise data.
• A query can use fields from two linked tables.

Security

User names and passwords, access levels (read only or full), encryption and regular backups.

External data and connection

Data can be imported from or exported to spreadsheets and text files (such as CSV). Programs and websites connect to a database to read and save data.