Types of Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)

 In the context of a relational database, Keys are one of the basic requirements of a relational database model. keys are fundamental components that ensure data integrity, uniqueness, and efficient access. 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. Let’s explore the various types of keys used in a relational model, which are essential for organizing and querying data effectively.

Why do we require Keys in a DBMS?

Keys are crucial in a Database Management System (DBMS) for several reasons:

Uniqueness: Keys ensure that each record in a table is unique and can be identified distinctly.

Data Integrity: Keys prevent data duplication and maintain the consistency of the data.

Efficient Data Retrieval: By defining relationships between tables, keys enable faster querying and better data organization. Without keys, it would be extremely difficult to manage large datasets, and queries would become inefficient and prone to errors.

Different Types of Database Keys

1. Super Key

The set of one or more attributes (columns) that can uniquely identify a tuple (record) is known as Super Key. It may include extra attributes that aren't essential for uniqueness but still uniquely identify the row. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc.


A super key is a group of single or multiple keys that uniquely identifies rows in a table. It supports NULL values in rows.

A super key can contain extra attributes that aren’t necessary for uniqueness. For example, if the "STUD_NO" column can uniquely identify a student, adding "SNAME" to it will still form a valid super key, though it's unnecessary.

Example: Consider the STUDENT table


STUD_NO SNAME ADDRESS PHONE

1                 Shyam Delhi         123456789

2                 Rakesh Kolkata         223365796

3                 Suraj Delhi         175468965

A super key could be a combination of STUD_NO and PHONE, as this combination uniquely identifies a student.

Relation between Primary Key, Candidate Key and Super Key


2. Candidate Key

The minimal set of attributes that can uniquely identify a tuple is known as a candidate key. For Example, STUD_NO in STUDENT relation.


A candidate key is a minimal super key, meaning it can uniquely identify a record but contains no extra attributes.

It is a super key with no repeated data is called a candidate key.

The minimal set of attributes that can uniquely identify a record.

A candidate key must contain unique values, ensuring that no two rows have the same value in the candidate key’s columns.

Every table must have at least a single candidate key.

A table can have multiple candidate keys but only one primary key.

Example: For the STUDENT table below, STUD_NO can be a candidate key, as it uniquely identifies each record.


STUD_NO SNAME ADDRESS PHONE

1                 Shyam Delhi 123456789

2                 Rakesh Kolkata 223365796

3                 Suraj Delhi 175468965

Table STUDENT_COURSE


STUD_NO TEACHER_NO COURSE_NO

1                 001                         C001

2                 056                         C005

A composite candidate key example: {STUD_NO, COURSE_NO} can be a candidate key for a STUDENT_COURSE table.


3. Primary Key

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).


A primary key is a unique key, meaning it can uniquely identify each record (tuple) in a table.

It must have unique values and cannot contain any duplicate values.

A primary key cannot be NULL, as it needs to provide a valid, unique identifier for every record.

A primary key does not have to consist of a single column. In some cases, a composite primary key (made of multiple columns) can be used to uniquely identify records in a table.

Databases typically store rows ordered in memory according to primary key for fast access of records using primary key.

Example:


STUDENT table -> Student(STUD_NO, SNAME, ADDRESS, PHONE) , STUD_NO is a primary key  

Table STUDENT

STUD_NO SNAME         ADDRESS                 PHONE

1                 Shyam         Delhi                         123456789

2                 Rakesh        Kolkata                         223365796

3                 Suraj         Delhi                         175468965


4. Alternate Key

An alternate key is any candidate key in a table that is not chosen as the primary key. In other words, all candidate keys that are not selected as the primary key are considered alternate keys.

Alternate keys are also referred to as secondary keys, as they can still uniquely identify records in a table, just like a primary key.

An alternate key can consist of one or more columns (fields) that, when combined, uniquely identify a record. However, it is not used as the main identifier (primary key).

Example:

Consider a STUDENT table with the following candidate keys:

  • STUD_NO

  • PHONE

If STUD_NO is chosen as the primary key, then PHONE becomes an alternate key.

You could also have combinations such as SNAME and ADDRESS act as an alternate key, if they together uniquely identify a student.


Primary Key, Candidate Key and Alternate Key

5. Foreign Key

A foreign key is an attribute in one table that refers to the primary key in another table. The table that contains the foreign key is called the referencing table, and the table that is referenced is called the referenced table.

