Database Management Systems

CSE-Artificial Intelligence and Machine Learning

V-Semester
AL-502 Database Management Systems

Syllabus
UNIT II: Relational Data models: Domains, Tuples, Attributes, Relations, Characteristics of
relations, Keys, Key attributes of relation, Relational database, Schemas, Integrity constraints.
Referential integrity, Intension and Extension, Relational Query languages: SQLDDL, DML,
integrity con straints, Complex queries, various joins, indexing, triggers, assertions , Relational
algebra and relational calculus, Relational algebra operations like select, Project ,Join,
Division, outer union. Types of relational calculus i.e. Tuple oriented and domain oriented
relational calculus and its operations.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Relational Data Models

The Relational Data Model is one of the most widely used data models in Database
Management Systems (DBMS). Introduced by Edgar F. Codd in 1970, it organizes data into
tables (also known as relations) consisting of rows and columns. This model emphasizes data
accessibility and flexibility, making it foundational for modern databases. Relational model
makes the query much easier than in hierarchical or network database systems. In 1970, E.F
Codd has been developed it. A relational database is defined as a group of independent tables
which are linked to each other using some common fields of each related table. This model can
be represented as a table with columns and rows. Each row is known as a tuple. Each table of
the column has a name or attribute. It is well knows in database technology because it is usually
used to represent real-world objects and the relationships between them. Some popular
relational databases are used nowadays like Oracle, Sybase, DB2, MySQL Server etc.

Relational Model Terminologies:

Following are the terminologies of Relational Model:

Relation Table

Tuple Row, Record

Attribute Column, Field

Domain It consists of set of legal values

Cardinality It consists of number of rows

Degree It contains number of columns

Let's explain each term one by one in detail with the help of example:

Example: STUDENT Relation

Stu_No S_Name PHONE_NO ADDRESS Gender

10112 Rama 9874567891 Islam ganj F

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
12839 Shyam 9026288936 Delhi M

33289 Laxman 8583287182 Gurugram M

27857 Mahesh 7086819134 Ghaziabad M

17282 Ganesh 9028939884 Delhi M

Operations on Relational Model:

o Insertion Operation
o Deletion Operation
o Update Operation
o Retrieval Operation

Insert operation: It is used to insert a new record in the table. Adding new records to the table
is much easier than other models. Data values will not be found in a relation when the following
condition occurs:
o If we try to insert a duplicate value for the field that is selected as a primary key.
o If we insert a NULL value in the attribute that contains primary key.
o If we try to enter a data value in the foreign key attribute that does not exist in
corresponding primary key attribute.
o If an attribute is assigned a value that does not exist in the corresponding domain.

DELETE operation: This operation is used to delete records from the table but problems arise
when the rows to be deleted have some attributes which are foreign key attributes.
Update operation: It is used to modify or change the data value of a record in a table. Updating
an attribute that is neither a primary key nor a foreign key requires only checking that the new
value is of the correct data type and domain. If we modify a data value of a primary key and
foreign key attribute then need to check:
o The modified value does not contain the value of the corresponding foreign key value.
o The new values must not already exist in the table.

Retrieval operation: It is used to save a record from a relation. This operation is very simple
and homogeneous.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
1. Domain
 Definition: A domain is the set of allowable values for one or more attributes.
 Examples:
o Domain for "age" could be integers between 0 to 150.
o Domain for "color" could be {red, blue, green}.
 Purpose: Restricts and validates the values entered into a database.
 Types of Domains: Numeric, alphanumeric, date, etc.
2. Tuple
 Definition: A tuple represents a single row in a table, which contains data for each
attribute.
 Example:
o In an Employee table, a tuple might be (1, John, 30, IT).
 Components: A tuple consists of multiple attribute values.
 Uniqueness: Each tuple in a relation is unique.
3. Attribute
 Definition: An attribute is a column in a table that represents a property of the entity.
 Example:
o In a "Student" table, attributes could be "Student_ID," "Name," "Age," and
"Department."
 Types:
o Primary Key: Uniquely identifies a tuple.
o Foreign Key: Refers to the primary key of another table.
 Properties: An attribute has a name, domain, and may have constraints (e.g., NOT
NULL, UNIQUE).
4. Relationship
 Definition: A relationship defines how entities are related to each other in a database.
 Example:
o "Teacher" and "Class" can have a relationship like "teaches."
 Types of Relationships:
o One-to-One: A record in one table is linked to only one record in another table.
o One-to-Many: A record in one table can be related to multiple records in
another table.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
o Many-to-Many: Records in both tables can be related to multiple records in
each other.
5. Characteristics of Relationships
 Cardinality: Specifies the number of occurrences in one entity that are related to the
number of occurrences in another.
o One-to-One (1:1): Each entity in the relationship will have only one related
entity.
o One-to-Many (1
): One entity can be associated with multiple entities.
o Many-to-Many (M
): Entities on both sides of the relationship can have multiple associations.
 Participation: Defines whether all entities in a set must participate in a relationship.
o Total Participation: Every entity in the set must be involved in the relationship.
o Partial Participation: Only some entities from the set are involved.
 Degree: The number of participating entities in the relationship (Unary, Binary,
Ternary).
 Attributes of Relationships: A relationship can have its own attributes (e.g.,
"date_of_joining" in an "employs" relationship between "Company" and "Employee").
6. Properties of Relations
o Each attribute in a relation has only one data value corresponding to it i.e. they do not
contain two or more values.

o Name of the relation is distinct from all other relations.

o Each relation cell contains exactly one atomic (single) value
o Each attribute contains a distinct name
o Attribute domain has no significance
o tuple has no duplicate value
o Order of tuple can have a different sequence

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
o It also provides information about metadata.

7. Merits of Relational Model:

Following are the various merits of relational model:
o This provides an abstract view of the data. It abstracts the physical structure from the
logical structure of data.
o This model is very easy to design. Tables can use different attributes as per
requirements.
o The relational model supports data independence. In a relational database the data is
stored in tables so that we can modify the data without changing the physical structure.
o Relational database helps the user to use a query language to query the database.
o It offers more flexibility than other models.
o By moving sensitive attributes, we can also implement database security control and
authorization in a particular table into a separate relation with its authorization controls.
o Relational database helps the user to use a query language to query the database.
o A relational model consists of simple relationships. The characteristics of a database
that make it immune to certain maintenance problems have been developed in the
context of relational models.
o It is useful for representing most real world objects and the relationships between them.
It is very easy to implement a relationship through the use of a composite key, so this
model persistence method dominates the market.

8. Demerits of Relational Model:

Most of the drawbacks of the relational database is not because of the shortcoming but because
of the way it is being implemented, we can avoid the drawbacks of the relational model by
using proper designing techniques and proper database standards are enforced. Following are
the various demerits of relational model:
o The main disadvantage of relational models is that they do not support binary data for
example: images, documents, spreadsheets etc.
o The relational model can easily adapt to new hardware so incurs large hardware
overhead.
o Relational databases use a simple mapping of logical tables to physical structures.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
o This mostly limits performance and allows non-relational systems such as object
oriented management systems to perform better on specialised applications such as
CAD, CAM etc.
o Enforcing data integrity in relational models is difficult because no single piece of
hardware has control over the data.
o The relational model is suitable for small databases but not suitable for complex
databases because the user needs to know the complex physical data storage details. So,
while designing the databases they don't come to light when they may cause problems.
When a database grows it will slow down the system and will result in performance
degradation and data corruption.

Keys in DBMS
Types of Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)
Keys are one of the basic requirements of a relational database model. It is widely used to
identify the tuples(rows) uniquely in the table. We also use keys to set up relations amongst
various columns and tables of a relational database.
Different Types of Database Keys
 Candidate Key
 Primary Key
 Super Key
 Alternate Key
 Foreign Key
 Composite Key
Keys play an important role in the relational database.
It is used to uniquely identify any record or row of data from the table. It is also used to establish
and identify relationships between tables.

For example, ID is used as a key in the Student table because it is unique for each student. In
the PERSON table, passport_number, license_number, SSN are keys since they are unique for
each person.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems

Definition:
In Database Management System (DBMS), a key is a unique identifier that distinguishes each
record (tuple) in a relational database table. Keys ensure data integrity, uniqueness, and
efficient data retrieval.
Importance:
1. Uniquely identifies each record.
2. Ensures data integrity and consistency.
3. Supports efficient data retrieval and sorting.
4. Enables relationships between tables.
5. Prevents data duplication.
Why We Need Keys:
1. Data organization and structuring.
2. Efficient querying and indexing.
3. Data integrity and consistency.
4. Scalability and performance.
5. Reduced data redundancy.

Types of Keys:
1. Primary Key (PK):
- Unique identifier for each record.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
- Cannot be null or duplicate.
- Automatically indexed.
2. Foreign Key (FK):
- References the primary key of another table.
- Establishes relationships between tables.
3. Unique Key (UK):
- Ensures uniqueness, but can be null.
- Can be composite (multiple columns)
4. Composite Key:
- Combination of multiple columns.
- Unique identifier for each record.
5. Alternate Key:
- Secondary unique identifier.
- Can be used instead of primary key.
6. Candidate Key:
- Potential primary key.
- Meets primary key requirements.
7. Super Key:
- Set of attributes that uniquely identifies each record.
- Includes primary key.
Key Characteristics:
1. Uniqueness.
2. Non-nullability.
3. Stability (minimal changes).
4. Singularity (one key per table)
Benefits:
1. Improved data organization.
2. Enhanced data integrity.
3. Efficient querying and indexing.
4. Better data relationships.
5. Reduced data redundancy.
Real-World Example:
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
In a customer database:
- Primary Key: Customer ID.
- Foreign Key: Order ID (references Customer ID).
- Unique Key: Email Address.
- Composite Key: Order ID + Product ID.
Understanding keys is fundamental to designing and managing efficient databases. Keys
ensure data integrity, uniqueness, and efficient retrieval, making them essential in DBMS
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.
 It is a minimal super key.
 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.
 It must contain unique values.
 It can contain NULL values.
 Every table must have at least a single candidate key.
 A table can have multiple candidate keys but only one primary key.
 The value of the Candidate Key is unique and may be null for a tuple.
 There can be more than one candidate key in a relationship.
A Candidate Key in a Database Management System (DBMS) is a set of one or more attributes
(columns) that can uniquely identify a tuple (row) in a relation (table). Among all candidate
keys in a table, one is selected as the Primary Key, while others remain as alternate keys.
o A candidate key is an attribute or set of attributes that can uniquely identify a tuple.
o Except for the primary key, the remaining attributes are considered a candidate key.
The candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the
attributes, like SSN, Passport_Number, License_Number, etc., are considered a candidate key.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems

Key Points about Candidate Key:

1. Uniqueness:
o Each value of a candidate key uniquely identifies a tuple in the table.
o No two tuples can have the same value for the candidate key attributes.
2. Minimality:
o A candidate key must be minimal, meaning it should not contain any
unnecessary attributes. If any attribute is removed from the candidate key, it
will no longer uniquely identify the tuple.
o No proper subset of a candidate key should be able to identify the tuples
uniquely.
3. Multiple Candidate Keys:
o A relation can have multiple candidate keys.
o For example, in a student table, both Student_ID and Email could serve as
candidate keys since they uniquely identify each student.
4. Primary Key:
o One of the candidate keys is selected as the Primary Key of the relation. The
primary key uniquely identifies tuples and does not allow NULL values.
5. Alternate Key:
o The remaining candidate keys, which are not selected as the primary key, are
called Alternate Keys.
6. Super Key vs. Candidate Key:
o A Super Key is a set of attributes that can uniquely identify a tuple, but it may
not be minimal.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
o A Candidate Key is a minimal super key, meaning it contains no unnecessary
attributes.
Example:
Employee Table:
Employee_ID Email Phone_No Name
101 john@example.com 1234567890 John Smith
102 alice@example.com 9876543210 Alice Jones
103 bob@example.com 5647382910 Bob Brown
In this table:
 Employee_ID, Email, and Phone_No are all candidate keys because they can uniquely
identify each employee.
 The Primary Key might be Employee_ID.
 Email and Phone_No are Alternate Keys.
Characteristics of Candidate Keys:
 Non-nullable: A candidate key must not have NULL values.
 Immutable: The values of a candidate key should not change frequently, as they
uniquely identify records.
 Uniqueness Constraint: All candidate keys enforce a uniqueness constraint in the
relation.
Example of Candidate Key and Minimality:
If we have a relation Student with the attributes {Student_ID, Email, Phone_Number, Name},
and both Student_ID and Email can uniquely identify students, then both are candidate keys.
However, Student_ID + Phone_Number would be a super key, not a candidate key, because
it's not minimal.
A Candidate Key plays a critical role in ensuring data integrity by uniquely identifying
records. It helps define the structure of a database by providing potential candidates for the
primary key, ensuring the uniqueness of data within a table.
Example:
STUD_NO is the candidate key for relation STUDENT.
Table STUDENT

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

 The candidate key can be simple (having only one attribute) or composite as well.
Example:
{STUD_NO, COURSE_NO} is a composite
candidate key for relation STUDENT_COURSE.
Table STUDENT_COURSE

STUD_NO TEACHER_NO COURSE_NO

1 001 C001

2 056 C005

Defining Candidate Keys Using SQL Constraints

Single-Attribute Candidate Keys
Example Scenario: Consider an Employees table where both Employee_ID and Email can
uniquely identify an employee.
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key (Candidate Key)
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Candidate Key)
Phone_No VARCHAR(15) UNIQUE -- Another Unique Constraint (Another
Alternate Candidate Key)
);
Explanation:
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Employee_ID is defined as the Primary Key.
 Email and Phone_No are defined with Unique Constraints, making them Alternate
