Defining Atomic Attributes and Domains

Identify essential data properties and enforce strict data types to ensure integrity at the field level.

In this lesson, you will learn how to enforce data integrity at the individual field level by breaking information into its smallest meaningful parts and strictly defining its valid range of values.

The Power of Atomicity

In the relational model, an attribute is atomic if it cannot be further subdivided into smaller, meaningful pieces. Imagine you have a column called customer_name that stores "John Q. Public." If you need to sort by last name or filter by middle initial, your database will struggle because the data is "bunched up."

By defining attributes as atomic, you force the data into a structure that is easy to query and maintain. Instead of one customer_name field, you would define three distinct attributes: first_name, middle_initial, and last_name. This ensures that every piece of data has a clear, singular purpose.

Defining Domains

Once you identify your atomic attributes, you must assign each one a domain. A domain is essentially the "rulebook" for a column. It specifies the allowed data type (like INTEGER, VARCHAR, or DATE) and any logical constraints.

By enforcing strict domains, you prevent "dirty data" from entering your system. For example, if you define a domain for a product_price as a positive decimal, the database will reject any attempt to input a negative number or a text string.

Practical Implementation

When you define a table schema, you are essentially setting these rules in stone:

CREATE TABLE products (
    product_id INT PRIMARY KEY,        -- Domain: Integer
    product_name VARCHAR(100),         -- Domain: String (Atomic: Name only)
    price DECIMAL(10, 2) CHECK (price >= 0) -- Domain: Positive Decimal
);

In this schema, price is an atomic attribute with a specific domain. By adding the CHECK constraint, we ensure the data remains logically consistent with business reality. If we had stored price as a text field, we could accidentally save "Ten dollars," which would break any future mathematical calculations. By being strict at the schema level, we guarantee the reliability of our data downstream.

Key Takeaway: Atomicity ensures your data is granular enough to be useful, while strict domains ensure that every value is valid, predictable, and ready for accurate analysis.