*Additional Constraint Examples*

On April 12, 2014, In Relational Algebra by Admin

Views (1878)

The same constraint notation permits us to express far more than referential integrity. For instance, we can express any functional dependency as an algebraic constraint, although the notation is more awkward than the FD notation introduced in "Functional Dependencies".**Example 1 :** Let us express the FD:

for the relation

MovieStar(name, address, gender, birthdate)

as an algebraic constraint. The idea is that if we make all pairs of Moviestar tuples (t1, t2), we must not find a pair that agree in the name component and disagree in the address component. To make the pairs we use a Cartesian product, and to search for pairs that violate the FD we use a selection. We then assert the constraint by equating the result to 0.

To begin, since we are taking the product of a relation with itself, we need to rename at least one copy, in order to have names for the attributes of the product. For conciseness, let us use two new names, MS1 and MS2, to refer to the MovieStar relation. Then the FD can be expressed by the algebraic constraint:

In the above, MS1 in the product MS1 x MS2 is shorthand for the renaming:

and MS2 is a similar renaming of Moviestar.

Some domain constraints can also be expressed in relational algebra. Sometimes, a domain constraint simply requires that values for an attribute have a specific data type, such as integer or character string of length 30, so we may associate that domain with the attribute. On the other hand, sometimes a domain constraint involves particular values that we need for an attribute. If the set of acceptable values can be expressed in the language of selection conditions, then this domain constraint can be expressed in the algebraic constraint language.

**Example 2 :**Assume we wish to specify that the only legal values for the gender attribute of MovieStar are F and M. We can express this constraint algebraically by:

That is, the set of tuples in MovieStar whose gender component is equal to neither F nor M is empty.

In the end, there are some constraints that fall into none of the categories outlined in "The Modeling of Constraints", nor are they functional or multivalued dependencies. The algebraic constraint language lets us express many new kinds of constraints. We offer one example here.

**Example 3 :**Assume we wish to require that one must have a net worth of at least $10,000,000 to be the president of a movie studio. This constraint cannot be classified as a domain, single-value, or referential integrity constraint. Yet we can express it algebraically as follows. First, we need to theta-join the two relations

MovieExec (name, address, cert#, networth)

Studio (name, address, presC#)

using the condition that presC# from Studio and cert# from MovieExec are equal. That join combines pairs of tuples consisting of a studio and an executive, such that the executive is the president of the studio. If we select from this relation those tuples where the net worth is less than ten million, we have a set that, according to our constraint, must be empty. Therefore, we may express the constraint as:

An other way to express the same constraint is to compare the set of certificates that represent studio presidents with the set of certificates that represent executives with a net worth of at least $10,000,000; the former must be a subset of the latter. The containment

expresses the above idea.

### Tags

- referential integrity
- functional dependency
- tuples
- attributes
- relational algebra
- Scrolling Cursors
- Protecting Against Concurrent Updates
- Modifications by Cursor
- Cursors
- Instead-Of Triggers
- Triggers in SQL
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Maintaining Referential Integrity
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Interpreting Queries Involving Views
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Tuple Variables
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Selection in SQL
- Projection in SQL
- The Database Language SQL
- Referential Integrity Constraints
- Constraints on Relations
- Extending the Projection Operator
- Grouping
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Information Integration Via Semistructured Data
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- Nested Relations
- The Object-Relational Model
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Boyce-Codd Normal Form
- Decomposing Relations
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Functional Dependencies
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- From E/R Diagrams to Relational Designs
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Referential Integrity in E/R Diagrams
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Converting Multiway Relationships to Binary
- Attributes on Relationships
- Multiway Relationships
- Elements of the E/R Model
- Database System Implementation
- Database Programming
- Database Design
- The Query Processor
- Multimedia Data
- Relational Database Systems