Database Concepts

In a file-based approach each application keeps its own separate file of data. If a school's library system and sports system each keep a copy of every student's name and address, the same data is stored in several places. This causes four classic problems:

Problem	What it means
Data redundancy	The same data is stored more than once, wasting storage.
Data inconsistency	If one copy is updated but the others are not, the copies disagree.
Lack of data integrity	Nothing enforces that linked data stays valid or complete.
Lack of data security	Access is controlled per file, so it is hard to give different users different rights.
Program-data dependence	The file format is built into each program, so changing the data structure means rewriting the programs.

Key idea: redundancy is the root cause — because data is duplicated, the copies can drift apart (inconsistency) and there is no single trustworthy version.

A relational database organises data into separate tables (one per entity) that are linked to each other using keys. Each fact is stored in only one place.

How this removes the file-based problems:

• Redundancy ↓ — a customer's address is stored once in the Customer table; an Order refers to the customer by a foreign key instead of copying the address.
• Inconsistency ↓ — because there is only one copy, one update changes the value everywhere it is used.
• Integrity ↑ — referential integrity rules stop you linking to records that don't exist.
• Security ↑ — access rights can be granted per table or even per field, centrally.
• Program-data independence — programs query the database through the DBMS, so the data structure can change without rewriting every program.

This separation into linked tables is achieved properly through normalisation (§6).

Each term has an everyday name and a relational-theory name — examiners use both.

Concept	Everyday term	Theory term	Means
A thing data is about	Entity	—	e.g. Student, Book
The structure storing it	Table	Relation	e.g. the Student table
One row	Record	Tuple	all data for one student
One column	Field	Attribute	e.g. Surname

Keys — the heart of this topic:

• Primary key — uniquely identifies each record in its table (e.g. StudentID). Cannot be null or duplicated.
• Candidate key — any field that could be the primary key. The primary key is chosen from the candidate keys; the rest stay candidate keys.
• Secondary key — a non-primary field chosen to make searching/sorting faster (e.g. Surname). Need not be unique.
• Foreign key — a field that refers to the primary key of another table, creating the link between tables.

Two entities can be related in three ways:

• One-to-one (1:1) — each A relates to exactly one B (e.g. Instructor ↔ Car).
• One-to-many (1:M) — one A relates to many B (e.g. one Customer has many Orders).
• Many-to-many (M:M) — many A relate to many B (e.g. Students ↔ Books they borrow).

We document relationships with an entity-relationship (E-R) diagram. The example below shows a one-to-many relationship using crow's-foot notation: the single bar on the Customer side means one, and the splayed 'crow's foot' on the Order side means many.

A many-to-many relationship cannot be implemented directly, so it is resolved with a linking (junction) table — see §5.

Resolving many-to-many. A foreign key can hold only one value, so it cannot represent 'many on both sides'. Instead, introduce a linking (junction) table that holds a foreign key to each of the two entities. For Students borrowing Books, a Loan table holds StudentID and BookID (plus loan data such as DateBorrowed). This turns one M:M into two 1:M relationships (Student 1:M Loan, Book 1:M Loan).

Referential integrity. This is a rule that every foreign key value must match an existing primary key value in the related table (or be null). It prevents orphaned records: you cannot add a Loan for a StudentID that doesn't exist, and you cannot delete a Student who still has Loans. This keeps linked data consistent — a guarantee a file-based system cannot make.

Indexing. An index is a separate structure that lets the database find records by a particular field quickly, without scanning every record (like an index at the back of a book). Indexing a field makes searching and sorting faster, but it uses extra storage and makes inserts/updates a little slower because the index must also be maintained.

Normalisation organises data into well-structured tables to remove redundancy and avoid update anomalies. You work through three forms in order.

First normal form (1NF): values are atomic (one value per field) and there are no repeating groups. Fix a repeating group (e.g. Product1, Product2, Product3) by moving it into its own table linked by a foreign key.

Second normal form (2NF): in 1NF and there is no partial dependency — that is, no non-key field depends on only part of a composite primary key. Fix by moving the partially dependent fields into a new table keyed on that part.

Third normal form (3NF): in 2NF and there is no non-key (transitive) dependency — that is, no non-key field depends on another non-key field. Fix by moving the dependent fields into a new table.

Form	Rule added	Remove…
1NF	atomic values, no repeating groups	repeating groups
2NF	1NF + no partial dependency	dependence on PART of a composite key
3NF	2NF + no transitive dependency	dependence on a NON-key field

Arguing whether tables are in 3NF. To say a table IS in 3NF: state it is in 2NF and that no non-key field depends on another non-key field. To say it is NOT in 3NF: name the offending dependency precisely, e.g. 'DepartmentName depends on DepartmentID, and DepartmentID is a non-key field — a transitive dependency'. A vague 'there is redundancy' does not earn the justification marks.

When asked to produce a normalised design from a description, sample data, or an un-normalised table, follow this routine:

1. List the fields and identify what the data is really about (the entities).
2. Reach 1NF — remove any repeating group into its own table; choose a primary key (a composite key if needed).
3. Reach 2NF — if the primary key is composite, check each non-key field: does it depend on the whole key? If it depends on only part, move it to a new table keyed on that part.
4. Reach 3NF — check for any non-key field that depends on another non-key field; move that pair into a new table.
5. Add the keys — mark each table's primary key, and add foreign keys so the tables are linked. Confirm referential integrity is possible.

Notation examiners accept for the answer:

• TableName(PrimaryKey, field, field, ForeignKey*) with the primary key underlined or listed first and foreign keys marked (e.g. with *).
• Example final design for an orders system:
  • Customer(CustomerID, CustomerName, Address)
  • Order(OrderID, OrderDate, CustomerID*)
  • Product(ProductID, ProductName, Price)
  • OrderLine(OrderID*, ProductID*, Quantity) with composite key (OrderID, ProductID).

This is fully in 3NF: every non-key field depends on the whole key and only the key.