Scribd
Upload
24 views5 pages

Understanding Keys in DBMS

Keys play an important role in relational databases by uniquely identifying rows and establishing relationships between tables. There are several types of keys including primary keys, which uniquely identify each row; foreign keys, which link rows between tables; and candidate keys, from which the primary key must be chosen. Keys help ensure identity, integrity, and allow navigation between related data.

Uploaded by

M S S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
24 views5 pages

Understanding Keys in DBMS

Keys play an important role in relational databases by uniquely identifying rows and establishing relationships between tables. There are several types of keys including primary keys, which uniquely identify each row; foreign keys, which link rows between tables; and candidate keys, from which the primary key must be chosen. Keys help ensure identity, integrity, and allow navigation between related data.

Uploaded by

M S S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

KEYS IN DBMS

Key plays an important role in relational database; it is used for identifying unique rows
from table. It also establishes relationship among tables.

Why we need a Key?


Keys help you to identify any row of data in a table. In a real-world application, a
table could contain thousands of records. Moreover, the records could be duplicated.
Keys ensure that you can uniquely identify a table record despite these challenges.

Allows you to establish a relationship between and identify the relation between tables

Help you to enforce identity and integrity in the relationship.

Types of keys in DBMS


Primary Key – A primary is a column or set of columns in a table that uniquely
identifies tuples (rows) in that table.

Table Name: STUDENT

Stu_Id Stu_Name Stu_Age


101 Steve 23
102 John 24
103 Robert 28
104 Steve 29
105 Carl 29

Points to Note regarding Primary Key

We denote usually denote it by underlining the attribute name (column name).


The value of primary key should be unique for each row of the table. The column(s) that
makes the key cannot contain duplicate values.

The attribute(s) that is marked as primary key is not allowed to have null values.

Primary keys are not necessarily to be a single attribute (column). It can be a set of more
than one attributes (columns). For example {Stu_Id, Stu_Name} collectively can identify the
tuple in the above table, but we do not choose it as primary key because Stu_Id alone is
enough to uniquely identifies rows in a table and we always go for minimal set. Having
that said, we should choose more than one columns as primary key only when there is no
single column that can uniquely identify the tuple in table.

Eg-:

1-

Create table STUDENT

Stu_Id int primary key,

Stu_Name varchar(255) not null,

Stu_Age int not null

2-

Create table ORDER

Customer_ID int not null,

Product_ID int not null,

Order_Quantity int not null,

Primary key (Customer_ID, Product_ID)

3-
ALTER TABLE ORDER

ADD CONSTRAINT PK_Order PRIMARY KEY (Customer_ID, Product_ID);

Super Key – A super key is a set of one of more columns (attributes) to uniquely
identify rows in a table. A Super key may have additional attributes that are not needed
for unique identification.

Example:

EmpSSN EmpNum Empname

9812345098 AB05 Shown

9876512345 AB06 Roslyn

199937890 AB07 James

In the above-given example, EmpSSN and EmpNum name are superkeys.

Candidate Key – A super key with no redundant attribute is known as candidate key
CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate
Key is a super key with no repeated attributes. The Primary key should be selected
from the candidate keys. Every table must have at least a single candidate key. A table
can have multiple candidate keys but only a single primary key.

Properties of Candidate key:

It must contain unique values

Candidate key may have multiple attributes

Must not contain null values

It should contain minimum fields to ensure uniqueness

Uniquely identify each record in a table


Example: In the given table Stud ID, Roll No, and email are candidate keys which
help us to uniquely identify the student record in the table.

StudID Roll No First Name LastName Email

1 11 Tom Price abc@[Link]

2 12 Nick Wright xyz@[Link]

3 13 Dana Natan mno@[Link]

Alternate Key – Out of all candidate keys, only one gets selected as primary key,
remaining keys are known as alternate or secondary keys.

Composite Key – A key that consists of more than one attribute to uniquely identify rows
(also known as records & tuples) in a table is called composite key.

Foreign Key – Foreign keys are the columns of a table that points to the primary key of
another table. They act as a cross-reference between tables.

