Fundamentals of databases flashcards

Topic 10: Fundamentals of databases

What are an entity, an attribute and a relationship?

Entity: something about which data is stored (e.g. Student). Attribute: a property of an entity (e.g. DateOfBirth). Relationship: a link between entities (one-to-one, one-to-many, many-to-many).

What are a primary key, a composite key and a foreign key?

Primary key: uniquely identifies each record. Composite key: a primary key made of two or more attributes. Foreign key: an attribute that is the primary key of another table, linking the two.

Why are databases normalised?

To remove data redundancy and so avoid update, insertion and deletion anomalies, keeping data consistent. A database in third normal form has attributes that depend on the key, the whole key and nothing but the key.

Write an SQL query to list names of students in Year 12, sorted by name.

SELECT Name FROM Student WHERE Year = 12 ORDER BY Name;

What do the SQL statements INSERT, UPDATE and DELETE do?

INSERT INTO adds a new record. UPDATE … SET … WHERE changes values in existing records. DELETE FROM … WHERE removes records.

What problem arises when two users update the same record at once in a client-server database?

Lost update: one user’s change overwrites the other’s. It is prevented by record locking, serialisation, timestamp ordering or commitment ordering.

What is record locking?

When one transaction is updating a record, the record is locked so no other transaction can change it until the first is finished. It can cause deadlock if two transactions wait for each other.