Databases (A Level) flashcards

Topic 15: Databases

What is a relational database?

Data held in separate tables, one per entity type, linked by common key fields.

Primary key, composite primary key and foreign key

Primary key: attribute(s) that uniquely identify each record. Composite key: two or more attributes combined. Foreign key: a field matching another table's primary key, linking the two tables.

How is a many-to-many relationship resolved?

By adding a link entity between the two, giving two one-to-many relationships, e.g. Climber and Route linked by Ascent.

Why normalise a database?

Every fact is stored in one place, so redundant copies disappear, data stays consistent, less storage is used, and update, insertion and deletion problems are avoided.

Give the SQL patterns for UPDATE, INSERT and DELETE

UPDATE table SET field = value WHERE condition INSERT INTO table (f1, f2) VALUES (v1, v2) DELETE FROM table WHERE condition

What does GROUP BY do in SQL?

It groups rows that share a value so an aggregate function such as COUNT, SUM or AVG gives one result per group, e.g. SELECT field, COUNT(*) FROM table GROUP BY field.

What is the lost update problem?

Two clients read the same record and both change it; the second save overwrites the first, so one update is lost.

How does record locking preserve integrity?

The record is locked while one client updates it, so no other client can update it until the lock is released.

What do volume and velocity mean for Big Data?

Volume: too much data to fit on a single server. Velocity: data arrives as a stream and responses are needed in milliseconds to seconds.