Integrity constraints over relations
An integrity constraint (IC) is a condition that is specified on a database schema and restricts the
data can be stored in an instance of the database.
Various restrictions on data that can be specified on a relational database schema in the form of
‘constraints’.
A DBMS enforces integrity constraints, in that it permits only legal instances to be stored in the
database. Integrity constraints are specified and enforced at different times as below.
[Link] the DBA or end user defines a database schema, he or she specifies the ICs that
must hold on any instance of this database.
[Link] a data base application is run, the DBMS checks for violations and disallows
changes to the data that violate the specified ICs.
Legal Instance:
If the database instance satisfies all the integrity constraints specified on the database schema.
The constraints can be classified into 4 types as below.
[Link] Constraints.
[Link] Constraints.
[Link] Integrity Constraints.
[Link] Integrity Constraints.
Explanation is as below.
[Link] Constraints
Domain constraints are the most elementary form of integrity constraints. They are tested easily
by the system whenever a new data item is entered into the database.
Domain constraints specify the set of possible values that may be associated with an attribute.
Such constraints may also prohibit the use of null values for particular attributes.
The data types associated with domains typically include standard numeric data types for
integers A relation schema specifies the domain of each field or column in the relation instance.
These domain constraints in the schema specify an important condition that each instance of the
relation to satisfy: The values that appear in a column must be drawn from the domain associated
with that column.
Thus the domain of a field is essentially the type of that field.
2. Keys in Relational Model 1
KEYS in DBMS is an attribute or set of attributes which helps you to
uniquely identify a row(tuple) in a relation(table).
They allow you to find the relation between two tables.
Help you to enforce identity and integrity in the relationship.
Types of Keys in DBMS (Database Management System)
There are mainly Eight different types of Keys in DBMS and each key has
it’s different functionality:
1. Primary Key
2. Super Key
3. Candidate Key
4. Alternate Key
5. Foreign Key
6. Compound Key
7. Composite Key
8. Surrogate Key
Types of Database Keys
1. Primary Key
A primary key is a unique key, meaning it can uniquely identify each record
(tuple) in a table.
It must have unique values and cannot contain any duplicate values.
A primary key cannot be NULL, as it needs to provide a valid, unique
identifier for every record.
A primary key does not have to consist of a single column. In some cases, a
composite primary key (made of multiple columns) can be used to uniquely
identify records in a table.
Databases typically store rows ordered in memory according to primary key
for fast access of records using primary key.
2
Example:
STUDENT table -> Student(STUD_NO, SNAME, ADDRESS, PHONE) ,
STUD_NO is a primary key
Table: STUDENT
STUD_NO SNAME ADDRESS PHONE
1 Tom New York 123456789
2 John Los Angeles 223365796
3 Alice Chicago 175468965
2. Super Key
The set of one or more attributes (columns) that can uniquely identify a tuple
(record) is known as Super Key. It may include extra attributes that aren't
important for uniqueness but still uniquely identify the row. For Example,
STUD_NO, (STUD_NO, STUD_NAME), etc.
A super key is a group of single or multiple keys that uniquely
identifies rows in a table. It supports NULL values in rows.
A super key can contain extra attributes that aren’t necessary for uniqueness.
For example, if the "STUD_NO" column can uniquely identify a student,
adding "SNAME" to it will still form a valid super key, though it's
unnecessary.
Example: Consider the STUDENT table
STUD_NO SNAME ADDRESS PHONE
1 Tom New York 123456789
3
STUD_NO SNAME ADDRESS PHONE
2 John Los Angeles 223365796
3 Alice Chicago 175468965
A super key could be a combination of STUD_NO and PHONE, as this
combination uniquely identifies a student.
3. Candidate Key
The minimal set of attributes that can uniquely identify a tuple is known as
a candidate key. For Example, STUD_NO in STUDENT relation.
A candidate key is a minimal super key, meaning it can uniquely identify a
record but contains no extra attributes.
It is a super key with no repeated data is called a candidate key.
The minimal set of attributes that can uniquely identify a record.
A candidate key must contain unique values, ensuring that no two rows have
the same value in the candidate key’s columns.
Every table must have at least a single candidate key.
A table can have multiple candidate keys but only one primary key.
Example: For the STUDENT table below, STUD_NO can be a candidate
key, as it uniquely identifies each record.
STUD_NO SNAME ADDRESS PHONE
1 Tom New York 123456789
2 John Los Angeles 223365796
4
STUD_NO SNAME ADDRESS PHONE
3 Alice Chicago 175468965
4. Alternate Key
Definition:
An Alternate Key is a candidate key that is not chosen as the primary key. It
can still uniquely identify a record in a table but is kept as an alternative to the
primary key.
Example
Consider a STUDENT table:
Student_ID Roll_No Email Name
S101 R01 a@[Link] Anil
S102 R02 b@[Link] Beena
S103 R03 c@[Link] Charu
Candidate Keys: Student_ID, Roll_No, Email
Primary Key: Student_ID (chosen to uniquely identify records)
Alternate Keys: Roll_No, Email (since they are candidate keys but not selected as
primary key)
5. Foreign Key
A Foreign Key is an attribute (or set of attributes) in one table that refers to the
Primary Key of another table. It is used to maintain referential integrity between
related tables, ensuring that the data remains consistent.
5
Example
Consider two tables: DEPARTMENT and STUDENT
DEPARTMENT Table
Dept_ID (Primary Key) Dept_Name
D01 CSE
D02 ECE
STUDENT Table
Student_ID (Primary Key) Student_Name Dept_ID (Foreign Key)
S101 Ravi D01
S102 Anu D02
Explanation
Dept_ID in the DEPARTMENT table is the Primary Key.
Dept_ID in the STUDENT table is a Foreign Key.
The foreign key Dept_ID in STUDENT must match an existing Dept_ID in the
DEPARTMENT table.
This ensures that every student is linked to a valid department.
6. Compound Key
A Compound Key (also called a Composite Key) is a key formed by combining
two or more attributes of a relation to uniquely identify a tuple (row) in a table.
Example
Consider a STUDENT_COURSE table:
Student_ID Course_ID Semester Grade
S101 C01 Sem-1 A
S101 C02 Sem-1 B
S102 C01 Sem-1 A
S101 C01 Sem-2 A+
Student_ID alone is not unique (a student can enroll in multiple courses).
Course_ID alone is not unique (many students can enroll in the same course).
Student_ID + Course_ID together uniquely identify each record.
Hence, (Student_ID, Course_ID) is a Compound Key.
7. Composite Key in DBMS
A Composite Key is a key formed by combining
6 two or more attributes (columns)
to uniquely identify a tuple (row) in a relation when no single attribute is sufficient
on its own.
Example
Consider a table ENROLLMENT:
Student_ID Course_ID Semester Grade
S101 C01 Sem1 A
S101 C02 Sem1 B
S102 C01 Sem1 A
Student_ID alone is not unique (a student can enroll in multiple courses).
Course_ID alone is not unique (a course can have many students).
But the combination of Student_ID and Course_ID uniquely identifies each record.
Hence, (Student_ID, Course_ID) together form a Composite Key.
8. Surrogate Key
Definition:
A Surrogate Key is an artificially generated, system-assigned unique identifier
used as the primary key of a table. It has no business meaning and is created
solely to uniquely identify each record.
Example
Consider a STUDENT table.
Without Surrogate Key (using natural key)
Roll_No Name Email
101 Amit amit@[Link]
102 Riya riya@[Link]
Here, Roll_No is a natural key. If the roll number system changes, the primary key
becomes problematic.
With Surrogate Key
7
Student_ID Roll_No Name Email
1 101 Amit amit@[Link]
2 102 Riya riya@[Link]
Student_ID is the Surrogate Key
It is auto-generated (e.g., AUTO_INCREMENT)
It never changes, even if Roll_No or Email changes
[Link] Integrity Constraints
Entity Integrity Constraint ensures that each tuple (row) in a relation is uniquely identifiable.
It states that the primary key of a table cannot contain NULL values.
[Link] Integrity Constraints
Referential Integrity Constraint ensures that a foreign key value in one relation must either
match a primary key value in the referenced relation or be NULL. It maintains consistency
between related tables.
Schema Diagram
A schema diagram is a graphical representation of database structure, showing tables,
attributes, primary keys, and relationships.
8
9
Representation:
STUDENT (Student_ID PK, Name, Age, Dept_ID FK)
DEPARTMENT (Dept_ID PK, Dept_Name)
ER to Relation Mapping
ER to Relation Mapping is the process of converting an Entity–Relationship (ER) diagram
into relational tables.
1
0
1
1
Super Key vs Candidate Key vs Primary Key
Basis Super Key Candidate Key Primary Key
Any attribute set that uniquely identifies a
Definition Minimal super key Chosen candidate key
tuple
Uniqueness Yes Yes Yes
Minimal No Yes Yes
NULL allowed? No No No
Number possible Many Few Only one
Example Table: STUDENT
Student_ID Email Name
Super Keys:
o {Student_ID}
o {Email}
o {Student_ID, Name}
Candidate Keys:
o {Student_ID}
o {Email}
Primary Key:
o {Student_ID} (chosen by designer)
1
2