Attributes-Entities   «Prev  Next»

Lesson 10 Constraints
ObjectiveList Entity and Attribute Constraints

List Entity and Attribute Constraints

Constraints are rules imposed upon certain elements in a database to preserve data integrity[1]. Entity and attribute constraints, of course, are rules imposed upon entities and attributes. Several constraints were mentioned in this module, and are summarized below.

Question: How do constraints imposed upon elements of a database preserve data integrity?
Data integrity is a crucial aspect of database management, as it ensures that information remains accurate, consistent, and reliable throughout its lifecycle. Constraints are rules applied to elements within a database to maintain data integrity. These constraints can be enforced at various levels, such as table, column, or relationship, and act as a safeguard against erroneous data entry or modification. In this article, we will discuss the primary types of constraints and their role in preserving data integrity.
  1. Primary Key Constraints: A primary key is a unique identifier for a record within a table, ensuring that no two rows have identical data. This constraint guarantees data consistency and accuracy, as it prevents duplicate entries and facilitates easy referencing of records. Primary keys are essential for establishing relationships between tables, which further enhances data integrity.
  2. Foreign Key Constraints: A foreign key constraint enforces referential integrity by linking two tables through a common field. This constraint ensures that any foreign key value in a referencing table corresponds to a valid primary key value in the referenced table. By maintaining referential integrity, foreign key constraints prevent the creation of orphaned records, which could lead to inconsistencies in data relationships.
  3. Unique Constraints: Unique constraints guarantee that no two rows within a table contain duplicate data for specified columns. This constraint is useful when a column, or a combination of columns, must maintain uniqueness, but is not suitable as a primary key. By enforcing uniqueness, these constraints contribute to data consistency and prevent redundancy in the database.
  4. Check Constraints: Check constraints validate the data entered into a column by verifying that it meets a specific condition or set of conditions. These constraints help maintain data accuracy by ensuring that only valid data is stored within the database. For example, a check constraint could ensure that the value for an employee's age falls within a specified range or that a product's price is always a positive number.
  5. Not Null Constraints: A not null constraint enforces that a specified column cannot contain a null value. This constraint is essential for maintaining data consistency and accuracy, as it ensures that critical information is always present within a record. By prohibiting null values in key fields, not null constraints contribute to the overall integrity of the database.
  6. Default Constraints: Default constraints provide a predefined value for a column when no value is explicitly entered during data insertion. These constraints help maintain data consistency by ensuring that a standard value is present when necessary, reducing the likelihood of missing or incorrect data.

Constraints are a vital component in preserving data integrity within a database. By enforcing rules on primary keys, foreign keys, unique values, check conditions, not null values, and default values, constraints prevent the entry of erroneous or inconsistent data. As a result, these constraints contribute to the accuracy, consistency, and reliability of information stored within a database, facilitating informed decision-making and efficient data management.

Entity constraints

The following two constraints were identified for entities:
  1. Every entity in a database must have a different name.
  2. Every entity must have an entity identifier ( key attribute).

Attribute constraints

The following two constraints were identified for attributes:
  1. Every attribute must have a specific domain type ( “domain constraint”).
  2. Every attribute must be single-valued.

As the course progresses, other constraints will be identified and explained.
The next lesson concludes the module.

Domains

A very common type of attribute constraint is a set of values that shows the possible values an attribute can have. Such a set is called a domain. Very common domains are, for example:
  1. Yesno: Yes, No
  2. Gender: Male, Female, Unknown
  3. Weekday: Sun, Mon, Tue, Wed, Thu, Fri, Sat
In a conceptual data model you can recognize these as entities with (usually) only two attributes:
Code and Description. These domain entities are referred to frequently but do not have any 'many' relationships of their own . Typically, you would know all the values before the system is built. The number of values is normally low. Often you would deliver such a system with non-empty code tables.
An alternative model for the (sometimes many) code entities is a more generic, two entity approach:
  1. CODE and
  2. CODE TYPE

The conceptual data model has the advantage of fewer relationships per entity as well as easy to understand entities.
The two entity approach has obviously fewer entities and therefore will lead to fewer tables.
Domains that have a large number of values, such as all positive integers up to a particular value, are usually not modeled. You should list and describe such a constraint in a separate document.
[1] Data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist. Problems with data integrity occur when a value in one table that is supposed to relate to a value in another cannot because the second value either has been deleted or was never entered.