A foreign key in one table points to the primary key in another table, establishing a relationship between them.

It helps connect two or more tables, enabling you to create relationships between them. This is essential for maintaining data integrity and preventing data redundancy.

They act as a cross-reference between the tables.

For example, DNO is a primary key in the DEPT table and a non-key in EMP

Example: Consider the STUDENT_COURSE table


STUD_NO TEACHER_NO COURSE_NO

1                  005                         C001

2                  056                         C005

Here, STUD_NO in the STUDENT_COURSE table is a foreign key that references the STUD_NO primary key in the STUDENT table.

Explanation:

Unlike the Primary Key of any given relation, a foreign key can be null as well as may contain duplicate tuples, i.e., it need not follow the uniqueness constraint. For Example, STUD_NO in the STUDENT_COURSE relation is not unique.

It has been repeated for the first and third tuples. However, the STUD_NO in the STUDENT relation is a primary key and must always be unique; it cannot be null.

Relation between Primary Key and Foreign Key

6. Composite Key

Sometimes, a table might not have a single column/attribute that uniquely identifies all the records of a table. To uniquely identify rows of a table, a combination of two or more columns/attributes can be used. It still can give duplicate values in rare cases. So, we need to find the optimal set of attributes that can uniquely identify rows in a table.


It acts as a primary key if there is no primary key in a table

Two or more attributes are used together to make a composite key .

Different combinations of attributes may give different accuracy in terms of identifying the rows uniquely.

Example: In the STUDENT_COURSE table, {STUD_NO, COURSE_NO} can form a composite key to uniquely identify each record.

Different Types of Keys





Comments

Post a Comment

Popular posts from this blog

Creating a class, the best practice way in D365F&O

 Some time ago, I shared my thoughts on the 'construct' pattern, which is commonly used in AX. While I often make use of it, it’s not without its flaws—it can actually violate some best practices. (I’ll cover the issue of deep class hierarchies resulting from this pattern in a future post.) First, it’s worth noting that you can customize the compiler level and the best practice checks it performs. To do this, compile something to bring up the compiler output window. Then, click the Setup button to adjust the settings. For this example, I used compiler level 4 and enabled all best practice checks. Now, let’s create a simple class. This class will simply display information from an InventTable record. 1. ClassDeclaration As we start designing this class, it quickly becomes clear that we need to declare an InventTable record as a member variable. /// <summary> /// Class written to test the construct and new... patterns /// </summary> /// <remarks> /// De...
Read more

Package Deployment using LCS for Microsoft Dynamics 365 Finance & Operations

Image
Most of us are familiar with Lifecycle Services(LCS), and package deployment is one of its uses. Below are the steps that need to be performed for successful deployment or application updates.   Prerequisites: 1) Admin account access for package creation. 2) LCS account with project assigned. Visual Studio Steps Step 1: Open Visual Studio using Run as administrator. Step 2: Click Dynamics 365--> Deploy --> Create deployment package.  Step 3: Select the model(s) to be deployed and the location of the package file where it will be stored.  LCS Steps  Step 4: Log in to LCS and select Asset Library from the top left dropdown.  Step 5 : Navigate and select Software deployable package and select + button to add package to the folder and confirm. Refresh the screen and wait for the package gets validated, tick will appear once validated. Step 6: Navigate to the environment where the package will be deployed. Select apply update under the Maintain dropdown....
Read more

How to remove deployed model in Tier2 environment in D365FO

 How to remove the deployed model in the Tier2 environment Dear all, I have an issue with the deployed model in the D365FO Tier 2 environment. I have some models in the development environment: Model A Model B Model C Model D I want to apply only Models A, B, and C for the Tier 2 environment. For the first time for deployment, I created a deploy package(including Model A, B, and C) in development and applied an update for the Tier 2 environment. However, yesterday, developer X on my team deployed a new deployment package(Model A, B, C, D). So today I could not deploy the package in Tier 2 with Models A, B, and C. Solutions: 1. Download the same package from the Asset Library that was previously deployed. Add File ModuleToRemove.txt (with model listed). Reupload to Asset Library and deploy to UAT. When I do this,  there's a warning on LCS that "it will first reinstall the model and then delete it. In order to not reinstall, delete the model from the package." 2. Create a n...
Read more