ISYS2120 Chap.4 SQL Definition and Schema Design
SQL Definition and Schema Design
SQL Definition and Schema Design makes domain rules executable. CREATE TABLE names each column and datatype, while NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints determine which instances the DBMS may accept. The chapter distinguishes a composite primary key from separate uniqueness claims and treats referential actions as explicit policy choices.
It then maps conceptual designs into relations: simple attributes become columns, composite attributes are flattened, multivalued attributes and many-to-many relationships require separate tables, weak entities combine owner keys with discriminators, and total participation can require a non-null foreign key. This is the bridge between the conceptual-model assignment and executable SQL.
Practice therefore centres on violating inserts and updates: a schema is only convincing when its constraints reject a precisely named invalid state without excluding valid domain facts.
What this chapter covers
- 01
Define tables as enforceable contracts
- 02
Select types for meaning
- 03
Place primary-key constraints
- 04
Express referential integrity
- 05
Choose referential actions cautiously
- 06
Use NOT NULL and CHECK for local rules
- 07
Insert and update without breaking rules
- 08
Map strong entities directly
- 09
Map one-to-many relationships
- 10
Map many-to-many relationships
- 11
Map weak entities and subtypes
Enforce a composite session key
- +1Declare event_code and sequence_no NOT NULL and use PRIMARY KEY(event_code, sequence_no); add CHECK(capacity > 0).
- +1Insert the three valid rows. Repeated E1 and repeated sequence_no 1 are allowed because neither column is uniquely identifying alone.
- +1Attempt the duplicate pair (E1,1). The database rejects it because the combined primary-key value already exists.
- +1Query the table in key order and report only the three committed rows, together with the named uniqueness failure.
Key terms
- CREATE TABLE
- A DDL statement that creates a named relation by declaring its columns, datatypes and table constraints. The new table begins with no rows.
- Column
- A named field in a relation whose declared datatype constrains the values stored in that position of every row.
- Constraint
- A schema rule that the DBMS enforces when rows are inserted or modified, preventing specified invalid instances.
- NOT NULL
- A column constraint requiring every stored row to provide a non-NULL value for that field.
- PRIMARY KEY
- The declared row identifier. Its value or value combination must be unique and cannot contain NULL.
- UNIQUE
- A constraint requiring distinct non-NULL values or combinations beyond the table's selected primary key. SQL permits multiple NULLs in a UNIQUE column unless it is also declared NOT NULL.
- FOREIGN KEY
- A constraint requiring a non-NULL referencing value to match a key value in the referenced relation.
- Composite key
- A row identifier formed from more than one column because no component column distinguishes rows by itself.
- Candidate key
- Any column combination capable of distinguishing every row. A table may have several candidate keys but declares at most one primary key.
- CHECK constraint
- A Boolean condition that each inserted or updated row must satisfy, such as requiring a capacity greater than zero.
- Referential action
- The declared response when an update or deletion affects a referenced key, such as rejecting the change, cascading it or setting the reference to NULL.
SQL Definition and Schema Design FAQ
What makes a CREATE TABLE statement more than documentation?
Types, primary keys, foreign keys, NOT NULL and CHECK constraints are evaluated when data changes. They turn domain rules into an enforceable contract that rejects invalid states regardless of which application submits them.
How should I choose between CASCADE and a restrictive action?
Choose from the domain meaning of dependent history. Cascading can remove or update children with their parent, while restriction preserves them by blocking the change. Historical records often demand retention instead of automatic deletion.
Where does the foreign key go in a one-to-many mapping?
The key of the one-side relation usually migrates into the many-side relation as a foreign key. This lets every many-side tuple name its one-side partner without storing a repeating list on the parent.
How is a many-to-many relationship represented in relations?
Create an associative relation containing foreign keys to both participants, normally using their combination as a key when appropriate. Put relationship attributes there because their values depend on the participant pairing.
What proves that an ER-to-relational mapping is complete?
Trace every entity, attribute, relationship, key and participation constraint into a relation or enforceable rule. Then create a database state that violates each original constraint and confirm the mapped design cannot silently accept it.
Exam move
Study DDL by pairing every requirement with both syntax and a failing row. Build a small table, declare its datatype and constraints, then attempt one duplicate key, one missing mandatory value, one dangling foreign key and one value that violates CHECK. Record the database response and the domain rule it protects.
For composite keys, vary one component at a time so you can see why repeated individual values remain valid while the full pair cannot repeat. Then take one ER fragment and map it systematically: entity attributes first, multivalued attributes in separate tables, relationship tables where needed, owner keys for weak entities, and NOT NULL where total participation is enforceable.
For the conceptual-model assignment, compare the diagram and DDL constraint by constraint. For the final examination, practise writing CREATE TABLE statements without execution, then mentally run the same invalid inserts; this exposes missing commas and, more importantly, missing business rules.
Working through SQL Definition and Schema Design in ISYS2120? Sia is AskSia’s AI Information Technology tutor — ask any ISYS2120 SQL Definition and Schema Design question and get a clear, step-by-step explanation grounded in how ISYS2120 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
