MySQL

Chapter 8 - RDBMS Concepts

Business key, Natural key and Candidate Key

In relational databases, understanding different types of keys is crucial for designing a well-structured database. Keys are attributes that uniquely identify rows in a table. Here’s a look at Business Keys, Natural Keys, and Candidate Keys:

  1. Business Key

    • A Business Key, also known as a Natural Key, is an attribute or a set of attributes that have a real-world meaning and are used to uniquely identify a record based on business rules.
    • Business Keys are often used in business processes and can be derived from existing business data.
    -- Example of a Business Key
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    SocialSecurityNumber VARCHAR(11) UNIQUE, -- Business Key
    EmployeeName VARCHAR(50)
);
    • In the example, SocialSecurityNumber is a Business Key because it uniquely identifies an employee in the real world.

  2. Natural Key

    • A Natural Key is similar to a Business Key but is specifically an attribute that inherently identifies a record based on natural or real-world data.
    • It is not always unique to the business but is meaningful within the business context.
    -- Example of a Natural Key
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentCode VARCHAR(10) UNIQUE, -- Natural Key
    DepartmentName VARCHAR(50)
);
    • In the example, DepartmentCode is a Natural Key because it is a real-world identifier for the department.

  3. Candidate Key

    • A Candidate Key is any attribute or set of attributes that can uniquely identify a record in a table. A table can have multiple Candidate Keys, but only one is selected as the Primary Key.
    • Candidate Keys are potential candidates for being the Primary Key.
    -- Example of Candidate Keys
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- Primary Key
    SocialSecurityNumber VARCHAR(11) UNIQUE, -- Candidate Key
    EmailAddress VARCHAR(100) UNIQUE, -- Candidate Key
    EmployeeName VARCHAR(50)
);
    • In this example, EmployeeID, SocialSecurityNumber, and EmailAddress are all Candidate Keys because each can uniquely identify an employee. However, EmployeeID is chosen as the Primary Key.

  4. Key Comparisons and Usage

    • Business Key vs. Natural Key: While often used interchangeably, a Business Key is typically more aligned with business processes, whereas a Natural Key is more about inherent real-world data.
    • Candidate Key vs. Primary Key: A Candidate Key is any key that can uniquely identify a record, but only one Candidate Key is selected as the Primary Key, which is used to enforce uniqueness and indexing in the table.

  5. Example of Key Usage

    • Let’s consider a table that tracks employee information with multiple potential keys.
    -- Table with multiple Candidate Keys
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- Chosen as Primary Key
    NationalID VARCHAR(20) UNIQUE, -- Candidate Key
    EmailAddress VARCHAR(100) UNIQUE, -- Candidate Key
    EmployeeName VARCHAR(50)
);
    • Here, EmployeeID, NationalID, and EmailAddress are all Candidate Keys. EmployeeID is selected as the Primary Key because it is often the simplest and most stable choice for indexing.

Understanding these key concepts is essential for designing databases that are efficient, maintainable, and aligned with business needs. Each key type plays a role in ensuring data integrity and supporting effective data management.

Tansy SQL Course | Business key, Natural key and Candidate Key | Chapter 8 | Lesson 8

RDBMS Overview

Business Key

Definition

A Business Key (also known as a Domain Key) is a data attribute or set of attributes that have a meaningful value in the business domain and are used to identify or reference an entity uniquely within a business context. Business keys are chosen based on business logic and are often visible and understandable by end-users.

Characteristics
  • Business Relevance:Directly related to the business domain, making them intuitively understandable to business users.
  • Uniqueness:Ideally, a business key should uniquely identify a record without ambiguity.
  • Stability:Should be relatively stable over time, although not as strictly invariant as surrogate keys.
Use Cases

Business keys are commonly used in scenarios where data needs to be integrated or matched across different systems or databases, relying on business concepts. For example, a customer's social security number (SSN) or a product's SKU number can serve as a business key.


Natural Key

Definition

A Natural Key (also known as a Natural Business Key) is a type of business key that naturally occurs in the real world and is used within databases to uniquely identify a piece of data. It is derived from the existing attributes of the data that are inherent to the entity being represented.

