Microsoft SQL Server

Chapter 8 - RDBMS Concepts

Business key, Natural key and Candidate Key

In a relational database, keys play a crucial role in uniquely identifying records in a table and establishing relationships between different tables. Business Key, Natural Key, and Candidate Key are three types of keys that help in this process, each serving a unique function depending on the nature of the data and the design of the database. Understanding these keys is essential for proper database modeling and maintaining data integrity.

Key Concepts:

  1. Business Key

    • A Business Key is a unique identifier that is meaningful in the context of the business and the data being stored. It is derived from the real-world business domain, often representing something that is already unique in the real world.
    • For example, in a store system, a ProductCode or CustomerEmail could be considered a business key.

    Example SQL:

    CREATE TABLE Products (
    ProductCode VARCHAR(50) PRIMARY KEY,  -- Business Key
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

  2. Natural Key

    • A Natural Key is a type of key that already exists in the real world and is naturally unique. It can often be used as a primary key in a table. A natural key is typically a business-related attribute, like a national ID number or product code.
    • For instance, the CustomerEmail column can serve as a natural key in a table that stores customer details.

    Example SQL:

    CREATE TABLE Customers (
    CustomerEmail VARCHAR(100) PRIMARY KEY,  -- Natural Key
    CustomerName VARCHAR(100),
    ContactNumber VARCHAR(15)
);

  3. Candidate Key

    • A Candidate Key is any column or combination of columns that can uniquely identify rows in a table. A table can have multiple candidate keys, and one of them is chosen as the primary key. Any candidate key that is not chosen as the primary key is called an alternate key.
    • For example, in a customer table, both CustomerID and CustomerEmail could be candidate keys, but only one would be used as the primary key.

    Example SQL (with multiple candidate keys):

    CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,  -- Chosen as Primary Key
    CustomerEmail VARCHAR(100) UNIQUE,  -- Alternate Candidate Key
    CustomerName VARCHAR(100),
    ContactNumber VARCHAR(15)
);

  4. Difference Between Business Key and Natural Key

    • A Business Key is a type of Natural Key when the business assigns it meaning. However, in some cases, a business key might not be a good candidate for a primary key if it can change over time or is not guaranteed to be unique in the long term.

  5. Multiple Candidate Keys

    • Tables can have more than one candidate key, but you can only have one primary key at a time. Any other candidate key would be considered an alternate key.

    Example SQL (showing multiple candidate keys):

    CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,  -- Chosen Candidate Key
    InvoiceNumber VARCHAR(50) UNIQUE,  -- Alternate Candidate Key
    SaleDate DATE
);

By understanding Business Key, Natural Key, and Candidate Key, you can design databases with the proper structure, ensuring data uniqueness and integrity, and efficiently supporting business processes.

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
Microsoft SQL Server SELECT * 142 views
Microsoft SQL Server WHERE Clause 74 views
Microsoft SQL Server TOP FETCH OFFSET 60 views
Microsoft SQL Server DDL ASSIGNMENT1 - Store Management System 68 views
Microsoft SQL Server Comparison Operators 65 views
Microsoft SQL Server COUNT, SUM, AVG, MIN, MAX Functions 62 views