IB COMPUTER SCIENCE | A3.2.4 Construct Relational Tables

Lesson objective

Construct linked relational tables using appropriate types, primary/foreign/composite keys and clear concatenated-key semantics, with integrity constraints.

Learn

A3.2.4 | CONSTRUCT TABLES FOR RELATIONAL DATABASES

01 | TURN THE MODEL INTO TABLE DEFINITIONS

The school club ERD identifies students, clubs and memberships. Now the design needs concrete tables: which columns exist, which values identify a row and which rules stop an invalid reference or duplicate membership?

To construct tables, supply the structure and its rules, not only a few example rows. A useful table specification names each column, gives a suitable type and identifies primary keys, foreign keys and other relevant constraints.

We will implement the Student–Club relationship through Enrolment, then test the definitions. The rule is one membership per student-club pair in this example; term-specific history would require a revised design.

The lesson also distinguishes a multi-column key from physically concatenating values into a string. Both ideas appear in discussions of combined keys, but they need careful handling.

02 | WRITE THE BUSINESS RULES FIRST

A student has a unique StudentID, a required name and an optional email. A club has a unique ClubID, a required name and a positive capacity. Students and clubs can exist before any memberships are recorded.

Each enrolment identifies exactly one existing student and one existing club. It has a required role and joining date. A student may join several different clubs, and a club may have several different students.

These requirements justify two parent tables and one linking table. Do not put a comma-separated list of clubs inside Student, or repeat the student’s contact details in every membership.

Capacity is a declared positive number here. The further rule “current members must not exceed capacity” compares records and needs suitable additional transactional logic; a simple positive-capacity check does not enforce it.

03 | CONSTRUCT THE STUDENT TABLE

ColumnIllustrative typeKey / constraintPurpose
StudentIDVARCHAR(5)PRIMARY KEYUnique non-null student identifier
StudentNameVARCHAR(80)NOT NULLRequired name; names may repeat
EmailVARCHAR(120)NULL permittedOptional contact value

The primary key identifies a specific row, not the first row on the screen. Two students can both be named Maya if their StudentIDs differ. The key must be unique and non-null.

The chosen character limits are illustrative requirements, not universal name or email limits. VARCHAR(5) does not alone mean exactly five characters or a particular ID pattern; add a format constraint if that is required.

NOT NULL requires a non-null value, but it does not universally reject an empty or whitespace-only text value. If a meaningful nonblank name is required, implement that additional rule too.

04 | CONSTRUCT THE CLUB TABLE

ColumnIllustrative typeKey / constraintPurpose
ClubIDVARCHAR(5)PRIMARY KEYClub identification
ClubNameVARCHAR(80)NOT NULLRequired descriptive name
CapacityINTEGERNOT NULL; CHECK (Capacity > 0)A recorded positive whole-number capacity

A type supplies one kind of rule and a constraint adds another. INTEGER represents whole numbers; the check requires a positive value. Neither rule makes ClubName unique unless that is separately required.

Keep one fact in its appropriate place. The club’s capacity belongs to Club, not independently in every student’s membership row. This reduces duplicated facts and conflicting updates.

A student count calculated from enrolments is a query result, not necessarily a separately stored attribute. If a derived count is stored for performance, it needs a consistency strategy.

05 | PLACE THE FOREIGN KEY ON THE MANY SIDE

In a one-to-many relationship, the child or many-side rows commonly carry the reference to their parent. Enrolment.StudentID references Student.StudentID, allowing several enrolments to identify the same student.

A foreign key must refer to an appropriate declared key under the DBMS’s rules. A primary key is the usual introductory target, but suitable unique keys can also be reference targets. Definitions must be compatible.

A foreign-key column does not automatically have to be unique. Making Enrolment.StudentID unique would incorrectly prevent a student joining multiple clubs in this model.

Because each enrolment needs a student, the reference is non-null here. Optional references in other designs may allow NULL under their rules; foreign keys do not universally make every reference mandatory.

06 | THE ENROLMENT TABLE HAS ONE COMPOSITE PRIMARY KEY