Candidate Keys.
Composite Candidate Keys
Example Scenario: Consider an OrderDetails table where the combination of Order_ID and
Product_ID uniquely identifies each order line.
CREATE TABLE OrderDetails (
Order_ID INT,
Product_ID INT,
Quantity INT,
PRIMARY KEY (Order_ID, Product_ID), -- Composite Primary Key (Candidate Key)
CONSTRAINT FK_Order FOREIGN KEY (Order_ID) REFERENCES
Orders(Order_ID),
CONSTRAINT FK_Product FOREIGN KEY (Product_ID) REFERENCES
Products(Product_ID)
);
Explanation:
 The combination of Order_ID and Product_ID is set as the Primary Key, making it a
Composite Candidate Key.
 This ensures that each pair of Order_ID and Product_ID is unique within the
OrderDetails table.
Altering Existing Tables to Add Candidate Keys
Adding a Unique Constraint
Example: Adding a unique constraint to the Email column in the Employees table.
ALTER TABLE Employees
ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email);
Explanation:
 UQ_Employees_Email is the name of the unique constraint.
 This ensures that all values in the Email column are unique across the Employees table.
Adding a Composite Primary Key
ALTER TABLE OrderDetails
ADD PRIMARY KEY (Order_ID, Product_ID);
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Explanation:
 This sets the combination of Order_ID and Product_ID as the Primary Key, ensuring
each pair is unique.
Practical Examples
a. Example 1: Employee Table with Multiple Candidate Keys
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key
Email VARCHAR(100) UNIQUE, -- Alternate Candidate Key
SSN VARCHAR(11) UNIQUE, -- Another Alternate Candidate Key
Name VARCHAR(100) NOT NULL,
Department VARCHAR(50)
);
Candidate Keys:
 Employee_ID (Primary Key)
 Email (Alternate Key)
 SSN (Alternate Key)
b. Example 2: University Enrollment Table with Composite Candidate Key
CREATE TABLE Enrollments (
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
PRIMARY KEY (Student_ID, Course_ID), -- Composite Primary Key
CONSTRAINT FK_Student FOREIGN KEY (Student_ID) REFERENCES
Students(Student_ID),
CONSTRAINT FK_Course FOREIGN KEY (Course_ID) REFERENCES
Courses(Course_ID)
);
Candidate Key:
 Combination of Student_ID and Course_ID

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Primary Key
o It is the first key used to identify one and only one instance of an entity uniquely. An
entity can contain multiple keys, as we saw in the PERSON table. The key which is
most suitable from those lists becomes a primary key.
o In the EMPLOYEE table, ID can be the primary key since it is unique for each
employee. In the EMPLOYEE table, we can even select License_Number and
Passport_Number as primary keys since they are also unique.
o For each entity, the primary key selection is based on requirements and developers.

There can be more than one candidate key in relation out of which one can be chosen as the
primary key. For Example, STUD_NO, as well as STUD_PHONE, are candidate keys for
relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many
candidate keys).
 It is a unique key.
 It can identify only one tuple (a record) at a time.
 It has no duplicate values, it has unique values.
 It cannot be NULL.
 Primary keys are not necessarily to be a single column; more than one column can also
be a primary key for a table.
Example:
STUDENT table -> Student(STUD_NO, SNAME,
ADDRESS, PHONE) , STUD_NO is a primary key
Table STUDENT

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

A Primary Key is a fundamental concept in Database Management Systems (DBMS) that

plays a crucial role in ensuring data integrity and facilitating efficient data retrieval.
Understanding primary keys is essential for designing robust and reliable databases.
Definition of Primary Key
A Primary Key is a specific choice of a minimal set of attributes (columns) that uniquely
identifies each tuple (row) in a relation (table). It ensures that each record within the table is
unique and can be precisely retrieved or referenced.
Key Characteristics of Primary Keys
a. Uniqueness
 Definition: Each value of the primary key must be unique across all records in the table.
 Purpose: Prevents duplicate records and ensures that each record can be uniquely
identified.
 Example: In an Employees table, Employee_ID can uniquely identify each employee.
b. Non-Nullability
 Definition: Primary key columns must not contain NULL values.
 Purpose: Ensures that every record has a valid identifier.
 Example: In a Students table, Student_ID cannot be NULL because every student must
have an ID.
c. Immutability
 Definition: The values of primary keys should not change over time.
 Purpose: Maintains consistency and prevents integrity issues in related tables.
 Example: Once assigned, an Employee_ID should not be altered.
d. Minimality

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Definition: The primary key should consist of the smallest number of attributes
necessary to maintain uniqueness.
 Purpose: Avoids redundancy and simplifies database design.
 Example: If Employee_ID alone can uniquely identify employees, there's no need to
include additional attributes like Email.
e. Stability
 Definition: Primary keys should remain stable and not be influenced by external
changes.
 Purpose: Ensures long-term reliability of the identifier.
 Example: Using a system-generated User_ID instead of a user’s email, which might
change.
Types of Primary Keys
a. Simple Primary Key
 Definition: A primary key consisting of a single attribute.
 Example: SSN in a Persons table.
b. Composite Primary Key
 Definition: A primary key composed of two or more attributes.
 Use Case: When no single attribute can uniquely identify a record.
 Example: In an OrderDetails table, a combination of Order_ID and Product_ID can
uniquely identify each order line.
c. Natural Primary Key
 Definition: A primary key that is derived from existing data within the table.
 Use Case: When an attribute naturally possesses uniqueness.
 Example: ISBN in a Books table.
Primary Key vs. Other Keys
a. Primary Key vs. Candidate Key
 Primary Key: Chosen from the candidate keys to uniquely identify records.
 Candidate Key: Any set of attributes that can uniquely identify records; there can be
multiple candidate keys in a table.
 Example: In a Users table, both Username and Email can be candidate keys. One is
selected as the primary key.
b. Primary Key vs. Unique Key
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Primary Key:
o Cannot contain NULL values.
o Only one primary key per table.
 Unique Key:
o Can contain NULL values (depending on DBMS).
o Multiple unique keys can exist in a table.
 Example: Employee_ID as a primary key and Email as a unique key in an Employees
table.
c. Primary Key vs. Foreign Key
 Primary Key: Uniquely identifies records within its own table.
 Foreign Key: References the primary key of another table to establish relationships
between tables.
 Example: Department_ID in an Employees table as a foreign key referencing
Department_ID in a Departments table.
5. Importance of Primary Keys
a. Ensures Data Integrity
 Prevents duplicate records and maintains the uniqueness of each record.
b. Facilitates Relationships
 Establishes connections between tables through foreign keys, enabling relational
database functionalities.
c. Enhances Query Performance
 Optimizes data retrieval by providing indexed access to records.
d. Simplifies Data Management
 Aids in updating, deleting, and referencing specific records without ambiguity.
e. Supports Referential Integrity
 Ensures that relationships between tables remain consistent, preventing orphan records.
How to Define a Primary Key
1. Single Attribute Primary Key:
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE,
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Department_ID INT
);
2. Composite Primary Key:
CREATE TABLE OrderDetails (
Order_ID INT,
Product_ID INT,
Quantity INT,
PRIMARY KEY (Order_ID, Product_ID)
);
Examples of Primary Keys
Employee Table

Employee_ID Name Email Department_ID

101 John Smith john@example.com 10

102 Alice Jones alice@example.com 20

103 Bob Brown bob@example.com 10

 Primary Key: Employee_ID

b. Students and Enrollments Tables (Composite Key Example)
Students Table
Student_ID Name Email

201 Emma Wilson emma@example.com

202 Liam Johnson liam@example.com

Enrollments Table

Student_ID Course_ID Enrollment_Date

201 CS101 2024-01-15

201 MATH201 2024-01-16

202 CS101 2024-01-17

 Primary Key: Combination of Student_ID and Course_ID

c. Orders and Products Tables
Orders Table

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Order_ID Order_Date Customer_ID

301 2024-02-10 401

302 2024-02-11 402

Products Table
Product_ID Product_Name Price

501 Laptop 1200

502 Smartphone 800

OrderDetails Table

Order_ID Product_ID Quantity

301 501 2

301 502 1

302 501 1

 Primary Key for OrderDetails: Combination of Order_ID and Product_ID

Common Issues and Solutions with Primary Keys
a. Duplicate Records
 Issue: Inadvertently inserting records with duplicate primary key values.
 Solution: Implement uniqueness constraints and use auto-incrementing keys where
applicable.
b. Null Values in Primary Keys
 Issue: Allowing NULL values in primary key columns violates the primary key
constraints.
 Solution: Enforce NOT NULL constraints on primary key columns.
c. Changing Primary Key Values
 Issue: Modifying primary key values can disrupt relationships and data integrity.
 Solution: Use immutable surrogate keys and avoid using natural, mutable attributes as
primary keys.
d. Composite Keys Complexity
 Issue: Managing composite primary keys can complicate queries and foreign key
relationships.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Solution: Consider using surrogate keys to simplify table relationships.
CREATE TABLE Employees (
Employee_ID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(100),
Email VARCHAR(100),
PRIMARY KEY (Employee_ID),
UNIQUE (Email)
);
Practical Example: Designing a Database with Primary Keys
Scenario: Designing a Library Management System
Entities:
 Books
 Members
 Loans
Tables and Primary Keys:
Books Table
Book_ID Title Author ISBN

1001 The Great Gatsby F. Scott Fitzgerald 9780743273565

1002 1984 George Orwell 9780451524935

 Primary Key: Book_ID (Surrogate Key)
 Unique Constraint: ISBN
Members Table

Member_ID Name Email Join_Date

2001 Alice Johnson alice@example.com 2023-05-21

2002 Bob Smith bob@example.com 2023-06-15

 Primary Key: Member_ID
Loans Table
Loan_ID Book_ID Member_ID Loan_Date Return_Date

3001 1001 2001 2024-01-10 2024-01-20

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Loan_ID Book_ID Member_ID Loan_Date Return_Date

3002 1002 2002 2024-01-15 2024-01-25

 Primary Key: Loan_ID

 Foreign Keys:
o Book_ID references Books(Book_ID)
o Member_ID references Members(Member_ID)
Explanation:
 Books: Each book is uniquely identified by Book_ID. The ISBN is unique but not used
as the primary key to allow flexibility if ISBN needs to change.
 Members: Each library member has a unique Member_ID.
 Loans: Each loan transaction is uniquely identified by Loan_ID, linking books and
members through foreign keys.
Common SQL Commands Involving Primary Keys
a. Creating a Table with Primary Key
CREATE TABLE Departments (
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(100) NOT NULL
);
b. Adding a Primary Key to an Existing Table
ALTER TABLE Employees
ADD PRIMARY KEY (Employee_ID);
c. Dropping a Primary Key Constraint
ALTER TABLE Employees
DROP PRIMARY KEY;
d. Modifying a Primary Key
Changing a primary key typically involves dropping the existing key and adding a new one.
ALTER TABLE Employees
DROP PRIMARY KEY,
ADD PRIMARY KEY (New_Employee_ID);
The Primary Key is a cornerstone of relational database design, ensuring that each record is
uniquely identifiable and that relationships between tables are maintained with integrity. Proper

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
selection and implementation of primary keys are vital for the efficiency, scalability, and
reliability of a database system. By adhering to best practices and understanding the underlying
principles, database designers can create robust structures that support complex applications
and evolving data requirements.
Super Key
Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a
candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the

name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence,
this combination can also be a key.
The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.
The set of attributes that can uniquely identify a tuple is known as Super Key. For Example,
STUD_NO, (STUD_NO, STUD_NAME), etc. A super key is a group of single or multiple
keys that identifies rows in a table. It supports NULL values.
 Adding zero or more attributes to the candidate key generates the super key.
 A candidate key is a super key but vice versa is not true.
 Super Key values may also be NULL.
 Consider the table shown above.
STUD_NO+PHONE is a super key.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems

A Super Key is a fundamental concept in Database Management Systems (DBMS) that plays
a crucial role in ensuring data integrity and facilitating efficient data retrieval. Understanding
super keys is essential for designing robust and reliable databases.
Definition of Super Key
A Super Key is a set of one or more attributes (columns) that can uniquely identify a tuple
(row) in a relation (table). In other words, no two distinct rows in a table can have the same
value for a super key. Super keys are the broadest form of keys in relational databases and
include all possible combinations of attributes that ensure uniqueness.
Key Characteristics of Super Keys
a. Uniqueness
 Definition: Each super key must ensure that no two distinct tuples have the same value
for the key attributes.
 Purpose: Guarantees that every record can be uniquely identified within a table.
 Example: In an Employees table, both Employee_ID alone and the combination of
Employee_ID and Email can serve as super keys because each uniquely identifies an
employee.
b. Minimality (Not Required)
 Definition: Unlike candidate keys, super keys do not need to be minimal. They can
contain additional attributes beyond those necessary for uniqueness.
 Implication: A super key may include unnecessary attributes, making it a broader
concept compared to candidate keys.
 Example: In the Employees table, {Employee_ID, Name} is a super key but not a
candidate key because Name is not required for uniqueness if Employee_ID alone is
sufficient.
c. Inclusivity
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Definition: Every candidate key is a super key, but not every super key is a candidate
key.
 Implication: Super keys encompass all possible unique identifier combinations,
including those that are not minimal.
Super Key vs. Other Keys
a. Super Key vs. Candidate Key
 Super Key:
o May contain additional attributes beyond what is necessary for uniqueness.
o Example: {Employee_ID, Email}, {Employee_ID, Email, Phone_No}
 Candidate Key:
o Must be minimal; no subset of the attributes can uniquely identify a tuple.
o Example: {Employee_ID}, {Email} (if both uniquely identify records)
 Relationship: All candidate keys are super keys, but not all super keys are candidate
keys.
b. Super Key vs. Primary Key
 Super Key:
o General concept encompassing all unique identifier combinations.
 Primary Key:
