ZIMFundi
Sign inJoin free

Topic 4 of 6 · Form 5

Databases

Limitations of file-based approaches; relational database terms (entity, table, record/tuple, field/attribute, primary key, candidate key, secondary key, foreign key, referential integrity); entity–relationship diagrams; normalisation to 1NF, 2NF and 3NF.

Notes for this topic 1 file, in order

Create a free account to download PDFs.

  1. PDFDatabases – Computer Science notes (Cambridge A Level)45.3 KB

Database concepts

Limitations of file-based approaches; relational database terms (entity, table, record/tuple, field/attribute, primary key, candidate key, secondary key, foreign key, referential integrity); entity–relationship diagrams; normalisation to 1NF, 2NF and 3NF.

Database management systems

Data dictionary, data modelling, logical schema, developer interface, query processor; data security (backups, access rights, validation).

SQL

  • DDL: CREATE DATABASE, CREATE TABLE, ALTER TABLE, PRIMARY KEY, FOREIGN KEY.
  • DML: SELECT, FROM, WHERE, ORDER BY, GROUP BY, INNER JOIN, SUM, COUNT, AVG, INSERT INTO, DELETE FROM, UPDATE.

Worked example: Write SQL to show each form and the average mark, for forms with more than 10 learners.

SELECT Form, AVG(Mark) FROM Learner GROUP BY Form HAVING COUNT(*) > 10;

Worked example: Join tables to list each learner with their teacher's name.

SELECT Learner.Name, Teacher.Name FROM Learner INNER JOIN Teacher ON Learner.TeacherID = Teacher.TeacherID;

Exam tip: In normalisation answers, show each table in standard notation: TableName(PrimaryKey, Attribute, ForeignKey).

Check yourself

  1. What is a candidate key?
  2. What problem does normalisation solve?
  3. Write SQL to delete learners in Form 7.
Show answers
  1. An attribute (or set) that could uniquely identify records and could be chosen as the primary key.
  2. Data redundancy and update anomalies (inconsistency).
  3. DELETE FROM Learner WHERE Form = 7;
Next topicAlgorithm design and data structures

Questions about this topic (0)

No questions about this topic yet.