A named set of tuples sharing the same attributes.
PART 1 • RELATIONAL FOUNDATIONS
Represent Data as Relations and Protect Its Meaning
Move from an ER design to precise relations, choose keys deliberately and use integrity constraints to prevent invalid states.
BY THE END, YOU CAN
- Use relational terminology precisely.
- Calculate degree and cardinality.
- Distinguish every important key type.
- Diagnose integrity violations.
- Map common ER patterns to tables.
- Explain NULL without treating it as zero.
A Relation Has Structure, Values and Meaning
The familiar word “table” is useful, but formal terms help us reason correctly.
Relation schemaSTUDENT(student_id, name, branch, cgpa)
| student_id | name | branch | cgpa |
|---|---|---|---|
| 101 | Anu | AIML | 8.6 |
| 102 | Bharat | CSE | 7.9 |
| 103 | Charan | AIML | 9.1 |
Attribute one named columnTuple one rowDomain allowed values for an attributeRelation instance the current set of tuples
An ordered collection of attribute values representing one fact.
A named role or property within the relation schema.
The permitted atomic values and meaning for an attribute.
Number of attributes. STUDENT above has degree 4.
Number of tuples. The displayed instance has cardinality 3.
Important Mathematical Properties
Unique relation name
A schema gives each relation a distinct name within its namespace.
Unique attribute names
Each attribute is distinguishable inside one relation.
Atomic domain values
Each cell holds one value from its attribute's domain in the basic relational model.
No duplicate tuples
A mathematical relation is a set, and sets do not contain duplicates.
Row order is irrelevant
A query must request ordering explicitly when presentation order matters.
Column order is not meaning
Attributes are referenced by role or name, not visual position.
Keys Form a Family, Not a List to Memorize
Any unique attribute set
{student_id} and {student_id, name} may both uniquely identify a student. The second contains an unnecessary attribute.
A minimal super key
It is unique, and removing any attribute destroys that guarantee.
The chosen candidate key
The designer selects one candidate as the main identifier. It must be unique and not NULL.
An unchosen candidate key
Still unique, but not selected as the primary key.
A key containing multiple attributes
(student_id, course_id) can identify one enrolment.
A reference to another relation
ENROLMENT.student_id references STUDENT.student_id.
A system-created identifier
An artificial ID has no business meaning but can provide a stable compact reference.
An identifier from the domain
Examples may include a carefully governed registration number or ISBN.
All attribute sets⊃Super keys⊃Candidate keys→ choose one →Primary key
Interactive Key Explorer
For this exercise, roll number, institutional email and Aadhaar each uniquely identify a student. Test different attribute sets.
STUDENT
What kind of attribute set is it?
A key must first guarantee uniqueness; a candidate key must also be minimal.
Integrity Constraints Keep Data Meaningful
Every value belongs to its allowed domain
CGPA may be constrained to 0–10; branch may be limited to approved codes.
CHECK (cgpa BETWEEN 0 AND 10)Candidate-key values remain unique
Two students cannot share the same institutional email when it is a candidate key.
UNIQUE (email)Primary-key components cannot be NULL
Every stored entity must have a known, unique identity.
PRIMARY KEY (student_id)A foreign key references an existing target or is NULL when allowed
An enrolment cannot name a student that does not exist.
FOREIGN KEY (student_id) REFERENCES studentWhat happens when a referenced row changes?
RESTRICT / NO ACTION reject the parent changeCASCADE propagate it to matching childrenSET NULL remove the reference when NULL is permittedSET DEFAULT replace it with a valid default reference
Try to Insert a Student Row
The simulated schema checks primary key, domain, candidate key and foreign-key rules.
The candidate row has not been checked.
Known student IDs: 101, 102 and 103. Known email: [email protected]. Valid branches: AIML, CSE and ECE.
Interactive ER-to-Relational Mapper
Select an ER pattern to see the resulting relations, keys and placement decision.
NULL Means Unknown, Missing or Not Applicable
The student's emergency phone exists but is not currently known.
The value was expected but has not yet been entered.
A spouse_name value does not apply to an unmarried person.
NULL is not:
0'' (empty string)'NULL' (text)FALSEComparisons involving NULL usually produce UNKNOWN. SQL uses IS NULL and IS NOT NULL, not = NULL.
Six Formative Concept Checks
1. What is the degree of R(A, B, C, D)?
2. Which is a minimal super key?
3. Which rule prevents a NULL primary key?
4. How is an M:N relationship normally mapped?
5. Which statement about NULL is correct?
6. A foreign key value normally must:
Answered correctly: 0 of 6
University and Interview Questions
- Define degree and cardinality.
- What is a domain?
- Define candidate key.
- State entity integrity.
- What does NULL represent?
- Explain all important key types with examples.
- Explain relational integrity constraints.
- Map 1:1, 1:N and M:N relationships.
- Describe the properties of a relation.
- Natural key or surrogate key—how do you decide?
- Can a foreign key contain duplicates?
- Can it contain NULL?
- Why is email risky as a primary key?
- What happens during ON DELETE CASCADE?
Show the key-identification reasoning framework
- List attribute sets that uniquely identify every legal tuple.
- Remove redundant attributes to obtain minimal super keys.
- Call every remaining minimal set a candidate key.
- Select a stable, compact and controlled candidate as primary key.
- Enforce other candidate keys with uniqueness constraints.
- Use foreign keys wherever another relation must be referenced.
LEVEL 4 SUMMARY
You Can Now Build and Protect Relations
- A relation schema defines structure; its instance contains current tuples.
- Degree counts attributes and relational cardinality counts tuples.
- Candidate keys are minimal super keys; one becomes primary.
- Domain, key, entity and referential integrity prevent invalid states.
- ER patterns map systematically to relations and foreign keys.
- NULL is a marker for missing meaning, not an ordinary value.
COURSE CHECKPOINT
Mark this level when you can identify keys, validate rows and map the common ER patterns without notes.
Saved in this browser only.