Topic 6 of 8 · Form 6
Databases
Data stored in related tables (relations). Key terms: entity, attribute, tuple (record), primary key, foreign key, candidate key, secondary key (index), referential integrity.
Notes for this topic 1 file, in order
Create a free account to download PDFs.
Relational databases
Data stored in related tables (relations). Key terms: entity, attribute, tuple (record), primary key, foreign key, candidate key, secondary key (index), referential integrity.
Entity–relationship modelling
One-to-one, one-to-many and many-to-many relationships; many-to-many relationships are resolved with a link table.
Normalisation
- 1NF: no repeating groups; atomic attributes.
- 2NF: 1NF and no partial dependencies on part of a composite key.
- 3NF: 2NF and no non-key dependencies (every non-key attribute depends on the key, the whole key and nothing but the key).
Normalisation reduces redundancy and update anomalies.
SQL
Worked example: Write SQL to list the names and marks of Form 6 learners with marks above 70, highest first.
SELECT Name, Mark
FROM Learner
WHERE Form = 6 AND Mark > 70
ORDER BY Mark DESC;
- Data definition language: CREATE TABLE, ALTER TABLE, primary and foreign keys.
- Data manipulation language: SELECT, INSERT INTO, UPDATE, DELETE; JOIN to combine tables; GROUP BY with COUNT, SUM, AVG.
DBMS features
Data dictionary, access rights, backups, concurrency control (record locking), and the client–server model. Database administrators manage security and integrity.
Check yourself
- What is a foreign key?
- How is a many-to-many relationship implemented?
- Write SQL to count learners in each form.
Show answers
- An attribute in one table that is the primary key of another table, linking them.
- With a link (junction) table containing the primary keys of both tables.
- SELECT Form, COUNT(*) FROM Learner GROUP BY Form;
Questions about this topic (0)
No questions about this topic yet.
Sign in or create a free account to join in.