අන්තර්ගතයට යන්න
mcqdinethlive

Database design from ER diagrams to normal forms and SQL

The A/L ICT database unit (Unit 8) through one school club register. How the papers ask it, the ER diagram, the tables and keys, the constraints, the normal forms, SQL, the joins with other lessons and past paper questions by step.

සිංහලෙන් කියවන්න

This post explains the whole A/L ICT database unit through one school club register. Unit 8 has seven levels, but they are seven steps of one job. A scenario becomes an ER diagram, and the diagram becomes tables with keys and constraints. Normalisation removes repeated data, and SQL builds and uses the result. The 2026 paper follows the same steps, from Paper I Q26 to Q33 and in Paper II, Part B, Q8.

The post has four parts:

  • How the papers ask: the questions and marks for each level (section 1).
  • Design the database: the ER diagram, the tables and keys, and the constraints (sections 2 to 4).
  • Fix and use it: the normal forms, then SQL (sections 5 and 6).
  • Beyond the unit: the joins with other lessons, then past paper questions by step (sections 7 and 8).

1. How the papers ask about Unit 8

Unit 8 has questions in all eight new-syllabus papers, from 2019 to 2026. Its seven levels hold 51 MCQs and about 133 Paper II marks in those eight papers. The 2023 (held 2024) Paper II marks are estimates, because that paper has no marking scheme.

Where the marks sit

The Paper II marks come from three levels. ER diagrams (8.4) carry 57 marks, normalisation (8.7) carries 41, and SQL (8.3) carries 18. Mapping an ER diagram to tables (8.6) adds 17. The other levels, such as keys (8.5) and constraints (8.2), come as MCQs. So a student who can draw the diagram, map it and normalise it has most of the Paper II marks of the unit.

The database question moved in Paper II. It was Part B Q5 in 2019, Part B Q9 from 2020 to 2025, and Part B Q8 in 2026. In 2025 the unit also had two Part A parts. 2025 P-II A Q1(a) asks for a CREATE TABLE statement, and 2025 P-II A Q2(b) asks for a mapping to tables.

The 2026 paper in order

The 2026 Paper I has eight Unit 8 MCQs in a row, one or more for each level. 2026 P-I Q26 describes three database models and asks for their names (8.1). Our own worked answer, not official: option 5. X is the network model, because a record can have more than one parent. Y is the hierarchical model, because each child has one parent. Z is the relational model, with records linked by shared values. Q27 to Q29 test constraints, Q30 tests a SELECT query, Q31 tests an ER diagram, and Q32 and Q33 test normal forms. Sections 2 to 6 solve each of them in the step where it belongs.

2. From a scenario to an ER diagram

An ER diagram shows the things a system stores data about (entities), the facts about each thing (attributes) and the links between them (relationships) (8.4). Each link also shows how many of each thing it joins (cardinality). Both Teachers' Guides give the same method. Find the nouns and the verbs in the scenario. The independent nouns become entities, the dependent nouns become their attributes, and the verbs become relationships (Teachers' Guide 8.4, page 60) (ගුරු අත්පොත, පිටුව 57).

Here is the scenario for this post, our own example. A school keeps a register of its clubs. Each student has a StudentID, a name in two parts, an NIC number, a date of birth and one or more parent phone numbers. Each club has a ClubID and a name. Each club is run by one advisor, a teacher with an AdvisorID and a name, and one advisor can run more than one club. A student can join many clubs, and a club has many students. The school records the date each student joins each club. Dineth (S101) is in the ICT Society and the Chess Club. Kasuni (S102) is in the ICT Society. Mr. Ruwan Silva advises the ICT Society, and Mrs. Anoma Jayasuriya advises the Chess Club and the Media Unit.

Entities and attributes

The nouns "student", "club" and "advisor" stand alone, so they become entities. The other nouns describe them, so they become attributes. The papers test five kinds of attribute:

  • Identifier: the attribute that names one entity, such as StudentID. Underline it.
  • Composite: an attribute with parts. StudentName has FirstName and LastName.
  • Multi-valued: an attribute with more than one value for one entity, drawn as a double oval. A student can have two parent phone numbers.
  • Derived: an attribute that can be calculated from another, drawn as a dashed oval. Age comes from DateOfBirth.
  • Simple: an attribute with no parts and one value, such as ClubName.