ColumnIllustrative typeKey / constraint
StudentIDVARCHAR(5)PK part; non-null FK to Student
ClubIDVARCHAR(5)PK part; non-null FK to Club
RoleVARCHAR(40)NOT NULL; default Member
JoinedDateDATENOT NULL

The composite primary key is (StudentID, ClubID). StudentID alone repeats across a student’s different clubs; ClubID alone repeats across a club’s different students. Their combination must be unique.

Both key parts are also foreign keys, but those are separate rules. The primary key prevents duplicate membership pairs; the foreign keys require existing parent rows.

A Role belongs to the membership because one student can have different roles in different clubs. The joining date also describes that particular association.

07 | CHECK THE DESIGN WITH SAMPLE ROWS

StudentID | PKStudentNameEmail
S01Mayamaya01@example.invalid
S02Leonleon02@example.invalid
S03MayaNULL
ClubID | PKClubNameCapacity
C01Robotics20
C02Game Design16
StudentID | PK part, FKClubID | PK part, FKRoleJoinedDate
S01C01Builder2026-09-01
S01C02Designer2026-09-02
S02C01Programmer2026-09-01

The two Mayas are distinct students. Repeated S01 in Enrolment is valid because its ClubID differs. A second S01,C01 row would violate the pair’s uniqueness, even if its Role were different.

These rows illustrate the rules; they do not replace them. An empty Enrolment table should still have its primary and foreign-key definitions. NULL in the sample represents an absent email, not the literal text “NULL”.

08 | COMPOSITE AND CONCATENATED KEYS

A composite key uses multiple attributes as one identifier. Some teaching materials use concatenated key as another name for a combined key. Always make clear whether the design means multiple key columns or one string built by joining their values.

With a multi-column primary key, the DBMS treats StudentID and ClubID as a tuple or combination. It need not store a new text column containing both. This is the approach used in our main design.

If values are literally concatenated, an unambiguous encoding is necessary. Joining “1” and “23” without a separator yields “123”; joining “12” and “3” yields the same string even though the pairs differ.

A separator can help only if its use and component rules are controlled. Fixed-width or properly encoded components are other possibilities. A casual string join is not a substitute for well-defined uniqueness and reference constraints.

09 | SEE A CONCATENATION COLLISION

Compare two different identifier pairs using a naive concatenation and an explicit separator. The chosen components contain only digits, so the separator is unambiguous for these examples.

This activity illustrates representation only. It does not create a key or imply that arbitrary components can always be separated safely with any character.

10 | AN ALTERNATIVE SURROGATE IDENTIFIER

A design might give Enrolment its own generated EnrolmentID primary key. That can be useful when other records need to reference a membership through one column. A surrogate key is an introduced identifier rather than a direct meaningful domain combination.

However, a new primary key does not automatically prevent repeated student-club pairs. If the one-membership-per-pair rule still applies, add a suitable UNIQUE(StudentID, ClubID) constraint and the required non-null and reference rules.

A generated sequence value is not necessarily gap-free, and a displayed number alone does not explain the business rule. Identification and domain uniqueness need separate consideration.

For the current worked solution, keep the composite primary key. The alternative shows why different valid implementations must preserve the same intended constraints.

11 | IMPLEMENTATION EXAMPLE: CREATE THE THREE TABLES

The following SQL-style definitions express the worked design using features supported by many relational DBMSs. Exact syntax, enforcement and supported actions must be checked for the chosen system. This page displays the code; it does not connect to a database.

Read the parent tables first, then Enrolment. The two-column PRIMARY KEY is declared once at table level. The foreign-key clauses separately connect it to each parent.

CREATE TABLE Student (
    StudentID VARCHAR(5) PRIMARY KEY,
    StudentName VARCHAR(80) NOT NULL,
    Email VARCHAR(120)
);

CREATE TABLE Club (
    ClubID VARCHAR(5) PRIMARY KEY,
    ClubName VARCHAR(80) NOT NULL,
    Capacity INTEGER NOT NULL CHECK (Capacity > 0)
);

