Database Management
Designs and develops database systems to manage data efficiently and effectively
Exam objectives
- Distinguish data from information, and structured from unstructured data
- Define a database and compare flat-file, hierarchical, network, relational and object-relational models
Important areas to cover
- Comparing database models is a quick, reliable short-structured question
Lesson content not added yet.
Exam objectives
- Define relations/tables, attributes/columns, tuples/rows and relationships
- Explain NOT NULL, unique, primary key, foreign key and check constraints
Important areas to cover
- Constraint definitions form a common MCQ set
Lesson content not added yet.
Exam objectives
- Write DDL to create, alter and drop tables and databases, including adding/removing keys
- Write DML — INSERT, UPDATE, DELETE and SELECT — including single- and multi-table SELECTs using an inner join
Important areas to cover
- SQL query writing, especially SELECT with joins and WHERE conditions, is one of the highest-value, most reliably examined practical skills in the whole syllabus
- CREATE TABLE with correct data types, PRIMARY KEY and FOREIGN KEY syntax is asked almost every year
Lesson content not added yet.
Exam objectives
- Identify entities, attributes, entity identifiers and relationships from a worded scenario
- Draw an ER diagram with correct cardinality notation
- Explain the Extended ER (EER) concept at a basic level
Important areas to cover
- Drawing a full ER diagram with correct cardinality from a scenario is one of the most heavily weighted essay questions across the whole exam — practise many different scenarios
Lesson content not added yet.
Exam objectives
- Define logical schema and relation instances
- Distinguish candidate key, primary key, alternate key, foreign key and domain
Important areas to cover
- Key-type definitions (candidate vs primary vs alternate) are a recurring short-answer trio
Lesson content not added yet.
Exam objectives
- Transform entities, attributes and relationships from an ER diagram into a relational logical schema
Important areas to cover
- Commonly paired with the 8.4 ER-diagram question into one longer essay
Lesson content not added yet.
Exam objectives
- Identify insert, update and delete anomalies caused by redundancy
- Define full, partial and transitive functional dependency
- Normalize a given unnormalized table through 1NF, 2NF and 3NF, showing the working at each step
Important areas to cover
- Normalizing a given table to 3NF with all intermediate steps shown is one of the most consistently examined essay questions in the Database unit
Notes
What normalisation is for
Storing the same fact in more than one place is what causes the three anomalies:
- Insert anomaly — a fact cannot be recorded because an unrelated one is missing. A new subject nobody has sat yet has no row to live in
- Update anomaly — a fact stored in twenty rows has to be corrected in twenty rows, and one row missed leaves the database contradicting itself
- Delete anomaly — removing one fact silently removes another. Deleting the last student in a class deletes the class
Normalisation splits tables until every fact is stored exactly once, which removes all three.
Functional dependency
Write X → Y to mean "X determines Y": knowing X fixes exactly one value of Y. StudentID → StudentName, because one student id gives one name.
- Full dependency — Y depends on the whole of a composite key X
- Partial dependency — Y depends on only part of a composite key
- Transitive dependency — the key determines X, and X in turn determines Y, so Y depends on the key only indirectly
Those three names are the answer to "state the dependency you removed", so use them.
First normal form
Every cell holds a single value, and there are no repeating groups.
Flatten the repeating group out into extra rows. The key normally has to grow into a composite key at this point, because the original key no longer identifies one row on its own.
Second normal form
In 1NF, and every non-key column depends on the whole primary key rather than part of it.
This can only bite when the key is composite. Look for a column fixed by half the key — a student's name fixed by the student id alone, when the key is student id plus subject code. Move that column, together with the part of the key it depends on, into a table of its own.
A 1NF table whose primary key is a single column is already in 2NF. Say so if the question gives you one; it is a mark.
Third normal form
In 2NF, and no non-key column depends on another non-key column.
Look for a chain. The key determines ClassID, and ClassID determines ClassName, so ClassName depends on the key only through ClassID. Move that pair into their own table and leave ClassID behind as a foreign key.
Writing the answer
The marks are spread across the intermediate stages, not concentrated on the final set of tables. A student who writes only the finished 3NF tables scores a fraction of the question. At each step write:
- the tables at that normal form, with primary keys underlined and foreign keys marked
- the dependency you removed, as an arrow — StudentID → StudentName
- the anomaly that dependency was causing
For A/L ICT the question stops at 3NF. BCNF and the higher normal forms are not examined.
Try it — take one table from unnormalised to 3NF
The same student results table at every stage. At each step, the highlighted columns are the ones that break the next rule.
Unnormalised form
The table as it arrives — one row per student, with all of that student's subject results crammed into repeating cells.
A table is unnormalised when a single cell holds more than one value, or a group of columns repeats for one row.
STUDENT_RESULT
Primary key: StudentID
StudentID StudentName ClassID ClassName SubjectCode SubjectName Marks S001 Nimal Perera C12A Grade 12 A ICTMAT Information & Comm. Tech.Combined Maths 7865 S002 Kavindi Silva C12A Grade 12 A ICTPHY Information & Comm. Tech.Physics 9271 S003 Ashan Fernando C12B Grade 12 B ICT Information & Comm. Tech. 55 Still wrong
Three columns repeat inside one row. There is no way to write a query for "every student who took ICT" when the subject sits in a list rather than a column.
Repeating group: (SubjectCode, SubjectName, Marks) repeats within one StudentID.
Anomalies this causes
- InsertA new subject nobody has sat yet cannot be recorded at all — there is no row to put it in until some student takes it.
- UpdateCorrecting a subject's name means editing it inside every student's list, and missing one leaves two names for one subject.
- DeleteRemoving the only student taking Physics deletes the fact that Physics exists.