o A specific candidate key chosen to uniquely identify records in a table.
o Example: If {Employee_ID} and {Email} are both candidate keys, one (e.g.,
{Employee_ID}) is selected as the primary key.
 Relationship: The primary key is one of the candidate keys, which in turn is a super
key.
c. Super Key vs. Foreign Key
 Super Key:
o Used within a table to uniquely identify its records.
 Foreign Key:
o A set of attributes in one table that references the primary key of another table
to establish a relationship.
 Example: Department_ID in an Employees table is a foreign key referencing
Department_ID in a Departments table.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Examples of Super Keys
Example 1: Simple Table with Single-Attribute Super Key
Table: Students

Student_ID Name Email Phone_Number

101 Alice Smith alice@example.com 123-456-7890

102 Bob Johnson bob@example.com 234-567-8901

103 Charlie Lee charlie@example.com 345-678-9012

 Super Keys:
o {Student_ID}
o {Student_ID, Name}
o {Student_ID, Email}
o {Student_ID, Phone_Number}
o {Student_ID, Name, Email}
o ...and so on.
Explanation: While {Student_ID} alone is sufficient to uniquely identify a student, adding
additional attributes like Name or Email still maintains uniqueness, thereby forming super
keys.
Example 2: Table with Multiple Super Keys
Table: Books

ISBN Title Author Publisher

978-3-16-148410-0 The Great Gatsby F. Scott Fitzgerald Scribner

978-0-14-028333-4 1984 George Orwell Penguin Books

978-0-452-28423-4 To Kill a Mockingbird Harper Lee J.B. Lippincott & Co.

 Super Keys:
o {ISBN}
o {ISBN, Title}
o {ISBN, Author}
o {ISBN, Publisher}
o {ISBN, Title, Author}
o {ISBN, Title, Publisher}
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
and so on.
Explanation: Here, ISBN uniquely identifies each book, and any combination that includes
ISBN also ensures uniqueness.
Example 3: Composite Super Key
Table: Enrollments

Student_ID Course_ID Enrollment_Date

101 CS101 2024-01-15

101 MATH201 2024-01-16

102 CS101 2024-01-17

 Super Keys:
o {Student_ID, Course_ID}
o {Student_ID, Course_ID, Enrollment_Date}
Explanation: The combination of Student_ID and Course_ID uniquely identifies each
enrollment record, making it a composite super key. Adding Enrollment_Date still maintains
uniqueness, forming another super key.
Identifying Super Keys in SQL

While SQL does not have a specific syntax to declare a super key directly, super keys are
implicitly defined through primary keys and unique constraints.

a. Using Primary Keys as Super Keys

When you define a primary key, you are effectively declaring a super key.

CREATE TABLE Employees (

Employee_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Another Super Key)
Phone_No VARCHAR(15) UNIQUE -- Another Unique Constraint (Another Super
Key)
);
Explanation:
 Employee_ID is a primary key and thus a super key.
 Email and Phone_No have unique constraints, making them alternate super keys.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
b. Using Unique Constraints to Define Additional Super Keys
CREATE TABLE Students (
Student_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Super Key)
Phone_Number VARCHAR(15) UNIQUE, -- Another Unique Constraint (Super Key)
Name VARCHAR(100) NOT NULL
);
Explanation:
 Student_ID, Email, and Phone_Number each serve as super keys due to their
uniqueness.
c. Composite Super Keys
CREATE TABLE OrderDetails (
Order_ID INT,
Product_ID INT,
Quantity INT,
PRIMARY KEY (Order_ID, Product_ID), -- Composite Primary Key (Super Key)
CONSTRAINT FK_Order FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
CONSTRAINT FK_Product FOREIGN KEY (Product_ID) REFERENCES
Products(Product_ID)
);
Explanation:
 The combination of Order_ID and Product_ID forms a composite primary key, thereby
acting as a composite super key.
Importance of Super Keys in Database Design
a. Ensuring Uniqueness
 Role: Super keys ensure that each record within a table can be uniquely identified,
preventing duplicate entries.
 Benefit: Maintains data integrity and consistency.
b. Facilitating Relationships
 Role: Super keys, especially primary keys, are used to establish relationships between
tables through foreign keys.
Benefit: Enables the creation of relational databases that reflect real-world associations.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
c. Optimizing Query Performance
 Role: Unique indexes created by super keys (primary keys and unique constraints)
improve the speed of data retrieval operations.
 Benefit: Enhances overall database performance and efficiency.
d. Supporting Data Integrity Constraints
 Role: Super keys enforce rules that maintain the validity and accuracy of data within
the database.
 Benefit: Prevents anomalies and inconsistencies that could compromise data quality.
Practical Examples
Example 1: Employee Table with Multiple Super Keys
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Super Key)
SSN VARCHAR(11) UNIQUE, -- Unique Constraint (Super Key)
Name VARCHAR(100) NOT NULL,
Department VARCHAR(50)
);
 Super Keys:
o {Employee_ID}
o {Email}
o {SSN}
o {Employee_ID, Email}
o {Employee_ID, SSN}
o ...and more.
Explanation: Employee_ID, Email, and SSN each uniquely identify an employee, making
them individual super keys. Combinations of these attributes also form super keys, although
they are not minimal and hence not candidate keys.
Example 2: University Enrollment Table with Composite Super Key
CREATE TABLE Enrollments (
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
PRIMARY KEY (Student_ID, Course_ID), -- Composite Primary Key (Super Key)
CONSTRAINT FK_Student FOREIGN KEY (Student_ID) REFERENCES
Students(Student_ID),
CONSTRAINT FK_Course FOREIGN KEY (Course_ID) REFERENCES
Courses(Course_ID)
);

 Super Keys:
o {Student_ID, Course_ID}
o {Student_ID, Course_ID, Enrollment_Date}

Explanation: The combination of Student_ID and Course_ID uniquely identifies each

enrollment record, making it a composite super key. Adding Enrollment_Date still maintains
uniqueness, forming another super key.

Example 3: Books Table with Multiple Super Keys

CREATE TABLE Books (

Book_ID INT PRIMARY KEY, -- Primary Key (Super Key)
ISBN VARCHAR(13) UNIQUE, -- Unique Constraint (Super Key)
Title VARCHAR(255) NOT NULL,
Author VARCHAR(100) NOT NULL,
Publisher VARCHAR(100)
);

 Super Keys:
o {Book_ID}
o {ISBN}
o {Book_ID, ISBN}
o {Book_ID, Title}
o {ISBN, Title}

Explanation: Both Book_ID and ISBN uniquely identify a book, making them individual
super keys. Combinations including these attributes also form super keys.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Foreign Key
A Foreign Key is a critical concept in Database Management Systems (DBMS) that establishes
and enforces a link between the data in two tables. Understanding foreign keys is essential for
maintaining referential integrity, designing robust relational databases, and facilitating
complex data relationships.
Definition of Foreign Key
A Foreign Key is an attribute or a set of attributes in one table that uniquely identifies a row
of another table or the same table. In essence, a foreign key is a reference used to link two
tables together, establishing a relationship between them. This relationship ensures that the
value in the foreign key column(s) corresponds to a valid primary key in the related table
Key Points:
 Referential Integrity: Foreign keys enforce referential integrity by ensuring that
relationships between tables remain consistent.
 Linking Tables: They create a connection between two tables, allowing for relational
data operations such as joins
 Example:
 Consider two tables, Departments and Employees. The Departments table has a primary
key Department_ID, and the Employees table includes a foreign key Department_ID to
link each employee to their respective department
-- Departments Table
CREATE TABLE Departments (
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(100) NOT NULL
);

-- Employees Table
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID)
);
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Importance of Foreign Keys
Foreign keys play a pivotal role in relational databases by ensuring data consistency and
enabling meaningful data relationships. Their importance can be highlighted through the
following points:
a. Maintaining Referential Integrity
 Consistency: Ensures that a foreign key value always points to an existing, valid record
in the related table.
 Prevents Orphan Records: Eliminates the possibility of having records in the child
table that do not correspond to any record in the parent table.
b. Enabling Relational Operations
 Joins: Facilitates SQL join operations, allowing for the retrieval of related data across
multiple tables.
 Data Navigation: Enables navigation through related data, making it easier to query
complex datasets.
c. Supporting Data Normalization
 Eliminating Redundancy: Helps in breaking down data into multiple related tables,
reducing data duplication.
 Improving Data Organization: Enhances the logical structure of the database by
organizing data into related entities.
d. Enforcing Business Rules
 Data Validity: Ensures that only valid data that complies with defined relationships is
entered into the database.
 Integrity Constraints: Supports the implementation of business rules through integrity
constraints.
Characteristics of Foreign Keys
Foreign keys have specific characteristics that define their behavior and role within the
database schema:
a. Attribute Matching
 Data Types: The foreign key and the referenced primary key must have compatible
data types.
 Value Constraints: The values in the foreign key column must either match the
primary key values in the parent table or be NULL (if allowed).
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
b. Nullability
 Optional Relationships: Foreign key columns can accept NULL values, indicating that
the relationship is optional.
 Mandatory Relationships: When NOT NULL constraints are applied, every foreign
key value must correspond to a valid primary key in the parent table.
c. Single vs. Composite Keys
 Single-Attribute Foreign Keys: Involve one column referencing a single primary key.
 Composite Foreign Keys: Involve multiple columns referencing a composite primary
key in the parent table.
d. Cascading Actions
 ON DELETE and ON UPDATE: Define what happens to the foreign key records
when the referenced primary key is deleted or updated.
o CASCADE: Automatically deletes or updates the child records.
o SET NULL: Sets the foreign key to NULL when the parent record is deleted or
updated.
o RESTRICT: Prevents the deletion or update if related child records exist.
o NO ACTION: Similar to RESTRICT, preventing changes that would violate
referential integrity.
e. Constraint Naming
 Naming Conventions: Foreign key constraints often follow naming conventions like
FK_TableName_ReferencedTable
Advantages of Foreign Keys
Implementing foreign keys in a database offers several benefits:
a. Data Integrity
 Referential Integrity: Ensures that relationships between tables remain consistent.
 Prevents Invalid Data: Stops the entry of foreign key values that do not correspond to
any primary key in the parent table.
b. Improved Data Navigation
 Ease of Access: Simplifies querying related data across multiple tables.
 Relational Queries: Facilitates complex queries involving multiple tables through
joins.
c. Enhanced Database Design
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Normalization Support: Aids in organizing data efficiently, reducing redundancy.
 Logical Structure: Creates a clear and logical structure, making the database easier to
understand and maintain.
d. Automatic Enforcement of Business Rules
 Constraint Enforcement: Automatically enforces business rules related to data
relationships without additional application logic.
 Consistency Maintenance: Maintains consistent data states across related tables.
e. Cascading Actions
 Automated Updates and Deletions: Simplifies data management by automating the
propagation of changes to related tables
Disadvantages of Foreign Keys
While foreign keys are beneficial, they also come with certain drawbacks:
a. Performance Overhead
 Slower Operations: Enforcing foreign key constraints can slow down INSERT,
UPDATE, and DELETE operations due to additional checks.
 Indexing Requirements: Efficient foreign key operations often require proper
indexing, which can consume additional storage and maintenance resources.
b. Complexity in Database Design
 Design Constraints: Requires careful planning and understanding of data
relationships, which can complicate database design.
 Migration Challenges: Modifying table structures with foreign keys can be
challenging, especially in large databases.
c. Potential for Deadlocks
 Locking Issues: Improperly managed foreign key constraints can lead to deadlocks,
especially in high-concurrency environments.
d. Limitations with Certain Data Models
 Hierarchical and Non-Relational Models: Foreign keys are inherently relational and
may not be suitable for other data models like hierarchical or graph-based databases.
e. Maintenance Overhead
 Constraint Management: Requires ongoing management to ensure constraints are
correctly maintained, especially when dealing with data migrations or schema changes

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Implementation of Foreign Keys
Implementing foreign keys involves defining constraints in SQL that link columns in one table
to columns in another table. Here's how you can implement foreign keys in SQL, including
examples and best practices
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID)
);
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department_ID INT,
CONSTRAINT FK_Employees_Departments
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID)
);
Adding Foreign Keys to Existing Tables
ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Departments
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID);
Explanation:
 This command adds a foreign key constraint to the Employees table, linking
Department_ID to Departments(Department_ID)
Composite Foreign Keys
CREATE TABLE OrderDetails (
Order_ID INT,
Product_ID INT,
Quantity INT,
PRIMARY KEY (Order_ID, Product_ID),
FOREIGN KEY (Order_ID, Product_ID) REFERENCES Orders(Order_ID,
Product_ID)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
);
Explanation:
 The OrderDetails table has a composite primary key (Order_ID, Product_ID) that also
serves as a composite foreign key referencing the Orders table.
Cascading Actions
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Explanation:
 ON DELETE CASCADE: Automatically deletes employee records if the referenced
department is deleted.
 ON UPDATE CASCADE: Automatically updates the Department_ID in Employees
if the Department_ID in Departments changes
Dropping Foreign Keys
ALTER TABLE Employees
DROP CONSTRAINT FK_Employees_Departments;
Explanation:
 This command removes the FK_Employees_Departments foreign key constraint from
the Employees table.
Practical Examples
Example 1: Departments and Employees
Scenario: Each employee belongs to one department. The Departments table holds department
information, and the Employees table holds employee details.
-- Creating Departments Table
CREATE TABLE Departments (
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(100) NOT NULL
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
);

-- Creating Employees Table with Foreign Key

CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
Explanation:
 Foreign Key: Department_ID in Employees references Department_ID in
Departments.
 ON DELETE SET NULL: If a department is deleted, set Department_ID in
Employees to NULL.
 ON UPDATE CASCADE: If Department_ID in Departments is updated, reflect the