CREATE TABLE Enrolment (
    StudentID VARCHAR(5) NOT NULL,
    ClubID VARCHAR(5) NOT NULL,
    Role VARCHAR(40) NOT NULL DEFAULT 'Member',
    JoinedDate DATE NOT NULL,
    PRIMARY KEY (StudentID, ClubID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE RESTRICT,
    FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE RESTRICT
);

The example deliberately does not claim that VARCHAR length, mandatory text or a positive Capacity fully validates the domain. Additional format, nonblank, date and membership-capacity rules may be needed. Do not copy a partial specification and assume all business requirements are enforced.

12 | READ EACH CONSTRAINT AS A TEST

ConstraintQuestion it answersWhat it does not prove
PRIMARY KEYIs this identifier unique and non-null?The real person’s name is correct
FOREIGN KEYDoes this reference satisfy its declared parent-key rules?The user is authorised or eligible to join
NOT NULLHas a non-null value been supplied?Text is necessarily nonblank or factually true
CHECKDoes this row obey the stated condition?Every cross-row business rule is enforced
DEFAULTWhat value is supplied under applicable omission rules?Explicit NULL is always replaced or the value is appropriate in every case

Well-defined tables improve integrity because the DBMS can reject inconsistent changes through these rules. They complement application checks, verification and permissions rather than replacing all of them.

In some systems a CHECK expression that evaluates to unknown can pass, so pair a range check with NOT NULL when a missing value is forbidden.

13 | TRY ADDING A MEMBERSHIP

Choose a student and club, then enter a nonblank role up to 40 characters for this activity. A successful proposed row is added to the in-page model, so trying the same pair again demonstrates the composite-key rule.

The activity explicitly rejects missing or unknown parents and duplicate pairs. It adds a nonblank role rule beyond the SQL example’s NOT NULL alone. The joining date is fixed for the teaching model; it is not a full database or a demonstration of all real DBMS checks.

Your rows last only while this page remains open. The activity does not enforce club capacity, eligibility or authorisation and does not send data anywhere.

14 | INSERT, UPDATE AND DELETE RELATIONSHIPS CAREFULLY

A child row needs valid parent references when the relevant constraint is checked. Creating Student and Club records before their Enrolment rows is the straightforward order in this example. Some DBMSs support deferred checks within transactions, but that is not assumed here.

Deleting a student who still has enrolments is restricted by the shown ON DELETE RESTRICT policy. This prevents leaving those references dangling. A system may choose cascading deletion or another valid policy for a different requirement, but it must be explicit and preserve intended history.

Updating a referenced identifier also needs a deliberate policy. Stable identifiers reduce avoidable changes; copying names into references does not solve the underlying identity problem.

A transaction can group related changes, and suitable concurrency control protects overlapping work. A table definition alone does not automatically solve every workflow race or cross-row capacity limit.

15 | ONE-TO-ONE AND ONE-TO-MANY TABLE DESIGNS

The same reference principle supports ordinary one-to-many relationships: Customer has a key and Order carries a non-unique customer reference. Add NOT NULL when every order must identify a customer.

For an appropriate one-to-one design, a child reference can also have a UNIQUE constraint, preventing several child rows referencing the same parent. Optionality still needs separate treatment, and the exact NULL/unique behaviour should be checked for the DBMS.

The rule “every parent must have at least one child” is not ordinarily enforced just by a foreign key in the child. It may need additional transactional, application or DBMS-supported mechanisms.

Construct tables from the stated rules, not from a memorised instruction that every foreign key must be unique or every relationship must use one identical template.

16 | TEST THE TABLES BEFORE TRUSTING THEM

Try valid and invalid cases: a new unique pair, a duplicate pair, an absent parent, a missing mandatory value and an out-of-range capacity. Check that valid work succeeds and the intended invalid work fails.

Also test assumptions the simple schema does not enforce. An empty name may need a nonblank rule; a full club needs a safe enrolment-capacity check; a real joining date may need a domain constraint. Distinguish a known gap from a constraint that merely looks reassuring.

Use a data dictionary to record each field’s meaning, type, permitted values and key/reference roles. Keep the ERD, definitions and application requirements consistent when the design changes.

Good integrity means meaningful rules are actually enforced and tested, not that the database can discover every real-world error without evidence.

17 | MATCH THE RULE TO THE CONSTRUCTION

Choose the mechanism most directly enforcing each stated requirement. A complete design needs several mechanisms working together.

18 | CONSTRUCT A COMPLETE TABLE ANSWER

For construct, provide the requested structure in a clear table specification, schema notation or suitable definition form. Name the tables and columns, give suitable types, label the complete primary key and show foreign-key targets explicitly.

For the club example, construct Student and Club with their own identifiers. Construct Enrolment with the pair as one primary key and each component as a reference. Include membership-specific attributes and suitable mandatory/range rules, and state the one-membership-per-pair assumption.

If you use the term concatenated key, clarify whether you mean a multi-attribute key or a physically combined representation. Avoid an ambiguous string that gives different pairs the same stored value.

Then test the construction with sample rows and edge cases. The next lesson examines normal forms and how dependencies influence which facts belong together.

MATCH THE TABLE RULE TO ITS PURPOSE

Terminology

Terminology

Table construction

Defining columns, types and rules implementing the intended relational structure.

Primary key

The chosen unique, non-null identifier for a table.

Foreign key

A declared reference constraint to a suitable key under DBMS rules.

Composite primary key

One primary key comprising several columns together.

Concatenated key

A term sometimes used for a combined key; literal string concatenation must be distinguished.

Concatenation

Joining representations into a sequence, which can be ambiguous without rules.

Tuple

A structured combination of values treated together rather than an unstructured joined string.

Key collision

Different intended identifiers yielding the same representation.

Surrogate key

An introduced identifier separate from the domain’s meaningful attribute combination.

UNIQUE constraint

A rule preventing prohibited duplicate column values or combinations.

NOT NULL

A rule preventing a missing/unknown NULL value in the column.

CHECK constraint

A declared condition applied under the DBMS’s checking rules.

DEFAULT

A value supplied according to the applicable omission rules.

Referencing table

A table containing the foreign-key values.

Referenced table

A table supplying the appropriate target key.

Parent row

A referenced row in the stated parent-child design.

Child row

A row referring to a parent under the stated design.

Linking table

A table explicitly representing associations between other entities.

Referential integrity

Reference values satisfy their declared relationship rules.

Entity integrity

Primary-key identification requirements, including uniqueness and non-nullness.

Domain integrity

Values satisfy suitable type and domain requirements.

Data dictionary

Documentation of field meaning, types, constraints and relationships.

ON DELETE RESTRICT

A policy preventing relevant deletion while references remain under its rules.

Cascading deletion

A configured action deleting dependent rows when the relevant parent is deleted.

Stable identifier

An identifier designed to avoid unnecessary change over an instance’s life.

Schema definition

The stated structure and rules, distinct from sample data.

Membership attribute

A fact about a particular association, such as a role or joining date.

Constraint testing

Checking valid and invalid cases against the intended rules.

Deferred constraint

A supported check postponed to a defined later point, often transaction completion.

Business invariant

A domain condition that must hold, sometimes requiring more than a simple row constraint.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which belong in a table construction?
2. Which describe the example composite key?
3. Which are foreign-key facts?
4. Which describe concatenation carefully?
5. Which apply to a surrogate EnrolmentID?
6. Which distinguish constraints?
7. Which respect reference integrity?
8. Which require further domain handling in the shown design?
9. Which are valid testing cases?
10. Which make a strong construction?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Construct Student and Club definitions for the stated example.

2. Construct Enrolment and label all key roles.

3. Explain why the foreign key is on the many side.

4. Distinguish composite and literal concatenated representation.

5. Explain how a surrogate key changes the construction.

6. Explain primary, foreign and domain integrity.

7. Explain NOT NULL and DEFAULT limits.

8. Describe insertion and deletion planning.

9. Explain why a positive capacity check does not enforce enrolment capacity.

10. Construct suitable tests.

11. Describe one-to-one and one-to-many implementation differences.

12. Construct a complete answer rather than sample data alone.

CONSTRUCT IT | IMPLEMENT THE CLUB ERD

Construct Student, Club and Enrolment definitions from the stated rules. Show types, the complete primary keys, foreign-key targets and relevant mandatory/range rules. Include valid example rows and three rejection tests. Explain what would change with a surrogate membership key, and identify a business rule needing more than the shown simple constraints.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.2.4 Construct Relational Tables is coming soon. Complete the matching task and written questions in your exercise book.