2023 (held 2024) P-I Q31 matches these kinds with their descriptions, and 2022 (held 2023) P-II B Q9(a) asks for a diagram with derived and multi-valued attributes.

Relationships and cardinality

Read each relationship in both directions. One student joins many clubs, and one club has many students, so JOINS is M:N. One advisor runs many clubs, but each club has one advisor, so SUPERVISES is 1:N, with the N on the club side.

The joining date is the trap. It is not a fact about the student, because Dineth has one date for the ICT Society and another for the Chess Club. It is not a fact about the club either, because each member joined on a different day. It belongs to one student in one club, so it is an attribute of the relationship JOINS.

2026 P-I Q31 tests exactly this. Students enrol in many courses, a course has many students, and each enrolment date must be stored. Our own worked answer, not official: option 2, an M:N relationship with EnrollmentDate on the Enroll diamond. Options 1, 4 and 5 have the wrong cardinality, and option 3 puts the date on Course.

The same shape in 2026 Paper II

2026 P-II B Q8(a)(i) asks for an ER diagram of a university system with lecturers, research projects and funding agencies, for 4 marks. Its shape is the same as the club register. Lecturers and projects are M:N, with Role and JoinedDate on the relationship. An agency funds many projects, and each project has one agency, so that link is 1:N. The lecturer's name is a composite attribute with FirstName, MiddleName and LastName. The paper names the lecturer's identifier "LectureID". It means a lecturer ID, and students should use the name the paper gives.

The syllabus also asks students to explain extended ER (EER) diagrams. In an EER diagram, an entity can have subclasses, such as full-time and part-time teachers (Syllabus 8.4, pages 33 and 34) (විෂය නිර්දේශය 8.4, පිටු 30 සහ 31). 2019 P-I Q24 and 2021 (held 2022) P-I Q37 ask about subclasses and specialisation.

3. From the ER diagram to tables and keys

The logical schema shows the same design as tables, with the keys that link them (8.5). Mapping is a fixed set of rules for entities, attributes and relationships (8.6). Apply the rules in order, and each part of the diagram has one place to go.

The six mapping rules

  1. Entity: each entity becomes a table, and its identifier becomes the primary key. STUDENT, CLUB and ADVISOR become tables.
  2. Composite attribute: store its parts. StudentName becomes FirstName and LastName.
  3. Derived attribute: do not store it. Age is calculated from DateOfBirth when it is needed.
  4. Multi-valued attribute: give it its own table with the owner's key. STUDENT_PHONE(StudentID, TelNumber) has the two attributes together as its primary key.
  5. 1:N relationship: put the primary key of the 1 side into the table of the N side as a foreign key. AdvisorID goes into CLUB.
  6. M:N relationship: make a new table. Its primary key is both primary keys together, and the relationship attributes go with it: MEMBERSHIP(StudentID, ClubID, JoiningDate).

2026 P-II B Q8(a)(ii) asks for the relational schema of its ER diagram, for 4 marks. It asks for the primary keys underlined and an arrow from each foreign key to the table it refers to, with the arrow head on that table's primary key. Diagram 3 uses the same notation. 2023 (held 2024) P-I Q34 tests rule 4: a multi-valued attribute does not stay in the entity's table.

Four kinds of key

The syllabus lists four keys (Syllabus 8.5, page 34) (විෂය නිර්දේශය 8.5, පිටුව 31). In the STUDENT table:

  • Candidate key: any attribute, or the smallest set of attributes, that names one row. StudentID and NIC are both candidate keys.
  • Primary key: the candidate key the designer chooses. Here it is StudentID.
  • Alternate key: a candidate key that was not chosen. Here it is NIC.
  • Foreign key: an attribute that holds the primary key value of another table. StudentID in MEMBERSHIP is a foreign key to STUDENT.

