Databases

Cambridge AS & A Level Computer Science 9618 · Paper 1 · Unit 8

Paper 1 · Unit 8. Relational databases, E-R diagrams, normalisation, DBMS and SQL.

1. Limitations of file-based systems

In a file-based approach, each application keeps its own files. Problems: data redundancy (the same data stored many times), data inconsistency (copies differ after updates), data dependence on programs, and poor security and sharing. A relational database stores data once in linked tables, reducing redundancy and keeping data consistent.

2. Relational database terms

Term	Meaning
Entity	A thing about which data is stored, e.g. STUDENT
Table	Stores data about one entity in rows and columns
Record / tuple	One row of a table
Field / attribute	One column of a table
Primary key	An attribute (or attributes) that uniquely identifies each record
Candidate key	Any attribute that could be the primary key
Secondary key	A non-unique attribute indexed for fast searching
Foreign key	A primary key from another table, used to link tables
Composite key	A primary key made of two or more attributes
Referential integrity	Every foreign key value must match an existing primary key value
Indexing	A data structure that speeds up searching on an attribute

3. Entity-relationship diagrams

An E-R diagram shows entities and the relationships between them: one-to-one, one-to-many and many-to-many. A many-to-many relationship cannot be implemented directly; it is resolved with a link table holding foreign keys from both tables (often forming a composite primary key).

4. Normalisation

Normal form	Rule
1NF	No repeating groups of attributes; every field holds atomic (single) values; there is a primary key
2NF	In 1NF and no partial dependencies: every non-key attribute depends on the whole primary key (only matters for composite keys)
3NF	In 2NF and no non-key dependencies: non-key attributes depend only on the primary key, not on other non-key attributes

Remember: each non-key attribute depends on “the key, the whole key, and nothing but the key”.

5. DBMS features

A Database Management System (DBMS) solves file-based problems through:

Data management: a data dictionary stores metadata (table names, field names, data types, validation rules, relationships), and the logical schema describes the overall structure.
Data security: passwords, access rights, backups, encryption.
Data integrity: validation rules and referential integrity.
Tools: a developer interface and a query processor that runs queries.

6. DDL and DML (SQL)

The DDL (Data Definition Language) creates and changes the structure; the DML (Data Manipulation Language) queries and maintains the data.

CREATE DATABASE School;
CREATE TABLE STUDENT (
  StudentID INTEGER NOT NULL,
  Name VARCHAR(30),
  Class CHAR(3),
  Score INTEGER,
  PRIMARY KEY (StudentID)
);
ALTER TABLE STUDENT ADD Email VARCHAR(50);

SELECT Name, Score FROM STUDENT
WHERE Score > 70 ORDER BY Score DESC;
SELECT Class, AVG(Score) FROM STUDENT GROUP BY Class;
INSERT INTO STUDENT VALUES (7, 'Gul', '12A', 88);
UPDATE STUDENT SET Score = 90 WHERE StudentID = 7;
DELETE FROM STUDENT WHERE StudentID = 7;

Also know: INNER JOIN to combine tables, and the aggregate functions SUM, COUNT and AVG.

Try it: build a SELECT query

Table STUDENT(StudentID, Name, Class, Score)

7. Examiner tips

Foreign key: say it is the primary key of another table.
Normalisation answers must refer to the data in the question.
Many-to-many relationships need a link table.
SQL: put text values in quotes, end statements with a semicolon, and use the exact field names given.
Data dictionary holds metadata, not the data itself.

8. Practice questions

Practice: answer and check instantly

Type a short answer, then press Check or Enter.

1. Which key links one table to another?

Show answer

A foreign key.

2. What is a primary key made of two or more attributes called?

Show answer

A composite key.

3. Which normal form removes partial dependencies?

Show answer

Second normal form.

4. Which normal form removes non-key dependencies?

Show answer

Third normal form.

5. Which SQL keyword sorts results?

Show answer

ORDER BY.

6. Which SQL command adds a new record?

Show answer

INSERT INTO.

7. Which DBMS component stores metadata? (two words)

Show answer

The data dictionary.

8. Which language (abbreviation) is used to create tables?

Show answer

Data Definition Language.

Exam-style questions

9. Explain why a relational database is better than a file-based approach. [4]

Show mark guide

Data is stored once so less redundancy [1] · reduces inconsistency because updates are made in one place [1] · data is independent of programs [1] · better security through access rights [1] · easier to share data and query it [1]. Max 4.

10. Write an SQL query to display the Name and Score of students in class 12B, highest score first. [4]

Show mark guide

SELECT Name, Score [1] · FROM STUDENT [1] · WHERE Class = ’12B’ [1] · ORDER BY Score DESC; [1]

9. Topic test: predict your grade

Frequently asked questions

What are the three normal forms?

1NF has no repeating groups and atomic values. 2NF also has no partial dependencies on part of a composite key. 3NF also has no non-key dependencies.

What is a foreign key?

A foreign key is a field in one table that is the primary key of another table, used to link the two tables.

What is the difference between DDL and DML?

DDL (Data Definition Language) creates and changes the structure, such as CREATE TABLE. DML (Data Manipulation Language) queries and changes data, such as SELECT, INSERT, UPDATE and DELETE.