The relational model, proposed by E.F. Codd in 1970, organises data into relations (tables) where each row is a tuple and each column is an attribute. It provides a mathematical foundation based on set theory and predicate logic. Integrity constraints ensure data correctness and consistency. Every popular RDBMS (PostgreSQL, MySQL, Oracle, SQL Server) implements this model. Relational model keys and constraints are the most tested GATE DBMS topic.
Terminology
| Formal term | Informal term | Example |
|---|---|---|
| Relation | Table | Student, Employee |
| Tuple | Row / Record | (101, "Ravi", "CSE", 3.8) |
| Attribute | Column / Field | StudentID, Name, GPA |
| Domain | Data type + constraints | Age: INTEGER, 0 to 120 |
| Degree | Number of columns | Student with 4 columns has degree 4 |
| Cardinality | Number of rows | Student with 1000 rows has cardinality 1000 |
| Schema | Table definition (structure) | Student(StudentID, Name, Dept, GPA) |
Keys — the most tested GATE topic in DBMS
| Key type | Definition | Example on Student(ID, Name, Email) |
|---|---|---|
| Super key | Any set of attributes that uniquely identifies a tuple | {ID}, {Email}, {ID, Name}, {ID, Email} |
| Candidate key | Minimal super key — no subset is also a super key | {ID}, {Email} |
| Primary key | The chosen candidate key | StudentID |
| Alternate key | Candidate keys not chosen as PK | Email (if StudentID is PK) |
| Foreign key | References PK of another table | DeptID in Student references Department.DeptID |
| Composite key | PK made of multiple attributes | (StudentID, CourseID) in Enrolls_In |
GATE: Super key vs Candidate key
Every candidate key is a super key, but not every super key is a candidate key. Super key = uniqueness only. Candidate key = uniqueness + minimality. If {A,B} uniquely identifies tuples but {A} alone also does, then {A,B} is a super key but NOT a candidate key.
Integrity constraints
All four integrity constraint types in SQL
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE Employee (
EmpID INT PRIMARY KEY, -- Entity integrity: no NULL PK
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
DeptID INT REFERENCES Department(DeptID) -- Referential integrity
ON DELETE SET NULL
ON UPDATE CASCADE,
Salary DECIMAL(10,2) CHECK (Salary > 0), -- Domain constraint
Age INT CHECK (Age BETWEEN 18 AND 65)
);
-- Referential integrity violation:
-- INSERT INTO Employee VALUES (1,'Ravi','r@x.com', 99, 50000, 30)
-- ERROR: DeptID 99 does not exist in Department- Entity Integrity: Primary key cannot be NULL.
- Referential Integrity: FK value must match existing PK or be NULL.
- Domain Integrity: Values must fit the column domain (data type + CHECK).
- User-defined Integrity: Business rules via CHECK constraints.
Practice questions (GATE-style)
- Relation R(A,B,C,D). FDs: A→B, B→C, CD→A. Find all candidate keys. (Answer: Test: CD→A,B,C,D — CD is a candidate key. AD→B,C and AD→D so AD→ABCD — AD is candidate key. BD→C, BD→CD→A — BD is candidate key. Answer: {CD, AD, BD}.)
- What constraint is violated when you insert a row with NULL as the primary key? (Answer: Entity integrity.)
- ON DELETE CASCADE: if Department with DeptID=5 is deleted, what happens to Employees with DeptID=5? (Answer: They are automatically deleted — CASCADE propagates to referencing rows.)
- Is {StudentID, CourseID, InstructorID} a candidate key if {StudentID, CourseID} already identifies each row? (Answer: No — it is a super key but not candidate key because it is not minimal.)
- Difference between primary key and unique key: (Answer: Primary key: cannot be NULL, only one per table. Unique key: can be NULL (in most DBMS), multiple unique keys allowed per table. Both enforce uniqueness.)
On LumiChats
LumiChats can identify candidate keys from functional dependencies, check integrity constraints, and debug referential integrity errors in SQL. Paste your schema and FDs and ask: 'What are all candidate keys?'
Try it free