FOREIGN KEY is a column that creates a relationship between two tables. The purpose
of Foreign keys is to maintain data integrity and allow navigation between two
different instances of an entity. It acts as a cross-reference between two tables as it
references the primary key of another table.

Example:

DeptCode DeptName

001 Science
002 English

005 Computer

Teacher ID Fname Lname

B002 David Warner

B017 Sara Joseph

B009 Mike Brunton

we have two table, teach and department in a school. However, there is no way to see
which search work in which department.

In this table, adding the foreign key in Deptcode to the Teacher name, we can create a
relationship between the two tables.

Teacher ID DeptCode Fname Lname

B002 002 David Warner

B017 002 Sara Joseph

B009 001 Mike Brunton

This concept is also known as Referential Integrity.

Common questions

Powered by AI

Improper key design can significantly impact performance and integrity by causing data redundancy, inconsistent data references, and unnecessary complexity. It can lead to inefficiencies in data retrieval if keys are not optimized for the minimal, necessary uniqueness. Moreover, poorly designed keys might not enforce the correct constraints, leading to errors in data relationships and compromise the overall data quality and consistency .

Alternate keys are necessary because they provide alternative options for uniquely identifying rows in a table when the primary key is not sufficient or applicable for certain operations. They are derived from candidate keys by excluding the one chosen as the primary key. Thus, alternate keys provide flexibility in accessing data and ensure that multiple unique paths exist for retrieving records, which is especially useful in certain query operations .

Deriving a composite key involves selecting two or more columns that together provide a unique identification for table rows, especially when no single attribute suffices. Practically, this is essential in tables capturing many-to-many relationships, such as ORDER tables where a combination of Customer_ID and Product_ID uniquely identifies each order. This approach ensures each record can be properly related and queried within the relational framework .

Super keys differ from candidate keys in that super keys may include extra attributes that are not necessary for the unique identification of a tuple, whereas candidate keys are minimal super keys without redundant attributes. This distinction is significant as it affects the selection of primary keys; a primary key should be derived from candidate keys, which are the most efficient and minimal keys possible. This minimalist approach in design helps in reducing data redundancy and optimizing database performance .

Candidate keys are crucial because they ensure each record can be uniquely identified by a minimum set of fields that do not permit duplicate values. This capability underpins the data schema's integrity, aiding in data organization, retrieval, and preventing redundancy. Without candidate keys, enforcing uniqueness and ensuring reliable retrieval of data would be significantly compromised .

Alternate keys introduce flexibility in querying databases by allowing multiple unique access points for data retrieval beyond the primary key. They enhance query optimization as different views or report requirements can selectively use these keys, improving query performance. Additionally, alternate keys enable adaptive indexing strategies, reducing retrieval times by aligning indexes with various query patterns, ensuring efficiency across diverse operational contexts .

Foreign keys maintain referential integrity by ensuring that a record in one table corresponds to a valid record in another. They create a link between tables, where a foreign key in one table points to the primary key of another, thus establishing a relationship. This setup ensures consistent data across tables, as any update in the referenced table is automatically checked against references in other tables, maintaining data validity and integrity .

A composite key is preferred when no single attribute is sufficient to uniquely identify a table's rows. This situation typically occurs in a many-to-many relationship where multiple columns together ensure uniqueness. For example, in an ORDER table, the combination of Customer_ID and Product_ID can uniquely identify an order, which neither of the individual fields alone can do .

Primary keys and foreign keys work together by establishing a link between tables in a relational database. The primary key uniquely identifies a row within its table, while a foreign key points back to the primary key of another table, ensuring referential integrity. This relationship allows for complex queries across tables, where data from different tables can be joined and correlated, enabling comprehensive data analysis and retrieval operations efficiently .

A primary key enforces data integrity within a database table by ensuring that each row has a unique identifier, preventing duplicate entries, and disallowing null values. This requirement ensures that each record can be uniquely accessed and identified. The integrity is maintained as every row is distinct and integral to its identification via the primary key, thus maintaining a form of internal consistency in the data structure .

You might also like