Characteristics
  • Inherent:Derived from the data's natural attributes without artificial generation.
  • Uniqueness:Uniquely identifies data entities in a natural manner.
  • Understandability:Often immediately recognizable and meaningful to users.
Use Cases

Natural keys are used when the uniqueness of a record is defined by real-world attributes. Examples include email addresses for user accounts or vehicle identification numbers (VINs) for cars.


Candidate Key

Definition

Candidate Keys are a set of attributes (or a single attribute) in a table that can qualify as a unique key for identifying rows in the table. A table can have multiple candidate keys, each capable of uniquely identifying a row.

Characteristics
  • Uniqueness:Every candidate key must uniquely identify each row in a database table.
  • Non-Redundancy:No two rows can have the same value for candidate keys.
  • Minimality:A candidate key must be minimal, meaning that removing any attribute from it would result in losing its property of uniqueness.
Use Cases

Candidate keys are considered when designing databases to ensure data integrity and uniqueness. From the set of candidate keys, one is chosen as the primary key for the table, while others can serve as alternate keys or be used to enforce uniqueness through unique constraints.


Key Differences and Relations

Business vs. Natural Key:While both are meaningful within the business domain, natural keys are a subset of business keys that are inherently found in the data. Business keys might not always be natural; they could be imposed by business processes.

Candidate Keys vs. Natural/Business Keys:Candidate keys are a broader concept that includes any potential key that can uniquely identify table rows, including both natural/business keys and surrogate keys. The primary key is a special case of a candidate key that is chosen to serve as the main unique identifier for table rows.


Conclusion

The selection of keys in database design is a critical decision that affects data integrity, performance, and usability. Business keys and natural keys are closely tied to the business domain, offering meaningful ways to identify records. Candidate keys offer a more technical perspective, highlighting all possible unique identifiers from which the primary key is chosen. Understanding these concepts is essential for effective database schema design, ensuring that data remains accurate, consistent, and accessible.

SAMPLE DATA FOR BUSINESS KEY, NATURAL KEY AND CNDIDATE KEY

i

RDBMS Overview

Department Table

Business Key:

department:This could be a Business Key as it represents a meaningful value in the business domain and is used to uniquely identify a department within the business context.

Natural Key:

There isn't a clear Natural Key in the Department Table as presented. However, if the department names are unique and not subject to change, thendepartmentcould also be considered a Natural Key.

Candidate Keys:
  • department_id:This is a strong candidate key because it is unique for each department.
  • department:This could also be a candidate key if the department names are guaranteed to be unique.

Employee Table

Business Key:
  • employee_number:This might be a Business Key since it appears to be a unique identifier used within the business to identify employees.
  • email:Could also serve as a Business Key because emails are unique identifiers in a business context.
Natural Key:

email:Assuming that each employee has a unique email address, this could be a Natural Key because it is a real-world unique identifier that naturally occurs.

Candidate Keys:
  • employee_id:Clearly a candidate key as it is a unique attribute for each employee.
  • employee_number:If this is unique across the organization, it also qualifies as a candidate key.
  • email:If unique, this is another candidate key.

Note that for thedepartment_idin the Employee Table, it's a foreign key that relates to thedepartment_idin the Department Table, establishing a relationship between the two tables.


Key Distinctions and Relations

  • Business Keysare defined by business need and may not always be the primary key in database design.
  • Natural Keysare keys that naturally and obviously identify an entity, such as an email or social security number.
  • Candidate Keysare any keys which could serve as the primary key. In practice, one of them is chosen to be the primary key based on factors like stability and simplicity, and the others may be used for alternate indices or constraints.
Previous Next
Comments(0 comments)

Comments Not Found

Most visited
MySQL Live SQL Training 572 views
MySQL SELECT * 142 views
MySQL ROWs and COLUMNs 78 views
MySQL WHERE Clause 74 views
MySQL CREATE TABLE 68 views
MySQL Arithmetic Operators 65 views