The Sinhala Teachers' Guide calls them "නිරූප්‍ය යතුර", "මුල්/ප්‍රාථමික යතුර", "විකල්ප යතුර" and "ආගන්තුක යතුර", and calls the domain "වසම" (ගුරු අත්පොත, පිටුව 59). The domain is the set of values an attribute is allowed to take, for example the 12 months of a year (Teachers' Guide 8.5, page 62).

4. Constraints in the tables

A constraint is a rule that the DBMS checks before it stores a row (8.2). The syllabus lists five constraints (Syllabus 8.2, pages 31 and 32) (විෂය නිර්දේශය 8.2, පිටු 29 සහ 30), and each one stops a different wrong row in the register.

Primary key and UNIQUE

A primary key is UNIQUE and NOT NULL together, and a table has only one primary key, even when it has more than one column. A table can have many UNIQUE constraints, and a UNIQUE column can hold NULL. 2026 P-I Q27 asks which statement is always true. Our own worked answer, not official: option 2, a table can have many UNIQUE constraints but only one primary key. 2026 P-I Q28 gives a COURSE table with a primary key, a UNIQUE name and CHECK (Credits BETWEEN 1 AND 4). Our own worked answer, not official: option 5, both CourseID and CourseName are unique, but only CourseID rejects NULL. A CHECK looks at one row only, so it never stops two courses from having the same credits.

Count the broken rules one by one

2026 P-I Q29 gives a STUDENT table with four constraints: a primary key, a UNIQUE NIC, CHECK(Age >= 18) and a NOT NULL name. The table already has one row, for Saman. The question asks which new row breaks exactly two constraints. Check each row against all four rules, then count:

  1. 'S002' with Saman's NIC: UNIQUE only. One rule.
  2. NULL key, Saman's NIC, age 16: primary key, UNIQUE and CHECK. Three rules.
  3. 'S004' with a NULL name: NOT NULL only. One rule.
  4. 'S003' with age 17: CHECK only. One rule.
  5. NULL key with Saman's NIC: primary key and UNIQUE. Two rules.

Our own worked answer, not official: option 5. The question has two printing errors. The table is created as STUDENT, but every statement inserts into Student. And the first row is given as INSERT INTO Student (StudentID = 'S001', ...), which is not SQL. Read it as the values 'S001', '200012345678', 20 and 'Saman'.

The Sinhala Teachers' Guide calls constraints "සම්බාධක" (ගුරු අත්පොත, පිටුව 61), but the 2026 Sinhala paper calls them "සංරෝධක". Both words mean constraint.

5. Normal forms from 0NF to 3NF

Normalisation splits a table so that each fact is stored once, which removes the insert, update and delete anomalies (8.7). The register in 2026 P-II B Q8(b) shows why. The school keeps all club data in one table:

CLUB_MEMBERSHIP(StudentID, ClubID, AdvisorID, StudentName, ClubName, AdvisorName, JoiningDate)

The paper gives three business rules: a student can be in many clubs and a club has many students, each club is supervised by one advisor, and (StudentID, ClubID) names each membership.

Three anomalies in one table

Fill the table with the register and the problems appear:

  • Update anomaly: Mr. Ruwan Silva's name is in every ICT Society row. A change in one row only leaves two different names for one advisor.
  • Insert anomaly: a new Robotics Club with its advisor cannot be stored until a student joins it, because a row needs a StudentID.
  • Delete anomaly: Tharindu is the only member of the Media Unit. When Tharindu leaves it, the last row with the Media Unit goes, and the club name and its advisor are lost too.

2022 (held 2023) P-II B Q9(b)(v) asks for an update anomaly shown with an SQL UPDATE.

Three kinds of dependency

A functional dependency A → B means that one value of A gives exactly one value of B. The syllabus names three kinds (Syllabus 8.7, pages 35 and 36) (විෂය නිර්දේශය 8.7, පිටුව 33):

  • Full dependency: the column needs the whole key. JoiningDate needs both StudentID and ClubID.
  • Partial dependency: the column needs only part of a composite key. StudentName needs only StudentID.
  • Transitive dependency: a non-key column depends on another non-key column. AdvisorName depends on AdvisorID, which depends on ClubID.

The four normal forms

