Key Concepts of The Relational Data Model and Relational Model Constraints

In this article, we shall consider key concepts of the relational data model and relational database constraints.

First, let us consider terms in the relational data model.

Terms in the Relational Data Model

1. Relational model: a collection of relations.
2. Relation: table
3. Tuple: row
4. Attribute: column
5. Domain: a set of atomic values
6. Atomic values: single values
7. Degree of a relation: number of attributes in a relation
8. Cardinality: number of tuples in a relation.
9. Relation schema: describes the relation by specifying relation name, list of attributes, and degree of the relation.

For example, a relation schema for student:

student(id, name, phone, address, age, gpa) 

student is a relation name. It has six attributes.

10. Relation state: relational instance at a particular moment in time (a set of tuples)

11. Relational database schema: contains a set of relational schemas and integrity constraints such as entity integrity constraint, referential integrity constraint, key and domain constraint.

Data Definition Language (DDL) helps us to describe the relational database schema.

12. Relational database state: a set of relational schema states at a particular moment in time.

13. Relational model: represents a collection of relational schemas and constraints.

Tuples in a relation are not ordered

A relation is a set of tuples. Set has no orders. Therefore tuples in a relation are not ordered. It will be identical: attributes and values are maintained. Relation represents data at a logical level.

Duplicate tuples are not allowed in a relation

Because it violates the key constraint. We can not identify each tuple uniquely in a relation if we have duplicate tuples.

Difference between a key and a superkey

Superkey is an attribute or set of attributes which uniquely identifies each tuple in a relation.

Key is the minimal superkey: the proper subset of a key can not have any superkey.

For example, in a student table, we have a superkey {id}. It can not be further divided. So, {id} is a key. If we combine id attribute with any other attribute will form superkey, For example, {id, name}. The proper subset of {id, name} will give us {id} and {name}. {id} is a superkey. Hence {id, name} is not a key.

We have superkeys A = {name, id}, B = {name,age}. The proper subset of superkey A is {name}, {id}. id is a superkey. So, superkey A is not a key. The proper subset of B is {name}, {age}. name, age both are not superkeys. So B is a key.

One of the candidate keys of a relation is assigned to be the primary key

If a relation has more than a key then each of them is called as candidate keys. Since candidate keys uniquely identify each tuple in a relation, we can make one of them as primary key. It is better to choose a primary key with a single attribute or minimum number of attributes.

Characteristics of relations that make them different from ordinary tables and files

Relation does not care about the order of tuples while ordinary files and tables have order among the records.

NULL values in relations

NULL values in relations may represent value unknown or value exists but not available and to specify that this attribute does not apply to this tuple.

1.Value unknown and not available

For example, student table has homeNumber attribute in which we need to enter each student’s home phone number. If we do not have a student’s number we can put it as NULL.

2.Attribute does not apply to this tuple

For example, if we have officeNumber attribute for the student_lecturer table. When inserting student data, officeNumber does not apply to student. Therefore, we can put NULL there.

Relational Model Constraints

We have three relational model constraints: inherent model-based constraints (implicit constraints), schema-based constraints (explicit constraints) and application-based constraints (business rules).

1. Inherent model-based constraints

For example, a relation can not have duplicate tuples.

2. Schema-based constraints

We specify constraints directly to the relational schemas using DDL (Data Definition Language). Schema-based constraints include four constraints.

They are domain constraints, key constraints, constraints on NULL, entity integrity constraints, and referential integrity constraints.

Referential integrity constraints are specified for two relations while others are for single relations.

I. Domain constraints

Domain constraints specify that each tuple must contain atomic values.

II. Key constraints

uniqueness constraint is specified by superkey: two different tuples in a relation can not have the same value for superkey. Every relation, we’ll have at least one superkey: a set of all the attributes.

Because of superkey’s uniqueness constraint, it uniquely identifies each tuple in a relation.

A key/ super key should maintain its uniqueness when we insert new tuples in a relation. For example, we can not take name attribute as a key in a student relation. Because over time, there will be a situation of more than a student having the same name.

III. Constraints on NULL

We can specify whether an attribute can have NULL or not using this constraint. For example, if we do not want a NULL value for the student’s name then we can specify and constraint it using NOT NULL.

IV. Entity integrity constraint

This constraint specifies that a primary key can not contain NULL values. Primary keys are used to identify each tuple uniquely. If more than a tuple contain NULL as their primary key then it is difficult to identify those tuples uniquely.

V. Referential integrity constraint

The referential integrity constraint is specified between two relations. It helps us to maintain consistency among the tuples in those two relations.

It references a tuple in one relation to an existing tuple in another relation via foreign key.

Child table: table which contains the foreign key.

3. Application-based constraints (business rules)

We specify constraints to the relational schemas using application programs.

For example, in a student table, we can specify our business rules such as students should not have age more than 19. Student can not have a phone number more than 10 digits.

NOTE: Data dependencies are also considered as constraints. It contains functional and multivalued dependencies. These two are used in normalization. Normalization helps us to maintain the database from redundancy, and insertion, update, deletion anomalies.

Conclusion

We have seen some of the main concepts of the relational data model and relational model constraints.

I followed Fundamentals of Database Systems (6th Edition) book by Ramez Elmasri and Shamkant B. Navathe.

I strongly encourage you to read this book.

Thank you!