Constraint & Keys in DBMS : MCQs & PYQs with Explanation

Option B

A Secondary Key is a field or a group of fields that is used to retrieve or search records in a table but does not uniquely identify a record. 
For example, in a student table, attributes like name or city can be used as secondary keys to retrieve multiple matching records.

• Foreign Key is used to establish relationships between tables.
• ERD is a diagram used for database design.

Option C

In DBMS, an attribute can have a NULL value unless it is specifically restricted using constraints like NOT NULL. NULL means the value is unknown, missing, or not applicable.
A Primary Key uniquely identifies each record in a relation. Since the primary key uniquely identifies a row, it can be used to retrieve all other attributes of that row.

Option D

In a relational database, a Super Key is any set of attributes that uniquely identifies tuples, but it may contain extra attributes and hence is not always minimal.
A Candidate Key is a minimal super key, meaning no subset of it can be a super key. Among candidate keys, one is chosen as the Primary Key to uniquely identify tuples.
Since the Primary Key itself is a candidate key, it cannot be a proper subset of another candidate key.

Option C

In a relational database, a Foreign Key is used to establish a relationship between two tables. It is a field in one table that refers to the Primary Key of another table, ensuring data consistency and integrity.
For example, if a Student table has rollno as a primary key, then another table like Marks can use rollno as a foreign key to link records between the two tables.

Option D

A column constraint is applied directly to a single column while defining a table.

• NOT NULL ensures that a column cannot have NULL values.
• PRIMARY KEY can be defined at the column level.
• CHECK restricts the values that can be stored in a column based on a condition.

Option A

Referential Integrity ensures that a foreign key in one table must match a primary key in another table or be NULL. Here:

• Table R(c) : Primary Key.
• Table S(d) : Foreign Key referencing R(c).

Option C

Referential integrity constraint ensures that a Foreign Key in one relation (R2) must always refer to an existing Primary Key in another relation (R1).
If we insert a tuple in R2 with a foreign key value that does not exist as a primary key in R1, it violates referential integrity.
If a tuple in R1 is deleted but there are still tuples in R2 that reference it, the relationship becomes invalid, causing a referential integrity violation.

Option A

A table can have only one primary key. The primary key uniquely identifies each record in a table.
It may consist of one attribute or a combination of multiple attributes (composite primary key).

Option D

In a Relational Database Management System (RDBMS), keys are used to uniquely identify records in a table or relation. A super key is an attribute or a combination of attributes that ensures the uniqueness of each record. Among these super keys, a candidate key is a minimal set of attributes that can still uniquely identify a record, meaning it does not contain any unnecessary attributes.

In other words, every candidate key is a subset of a super key, but not all super keys are candidate keys because some may include extra attributes that are not needed for uniqueness.

Option A

Referential Integrity means that you cannot enter a value in a foreign key column unless that value already exists as a primary key in another table. This rule ensures that the relationship between tables remains valid and consistent.
E.g.:
CREATE TABLE Student
(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50)
);
The RollNo is the primary key of the Student table.
CREATE TABLE Course
(
    CourseNo INT PRIMARY KEY,
    RollNo INT,
    FOREIGN KEY (RollNo) REFERENCES Student(RollNo)
);
The CourseNo is the primary key of the Course table and RollNo is a foreign key that refers to Student(RollNo).
INSERT INTO Student VALUES (101, 'Suresh Kulahry'); /* Valid (Allowed) */
INSERT INTO Course VALUES (201, 101); /* Valid (Allowed) */
INSERT INTO Course VALUES (202, 105); /* Invalid (Not Allowed) */

Option A

A key is one or more attributes (columns) used to identify records uniquely or to establish relationships between tables in a relational database.
There are different types of keys in DBMS, such as Super Key, Candidate Key, Primary Key, Alternate Key, and Foreign Key. Each key has a specific purpose.
A Foreign Key is an attribute (or a set of attributes) in one table that refers to the Primary Key (or Candidate Key) of another table. It is used to establish and maintain a relationship between two tables.
A table can have multiple foreign keys if it references multiple tables or references the same table more than once.

Types of Keys in DBMS:

Option D

A Super Key is any set of one or more attributes that uniquely identifies each record in a table. It may contain unnecessary (redundant) attributes.
A Candidate Key is a Minimal Super Key, meaning no attribute can be removed without losing uniqueness.
Hence, only Candidate Keys satisfy the minimality condition, not Super Keys.

Option C

A super key is a set of one or more attributes that uniquely identify each record in a table. However, a super key may include extra attributes that are not necessary for uniqueness.

When we remove all unnecessary attributes from a super key, leaving the minimal set of attributes that still uniquely identify each record, it becomes a candidate key.

Every candidate key is a minimal super key, meaning it cannot be reduced any further without losing the ability to uniquely identify records. From among the candidate keys, one is selected as the primary key to uniquely identify rows in the table.

Option D

A Foreign Key is used to create a relationship between two tables. Its main purpose is to ensure Referential Integrity, which means that the value in the foreign key column must match a value in the primary key of the referenced table or be NULL, if allowed.
This ensures that there are no invalid or orphan records in the database.

• Null Integrity deals with NOT NULL constraints.
• Domain Integrity ensures valid data types and ranges.

Option C

A superkey is any set of attributes that can uniquely identify a tuple in a relation. If you have a relation with n attributes and one candidate key consisting of k attributes, the total number of super keys is 2^n−k.
Remember, this formula can only work with a relation has only one candidate key consisting of k attributes.
Given,
Relation: R(A, B, C)
Total Attributes (A,B,C) n = 3.
Candidate key B : k = 1
Total super keys: 2³⁻¹ = 2² = 4.