Relational Constructs in Database Design

This module reviews the fundamental concepts and terminology of relational database design. Even if you are an experienced database designer, working through this module will reinforce the vocabulary and conceptual framework used throughout the course. The lessons progress from the relational database model itself through the constraints that govern table structure, data integrity, and the relationships between tables.

Module Learning Objectives

After completing the lessons in this module, you should be able to:

1. List the steps in the database life cycle (DBLC)
2. Explain why an ER diagram is translated into relational notation
3. Describe the characteristics of tables and table types
4. List the rules for table columns and rows
5. Describe column domains and domains that support calculations
6. Describe and explain the purpose of null values
7. Differentiate key and descriptor columns
8. Define and identify single-field and composite primary keys
9. Describe all-key relations
10. Define and identify foreign keys
11. Define referential integrity

The relational database model improves on the restrictions of a hierarchical structure without completely abandoning the concept of data hierarchy. In a purely hierarchical model, retrieving data requires traversing the hierarchy from the top downward - to find a single employee record, a query must first locate the company, then the department, then the employee. The relational model eliminates this constraint: any table can be accessed directly without navigating through parent objects.

Consider a practical example. To find the address of a specific employee, you need only query the employee table directly. You do not need to search the entire organizational hierarchy from the company level downward. If you do not know the specific employee, you can examine all employees in a single table scan. The key is knowing what to look for - and the relational model ensures the data is there to be found.

A second advantage of the relational model is that tables can be linked together regardless of their hierarchical position. A relationship between two tables must exist to justify the link, but the structure of that relationship is not dictated by a rigid parent-child hierarchy. A table can be linked to any number of parent tables and any number of child tables simultaneously. This flexibility is what makes the relational model well-suited to representing the complex, interconnected data structures that real-world business applications require.

Many of today's businesses rely on their database systems for accurate, up-to-date information. Without those repositories of mission-critical data, most businesses are unable to perform their normal daily transactions, much less generate the summary reports that help management make strategic corporate decisions. To be useful, data in a database must be accurate, complete, and organized so that it can be retrieved when needed and in the format desired.

Well-written database application programs - whether they execute locally, run over a local area network, or feed information to a website - are fundamental to timely and accurate data retrieval. However, without a sound underlying database design, even the best application program cannot prevent problems with inaccurate and inconsistent data. The design of the database itself is the foundation on which all application logic depends.

This is why relational constraints matter. They are not optional refinements added after a database is built - they are the structural rules that make a database trustworthy. A database without properly defined constraints is a database that will eventually contain incorrect, incomplete, or contradictory data, regardless of how carefully the application code is written.

Relational constraints are rules enforced by a relational database management system (RDBMS) to ensure data integrity and consistency across tables. These constraints define the conditions that data must satisfy to be considered valid, preventing incorrect or inconsistent data from entering the database in the first place. Rather than relying on application code to enforce data rules, constraints embed those rules directly into the database schema - where they apply universally, regardless of which application or user is inserting or modifying data.

The purpose of relational constraints can be summarized in four areas:

• Data integrity and consistency - constraints ensure that every value stored in the database conforms to defined rules, preventing corruption from invalid inputs.
• Relationship maintenance - constraints preserve the logical connections between tables, ensuring that related data remains synchronized and coherent.
• Business rule enforcement - constraints translate organizational policies into database-level rules, making compliance automatic rather than dependent on developer discipline.
• Redundancy prevention - constraints prevent duplicate and contradictory data from accumulating across tables, which would degrade query reliability over time.

The following constraint types define the functional mechanisms by which relational databases enforce data integrity. Each serves a distinct purpose within the overall constraint framework.

1. Domain Constraints - ensure that values stored in a column are atomic (indivisible) and consistent with the column's defined data type. A domain constraint on a date column, for example, prevents a string value from being stored where a date is required. Domain integrity is the most fundamental layer of data validation.
2. Key Constraints (Uniqueness Constraints) - ensure that every tuple (row) in a table can be uniquely identified. A key constraint requires that the values in the designated column or combination of columns are distinct across all rows. No two rows may carry the same key value.
3. Entity Integrity Constraints - ensure that primary key columns cannot contain null values. Because a primary key is the unique identifier for a row, a null primary key would mean the row cannot be identified - which violates the fundamental premise of the relational model. Entity integrity makes every row addressable.
4. Referential Integrity Constraints - maintain consistency between related tables by ensuring that foreign key values in a child table correspond to valid primary key values in the parent table. A foreign key constraint prevents orphaned records - for example, an order record that references a customer ID which does not exist in the customer table.
5. NOT NULL - ensures that a column cannot store a null value. Applied to columns where a value is always required - such as a customer's last name or an order's creation date - this constraint guarantees that critical fields are always populated.
6. UNIQUE - prevents duplicate values in a specified column or combination of columns, while still permitting null values where appropriate. A UNIQUE constraint on an email address column, for example, ensures no two customers share the same email, but does not require every customer to have one.
7. PRIMARY KEY - combines the NOT NULL and UNIQUE constraints into a single declaration, designating the column or columns whose values uniquely identify each row in the table. A table can have only one primary key, though that key may span multiple columns (a composite primary key).
8. FOREIGN KEY - links two tables by requiring that values in the foreign key column of a child table match values in the primary key (or unique key) column of a parent table. This constraint is the mechanism by which relationships between tables are enforced at the database level.
9. CHECK - enforces domain integrity by restricting the values a column may contain to a specified range or condition. A CHECK constraint on a product price column might require that the value be greater than zero. A CHECK constraint on an order status column might restrict values to a defined set of valid status codes.
10. DEFAULT - specifies the value to be stored in a column when no explicit value is provided during an insert operation. DEFAULT constraints reduce the risk of unintended null values in columns that have a known standard value, such as a status column that should default to "active" for new records.

Together these constraints act as a protective framework ensuring that the database adheres to its intended logical structure and business rules at all times. This not only maintains data quality but also supports reliable querying and reporting, as data remains consistent and trustworthy across all related tables regardless of how it was inserted or modified.

Summary

The relational database model provides flexible, direct access to data without the traversal overhead of hierarchical structures. Tables can be linked to any number of related tables, and those relationships are governed by relational constraints that enforce integrity at the database level.

Relational constraints - domain, key, entity integrity, referential integrity, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT - form the structural foundation of a well-designed database. They are not additions to be configured after the schema is built; they are integral to the schema design itself. A database without appropriately defined constraints cannot guarantee the accuracy or consistency of its data, regardless of the quality of the application code that uses it.

The next lesson reviews the steps in the database life cycle.