relational schema and concept of keys | Relational Model and Relational Algebra | 2114112 Database Management System

Relation Schema & Keys

The structural blueprint of a database — how tables are defined, how rows are uniquely identified, and how tables link together.

What is a Relation Schema?

A relation schema defines the logical blueprint of a single table in a database. It specifies the relation's name and the list of attributes (columns) it contains — along with the domain (permitted data type) for each attribute. It is the time-invariant template describing data, distinct from the actual data rows (the relation instance) which change over time.

Formal Notation R(A₁, A₂, …, A_n)

📋

Relation Name (R)

The unique identifier for the table, e.g., EMPLOYEE, DEPARTMENT.

🏷️

Attributes (A₁…Aₙ)

The named columns of the relation. Each attribute plays a role within a specific domain (data type).

🔢

Degree / Arity

The total number of attributes n in the schema. A relation with 5 columns has degree 5.

🗂️

Domain (D)

The predefined set of permitted atomic values for an attribute, e.g., INTEGER, VARCHAR(50), DATE.

📸

Relation Instance

The actual data rows at a point in time. Instances change; the schema stays constant.

🏛️

Database Schema

A collection of relation schemas plus integrity constraints forms a complete relational database schema.

-- Example relation schema notation EMPLOYEE(emp_id, name, salary, hire_date, dept_id) DEPARTMENT(dept_id, dept_name, location) -- Primary key attributes are underlined in schema diagrams -- Foreign keys reference the primary key of another relation

The Concept of Keys

Because a relation is mathematically a set of tuples, no two tuples can be identical. Keys formalise and enforce this uniqueness. They act as unique identifiers for rows and establish how different tables link together — forming the backbone of relational integrity.

🔑 Key Hierarchy

Superkey
Any set of attributes that uniquely identify a tuple

Candidate Key
Minimal superkey — no redundant attributes

Primary Key
The chosen candidate key

Secondary Key
Remaining candidate keys

Key Type	Minimal?	NULL Allowed?	One per Relation?	Purpose
Superkey	✗	—	✗ (many)	Broad uniqueness guarantee; may have redundant attributes.
Candidate Key	✓	—	✗ (many)	Minimal unique identifier; all PKs come from this pool.
Primary Key	✓	✗ Never	✓ Exactly one	Principal row identifier; enforces entity integrity.
Secondary Key	✓	Context	✗ (many)	Unused candidate keys; still enforce uniqueness.
Foreign Key	✗	✓	✗ (many)	Links tables; enforces referential integrity.

Key Types — In Depth

🔷 Superkey Broad

• Definition: Any set of one or more attributes whose combined values uniquely identify every tuple in a relation.
• Uniqueness constraint: No two distinct tuples can ever share the same value for a superkey's attributes.
• Default superkey: Every relation has at least one — the set of all its attributes combined.
• Limitation: A superkey may contain extraneous (redundant) attributes that aren't necessary for uniqueness.

-- EMPLOYEE(emp_id, name, salary, dept_id) -- Valid superkeys: {emp_id} -- minimal {emp_id, name} -- redundant (name not needed) {emp_id, name, salary, dept_id} -- all attributes (always a superkey)

🔑 Candidate Key Minimal Superkey

• Definition: A superkey with no redundant attributes — the smallest possible set that still guarantees uniqueness.
• Minimality property: Removing any single attribute from a candidate key must break its ability to uniquely identify tuples.
• Multiple candidates: A relation schema may have several distinct candidate keys.
• Pool for PK: The primary key is always chosen from the set of candidate keys.

-- STUDENT(student_id, email, name, phone) -- Candidate keys (both are minimal and unique): {student_id} -- candidate key 1 {email} -- candidate key 2 (emails are unique per student)

🔴 Primary Key Principal Identifier

• Definition: The one candidate key chosen by the database designer to be the principal row identifier.
• Notation: Primary key attributes are underlined in schema diagrams.
• Stability recommendation: Choose a primary key whose values are never (or very rarely) changed over time.

🚫

Entity Integrity Constraint: No primary key attribute can ever hold a NULL value. A NULL primary key would make it impossible to uniquely identify the corresponding tuple, violating the fundamental purpose of a primary key.

-- Schema notation: underline denotes primary key EMPLOYEE(emp_id, name, salary, dept_id) -- emp_id cannot be NULL -- emp_id values must be unique across all rows

⬜ Secondary Keys Unique Keys

• Definition: Any candidate key that was not selected as the primary key.
• Behaviour: Still enforce uniqueness across the relation — equivalent to a UNIQUE constraint in SQL.
• Alternate name: Often called alternate keys or unique keys.

-- STUDENT(student_id, email, name) -- student_id chosen as PK → email becomes a secondary key UNIQUE(email) -- SQL enforcement of secondary key

🟢 Foreign Key Referential Link

• Definition: A set of attributes in one relation (the referencing relation) whose values must match the primary key values in another relation (the referenced relation).
• NULL allowed: A foreign key can be NULL — meaning the tuple does not currently reference any row in the referenced relation.
• Direction matters: FK values must exist in the referenced PK, but not every PK value needs to be referenced.

🔗

Referential Integrity Constraint: A tuple that references another relation must point to an existing, valid tuple in that relation — or the foreign key value must be NULL. Dangling references (pointing to non-existent rows) are not permitted.

-- EMPLOYEE.dept_id is a foreign key referencing DEPARTMENT.dept_id EMPLOYEE(emp_id, name, salary, dept_id) DEPARTMENT(dept_id, dept_name, location) -- Every dept_id in EMPLOYEE must exist in DEPARTMENT.dept_id -- OR dept_id in EMPLOYEE can be NULL (employee not assigned yet) FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(dept_id)

Relation Schema & Keys — the structural foundation that keeps your data unique, consistent, and correctly linked.