Essential Concepts: DBMS Keys, Constraints, and Relational Model

Posted on Nov 26, 2025 in Commercialization and Logistics

Understanding Keys in DBMS

A Key in a Database Management System (DBMS) is an attribute or a set of attributes used to uniquely identify each record (tuple) in a table and to maintain relationships between tables.

Types of Database Keys

  1. Super Key

    A set of one or more attributes that can uniquely identify a record in a table.

    Example: Given Student(Roll_No, Name, Email), both {Roll_No} and {Roll_No, Name} are Super Keys.

  2. Candidate Key

    A minimal Super Key, meaning no unnecessary attribute is included.

    Example: If both {Roll_No} and {Email} can uniquely identify a student, they are both Candidate Keys.

  3. Primary Key

    One of the candidate keys selected to uniquely identify records. It cannot have NULL or duplicate values.

    Example: Roll_No is often chosen as the Primary Key.

  4. Alternate Key

    The candidate keys which are not selected as the Primary Key.

    Example: If Roll_No is the Primary Key, then Email is an Alternate Key.

  5. Foreign Key

    An attribute in one table that refers to the Primary Key of another table. It is used to maintain referential integrity.

    Example:

    • Student(Roll_No, Name, Course_ID)
    • Course(Course_ID, Course_Name)

    Here, Course_ID in the Student table is a Foreign Key referencing the Course table.

Importance of Keys

Keys ensure uniqueness, data integrity, and relationships among different tables in a database.

Database Integrity Constraints

Integrity Constraints are the rules used to maintain the accuracy, consistency, and correctness of data within a database. They ensure that only valid data is entered into the tables.

Types of Integrity Constraints

  1. Domain Integrity

    Ensures that the value of each attribute must be from a defined domain (data type, range, or format).

    Example: Age INT CHECK (Age > 0) ensures that Age cannot be negative.

  2. Entity Integrity

    Ensures that every table has a Primary Key, and its value cannot be NULL or duplicate.

    Example: Emp_ID in the Employee table must be unique and not NULL.

  3. Referential Integrity

    Ensures that a Foreign Key value must match an existing Primary Key value in another table or be NULL.

    Example: Dept_ID in the Employee table must exist in the Department table.

  4. Key Integrity

    Ensures that each key (especially the Primary Key) uniquely identifies a record in the table.

  5. Null Integrity (Optional)

    Restricts attributes from taking NULL values where they are not allowed.

The Relational Database Model Structure

A Relational Database is a type of database that stores data in the form of tables (relations). Each table consists of rows and columns.

Core Components of the Relational Model

Rows (Tuples)
Represent individual records.
Columns (Attributes)
Represent data fields of each record.

Structure Terminology

Relation (Table)
A table containing rows and columns.
Tuple
A single row in a relation. Example: (101, Riya, 50000, D1)
Attribute
A column in a relation (e.g., Emp_ID, Emp_Name, Salary).
Domain
The set of possible values for an attribute (e.g., Salary domain = numeric values).
Key
A unique identifier for each tuple (e.g., Emp_ID).
Relation Schema
Describes the structure of a relation. Example: Employee(Emp_ID, Emp_Name, Salary, Dept_ID)

Example Relation: Employee

Emp_IDEmp_NameSalaryDept_ID
101Riya50000D1
102Arjun60000D2
Conclusion on Relational Model

The Relational Model organizes data into tables that are easy to understand, manage, and connect using keys and relationships.