ISYS1055 Chap.2 Relational Model Building Blocks: Keys and Constraints
Relational Model Building Blocks: Keys and Constraints
Week 2 sets the vocabulary the rest of the course is written in: relation, tuple, attribute, domain, and the family of keys that identify and connect rows. The stated outcomes are to explore the structure of the relational database model, learn its basic building blocks, identify the different types of keys, and explore the integrity constraints that protect stored data (the set reading is Connolly and Begg on the relational model). It shows up in assessment three ways - as terminology and "is this a relation?" quiz items in the first in-practical quiz, as the Understanding the Model questions in Assessment Task 1, and as the CREATE TABLE work in Part C of the Database Design Project, where your constraints have to actually fire.
What this chapter covers
- 01Table vs relation: the five rules a table must satisfy - atomic cells, one domain per column, unique column names, insignificant row and column order, no duplicate rows
- 02The terminology map: relation, relation schema, attribute, tuple, tuple component, domain; relation schema vs relation instance
- 03Written schema conventions used all semester - primary key underlined (written first), foreign key marked with an asterisk
- 04Superkey, candidate key, primary key, composite primary key, alternate key, foreign key - and the rule that a key claim rests on semantics, not on the sample rows
- 05The three integrity constraints: key constraint (uniqueness), entity integrity (no null in a primary key), referential integrity (a foreign key matches an existing primary key or is null)
- 06NULL as a marker for an absent value - unknown, not applicable or not yet supplied - and why it is neither zero nor the string 'NULL'
- 07DDL patterns: CREATE TABLE with NOT NULL, PRIMARY KEY (...) and FOREIGN KEY (...) REFERENCES parent(...)
- 08SQLite specifics: the five storage classes, PRAGMA foreign_keys = ON, doubling an apostrophe inside a string literal, and running a whole script by highlighting it first
Keys and constraint violations on a two-relation schema
- +1(a) Two attributes are unique by definition rather than by accident: memberID, which the library issues once per member, and email, which the library requires to be unique. Both are minimal - neither can be reduced - so both are candidate keys. Choose memberID as the primary key because it is short, stable and never re-used; email then becomes the alternate key and would be declared UNIQUE.
- +1(b) fullName may happen to be unique in the current rows, but a key claim rests on the semantics of the data, not on the sample: two members can share a name, so fullName is not a candidate key. This is the judgement the course keeps returning to - test a proposed key against what the business allows for all time, not against today's instance.
- +1(c1) Re-inserting an existing memberID violates the key constraint, which enforces the uniqueness half of the primary key. (c2) Leaving memberID blank violates entity integrity, which forbids a null in any primary-key attribute; note that a partial-column INSERT that names only the other columns is exactly how students trip this.
- +1(c3) Inserting a Loan whose memberID has no matching Member row violates referential integrity: every foreign-key value must already exist in the referenced primary key, or be wholly null. The same constraint also protects the parent side, so deleting a Member who still has Loan rows would leave dangling children and is rejected too. In SQLite this constraint is only enforced once PRAGMA foreign_keys = ON has been set on the connection - with it off, the bad row is accepted silently.
Key terms
- Relation
- A table that also satisfies all five relational rules: every cell holds a single atomic value, each column draws its values from one domain, column names are unique, row and column order are insignificant, and no two rows are identical. Hence the course's line: every relation is a table, but every table cannot be a relation.
- Tuple, attribute, domain
- The formal names for row, column and the set of permitted values for a column. A cell is a tuple component, and every component holds a value drawn from its attribute's domain.
- Candidate key vs primary key
- A candidate key is a minimal superkey - a set of attributes whose values are unique for every tuple and which cannot be reduced further. The candidate key chosen to identify tuples is the primary key; it must exist, must be unique and may never be null. Candidate keys not chosen are alternate keys.
- Composite primary key
- A primary key made of more than one attribute, where uniqueness comes from the combination of values. Every participating attribute must be non-blank in every tuple. Composite keys arise naturally from weak entities and from many-to-many link relations.
- Foreign key
- An attribute (or set of attributes) in one relation whose values must appear in the primary key of the referenced relation. Unlike a primary key it may be null, but it may not take an arbitrary value. It is also the one place where duplication in a well-designed schema is legitimate.
- NULL
- A special marker meaning the value is absent - unknown, not applicable, or not yet specified. It is not zero and not the string 'NULL', and it is the reason comparisons in Week 5 must use IS NULL rather than = NULL.
Relational Model Building Blocks: Keys and Constraints FAQ
What is the difference between the key constraint and entity integrity?
They protect the two different halves of what a primary key promises. The key constraint enforces uniqueness - no two tuples may carry the same primary-key value - and is implemented by PRIMARY KEY or UNIQUE. Entity integrity enforces non-blankness - no primary-key attribute may hold a null - and is implemented by NOT NULL on every primary-key column. A composite key needs both halves on every participating attribute.
Can a foreign key be null when a primary key cannot?
Yes, and that asymmetry is deliberate. A null foreign key means the relationship simply does not exist for that row - an employee not yet assigned to a department - which is a legitimate state. A null primary key would mean a row that cannot be identified, which is never legitimate. What a foreign key may not do is hold a value that has no match in the referenced primary key.
Why is a table with a comma-separated list in one cell not a relation?
Because it breaks the first rule: every cell must contain a single atomic value that cannot be broken into smaller meaningful components. A cell holding three tutor names is three facts in one place, and it makes every query, update and count unreliable. The fix is to decompose so there is one row per combination - which is also the 1NF move you will meet again in Week 9.
Does SQLite enforce foreign keys automatically?
No - foreign keys are off by default and must be switched on per connection with PRAGMA foreign_keys = ON. This surprises students during the Week 2 constraint drill, because the bad child row is accepted silently with the pragma off. For a drop-and-recreate script the usual pattern is to turn them off, drop children before parents, create parents before children, then turn them back on.
Assessment move
Run the constraint-violation drill yourself rather than reading it: build a small two-table schema in SQLite Studio twice, once with no constraints and once with PRIMARY KEY, NOT NULL and FOREIGN KEY declared, then attempt the same three bad inserts against each and diff the CREATE TABLE clauses. Nothing else makes the three constraints stick as fast. While you are there, learn the two SQLite Studio habits that save whole practicals - highlight an entire script before executing, or only the topmost statement runs, and double an apostrophe to escape it inside a string literal. Finally, get the written notation into muscle memory now (primary key first and underlined, foreign key asterisked), because you will write it in the seven-step mapping in Week 4, in Assessment Task 1 and again in Part B of the Database Design Project.
Working through Relational Model Building Blocks: Keys and Constraints in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Relational Model Building Blocks: Keys and Constraints question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