change in Employees.
Example 2: Students and Courses (Many-to-Many Relationship)
Scenario: Students can enroll in multiple courses, and courses can have multiple students. This
requires a junction table Enrollments with foreign keys referencing both Students and Courses.
-- Creating Students Table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL
);

-- Creating Courses Table

CREATE TABLE Courses (
Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(100) NOT NULL,
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Credits INT NOT NULL
);

-- Creating Enrollments Table with Composite Foreign Keys

CREATE TABLE Enrollments (
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Explanation:
 Enrollments Table: Acts as a junction table with a composite primary key
(Student_ID, Course_ID).
 Foreign Keys:
o Student_ID references Students(Student_ID).
o Course_ID references Courses(Course_ID).
 Cascading Actions: Deleting a student or course automatically deletes related
enrollments.
Disadvantages of Foreign Keys in Detail
While foreign keys are essential for maintaining data integrity, they introduce certain
challenges and limitations:
a. Performance Overhead
 Constraint Checks: Every INSERT, UPDATE, or DELETE operation on a table with
foreign keys requires the DBMS to check the integrity constraints, which can slow
down these operations.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Impact on Bulk Operations: Bulk data loading or mass updates can be significantly
slower due to the overhead of enforcing foreign keys.
b. Complexity in Database Design and Maintenance
 Schema Complexity: Foreign keys add layers of complexity to the database schema,
making it more challenging to design and understand.
 Schema Changes: Modifying tables with foreign keys (e.g., changing primary keys or
deleting referenced records) requires careful handling to avoid integrity violations.
c. Potential for Deadlocks
 Concurrency Issues: In high-concurrency environments, foreign keys can contribute
to deadlocks if multiple transactions attempt to modify related tables simultaneously.
 Locking Overhead: Foreign key constraints may lead to increased locking, impacting
the overall performance and scalability of the database.
d. Difficulty in Testing and Development
 Data Dependency: Foreign keys enforce strict data dependencies, making it harder to
test individual components without considering the entire relational context.
 Mock Data Generation: Creating test data requires ensuring that all foreign key
relationships are satisfied, complicating the testing process.
e. Limited Flexibility in Certain Scenarios
 Hierarchical Relationships: Representing hierarchical or recursive relationships can
be cumbersome with traditional foreign key constraints.
 Non-Relational Models: Foreign keys are inherently relational and may not be suitable
for non-relational or semi-structured data models like JSON or XML.
f. Managing Orphan Records
 Orphan Prevention: Foreign keys prevent orphan records (child records without
corresponding parent records), but managing scenarios where parent records are
intentionally deleted can be restrictive.
Practice Questions on Foreign Keys
Question 1: Basic Foreign Key Creation
Scenario:
You have two tables, Authors and Books. Each book is written by one author. Define the
foreign key relationship in the Books table.
Tables:
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Authors
o Author_ID (Primary Key)
o Author_Name
 Books
o Book_ID (Primary Key)
o Title
o Author_ID (Foreign Key)
Task:
Write the SQL statement to create the Books table with the appropriate foreign key.
Answer:
CREATE TABLE Authors (
Author_ID INT PRIMARY KEY,
Author_Name VARCHAR(100) NOT NULL
);

CREATE TABLE Books (

Book_ID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Author_ID INT,
FOREIGN KEY (Author_ID) REFERENCES Authors(Author_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Explanation:
 The Books table includes a foreign key Author_ID that references Author_ID in the
Authors table.
 ON DELETE CASCADE: If an author is deleted, all their books are also deleted.
 ON UPDATE CASCADE: If an author's Author_ID is updated, it cascades to the
Books table.
Super Key
A Super Key is a fundamental concept in Database Management Systems (DBMS) that plays
a crucial role in ensuring data integrity and facilitating efficient data retrieval.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Definition of Super Key
A Super Key is a set of one or more attributes (columns) in a table that can uniquely identify
a tuple (row) in that table. In other words, no two distinct rows can have the same value for a
super key. Super keys are the most general form of keys in relational databases and encompass
all possible combinations of attributes that ensure uniqueness.
Key Points:
 Uniqueness: Ensures that each tuple is unique within the table.
 Inclusivity: Can include additional attributes beyond what is necessary for uniqueness.
 Multiple Super Keys: A table can have multiple super keys, including both minimal
and non-minimal sets of attributes.
Example:
Consider an Employees table:

Employee_ID Email Phone_No Name

101 john@example.com 1234567890 John Smith

102 alice@example.com 9876543210 Alice Jones

103 bob@example.com 5647382910 Bob Brown

Possible Super Keys:

 {Employee_ID}
 {Email}
 {Phone_No}
 {Employee_ID, Email}
 {Employee_ID, Phone_No}
 {Email, Phone_No}
 {Employee_ID, Email, Phone_No}
Importance of Super Keys
Super keys are pivotal in relational database design for several reasons:
a. Ensuring Data Uniqueness
 Data Integrity: Super keys guarantee that each record within a table is unique,
preventing duplicate entries.
 Accurate Data Retrieval: Facilitates precise data retrieval operations by ensuring that
queries return distinct records.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
b. Foundation for Other Keys
 Candidate Keys: Super keys are the broader category from which candidate keys are
derived by removing unnecessary attributes.
 Primary and Alternate Keys: Among candidate keys, one is selected as the primary
key, and others serve as alternate keys.
c. Facilitating Relationships
 Foreign Keys: Super keys (especially candidate and primary keys) are used to establish
relationships between tables through foreign keys.
 Referential Integrity: Maintains consistency across related tables by ensuring that
foreign keys correspond to valid primary keys.
d. Supporting Normalization
 Data Organization: Helps in organizing data into well-structured tables, reducing
redundancy and dependency.
 Normalization Forms: Super keys are integral in achieving higher normal forms,
which aim to eliminate data anomalies.
Characteristics of Super Keys
Super keys possess specific attributes that define their behavior and role within a database
schema:
a. Uniqueness
 Definition: Each super key must uniquely identify every tuple in the table.
 Implication: No two rows can share the same super key value(s).
b. Inclusivity
 Definition: Super keys can contain additional attributes that are not necessary for
uniqueness.
 Implication: They are not required to be minimal; extra attributes can be included
without violating uniqueness.
c. Multiple Super Keys
 Definition: A single table can have multiple super keys.
 Implication: This allows flexibility in identifying records through different
combinations of attributes.
d. Non-Minimality (Not Necessarily Minimal)

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Definition: Super keys may include unnecessary attributes, making them broader than
necessary.
 Implication: They encompass all possible unique identifier combinations, including
those with redundant attributes.
e. Relation to Candidate Keys
 Definition: Every candidate key is a super key, but not every super key is a candidate
key.
 Implication: Candidate keys are the minimal super keys without any unnecessary
attributes.
Advantages of Super Keys
Implementing super keys in a database offers several benefits:
a. Data Integrity and Accuracy
 Uniqueness Enforcement: Prevents duplicate records, ensuring that each entry is
distinct and accurately represented.
 Reliable Data Retrieval: Enhances the accuracy of queries by ensuring that each super
key corresponds to a single, unique record.
b. Flexibility in Database Design
 Multiple Identification Methods: Allows for various attribute combinations to
uniquely identify records, providing flexibility in how data can be accessed and
managed.
 Support for Complex Relationships: Facilitates the establishment of complex
relationships between tables through different super key combinations.
c. Foundation for Normalization
 Redundancy Reduction: Helps in organizing data into separate tables based on unique
identifiers, reducing data duplication.
 Dependency Management: Assists in managing functional dependencies, which are
essential for achieving higher normal forms.
d. Facilitating Referential Integrity
 Establishing Links: Super keys enable the creation of foreign keys, which are crucial
for maintaining referential integrity across related tables.
 Consistency Maintenance: Ensures that references between tables remain consistent,
preventing orphaned records.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
e. Enhanced Query Performance
 Indexed Access: Super keys, especially primary keys, are often indexed, leading to
faster query execution and data retrieval.
 Optimized Joins: Improves the efficiency of join operations by providing clear, unique
links between tables.
Disadvantages of Super Keys
While super keys are beneficial, they also come with certain limitations:
a. Potential for Redundancy
 Non-Minimality: Since super keys can include unnecessary attributes, they may lead
to redundancy in data representation.
 Storage Overhead: Extra attributes in super keys can consume additional storage
space, especially in large tables.
b. Complexity in Database Design
 Multiple Identifiers: Managing numerous super keys can complicate database design
and maintenance.
 Choice of Primary Key: Selecting the most appropriate primary key from multiple
super keys requires careful consideration.
c. Performance Overheads
 Indexing Costs: Creating indexes for multiple super keys can increase storage
requirements and maintenance efforts.
 Slower Operations: Enforcing uniqueness across various super keys may introduce
additional processing overhead during data manipulation operations.
d. Maintenance Challenges
 Schema Changes: Modifying tables with multiple super keys can be more complex,
requiring updates to all relevant key definitions and constraints.
 Constraint Management: Ensuring that all super key constraints are consistently
maintained adds to the administrative burden.
e. Potential for Inconsistencies
 Redundant Keys: Having multiple super keys can sometimes lead to inconsistencies
if not properly managed, especially if some keys are updated or deleted without
reflecting changes across all relevant keys.
Implementation of Super Keys
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Primary Key as a Super Key
The Primary Key is inherently a super key because it uniquely identifies each record in the
table.
Example:
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_No VARCHAR(15) UNIQUE -- Unique Constraint (Another Alternate
Super Key)
);
Explanation:
 Employee_ID is defined as the primary key, making it a super key.
 Email and Phone_No are defined with unique constraints, making them alternate super
keys.
Unique Constraints as Alternate Super Keys
Unique Constraints ensure that all values in a column or a group of columns are unique across
the table, effectively acting as alternate super keys.
Example:
CREATE TABLE Students (
Student_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_Number VARCHAR(15) UNIQUE, -- Unique Constraint (Another Alternate
Super Key)
Name VARCHAR(100) NOT NULL
);
Explanation:
 Student_ID serves as the primary key.
 Email and Phone_Number are unique, making them alternate super keys.
Composite Super Keys

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
When a super key consists of multiple attributes, it is known as a Composite Super Key. These
are typically implemented as Composite Primary Keys or through combined unique
constraints.
Example:
CREATE TABLE OrderDetails (
Order_ID INT,
Product_ID INT,
Quantity INT,
PRIMARY KEY (Order_ID, Product_ID), -- Composite Primary Key (Composite
Super Key)
CONSTRAINT FK_Order FOREIGN KEY (Order_ID) REFERENCES
Orders(Order_ID),
CONSTRAINT FK_Product FOREIGN KEY (Product_ID) REFERENCES
Products(Product_ID)
);
Explanation:
 The combination of Order_ID and Product_ID uniquely identifies each record, forming
a composite super key.
Adding Super Keys to Existing Tables
You can add unique constraints to existing tables to define additional super keys.
Example:
ALTER TABLE Employees
ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email);

ALTER TABLE Employees

ADD CONSTRAINT UQ_Employees_Phone UNIQUE (Phone_No);
Explanation:
 These ALTER TABLE statements add unique constraints to the Email and Phone_No
columns, establishing them as alternate super keys.
Practice Questions on Super Keys
Question 1: Identifying Super Keys
Scenario:
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Consider the following Books table:

ISBN Edition Title Author Publisher

978-3-16-148410-0 1 The Great Gatsby F. Scott Fitzgerald Scribner

978-0-14-028333-4 2 1984 George Orwell Penguin Books

978-0-452-28423-4 1 To Kill a Mockingbird Harper Lee J.B. Lippincott & Co.

Task:
List all possible super keys for the Books table.
Answer:
Possible super keys include:
1. {ISBN}
2. {ISBN, Edition}
3. {ISBN, Title}
4. {ISBN, Author}
5. {ISBN, Publisher}
6. {ISBN, Edition, Title}
7. {ISBN, Edition, Author}
8. {ISBN, Edition, Publisher}
9. {ISBN, Title, Author}
10. {ISBN, Title, Publisher}
11. {ISBN, Author, Publisher}
12. {ISBN, Edition, Title, Author}
13. {ISBN, Edition, Title, Publisher}
14. {ISBN, Edition, Author, Publisher}
15. {ISBN, Title, Author, Publisher}
16. {ISBN, Edition, Title, Author, Publisher}
Explanation:
 {ISBN} alone is sufficient to uniquely identify each book.
 Adding more attributes still maintains uniqueness, thus forming additional super keys.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Question 2: Differentiating Super Keys and Candidate Keys
Scenario:
Given the Employees table:

Employee_ID Email Phone_No Name

101 john@example.com 1234567890 John Smith

102 alice@example.com 9876543210 Alice Jones

103 bob@example.com 5647382910 Bob Brown

Task:
Identify which of the following are super keys and which are candidate keys:
1. {Employee_ID}
2. {Email}
3. {Phone_No}
4. {Employee_ID, Email}
5. {Employee_ID, Phone_No}
6. {Email, Phone_No}
7. {Employee_ID, Email, Phone_No}
Answer:
 Super Keys:
1. {Employee_ID}
2. {Email}
3. {Phone_No}
4. {Employee_ID, Email}
5. {Employee_ID, Phone_No}
6. {Email, Phone_No}
7. {Employee_ID, Email, Phone_No}
 Candidate Keys:
1. {Employee_ID}
2. {Email}
3. {Phone_No}
Explanation:
 All listed sets are super keys because they uniquely identify each record.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 {Employee_ID}, {Email}, and {Phone_No} are also candidate keys as they are
minimal super keys without any unnecessary attributes.
Question 3: Creating a Table with a Composite Super Key
Scenario:
You are designing a CourseEnrollments table where each enrollment is uniquely identified by
a combination of Student_ID and Course_ID.
Task:
Write the SQL statement to create the CourseEnrollments table with a composite primary key
serving as a composite super key.
Answer:
CREATE TABLE CourseEnrollments (
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);
Explanation:
 The combination of Student_ID and Course_ID serves as a composite primary key,
making it a composite super key.
 Foreign keys link to the respective Students and Courses tables
Question 4: Implementing Alternate Super Keys
Scenario:
In the Employees table, besides Employee_ID, both Email and Phone_No can uniquely identify
an employee.
Task:
Modify the SQL statement to include Email and Phone_No as alternate super keys using unique
constraints.
Answer:
sql
Copy code
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_No VARCHAR(15) UNIQUE -- Unique Constraint (Another Alternate Super
Key)
);
Explanation:
 Email and Phone_No are defined with UNIQUE constraints, making them alternate
