Database and file concepts (AS Level) flashcards

Topic 10: Database and file concepts

Primary key, foreign key and composite key

Primary key: a unique value for every record. Foreign key: the primary key of another table stored to make a link; it may repeat. Composite key: two or more fields that are unique together.

What is referential integrity?

The rule that every foreign key value must match an existing primary key value. It prevents orphan records.

How is a many-to-many relationship built?

As two one-to-many relationships through a link table, e.g. Student and Course linked by an Enrolment table.

What are the tests for 1NF, 2NF and 3NF?

1NF: no repeating groups, atomic values, a primary key. 2NF: 1NF and no field depends on only part of a composite key. 3NF: 2NF and no non-key field depends on another non-key field.

Employee(EmpID, DeptID, DeptName). Which normal form does it break?

3NF. DeptName depends on DeptID, which is a non-key field (a transitive dependency).

Static vs dynamic parameter query

A static parameter query has its criterion fixed in the design. A dynamic parameter query asks the user to type the value each time it runs.

Name the four action queries

Make-table (copy results into a new table), append (add results to an existing table), delete (remove matching records) and update (change values in matching records).

Indexed sequential vs direct access files

Indexed sequential: records in key order plus an index; good for sequential runs and fairly fast single look-ups. Direct access: hashing turns the key into an address; fastest single look-up, poor for processing in order.

Name eight components of a data dictionary

Any eight of: field name, data type, field size, format, key, validation rule, error text, default value, whether required, description, table name.

Conceptual vs logical vs physical ERD

Conceptual: entities and relationships only. Logical: adds attributes and keys. Physical: adds real table and field names, data types and sizes.