1

Keys
• Candidate key
• A candidate key is a minimal set of attributes (columns) that uniquely identifies each row in a
relation (table).
• This means that no subset of the attributes can uniquely identify the row on its own.
• Multiple Candidate Keys: A table can have multiple candidate keys.
• Primary Key: One of the candidate keys is chosen as the primary key, which is used to
uniquely identify each row and establish relationships with other tables.

Consider a Products table with the following attributes:

ProductID (INT)
ProductName (VARCHAR)
ProductDescription (TEXT)
UPC (VARCHAR)
In this case, there could be two candidate keys:
[Link]: This is the most likely primary key, as it's unique for each product.
[Link]: If UPC codes are unique for each product, it could also be a candidate key

2
 Customer_Details Table

Assumptions
• One customer can have only one account
• An account can belong to only one customer
Simple Candidate key:
• A candidate key comprising of one attribute only.
• Eg: Account_No, Cust_ID, Cust_Email
Composite candidate key:
• A candidate key comprising two or more attributes.
• {Cust_Last_Name, Cust_First_Name}
Candidate Keys are identified during the design of the database 3
Primary Key:
• During the creation of the table (the implementation phase), the database designer
chooses one of the candidate key from amongst the several available, to uniquely
identify rows in the customer_details table. The candidate key so chosen is called the
primary key.
• A primary key that is a combination of more than one attribute is called a composite
primary key.

4
Both candidate keys and primary keys are used to uniquely identify rows in a table.
However, there are some key differences between them:
Candidate Key:
•Definition: A minimal set of attributes that uniquely identifies a row in a table.
•Uniqueness: All values in a candidate key must be unique.
•Multiple: A table can have multiple candidate keys.
Primary Key:
•Definition: A candidate key that has been chosen as the unique identifier for a table.
•Uniqueness: All values in a primary key must be unique and non-null.
•One per table: A table can only have one primary key.

A candidate key is a minimal superkey.

It's a combination of attributes that uniquely identifies rows, but it contains no redundant attributes.
If you remove any attribute from a candidate key, it will no longer be unique.

5
Entity integrity constraint:
• The primary key of a table is always not null and unique
• The attributes which constitute the primary key cannot have duplicate values in the
rows of the table.
• It is mandatory to provide input for the primary key attributes.
• This constraint is referred to as the entity integrity constraint.
• It is preferable to select a candidate key with a minimal number of attributes to
function as a primary key

• Selection guidelines
• Give preference to numeric columns
• Give preference to single attribute keys
• Give preference to minimal composite keys
• If the candidate keys are {a1,a2} and {a3,a4,a5}; then choose {a1,a2}

6
• Foreign key
• A Foreign Key is a set of the attribute (s) whose values are required to match
values of a Candidate key in the same or another table.
• It can have null or duplicate values.

7
• Referencing relation: The table that contains the foreign key.
• Referenced relation: The table that contains the primary key or unique constraint
referenced by the foreign key.
• Referential constraint: The rule that specifies the relationship between the
referencing and referenced relations. Typically implemented using foreign keys.
• Referential Integrity Problem: A violation of a referential constraint, often
caused by attempts to delete or update data that is referenced by other rows.
• The relation that contains the foreign key is the referencing relation (also called
the child table) and the relation that contains the corresponding primary key is
the referenced relation (also called the parent table).
• Invalid foreign key:
• A value of 1053 in the Account_No attribute of Customer_Transaction table is
invalid because a value of 1053 is not present in the Account_No attribute of
the Customer_Details table.

8
• Foreign key constraint: Value in one relation must appear in
another relation.
• dept_name of instructor is a foreign key from instructor ,
referencing department since dept_name is the primary key of
department
• Every value for instructor.dept_name should be present in
department.dept_name
Referencing (child) relation Referenced (parent)relation

instructor department

ID dept_name
name building
dept_name
salary