super keys.
 This ensures that each Email and Phone_No is unique across the table.
Question 5: Identifying Non-Candidate Super Keys
Scenario:
Given the Products table:

Product_ID SKU Serial_No Name

201 SKU12345 SN1001 Widget A

202 SKU67890 SN1002 Widget B

203 SKU54321 SN1003 Widget C

Task:
Determine which of the following sets are super keys but not candidate keys:
1. {Product_ID, SKU}
2. {Product_ID, Serial_No}
3. {SKU, Serial_No}
4. {Product_ID, SKU, Serial_No}
Answer:
All the listed sets are super keys but not candidate keys because they contain unnecessary
attributes beyond the minimal requirement for uniqueness.
 {Product_ID, SKU}
 {Product_ID, Serial_No}
 {SKU, Serial_No}
 {Product_ID, SKU, Serial_No}
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Explanation:
 Each of these sets includes multiple attributes that individually are sufficient to
uniquely identify a product (Product_ID, SKU, or Serial_No).
 Since they are not minimal, they are super keys but not candidate keys.
Question 7: Super Key vs. Primary Key in SQL
Scenario:
You have a Users table with User_ID, Username, and Email, all of which are unique.
Task:
Explain how to implement super keys in SQL and select a primary key.
Answer:
Implementation:
CREATE TABLE Users (
User_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Username VARCHAR(50) UNIQUE, -- Unique Constraint (Alternate Super Key)
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Another Alternate Super
Key)
Password VARCHAR(255) NOT NULL
);
Explanation:
 User_ID is chosen as the primary key, making it a super key.
 Username and Email are defined with UNIQUE constraints, making them alternate
super keys.
Selection of Primary Key:
 Criteria: Stability, minimality, and non-meaningful (to prevent changes).
 Choice: User_ID is preferred over Username and Email because it is typically auto-
generated, ensuring stability and simplicity.
Common Issues and Solutions with Super Keys
a. Redundant Super Keys
 Issue: Including unnecessary attributes in super keys can lead to redundancy.
 Solution: Focus on identifying candidate keys by removing superfluous attributes,
ensuring minimality.
b. Composite Super Keys Complexity
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Issue: Composite super keys (keys with multiple attributes) can complicate queries and
foreign key relationships.
 Solution: Where possible, use single-attribute super keys or surrogate keys to simplify
table relationships and queries.
c. Managing Multiple Super Keys
 Issue: Handling numerous super keys can complicate database design and maintenance.
 Solution: Clearly define and document all super keys, and prioritize candidate keys for
primary key selection.
d. Performance Overheads
 Issue: Indexing multiple super keys can consume additional storage and impact
performance.
 Solution: Index only those super keys that are frequently used in queries or as foreign
keys, balancing performance and storage needs.
e. Ensuring Consistency
 Issue: Maintaining consistency across multiple super keys requires diligent constraint
management.
 Solution: Implement automated checks through database constraints and regular
integrity audits.
Practical Examples: Designing a Database with Super Keys
Example 1: Employees and Departments
Scenario: Each employee belongs to one department. Both Employee_ID and Email uniquely
identify an employee.
Tables and Super Keys:
-- Creating Departments Table
CREATE TABLE Departments (
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(100) NOT NULL
);

-- Creating Employees Table with Super Keys

CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_No VARCHAR(15) UNIQUE, -- Unique Constraint (Another Alternate
Super Key)
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID)
);
Super Keys in Employees Table:
 {Employee_ID}
 {Email}
 {Phone_No}
 {Employee_ID, Email}
 {Employee_ID, Phone_No}
 {Email, Phone_No}
 {Employee_ID, Email, Phone_No}
Explanation:
 Employee_ID, Email, and Phone_No are individual super keys.
 Combinations including these attributes also form super keys.
Example 2: Students and Courses
Scenario: Students can enroll in multiple courses. Each enrollment is uniquely identified by
Student_ID and Course_ID.
Tables and Super Keys:
-- Creating Students Table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_Number VARCHAR(15) UNIQUE -- Unique Constraint (Another Alternate
Super Key)
);

-- Creating Courses Table

Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
CREATE TABLE Courses (
Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(100) NOT NULL,
Credits INT NOT NULL
);

-- Creating Enrollments Table with Composite Super Key

CREATE TABLE Enrollments (
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
PRIMARY KEY (Student_ID, Course_ID), -- Composite Primary Key (Composite
Super Key)
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);
Super Keys in Enrollments Table:
 {Student_ID, Course_ID}
 {Student_ID, Course_ID, Enrollment_Date}
Explanation:
 The combination of Student_ID and Course_ID uniquely identifies each enrollment
record.
 Adding Enrollment_Date still maintains uniqueness, forming another super key.
Creating a Table with Multiple Super Keys
Scenario:
Design a LibraryMembers table where each member is uniquely identified by either
Membership_ID or Email.
Task:
Write the SQL statement to create the LibraryMembers table with appropriate super keys.
Answer:
CREATE TABLE LibraryMembers (
Membership_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_No VARCHAR(15)
);
Explanation:
 Membership_ID is the primary key, making it a super key.
 Email has a unique constraint, serving as an alternate super key.
Question 12: Evaluating Super Keys in a Table with Redundant Attributes
Scenario:
Given the Vehicles table:

VIN License_Plate Owner_ID Color

1HGCM82633A004352 ABC123 501 Red

1HGCM82633A004353 XYZ789 502 Blue

1HGCM82633A004354 DEF456 503 Green

Task:
Identify whether {VIN, License_Plate} is a super key and if it is a candidate key.
Answer:
 Super Key: {VIN, License_Plate} is a super key because the combination uniquely
identifies each vehicle.
 Candidate Key: It is not a candidate key if VIN alone uniquely identifies each vehicle.
Explanation:
 Minimality: Since VIN alone is sufficient to uniquely identify a vehicle, adding
License_Plate makes the key non-minimal, hence not a candidate key.
Summary
 Super Key: A set of one or more attributes that can uniquely identify a tuple in a table.
It is the most general form of a unique identifier in a database.
 Candidate Key: A minimal super key with no unnecessary attributes. Every candidate
key is a super key, but not every super key is a candidate key.
 Primary Key: A chosen candidate key used to uniquely identify records within a table.
It is enforced by primary key constraints and typically indexed for performance.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Unique Constraint: Used to enforce alternate super keys by ensuring that specified
columns contain unique values across the table.
 Characteristics:
o Uniqueness: Each super key ensures uniqueness of records.
o Minimality: Candidate keys are minimal super keys.
o Inclusivity: Super keys can include additional attributes beyond what is
necessary for uniqueness.
 Advantages:
o Ensures data integrity and accuracy.
o Provides flexibility in data identification.
o Facilitates normalization and efficient query performance.
 Disadvantages:
o Potential for redundancy and storage overhead.
o Increased complexity in database design and maintenance.
o Performance impacts due to indexing multiple super keys.
 Implementation:
o Primarily through defining primary keys and unique constraints in SQL.
o Composite super keys involve multiple attributes and are implemented as
composite primary keys or combined unique constraints.
Composite key
A Composite Key is a fundamental concept in Database Management Systems (DBMS) that
plays a crucial role in uniquely identifying records within a table.
Definition of Composite Key
A Composite Key (also known as a Compound Key) is a combination of two or more columns
(attributes) in a table that together uniquely identify a single record (row) in that table. Each
individual column within the composite key may not be unique by itself, but when combined,
they ensure the uniqueness of each record.
Key Points:
 Multiple Attributes: Consists of two or more columns.
 Uniqueness: The combination of values in these columns must be unique across all
records.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Minimality: Typically, the composite key is minimal, meaning that removing any
attribute from the key would destroy its uniqueness.
Example:
Consider a CourseEnrollments table where each student can enroll in multiple courses, and
each course can have multiple students. Neither Student_ID nor Course_ID alone can uniquely
identify a record, but their combination can.

Student_ID Course_ID Enrollment_Date

101 CS101 2024-01-15

101 MATH201 2024-01-16

102 CS101 2024-01-17

In this table:
 Composite Key: {Student_ID, Course_ID}
 Explanation: While Student_ID 101 appears multiple times and Course_ID CS101
appears multiple times, the combination {101, CS101}, {101, MATH201}, and {102,
CS101} are all unique.
Importance:
1. Unique identification: Ensures that each tuple is uniquely identifiable.
2. Improved data integrity: Prevents duplicate rows and ensures data consistency.
3. Efficient data retrieval: Supports efficient querying and indexing.
Advantages:
1. Improved data integrity
2. Eliminates data redundancy
3. Enhances query performance
4. Supports data normalization
5. Flexible data modeling
Characteristics:
1. Uniqueness: Composite key values must be unique for each tuple.
2. Minimality: A composite key should have the minimum number of attributes necessary.
3. Non-nullability: Composite key attributes should not be null.
4. Orthogonality: Each attribute should provide unique information.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems

Disadvantages:
1. Complexity: Managing composite keys can be challenging.
2. Performance overhead: Maintaining composite keys can impact database performance.
3. Indexing challenges: Indexing composite keys can be complex.
Composite Key Types:
1. Simple Composite Key: Two or more attributes form the primary key.
Example: OrderID + CustomerID
2. Compound Composite Key: Combination of simple and composite keys.
Example: OrderID + (CustomerID + Address)
3. Nested Composite Key: Composite key with another composite key as an attribute.
Example: (OrderID + CustomerID) + (ProductID + Quantity)
4. Overlapping Composite Key: Two or more composite keys share common attributes.
Example: (OrderID + CustomerID) and (CustomerID + Address)
5. Non-Overlapping Composite Key: Composite keys with no shared attributes.
Example: (OrderID + CustomerID) and (ProductID + Quantity)
Design Considerations:
1. Choose attributes that ensure uniqueness.
2. Minimize the number of attributes.
3. Avoid redundant or null values.
4. Consider indexing strategies.
Key Differences:
- Composite Key vs. Primary Key: A primary key can be simple or composite.
- Composite Key vs. Super Key: A composite key is a type of super key.
- Composite Key vs. Foreign Key: A foreign key references the primary key of another table.
a. Defining Composite Primary Keys During Table Creation
CREATE TABLE CourseEnrollments (
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);
Explanation:
 PRIMARY KEY (Student_ID, Course_ID): Defines a composite primary key
comprising Student_ID and Course_ID.
 Foreign Keys: Establishes referential integrity by linking to the Students and Courses
tables.
b. Adding Composite Keys to Existing Tables
ALTER TABLE OrderDetails
ADD PRIMARY KEY (Order_ID, Product_ID);
Explanation:
 ADD PRIMARY KEY (Order_ID, Product_ID): Establishes a composite primary key
using Order_ID and Product_ID.
Practice Questions on Composite Keys
Question 1: Identifying Composite Keys
Scenario:
Consider the following Enrollments table:

Student_ID Course_ID Enrollment_Date

101 CS101 2024-01-15

101 MATH201 2024-01-16

102 CS101 2024-01-17

Task:
Determine whether {Student_ID, Course_ID} is a composite key and explain why.
Answer:
 Composite Key: {Student_ID, Course_ID} is a composite key because the
combination of Student_ID and Course_ID uniquely identifies each record in the
Enrollments table.
 Explanation:
o Uniqueness: Each combination of Student_ID and Course_ID is unique.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
o Minimality: Neither Student_ID nor Course_ID alone can uniquely identify a
record, making the combination minimal and necessary for uniqueness.
Question 2: Creating a Table with a Composite Primary Key
Scenario:
creating a ProjectAssignments table where each assignment is uniquely identified by the
combination of Employee_ID and Project_ID.
Task:
Write the SQL statement to create the ProjectAssignments table with a composite primary key.
Answer:
CREATE TABLE ProjectAssignments (
Employee_ID INT,
Project_ID INT,
Assignment_Date DATE,
PRIMARY KEY (Employee_ID, Project_ID),
FOREIGN KEY (Employee_ID) REFERENCES Employees(Employee_ID),
FOREIGN KEY (Project_ID) REFERENCES Projects(Project_ID)
);
Explanation:
 PRIMARY KEY (Employee_ID, Project_ID): Defines a composite primary key
using Employee_ID and Project_ID.
 Foreign Keys: Establishes referential integrity by linking to the Employees and
Projects tables.
Question 3: Understanding Minimality in Composite Keys
Scenario:
In a BookAuthors table, each record is uniquely identified by Book_ID, Author_ID, and
Contribution_Type.
Task:
Explain whether {Book_ID, Author_ID, Contribution_Type} is a candidate key.
Answer:
 Candidate Key: {Book_ID, Author_ID, Contribution_Type} is a candidate key if:
o The combination of Book_ID, Author_ID, and Contribution_Type uniquely
identifies each record.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
o No subset of these attributes ({Book_ID, Author_ID}, {Book_ID,
Contribution_Type}, {Author_ID, Contribution_Type}) can uniquely identify
a record.
 Explanation:
o Minimality: If any one of the attributes can be removed without losing
uniqueness, the remaining set would still uniquely identify records, making the
original set non-minimal and thus not a candidate key.
o Assumption: If all three attributes are necessary for uniqueness, then
{Book_ID, Author_ID, Contribution_Type} is a minimal super key and
qualifies as a candidate key.
Practical Examples: Designing a Database with Composite Keys
Example 1: University Course Enrollment
Scenario: Each student can enroll in multiple courses, and each course can have multiple
students. Each enrollment is uniquely identified by the combination of Student_ID and
Course_ID.
Tables and Composite Keys:
-- Creating Students Table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone_Number VARCHAR(15) UNIQUE
);

-- Creating Courses Table

CREATE TABLE Courses (
Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(100) NOT NULL,
Credits INT NOT NULL
);

-- Creating Enrollments Table with Composite Primary Key

Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
CREATE TABLE Enrollments (
Student_ID INT NOT NULL,
Course_ID INT NOT NULL,
Enrollment_Date DATE NOT NULL,
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);
Explanation:
 Enrollments Table:
o Composite Primary Key: {Student_ID, Course_ID} ensures each enrollment
is unique.
o Foreign Keys: Links to Students and Courses tables to maintain referential
integrity.
Example 3: Library Book Holdings
Scenario: A library can hold multiple copies of a book, and each copy is uniquely identified
by Book_ID and Copy_Number.
Tables and Composite Keys:
-- Creating Books Table
CREATE TABLE Books (
Book_ID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Author VARCHAR(100) NOT NULL,
ISBN VARCHAR(13) UNIQUE
);

-- Creating BookCopies Table with Composite Primary Key

CREATE TABLE BookCopies (
Book_ID INT NOT NULL,
Copy_Number INT NOT NULL,
Acquisition_Date DATE,
PRIMARY KEY (Book_ID, Copy_Number),
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
FOREIGN KEY (Book_ID) REFERENCES Books(Book_ID)
);
Explanation:
 BookCopies Table:
o Composite Primary Key: {Book_ID, Copy_Number} uniquely identifies each
copy of a book.
o Foreign Key: Links to the Books table to maintain referential integrity
Adding a Composite Primary Key to an Existing Table
ALTER TABLE OrderDetails
ADD PRIMARY KEY (Order_ID, Product_ID);
Dropping a Composite Primary Key
ALTER TABLE Enrollments
DROP PRIMARY KEY;
Defining Composite Foreign Keys
CREATE TABLE CourseAssignments (
Student_ID INT NOT NULL,
Course_ID INT NOT NULL,
Assignment_Date DATE NOT NULL,
FOREIGN KEY (Student_ID, Course_ID) REFERENCES Enrollments(Student_ID,
Course_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Explanation:
 FOREIGN KEY (Student_ID, Course_ID): References the composite primary key
in the Enrollments table.
 Cascading Actions: Defines behavior on deletion or updating of referenced records.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Alternate Key
Definition of Alternate Key
An Alternate Key is a candidate key that is not selected as the primary key in a table. In
essence, while a table can have multiple candidate keys, the one chosen as the primary key is
the primary means of uniquely identifying records. The remaining candidate keys are termed
alternate keys. These keys also possess the ability to uniquely identify records but are not used
as the main identifier.
Key Points:
 Candidate Key: A minimal set of attributes that can uniquely identify a record.
 Primary Key: The candidate key selected to uniquely identify records in a table.
 Alternate Key: Other candidate keys not chosen as the primary key.
Example:
Consider an Employees table:

Employee_ID Email Phone_No Name

101 john@example.com 1234567890 John Smith

102 alice@example.com 9876543210 Alice Jones

103 bob@example.com 5647382910 Bob Brown

In this table:
 Candidate Keys: {Employee_ID}, {Email}, {Phone_No}
 Primary Key: {Employee_ID}
 Alternate Keys: {Email}, {Phone_No}
Importance of Alternate Keys
Alternate keys hold significant importance in relational database design for several reasons:
a. Ensuring Data Uniqueness
 Data Integrity: Alternate keys enforce additional layers of uniqueness beyond the
primary key, preventing duplicate records based on different attributes.
 Flexible Identification: They provide alternative ways to identify records, enhancing
flexibility in data retrieval and manipulation.
b. Supporting Database Normalization
 Normalization Forms: Alternate keys assist in achieving higher normal forms by
ensuring that data dependencies are correctly managed.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Eliminating Redundancy: By identifying multiple unique identifiers, alternate keys
help in organizing data efficiently, reducing redundancy.
c. Facilitating Referential Integrity
 Foreign Keys: Alternate keys can be used as targets for foreign key relationships,
maintaining consistent and valid references between tables.
 Data Consistency: They ensure that relationships between tables remain intact,
preventing orphaned records and maintaining data consistency.
d. Enhancing Query Performance
 Indexing: Alternate keys are often indexed, which can significantly improve the
performance of queries that filter or join based on these keys.
 Optimized Searches: They provide additional pathways for efficient data retrieval,
especially in large datasets.

3. Characteristics of Alternate Keys

Alternate keys possess specific attributes that define their behavior and role within a database
schema:
a. Uniqueness
 Definition: Each alternate key must ensure that every record in the table is unique based
on its attribute(s).
 Implication: No two records can share the same value(s) for an alternate key.
b. Minimality
 Definition: Alternate keys are minimal, meaning that they contain only the necessary
attributes required to ensure uniqueness.
 Implication: No subset of an alternate key can uniquely identify records.
c. Non-Nullability
 Definition: Attributes comprising an alternate key must not contain NULL values.
 Implication: Every record must have valid values for alternate key attributes, ensuring
that they can reliably identify records.
d. Distinct from Primary Key
 Definition: Alternate keys are distinct from the primary key; they represent other
unique identifiers within the table.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Implication: While the primary key is the main identifier, alternate keys offer
additional means of identification.
e. Potential for Multiple Alternate Keys
 Definition: A table can have multiple alternate keys, each representing a different
unique identifier.
 Implication: This provides flexibility in how records can be uniquely identified and
accessed.
4. Advantages of Alternate Keys
Implementing alternate keys in a database offers several benefits:
a. Enhanced Data Integrity
 Multiple Layers of Uniqueness: Alternate keys provide additional constraints that
ensure the uniqueness of records beyond the primary key.
 Prevention of Duplicate Records: They help prevent the insertion of duplicate records
based on different attributes, maintaining data integrity.
b. Flexible Data Retrieval
 Multiple Identification Methods: Alternate keys allow records to be accessed and
identified through various attributes, enhancing query flexibility.
 Improved Search Capabilities: Users can retrieve records using any unique attribute,
not limited to the primary key.
c. Support for Complex Relationships
 Foreign Key Relationships: Alternate keys can be referenced by foreign keys in other
tables, facilitating complex relational mappings.
 Referential Integrity: They help maintain consistent and valid references across
related tables.
d. Optimized Performance
 Indexed Access: Alternate keys are typically indexed, leading to faster query execution
for operations involving these keys.
 Efficient Joins: They improve the performance of join operations by providing
additional pathways for linking tables.
e. Aiding Database Normalization
 Higher Normal Forms: Alternate keys support the normalization process by ensuring
that data dependencies are properly managed.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Reduction of Redundancy: They help organize data into logically related tables,
minimizing data duplication.
5. Disadvantages of Alternate Keys
While alternate keys offer numerous benefits, they also come with certain drawbacks:
a. Increased Complexity
 Schema Complexity: Introducing multiple alternate keys can make the database
schema more complex and harder to manage.
 Maintenance Challenges: Managing and maintaining several unique constraints can
be more demanding, especially in large databases.
b. Performance Overheads
 Indexing Costs: Each alternate key typically requires an index, which consumes
additional storage space and can slow down write operations (INSERT, UPDATE,
DELETE).
 Slower Operations: Enforcing uniqueness across various alternate keys may introduce
additional processing overhead during data manipulation operations.
c. Maintenance Challenges
 Schema Changes: Modifying tables with multiple alternate keys can be more complex,
requiring updates to all relevant key definitions and constraints.
 Constraint Management: Ensuring that all alternate key constraints are consistently
maintained adds to the administrative burden.
d. Potential for Inconsistencies
 Redundant Keys: Having multiple alternate keys can sometimes lead to
inconsistencies if not properly managed, especially if some keys are updated or deleted
without reflecting changes across all relevant keys.
e. Storage Overhead
 Additional Storage: Unique constraints and indexes for alternate keys consume extra
storage space, which can be significant in large tables.
Primary Key as a Super Key
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Phone_No VARCHAR(15) UNIQUE -- Unique Constraint (Another Alternate Super
Key)
);
Explanation:
 Employee_ID is defined as the primary key, making it a super key.
 Email and Phone_No are defined with unique constraints, making them alternate super
keys.
Defining Alternate Keys Using Unique Constraints
CREATE TABLE Students (
Student_ID INT PRIMARY KEY, -- Primary Key (Super Key)
Email VARCHAR(100) UNIQUE, -- Unique Constraint (Alternate Super Key)
Phone_Number VARCHAR(15) UNIQUE, -- Unique Constraint (Another Alternate Super
Key)
Name VARCHAR(100) NOT NULL
);
Explanation:
 Student_ID serves as the primary key.
 Email and Phone_Number are defined with unique constraints, making them alternate
super keys.
Adding Alternate Keys to Existing Tables
ALTER TABLE Employees
ADD CONSTRAINT Employees_Email UNIQUE (Email);

ALTER TABLE Employees

ADD CONSTRAINT Employees_Phone UNIQUE (Phone_No);
Explanation:
 These ALTER TABLE statements add unique constraints to the Email and Phone_No
columns, establishing them as alternate super keys.
Practice Questions on Alternate Keys
Question 1: Identifying Alternate Keys
Scenario:
Consider the following Books table:
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
ISBN Edition Title Author Publisher

978-3-16-148410-0 1 The Great Gatsby F. Scott Fitzgerald Scribner

978-0-14-028333-4 2 1984 George Orwell Penguin Books

978-0-452-28423-4 1 To Kill a Mockingbird Harper Lee J.B. Lippincott & Co.

Task:
Identify all possible alternate keys for the Books table, assuming ISBN is chosen as the primary
key.
Answer:
Given that ISBN is the primary key, we need to identify other candidate keys that can uniquely
identify records.
 Candidate Keys: {ISBN}, {Edition, Title}, {ISBN, Edition, Title} (but these are not
minimal)
 Alternate Keys: Since ISBN is the primary key, the alternate keys could be:
o If Edition and Title together are unique, then {Edition, Title} is an alternate key.
o If no other combination of columns uniquely identifies a book, then there may
be no alternate keys beyond ISBN.
Explanation:
In this case, {Edition, Title} can uniquely identify a book if each title can have multiple editions
but no two books share the same title and edition combination.
Question 2: Differentiating Alternate Keys from Primary Keys
Scenario:
Given the Employees table:

Employee_ID Email Phone_No Name

101 john@example.com 1234567890 John Smith

102 alice@example.com 9876543210 Alice Jones

103 bob@example.com 5647382910 Bob Brown

Task:

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Identify which of the following are alternate keys:
1. {Email}
2. {Phone_No}
3. {Name}
4. {Email, Phone_No}
Answer:
 Alternate Keys:
1. {Email}
2. {Phone_No}
 Not Alternate Keys: 3. {Name} (Assuming Name is not unique) 4. {Email,
Phone_No} (This is a super key but not a minimal candidate key)
Explanation:
 {Email} and {Phone_No} are alternate keys as they are candidate keys not chosen as
the primary key.
 {Name} is not an alternate key unless names are unique.
 {Email, Phone_No} is a super key but not a candidate key since both {Email} and
{Phone_No} are already unique.

Unique Key
Definition:
A Unique Key is a database constraint that ensures that all the values in a specific column (or
group of columns) are unique across the rows in a table. It enforces the uniqueness of the data
and prevents duplication. A table can have multiple unique keys, but only one primary key.
Importance of Unique Key:
 Data Integrity: Ensures that each record in the table is unique and prevents the
insertion of duplicate records.
 Efficient Search: Improves the efficiency of searches and queries, especially when
combined with indexing.
 Ensures Uniqueness: It provides a mechanism to guarantee that certain fields within a
table contain distinct values, which is essential for identifying records uniquely without
redundancy.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
 Relationship Between Tables: Plays a key role in maintaining relationships between
different tables in a database, particularly in the context of foreign keys.
Characteristics of a Unique Key:
1. Uniqueness: Each value in the column(s) defined by the unique key must be distinct.
2. Allows NULL Values: Unique keys can contain one NULL value, but no two NULLs.
3. Multiple Unique Keys: A table can have more than one unique key.
4. Non-Primary Constraint: A unique key is not the primary key of the table, but it can
be used in combination with the primary key for identifying records uniquely.
5. Indexing: Unique keys are automatically indexed in most databases, ensuring fast
lookup.
Advantages of a Unique Key:
1. Ensures Uniqueness: Guarantees that no duplicate data is entered in the specified
column(s).
2. Improves Query Performance: By ensuring the uniqueness of values, it improves the
performance of queries, especially those involving joins.
3. Data Consistency: Promotes consistency across data, particularly in complex systems
with multiple tables.
4. Supports Relational Integrity: Helps in defining relationships between tables (foreign
key referencing unique key columns).
Disadvantages of a Unique Key:
1. Limited to One NULL Value: A unique key allows only one NULL value, which may
limit flexibility in some situations.
2. Slower Insertions/Updates: Enforcing uniqueness requires additional checks during
data insertions and updates, which can slow down these operations.
3. Complex Maintenance: If a table has multiple unique keys, it may become more
complex to manage, especially when altering the table structure.
Implementation of Unique Key:
Creating a Unique Key During Table Creation:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE,
PhoneNumber VARCHAR(15) UNIQUE
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
);
Adding a Unique Key to an Existing Table:
ALTER TABLE Employee
ADD CONSTRAINT unique_email UNIQUE (Email);

Key Type Description Example