Each step removes one kind of problem:

  1. 0NF: the table has a repeating group, for example one row for Dineth with a list of two clubs in it.
  2. 1NF: each cell holds one value, so each membership gets its own row. CLUB_MEMBERSHIP is in 1NF.
  3. 2NF: 1NF with no partial dependency. Move each partial dependency into its own table.
  4. 3NF: 2NF with no transitive dependency. Move each transitive dependency into its own table.

Our own worked answer to 2026 P-II B Q8(b), not official:

  • (i) Two partial dependencies: StudentID → StudentName, and ClubID → ClubName (ClubID → AdvisorID is a third one).
  • (ii) One transitive dependency: ClubID → AdvisorID → AdvisorName.
  • (iii) The 3NF tables are STUDENT(StudentID, StudentName), CLUB(ClubID, ClubName, AdvisorID), ADVISOR(AdvisorID, AdvisorName) and MEMBERSHIP(StudentID, ClubID, JoiningDate), with StudentID, ClubID, AdvisorID and the pair (StudentID, ClubID) as the primary keys.

These are the same tables as the mapping in section 3, without the phone and date of birth columns. A good ER diagram, mapped by the rules, is usually in 3NF already.

The 2026 MCQs on normal forms

2026 P-I Q32 gives ORDER_DETAIL(OrderID, ProductID, CustomerID, CustomerName, ProductName, Quantity) and asks which dependency breaks 2NF. Our own worked answer, not official: option 2, OrderID → CustomerID, because OrderID is only part of the key. CustomerID → CustomerName (option 4) is transitive, so it breaks 3NF, not 2NF. Options 1 and 3 are printed the same, (OrderID, ProductID) → Quantity, which is a full dependency.

2026 P-I Q33 matches the normal forms with their conditions. Our own worked answer, not official: option 1, 0NF with repeating records (D), 1NF with atomic attributes (B), 2NF with no partial dependency (C) and 3NF with no transitive dependency (A).

What the books say about normalisation

Both Teachers' Guides say normalisation "improves the performance of a database in inserting, updating and deleting data by reducing redundancies and anomalies" (Teachers' Guide 8.7, page 64) (ගුරු අත්පොත, පිටුව 65). That is right for changes to the data. It does not mean every query gets faster. A query on 3NF tables often needs a join, which is extra work. The gain is correct data, stored once.

The Sinhala books use three words for anomaly on one page: "විෂමතා" in the contents, "විපරීතතා" in the learning outcomes and "අසාමාන්‍යතා" in the concepts (ගුරු අත්පොත, පිටුව 65). They also use both "ප්‍රමත අවස්ථාව" and "ප්‍රමත ආකාරය" for normal form, and spell "functional dependancies" with an "a" (විෂය නිර්දේශය 8.7, පිටුව 33) (ගුරු අත්පොත, පිටුව 65). The 2026 Sinhala paper uses "ප්‍රමත අවස්ථාව", but calls a partial dependency "ආංශික පරායත්තතා" in Paper I Q33 and "අර්ධ පරායත්තතා" in Paper II Q8(b). All these words have one meaning each, so learn the English term with them.

6. SQL on the finished tables