9
Foreign key constraints
• Enforce referential integrity
• A new row cannot be added to referencing relation unless it has a
matching record in the referenced relation
• Prevent actions that would destroy links between tables
• Handle modifications and deletions in the parent table
• When deleting or updating a table referential integrity constraint is violated.
• To maintain referential integrity, we attach referential triggered action to the
foreign key when creating the table.
• CREATE TABLE orders ( order_id integer PRIMARY KEY, order_date date,
customer integer REFERENCES customers ON DELETE CASCADE );
• CASCADE option deletes or updates the row from the parent table
(containing PRIMARY KEYs), and automatically delete or update the
matching rows in the child table (containing FOREIGN KEYs).

10
• Self- Referencing
table
• – A table that has a
Foreign Key
referring to its own
Candidate Key is
known as self
referencing.
• From the example
Prerequisite can only
have any existing
value from the
Course_ID attribute

11
 Super Key
• Any superset of a candidate Key is a super key.
• A superkey is any combination of attributes (columns) that can uniquely identify a row in a table.
• A superkey can contain redundant attributes
Consider the following sets comprising of attributes from the Customer_Details table:
• {Account_No}
• {Account_No,Account_Type}
• {Account_No,Account_Type,Bank_Branch}
• {Account_No} is a candidate key for the table. {Account_No,Account_Type} is a superset of {Account_No}
and hence super key for the table

Relationship between Superkey and Candidate Key:

•Every candidate key is also a superkey.
•Not every superkey is a candidate key. Candidate keys are the minimal superkeys.

12
Non-Key Attributes
• The attributes other than the Candidate Key attributes in a table/relation are called
Non-Key attributes.
• OR
• The attributes which do not participate in any of the Candidate keys..
• Egs: Cust_Last_Name, Cust_Mid_Name,Cust_First_Name, Bank_Branch etc are
non-key attributes in the Customer_Details table

13
Points to remember
• A table can have only 1 primary key. It may be a simple or a composite key
• Primary key attribute cannot contain NULL values
• Values for the primary key in a relation should be unique
• A table can have any number of foreign keys (simple or composite)
• Values of foreign key attribute need not be unique
• Foreign key attribute in the referencing relation can have null values

14
1

Let n be the total number of attributes in R. Since A and B are candidate keys, they are also super keys.
Since each super key is a subset of all attributes, and there are 6 super keys, the total number of attributes
is 3. The super keys are {A}, {B}, {A, B}, {A, C}, {B, C}, {A, B, C}.

Here are all the possible superkeys:

1.{id} (This is also a candidate key)
2.{name} (This is also a candidate key)
3.{id, name}
4.{id, salary}
15
5.{name, salary}
2

16
3

17
4

18
5

19
6

20
7

21
9

22
10

23
11

24
12

25
13

26
14

27
15

28
29
30
31
32
33
34
Database
• A database consists of multiple relations
• Information about an enterprise is broken up into parts
instructor
student
advisor
• Bad design:
univ (instructor -ID, name, dept_name, salary, student_Id, ..)
results in
– repetition of information (e.g., two students have the same instructor)
– the need for null values (e.g., represent an student with no advisor)
• Normalization theory (Chapter 7) deals with how to design
“good” relational schemas
35
Terms
• Attribute - a property or description of an entity. A toy
department employee entity could have attributes
describing the employee’s name, salary, and years of
service.
• Domain is synonymous with data type. Attributes can be
thought of as columns in a table. Therefore, an attribute
domain refers to the data type associated with a column
• Entity - an object in the real world that is distinguishable
from other objects such as the green dragon toy.
• Relational Database: It is a collection of tables each of
which is assigned a unique name or entity.

36
Terms
• A relation schema can be thought of as the basic
information describing a table or relation. This includes a
set of column names, the data types associated with each
column, and the name associated with the entire table. For
example, a relation schema for the relation called Students
could be expressed using the following representation:
• Students(sid: string, name: string, login: string, age:
integer, gpa: real)
• A relational database schema is a collection of relation
schemas, describing one or more relations
• A relation instance is a set of tuples (also known as rows or
records) that each conform to the schema of the relation

37
• How candidate key is different from super key?

• Candidate keys are selected from the set of super keys

• It should not have any redundant attribute.
• They are also termed as minimal super key.

Employee table

38