A unique key that identifies each In a table of employees, the EmployeeID
Primary Key record in a table. Cannot have can be the primary key as it uniquely
NULL values. identifies each employee.
A minimal set of attributes that
In a table of students, both StudentID and
Candidate can uniquely identify a record in
EmailID can be candidate keys if both
Key a table. A table can have
uniquely identify a student.
multiple candidate keys.
A set of one or more attributes In a table of products, the combination of
Super Key that can uniquely identify a ProductID and Category can be a super key if
record in a table. they together uniquely identify a product.
A key used to link two tables
In a table Orders, CustomerID can be a
together. It is a field in one table
Foreign Key foreign key referencing CustomerID in the
that refers to the primary key of
Customers table.
another table.
In a Students table, if StudentID is the
A candidate key that is not the
Alternate Key primary key, then EmailID can be an
primary key.
alternate key.
A key that consists of two or In a CourseRegistrations table, StudentID and
Composite
more attributes that together CourseID together form a composite key to
Key
uniquely identify a record. uniquely identify each registration.
Similar to a primary key, but In a Users table, Email can be a unique key
allows NULL values and ensures to ensure no two users share the same
Unique Key
that all values in a column are email address, but it can still allow one
unique. NULL value.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Joins
In Database Management Systems (DBMS), joins are fundamental operations that allow you
to retrieve related data spread across multiple tables. By combining rows from two or more
tables based on a related column, joins enable complex queries that can extract meaningful
information from relational databases.
Why Joins Are Important
 Data Normalization: Databases are often normalized to reduce redundancy. This
means related data is stored in separate tables. Joins are essential to reconstruct and
utilize this data effectively.
 Efficiency: Instead of storing all data in a single table (which can lead to large,
unwieldy tables), normalization and joins enable more efficient data management and
retrieval.
 Flexibility: Joins provide the flexibility to query data in various ways, accommodating
diverse analytical and reporting needs.
Types of Joins in DBMS
There are several types of joins, each serving a specific purpose. Below is an in-depth
exploration of each join type, complete with definitions, use cases, and SQL examples.
1. INNER JOIN
Definition:
An INNER JOIN retrieves records that have matching values in both tables involved in the
join. It returns only the rows where there is a match based on the specified condition.
Use Case:
When you need to fetch records that exist in both tables, such as employees who are assigned
to departments.
Example Tables:
 Employees
EmployeeID Name DeptID

1 Alice 10

2 Bob 20

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
EmployeeID Name DeptID

3 Charlie 30

4 David NULL

 Departments
DeptID DeptName

10 HR

20 Engineering

40 Marketing

SQL Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;
Result:

Name DeptName

Alice HR

Bob Engineering

Explanation:
Only Alice and Bob have matching DeptID values in both tables. Charlie has a DeptID of 30,
which doesn't exist in the Departments table, and David has a NULL DeptID. Thus, only Alice
and Bob are returned.
2. LEFT (OUTER) JOIN
Definition:
A LEFT JOIN returns all records from the left table and the matched records from the right
table. If there is no match, the result is NULL on the right side.
Use Case:
When you need all records from the left table regardless of whether there's a matching record
in the right table, such as listing all employees and their departments, including those without
a department.
SQL Example:
SELECT Employees.Name, Departments.DeptName
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;
Result:

Name DeptName

Alice HR

Bob Engineering

Charlie NULL

David NULL

Explanation:
All employees are listed. Alice and Bob have matching departments, while Charlie and David
do not, resulting in NULL for their DeptName.
3. RIGHT (OUTER) JOIN
Definition:
A RIGHT JOIN returns all records from the right table and the matched records from the left
table. If there is no match, the result is NULL on the left side.
Use Case:
When you need all records from the right table regardless of whether there's a matching
record in the left table, such as listing all departments and their employees, including
departments with no employees.
SQL Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
RIGHT JOIN Departments ON Employees.DeptID = Departments.DeptID;
Result:

Name DeptName

Alice HR

Bob Engineering

NULL Marketing

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Explanation:
All departments are listed. HR and Engineering have employees (Alice and Bob), while
Marketing has no employees, resulting in NULL for Name.
4. FULL (OUTER) JOIN
Definition:
A FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. It returns all
records when there is a match in either left or right table. Rows without a match in one of the
tables will have NULL values for the missing side.
Use Case:
When you need a complete view of all records from both tables, whether they match or not,
such as a comprehensive list of employees and departments, including employees without
departments and departments without employees.
SQL Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;
Result:

Name DeptName

Alice HR

Bob Engineering

Charlie NULL

David NULL

NULL Marketing

Explanation:
All employees and all departments are listed. Alice and Bob are matched, Charlie and David
have no departments, and Marketing has no employees.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
5. CROSS JOIN
Definition:
A CROSS JOIN returns the Cartesian product of the two tables, meaning every row from the
first table is paired with every row from the second table.
Use Case:
When you need all possible combinations of records from two tables, such as pairing each
employee with every department for potential assignment scenarios.
SQL Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
CROSS JOIN Departments;
Result:
Name DeptName

Alice HR

Alice Engineering

Alice Marketing

Bob HR

Bob Engineering

Bob Marketing

Charlie HR

Charlie Engineering

Charlie Marketing

David HR

David Engineering

David Marketing

Explanation:
Each of the four employees is paired with each of the three departments, resulting in 12
combinations.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
6. SELF JOIN
Definition:
A SELF JOIN is a join where a table is joined with itself. It is useful for querying hierarchical
data or comparing rows within the same table.
Use Case:
When you need to compare records within the same table, such as finding employees and
their managers from the Employees table.
Example Table:
 Employees

EmployeeID Name ManagerID

1 Alice 3

2 Bob 3

3 Charlie NULL

4 David 2

SQL Example:
SELECT E.Name AS Employee, M.Name AS Manager
FROM Employees E
LEFT JOIN Employees M ON E.ManagerID = M.EmployeeID;
Result:
Employee Manager

Alice Charlie

Bob Charlie

Charlie NULL

David Bob

Explanation:
Each employee is paired with their manager. Charlie has no manager (NULL), while David's
manager is Bob.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
7. NATURAL JOIN
Definition:
A NATURAL JOIN automatically joins tables based on columns with the same name and
compatible data types in both tables. It eliminates the need to specify the join condition
explicitly.
Use Case:
When tables share common column names and you want to join them without specifying the
join condition, making queries more concise.
Cons:
Use with care, as unintended columns with the same name can lead to unexpected results.
Example Tables:
 Students
StudentID Name ClassID

1 John 101

2 Emma 102

3 Liam 103
 Classes

ClassID ClassName

101 Mathematics

102 Science

104 History

SQL Example:
SELECT Students.Name, Classes.ClassName
FROM Students
NATURAL JOIN Classes;
Result:
Name ClassName

John Mathematics

Emma Science

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Explanation:
The NATURAL JOIN automatically uses ClassID as the common column. Liam is not
included because his ClassID (103) doesn't exist in Classes, and ClassID 104 (History) has no
matching student.
1. THETA JOIN
 Definition: A Theta Join is a general type of join that allows the use of comparison
operators other than equality (=) to combine rows from two tables based on a specified
condition.
 Comparison Operators: These can include <, >, <=, >=, <>, and =.
 Use Cases: Useful when the relationship between the tables isn't based solely on
equality. For example, finding all pairs of records where a value in one table is greater
than a value in another table.
 Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
JOIN Departments ON Employees.Salary > Departments.Budget;
In this example, the join condition uses the > (greater than) operator, making it a Theta Join.

Practical Example with Sample Data

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Tables:
 Customers
CustomerID CustomerName Country

1 Alice USA

2 Bob Canada

3 Charlie USA

4 David UK
 Orders

OrderID CustomerID OrderDate

101 1 2024-01-15

102 2 2024-02-20

103 1 2024-03-05

104 5 2024-04-10

Scenario 1: INNER JOIN

Objective: List all customers with their orders.
SQL Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
CustomerName OrderID OrderDate

Alice 101 2024-01-15

Alice 103 2024-03-05

Bob 102 2024-02-20

Explanation:
Only customers with matching orders are listed. David has no orders, and OrderID 104 has no
matching customer.
Scenario 2: LEFT JOIN
Objective: List all customers and their orders, including customers without orders.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
SQL Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
CustomerName OrderID OrderDate

Alice 101 2024-01-15

Alice 103 2024-03-05

Bob 102 2024-02-20

Charlie NULL NULL

David NULL NULL

Explanation:
All customers are listed. Charlie and David have no orders, resulting in NULL values.
Scenario 3: RIGHT JOIN
Objective: List all orders and the corresponding customers, including orders without valid
customers.
SQL Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
CustomerName OrderID OrderDate

Alice 101 2024-01-15

Bob 102 2024-02-20

Alice 103 2024-03-05

NULL 104 2024-04-10

Explanation:
All orders are listed. OrderID 104 has no matching customer, resulting in NULL for
CustomerName.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Scenario 4: FULL OUTER JOIN
Objective: List all customers and all orders, matching where possible.
SQL Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
CustomerName OrderID OrderDate

Alice 101 2024-01-15

Alice 103 2024-03-05

Bob 102 2024-02-20

Charlie NULL NULL

David NULL NULL

NULL 104 2024-04-10

Explanation:
All customers and all orders are listed. Unmatched records have NULL values.
Integrity constraints. Referential integrity, Intension and Extension
1. Integrity Constraints
Integrity constraints are rules that ensure the accuracy and consistency of data in a database.
These constraints enforce the correctness of the data being entered and maintained in the
database. There are several types of integrity constraints:
 Domain Constraints: Specify that all values in a column must belong to a predefined
domain (e.g., age must be a positive integer).
 Entity Integrity: Ensures that the primary key of a table is unique and not null,
meaning each entity (row) is identifiable.
 Referential Integrity: Ensures that a foreign key value in one table always refers to an
existing record in another table.
2. Referential Integrity
Referential integrity ensures that relationships between tables remain consistent. Specifically,
it ensures that a foreign key in one table points to a valid primary key in another table.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
For example, in a database with two tables, Orders and Customers, where Orders.CustomerID
is a foreign key that refers to Customers.CustomerID, referential integrity guarantees that:
 Every CustomerID in the Orders table corresponds to an existing CustomerID in the
Customers table.
 If a record in the Customers table is deleted, the related records in the Orders table will
either be deleted (cascading delete) or restricted from deletion.
3. Intension and Extension in DBMS
 Intension (Schema): Intension refers to the description of the database, often defined
by the schema. It specifies the structure of the database, including tables, columns, and
data types. Intension is generally static and does not change frequently.
For example, if you define a table called Employee with columns like EmployeeID,
Name, and Salary, that schema definition is the Intension of the table.
 Extension (Instance): Extension refers to the actual data present in the database at a
specific point in time. This represents the current state of the database. While Intension
is about structure, Extension is about the content of the database. For example, the
actual rows in the Employee table (such as the details of each employee) represent the
Extension of the table at that time.
Complex queries in DBMS
In DBMS (Database Management System), complex queries involve multiple operations
such as joins, nested subqueries, grouping, and filtering to retrieve data from one or more
tables. These queries are often used to answer more sophisticated or business-critical questions
and typically require combining multiple SQL clauses and functions.
Key Components of Complex Queries in DBMS
1. JOINs
o JOIN is used to retrieve data from multiple tables based on a related column
between them.
 INNER JOIN: Returns rows when there is a match in both tables.
 LEFT (OUTER) JOIN: Returns all rows from the left table, and the
matched rows from the right table. Returns NULL for non-matching
rows.
 RIGHT (OUTER) JOIN: Returns all rows from the right table, and the
matched rows from the left table. Returns NULL for non-matching rows.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 FULL (OUTER) JOIN: Returns rows when there is a match in either
the left or right table.
Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Nested Subqueries
 Subqueries are queries inside another query. Nested subqueries are typically used in the
SELECT, FROM, WHERE, or HAVING clauses.
Example:
SELECT EmployeeName
FROM Employees
WHERE EmployeeID IN (
SELECT EmployeeID FROM Salaries WHERE Salary > 50000
);
In this query, the subquery retrieves all EmployeeID with a salary greater than 50,000,
and the outer query uses that list to get the employee names.
3. Aggregate Functions with GROUP BY
 Used for summarizing data by grouping rows that have the same values in specified
columns, often combined with aggregate functions like COUNT, SUM, AVG, MAX,
MIN.
Example:
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 500;

INDEXING, TRIGGERS, ASSERTIONS

1. Indexing in DBMS
Indexing is a data structure technique used to improve the speed of data retrieval operations
on a database table. An index is created on columns that are frequently used in queries, allowing
the database to locate rows more quickly without scanning the entire table.
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Types of Indexes:
 Primary Index: Automatically created on the primary key field, ensuring quick lookup
of rows by primary key.
 Secondary Index: Created on non-primary key columns to enhance performance on
specific queries.
 Unique Index: Ensures that all values in the indexed column(s) are unique.
 Clustered Index: Sorts the actual data rows in the table according to the indexed
column. Only one clustered index can exist per table.
 Non-clustered Index: Creates a separate structure from the actual table, storing
pointers to the rows.
Example:
CREATE INDEX idx_employee_name
ON Employees (EmployeeName);
In this example, an index is created on the EmployeeName column to speed up search
operations involving this field.
2. Triggers in DBMS
Triggers are special procedures that are automatically executed or “triggered” when certain
events occur in a database, such as INSERT, UPDATE, or DELETE. They are often used to
maintain data integrity and enforce business rules.
Types of Triggers:
 Before Trigger: Executes before an insert, update, or delete operation.
 After Trigger: Executes after the event has occurred.
 Instead Of Trigger: Used to override the standard behavior of INSERT, UPDATE, or