SQL has two parts (8.3). The data definition language (DDL) builds and changes the structure, and the data manipulation language (DML) adds, changes, removes and reads the rows (Teachers' Guide 8.3, page 58). The Sinhala Teachers' Guide calls SQL "ව්‍යුහගත විමසුම් බස", DDL "දත්ත නිර්වචන භාෂාව" and DML "දත්ත හැසුරුම් බස" (ගුරු අත්පොත, පිටුව 63).

DDL builds the tables

Create the tables in the order of their keys: a table must exist before another table can refer to it.

CREATE TABLE ADVISOR (
  AdvisorID   CHAR(3) PRIMARY KEY,
  AdvisorName VARCHAR(50) NOT NULL
);

CREATE TABLE CLUB (
  ClubID    CHAR(3) PRIMARY KEY,
  ClubName  VARCHAR(40) NOT NULL UNIQUE,
  AdvisorID CHAR(3) NOT NULL,
  FOREIGN KEY (AdvisorID) REFERENCES ADVISOR(AdvisorID)
);

CREATE TABLE MEMBERSHIP (
  StudentID   CHAR(4),
  ClubID      CHAR(3),
  JoiningDate DATE NOT NULL,
  PRIMARY KEY (StudentID, ClubID),
  FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
  FOREIGN KEY (ClubID) REFERENCES CLUB(ClubID)
);

The composite primary key of MEMBERSHIP is one PRIMARY KEY clause with two columns. 2025 P-II A Q1(a) asks for a CREATE TABLE statement with data types and NOT NULL. ALTER TABLE adds or drops a column or a key after the table exists, and 2019 P-I Q17 asks for ALTER TABLE ... ADD CHECK.

DML adds and changes the rows

INSERT INTO MEMBERSHIP VALUES ('S101', 'C01', '2026-02-10');

UPDATE ADVISOR SET AdvisorName = 'Ruwan K. Silva' WHERE AdvisorID = 'A01';

DELETE FROM MEMBERSHIP WHERE StudentID = 'S103' AND ClubID = 'C02';

After normalisation, each command touches one fact. The UPDATE changes one row, so the update anomaly from section 5 is gone. The DELETE removes one membership, and the Media Unit stays in CLUB. A DELETE or an UPDATE with no WHERE changes every row of the table.

SELECT reads the rows

WHERE keeps a row only when its condition is true, and AND needs both parts to be true. 2026 P-I Q30 asks for the result of SELECT Name, Salary FROM EMPLOYEE WHERE DeptID = 'D01' AND Salary > 80000. Our own worked answer, not official: option 1, only Kasun with 85 000. Ruwan is in D01, but 75 000 is not more than 80 000.

An inner join puts rows of two tables together where a key matches. This query lists Dineth's clubs:

SELECT STUDENT.FirstName, CLUB.ClubName
FROM STUDENT
INNER JOIN MEMBERSHIP ON STUDENT.StudentID = MEMBERSHIP.StudentID
INNER JOIN CLUB ON MEMBERSHIP.ClubID = CLUB.ClubID
WHERE STUDENT.StudentID = 'S101';

The result has two rows: Dineth with ICT Society, and Dineth with Chess Club. 2021 (held 2022) P-I Q33 tests the INNER JOIN ... ON form, and 2022 (held 2023) P-II B Q9(b)(i) gives the same join as a WHERE condition.

Questions beyond the syllabus list

The syllabus lists SELECT from one table, SELECT with an inner join, INSERT, UPDATE and DELETE (Syllabus 8.3, pages 32 and 33) (විෂය නිර්දේශය 8.3, පිටුව 30). The papers have also asked parts that the list does not name. 2024 P-I Q37 asks for the written order of SELECT, FROM, WHERE, GROUP BY and HAVING. Our own worked answer, not official: option 1, in that order. 2023 (held 2024) P-I Q32 asks for the result of SELECT COUNT(*) FROM Employee WHERE Salary > ANY (SELECT Salary FROM Emplyoee). Our own worked answer, not official: option 2. > ANY keeps every salary that is greater than the smallest one (55000), so the count is 4. The table name is misspelt as "Emplyoee" inside the subquery, and 2023 (held 2024) P-I Q33 writes "NOTNULL" with no space. Read both as the paper means them. Learn COUNT, GROUP BY and HAVING too, because they have come in the papers.

7. Where the unit joins other lessons

A database is the store behind most systems in the syllabus, so Unit 8 joins at least four other units. The papers often keep the database inside a bigger scenario.

Web development (Unit 10)

A PHP page connects to MySQL and sends the same SQL as section 6. A form can add a member with INSERT, and a page can list a club's members with SELECT. PHP and MySQL past paper questions and 2027 predictions covers every PHP question from 2019 to 2026. Learn the tables and the keys of this unit first, because every PHP database question uses them.

Systems analysis and design (Unit 7)

In a data flow diagram, a data store holds data between processes. In the design step, each data store becomes one or more tables, and the ER diagram of this unit gives their structure. Systems analysis and design questions and the lessons they join follows one system from the problem to deployment.

Python (Unit 9) and business (Unit 12)

A Python program can read the same tables. The syllabus covers files and databases in programs (9.11, 9.12). The rows come back as tuples, like the list of tuples in 2026 P-II B Q9. In e-business (Unit 12), a shop needs customer, product and order tables, with an M:N link between orders and products. That link is the ORDER_DETAIL table of 2026 P-I Q32.

8. Past paper questions to try

Try these in the order of the design steps. Name the step first, then answer.

  • 2026 P-I Q26: database models (section 1).
  • 2026 P-I Q31, 2026 P-II B Q8(a)(i), 2025 P-II B Q9(a)(i), 2024 P-II B Q9(a)(i) and 2022 (held 2023) P-II B Q9(a): ER diagrams from a scenario (section 2).
  • 2023 (held 2024) P-I Q31 and 2021 (held 2022) P-I Q37: kinds of attribute, and EER diagrams (section 2).
  • 2026 P-II B Q8(a)(ii), 2025 P-II A Q2(b), 2025 P-II B Q9(a)(ii) and 2023 (held 2024) P-I Q34: ER diagram to tables (section 3).
  • 2024 P-I Q31, 2024 P-I Q32, 2025 P-I Q31 and 2022 (held 2023) P-I Q30: candidate, primary, alternate and foreign keys (section 3).
  • 2026 P-I Q27, 2026 P-I Q28, 2026 P-I Q29, 2025 P-I Q32 and 2023 (held 2024) P-I Q33: constraints (section 4).
  • 2026 P-II B Q8(b), 2025 P-II B Q9(b), 2024 P-II B Q9(b) and 2022 (held 2023) P-II B Q9(b)(v): dependencies, anomalies and 3NF (section 5).
  • 2026 P-I Q32, 2026 P-I Q33, 2024 P-I Q34 and 2022 (held 2023) P-I Q33: normal forms as MCQs (section 5).
  • 2026 P-I Q30, 2025 P-II A Q1(a), 2025 P-II A Q1(b), 2024 P-I Q36 and 2021 (held 2022) P-I Q33: SQL (section 6).
  • 2024 P-I Q37 and 2023 (held 2024) P-I Q32: SQL beyond the syllabus list (section 6).
Past PaperA/L ICT 2026 Past PaperMCQ 50 · මිනි. 120 · ලියූ වාර 3Open paper Past PaperA/L ICT 2025 Past PaperMCQ 50 · මිනි. 120 · ලියූ වාර 0Open paper Past PaperA/L ICT 2024 Past PaperMCQ 50 · මිනි. 120 · ලියූ වාර 1Open paper

Sources

  • G.C.E. (A/L) Information and Communication Technology question papers, 2019 to 2026, Department of Examinations, Sri Lanka.
  • The club register is our own example. The answers in this post are our own worked answers, not official.

මේ Explained එක බෙදාගන්න

A/L ICT කරන යාළුවෙකුට යවන්න.

මේ ලිපියට link කරන ලිපි

  1. English

    Normalisation by spotting dependencies from 1NF to 3NF

    One four-step method for every A/L ICT Paper II normalisation question. Find the key, draw the dependencies, name the normal form and split the table, proved on all eight papers from 2019 to 2026.

  2. English

    Database design in one school register

    The A/L ICT database unit is one job in five steps. One school club register goes from a scenario to an ER diagram, tables and keys, normal forms and SQL, the same order as the 2026 paper.

තවත් Explained

  1. Layer 2 vs Layer 3 switch differences and real uses

    How a Layer 2 switch and a Layer 3 switch differ, why each exists, where networks use them now and next, and the A/L ICT past paper questions that test them, with answers.

    Englishපියවර 7ක්
  2. A/L ICT second attempt study plan for 2027 by unit weight and past papers

    A study plan for students who sit A/L ICT again in 2027. It covers the 2026 results, the units that carry most marks, a lesson method with past papers, a month by month timeline and the free papers on this site.

    Englishපියවර 7ක්
  3. Python functions with lists of tuples and sequential search in 2026 Paper II Q9

    2026 Paper II Q9 worked part by part. The fine rule and its two readings, a function with if and elif, a list of tuples built with a loop, a sequential search that returns -1, and the same skills in Paper I.

    Englishපියවර 7ක්
All Explained