Database Management System: Tables, Keys, Queries, Forms and Reports
Objects and terms
- Table - stores data in fields (columns) and records (rows); each field has a data type (text, number, date, yes/no).
- Query - asks a question of the tables; a select query returns matching records.
- Form - a screen for entering or editing one record at a time.
- Report - formatted output for printing or sharing.
Keys
- Primary key: uniquely identifies each record, can never be null, one per table.
- Foreign key: a field in one table holding the primary key of another table, creating the relationship.
- Candidate key: any field that could serve as the primary key; the unchosen ones become alternate keys. A key formed from two fields is a composite key.
Query criteria
Criteria written on the same row are joined by AND; criteria on different rows are joined by OR. Text values need quotes ('XI'), dates need the software's date format or # delimiters, and wildcards * (any number of characters) and ? (one character) support partial matching.
Worked example
Student(RollNo primary key, Name, Class, Marks). Query: Class = 'XI' AND Marks > 75. This returns only Class XI students scoring above 75. A Class XII student with 90 marks is excluded, because AND requires both conditions to be true on the same record. Replacing AND with OR would return every XI student plus every student above 75 from any class.
Traps
Allowing duplicate or blank primary keys; storing numbers in a text field so Marks > 75 fails; using a form as a printed report; confusing a filter with a saved query; setting relationships after entering inconsistent data; forgetting referential integrity when deleting a parent record.