DELETE operations.
Example:
CREATE TRIGGER update_salary
BEFORE UPDATE ON Employees
FOR EACH ROW
WHEN (NEW.Salary < 0)
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END;
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
In this example, a trigger prevents updating an employee's salary to a negative value.
3. Assertions in DBMS
Assertions are constraints that enforce rules across multiple tables. They are conditions that
must always be true in the database, and they can be used to enforce complex business rules
that go beyond individual table constraints like CHECK or FOREIGN KEY.
Example:
CREATE ASSERTION valid_salary
CHECK (NOT EXISTS (
SELECT *
FROM Employees
WHERE Salary < 0
));
In this example, the assertion ensures that no employee has a negative salary. If a condition in
the assertion is violated (i.e., a negative salary is inserted or updated), the system rejects the
operation.
Relational Algebra
Relational Algebra is a formal system for manipulating and querying relational databases. It
provides a set of operations (like selection, projection, and joins) that take one or more
relations as input and produce a new relation as output.
Below are explanations and examples of Selection, Projection, and Joins in Relational
Algebra.
1. Selection (σ)
Selection is used to retrieve rows from a relation that satisfy a specific condition. It’s analogous
to the WHERE clause in SQL.
 Symbol: σ
 Syntax: σ_condition(Relation)
Example:
Relations:
 Employees
EmpID Name DeptID Salary

1 Alice 10 70000

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
EmpID Name DeptID Salary

2 Bob 20 60000

3 Charlie 10 80000

4 Diana 30 75000

Operation:
Retrieve all employees who work in department 10.
 Relational Algebra Expression:
σ_{DeptID = 10}(Employees)
 Result:

EmpID Name DeptID Salary

1 Alice 10 70000

3 Charlie 10 80000

2. Projection (π)
Projection is used to retrieve specific columns from a relation, effectively removing unwanted
attributes. It’s similar to the SELECT clause in SQL.
 Symbol: π
 Syntax: π_attributes(Relation)
Example:
Relation:
 Employees (as above)
Operation:
Retrieve the names and salaries of all employees.
 Relational Algebra Expression:
π_{Name, Salary}(Employees)
 Result:

Name Salary

Alice 70000

Bob 60000

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Name Salary

Charlie 80000

Diana 75000

3. Joins
Joins combine rows from two or more relations based on a related column between them. There
are several types of joins in relational algebra, including Natural Join, Theta Join, and Equi
Join. Here, we'll focus on the Natural Join, which is the most commonly used.
 Symbol: ⋈
 Syntax: Relation1 ⋈ Relation2
Example:
Relations:
 Employees
EmpID Name DeptID Salary

1 Alice 10 70000

2 Bob 20 60000

3 Charlie 10 80000

4 Diana 30 75000

 Departments

DeptID DeptName Location

10 HR New York

20 Engineering San Francisco

30 Marketing Chicago

40 Sales Boston

Operation:
Combine Employees and Departments to list employees along with their department names.
 Relational Algebra Expression:
Employees ⋈ Departments
(Assuming a natural join on the common attribute DeptID)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
 Result:
EmpID Name DeptID Salary DeptName Location
1 Alice 10 70000 HR New York
3 Charlie 10 80000 HR New York
2 Bob 20 60000 Engineering San Francisco
4 Diana 30 75000 Marketing Chicago
Explanation:
 The natural join automatically matches rows from Employees and Departments where
DeptID is the same.
 Only departments that have matching employees are included in the result. If you want
to include all departments or all employees regardless of matches, you would use
different types of joins like Left Outer Join or Right Outer Join (though these are
extensions beyond basic relational algebra).
Combining Selection, Projection, and Joins
Often, you'll need to use multiple operations together to formulate complex queries. Here's an
example that combines all three operations.
Example:
Task:
Retrieve the names and salaries of employees who work in the "HR" department.
Relational Algebra Steps:
1. Join Employees and Departments on DeptID.
Employees ⋈ Departments
2. Select the rows where DeptName = 'HR'.
σ_{DeptName = 'HR'}(Employees ⋈ Departments)
3. Project the Name and Salary columns.
π_{Name, Salary}(σ_{DeptName = 'HR'}(Employees ⋈
Departments))
Result:
Name Salary
Alice 70000

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Name Salary
Charlie 80000

Additional Join Types

While Natural Join is straightforward, understanding other join types can be beneficial.
Theta Join
Theta Join allows you to specify a condition other than equality.
 Symbol: ⋈_θ
 Syntax: Relation1 ⋈_condition Relation2
Example:
Retrieve all employee-department pairs where the employee’s salary is greater than 70000.
 Relational Algebra Expression:
Employees ⋈_{Employees.DeptID = Departments.DeptID AND
Employees.Salary > 70000} Departments
 Result:
EmpID Name DeptID Salary DeptName Location

3 Charlie 10 80000 HR New York

4 Diana 30 75000 Marketing Chicago

Outer Joins
Outer Joins include rows that do not have matching counterparts in the other relation. They
are typically categorized as:
 Left Outer Join: All rows from the left relation and matching rows from the right.
 Right Outer Join: All rows from the right relation and matching rows from the left.
 Full Outer Join: All rows when there is a match in either left or right relation.
Note: Basic relational algebra does not include outer joins, but they are common extensions.

Summary of Symbols and Operations

Operation Symbol Description

Selection σ Selects rows based on a condition

Projection π Selects specific columns (attributes)

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Operation Symbol Description

Natural Join ⋈ Combines two relations based on common attributes

Theta Join ⋈_θ Combines two relations based on a specific condition

Fundamental Operators
These are the basic/fundamental operators used in Relational Algebra.
1. Selection(σ)
2. Projection(π)
3. Union(U)
4. Set Difference(-)
5. Set Intersection(∩)
6. Rename(ρ)
7. Cartesian Product(X)
1. Selection(σ): It is used to select required tuples of the relations.
Example:

A B C

1 2 4

2 2 3

3 2 3

4 3 4

For the above relation, σ(c>3)R will select the tuples which have c more than 3.

A B C

1 2 4

4 3 4

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Note: The selection operator only selects the required tuples but does not display them. For
display, the data projection operator is used.
2. Projection(π): It is used to project required column data from a relation.
Example: Consider Table 1. Suppose we want columns B and C from Relation R.
π(B,C)R will show following columns.

B C

2 4

2 3

3 4

Note: By Default, projection removes duplicate data.

3. Union(U): Union operation in relational algebra is the same as union operation in set
theory.
Example:
FRENCH

Student_Name Roll_Number

Ram 01

Mohan 02

Vivek 13

Geeta 17

GERMAN

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Student_Name Roll_Number

Vivek 13

Geeta 17

Shyam 21

Rohan 25

Consider the following table of Students having different optional subjects in their course.
π(Student_Name)FRENCH U π(Student_Name)GERMAN

Student_Name

Ram

Mohan

Vivek

Geeta

Shyam

Rohan

Note: The only constraint in the union of two relations is that both relations must have the
same set of Attributes.
4. Set Difference(-): Set Difference in relational algebra is the same set difference operation
as in set theory.
Example: From the above table of FRENCH and GERMAN, Set Difference is used as
follows
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
π(Student_Name)FRENCH - π(Student_Name)GERMAN

Student_Name

Ram

Mohan

Note: The only constraint in the Set Difference between two relations is that both relations
must have the same set of Attributes.
5. Set Intersection(∩): Set Intersection in relational algebra is the same set intersection
operation in set theory.
Example: From the above table of FRENCH and GERMAN, the Set Intersection is used as
follows
π(Student_Name)FRENCH ∩ π(Student_Name)GERMAN

Student_Name

Vivek

Geeta

Note: The only constraint in the Set Difference between two relations is that both relations
must have the same set of Attributes.
6. Rename(ρ): Rename is a unary operation used for renaming attributes of a relation.
ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.
7. Cross Product(X): Cross-product between two relations. Let’s say A and B, so the cross
product between A X B will result in all the attributes of A followed by each attribute of B.
Each record of A will pair with every record of B.
Example:
A

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Name Age Sex

Ram 14 M

Sona 15 F

Kim 20 M

ID Course

1 DS

2 DBMS

AXB

Name Age Sex ID Course

Ram 14 M 1 DS

Ram 14 M 2 DBMS

Sona 15 F 1 DS

Sona 15 F 2 DBMS

Kim 20 M 1 DS

Kim 20 M 2 DBMS

Note: If A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘ n*m ‘ tuples.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Derived Operators
These are some of the derived operators, which are derived from the fundamental operators.
1. Natural Join(⋈)
2. Conditional Join
1. Natural Join(⋈): Natural join is a binary operator. Natural join between two or more
relations will result in a set of all combinations of tuples where they have an equal common
attribute.
Example:
EMP

Name ID Dept_Name

A 120 IT

B 125 HR

C 110 Sales

D 111 IT

DEPT

Dept_Name Manager

Sales Y

Production Z

IT A

Natural join between EMP and DEPT with condition :

EMP.Dept_Name = DEPT.Dept_Name
EMP ⋈ DEPT

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Name ID Dept_Name Manager

A 120 IT A

C 110 Sales Y

D 111 IT A

2. Conditional Join: Conditional join works similarly to natural join. In natural join, by
default condition is equal between common attributes while in conditional join we can
specify any condition such as greater than, less than, or not equal.
Example:
R

ID Gender Marks

1 F 45

2 F 55

3 F 60

ID Gender Marks

10 M 20

11 M 22

12 M 59

Join between R and S with condition R.marks >= S.marks

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
R.ID R.Gender R.Marks S.ID S.Gender S.Marks

1 F 45 10 M 20

1 F 45 11 M 22

2 F 55 10 M 20

2 F 55 11 M 22

3 F 60 10 M 20

3 F 60 11 M 22

3 F 60 12 M 59

Introduction to Relational Calculus

Relational Calculus is a non-procedural query language for relational databases. Unlike
Relational Algebra, which is procedural and specifies how to obtain the desired result,
Relational Calculus focuses on what the desired result is, without specifying the steps to
achieve it.
Introduced by E.F. Codd, the founder of the relational model, relational calculus forms the
theoretical foundation for SQL (Structured Query Language), which is widely used for
managing and querying relational databases.
Relational Calculus is essential for formulating precise queries and understanding the
semantics of database operations. It ensures that queries are declarative, expressing the
properties of the desired data rather than the procedural steps to retrieve it.
Types of Relational Calculus
Relational Calculus is divided into two primary types:
1. Tuple Relational Calculus (TRC)
2. Domain Relational Calculus (DRC)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Both types serve the same purpose of querying relational databases but differ in their approach
and syntax.
Tuple Relational Calculus (TRC)
Tuple Relational Calculus (TRC) is a form of relational calculus where queries are expressed
by specifying tuples that satisfy certain conditions. In TRC, variables represent entire tuples
from a relation.
Syntax of TRC:
The general form of a TRC query is:
{𝑡|𝑃(𝑡)}
 t: A tuple variable.
 P(t): A predicate (condition) that the tuple t must satisfy.
Example:
Problem: Retrieve the names of all employees who work in the "HR" department.
Assumptions:
 Employees relation with attributes: EmpID, Name, DeptID, Salary.
 Departments relation with attributes: DeptID, DeptName, Location.
TRC Query:
{e.Name ∣ e ∈ Employees ∧ ∃d ∈ Departments (e.DeptID=d.DeptID ∧ d.DeptName=′HR′)}
Explanation:
 e is a tuple variable ranging over the Employees relation.
 The query selects the Name attribute of all tuples e where:
o There exists a tuple d in Departments such that:
 The DeptID of e matches the DeptID of d.
 The DeptName of d is 'HR'.
Result:

Name

Ram

Shyam

...
(Assuming Ram and Shyam work in HR)
Domain Relational Calculus (DRC)
Prof. Vineeta Shrivastava
A.P CSE
LNCTE
 Database Management Systems
Domain Relational Calculus (DRC) is another form of relational calculus where queries are
expressed by specifying domains (attributes) that satisfy certain conditions. In DRC, variables
represent individual attributes rather than entire tuples.
Syntax of DRC:
The general form of a DRC query is:
{⟨x1,x2,…,xn⟩∣P(x1,x2,…,xn)}
 x₁, x₂, …, xn: Domain variables representing attributes.
 P(...): A predicate that these variables must satisfy.
Example:
Problem: Retrieve the names of all employees who work in the "HR" department.
Assumptions:
 Employees relation with attributes: EmpID, Name, DeptID, Salary.
 Departments relation with attributes: DeptID, DeptName, Location.
DRC Query:
{⟨eName⟩∣∃eID∃dID∃salary(Employees(eID,eName,dID,salary)∧Departments(dID,′HR′,loca
tion))}
Explanation:
 eName is the domain variable for the employee's name.
 The query selects the Name attribute (eName) of all employees where:
o There exists eID, dID, and salary such that:
 A tuple exists in Employees with EmpID = eID, Name = eName,
DeptID = dID, and Salary = salary.
 A tuple exists in Departments with DeptID = dID and DeptName =
'HR'.
Result:

Name

Ram

Shyam

...
(Assuming Ram and Shyam work in HR)

Prof. Vineeta Shrivastava

A.P CSE
LNCTE
 Database Management Systems
Comparison: Relational Algebra vs. Relational Calculus
Both Relational Algebra and Relational Calculus are formal query languages for relational
databases, but they differ in approach:

Feature Relational Algebra Relational Calculus

Procedural (specifies how to perform Non-procedural (specifies what to

Nature
operations) retrieve)

Set of operations (e.g., selection, Logical predicates to describe desired

Operators
projection, join) results

TRC: Operate on tuples; DRC:

Variables Operate on entire relations
Operate on domains

Equivalent in expressiveness to Equivalent in expressiveness to

Expressiveness
Relational Calculus Relational Algebra

Usage in SQL Underlies SQL's operational aspects Mirrors SQL's declarative aspects
Key Takeaway: While Relational Algebra provides a set of operations to transform relations
step-by-step, Relational Calculus allows for specifying the properties of the desired result
without detailing the transformation process.

Prof. Vineeta Shrivastava

A.P CSE
LNCTE