Database concepts

Computer Science AS · Databases. The idea in plain words, the rules to remember, the mistake to avoid and a worked example, written and checked by Mega Lecture.

Test this topic free All Computer Science AS topics

The idea

In a file-based approach each program keeps its own files. The same data is stored many times (redundancy), copies stop matching (inconsistency) and the programs depend on the file structure. A relational database stores each fact once in linked tables.

An entity is a thing we store data about, and it becomes a table. A record (tuple) is a row and a field (attribute) is a column. Tables are linked when the primary key of one table appears as a foreign key in another.

Normalisation organises the tables step by step, from 1NF to 3NF, to remove redundancy.

Rules to remember

Candidate key: any attribute or smallest set of attributes that could identify each record. Primary key: the candidate key that is chosen. Secondary key: a candidate key that was not chosen, or a field indexed for searching.
Foreign key: a field in one table that refers to the primary key of another table.
Referential integrity: every foreign key value must match an existing primary key value.
Relationships are one-to-one, one-to-many or many-to-many. A many-to-many relationship is replaced by a link table and two one-to-many relationships.
1NF: no repeating groups; every field holds one value. 2NF: in 1NF, and no non-key field depends on only part of a composite primary key.
3NF: in 2NF, and no non-key field depends on another non-key field.
Index: a separate structure that makes searching on a field faster.

Common mistake

When a table has a single-field primary key, students look for a 2NF problem. A partial dependency is only possible with a composite key, so go straight to checking 3NF.

Worked example

LOAN(LoanID, BookID, BookTitle, DateOut) has primary key LoanID. Every field holds one value. Is the table in 3NF? If not, give a 3NF design.

Every field holds one value, so it is in 1NF. The primary key is a single field, so there is no partial dependency and it is in 2NF.
BookTitle depends on BookID, and BookID is not the key. This is a non-key dependency, so the table is not in 3NF.
Move the dependent field to its own table with BookID as the primary key, and keep BookID in LOAN as a foreign key.

Answer: Not in 3NF. Use LOAN(LoanID, BookID, DateOut) and BOOK(BookID, BookTitle).

Ask about this

Type what you are stuck on. The helper finds the explanation, worked examples and the notes to read, free and instant.

Keep going

← Ethics and ownership Database Management Systems (DBMS) →Take a Computer Science AS test