Relational Model Overview
In the relational model, all data is logically organized into relation (table) structures. Each relation has its own name and consists of a set of named attributes (columns in a table). Each tuple (row in a table) contains one value for each attribute. The greatest advantage of the relational model is its simple logical structure, yet this simple structure is built upon a solid theoretical foundation.
Relations
The relational model is based on the mathematical concept of a relation, which is typically represented as a table.
- A relation is a table composed of rows and columns.
- An attribute is a named column in a relation.
- Each row in a relation is called a tuple.
- The number of attributes contained in a relation is called its degree.
- The number of tuples contained in a relation is called its cardinality.
- A domain is defined as the set of possible values for one or more attributes.
In the relational model, relations are used to store information about the objects described by the database. A relation is represented as a two-dimensional table, where each row corresponds to a single record, i.e., a tuple. Each column in the table corresponds to an attribute. Regardless of how the attributes are arranged, it remains the same relation. Domains play a very important role in the relational model. A domain must be defined for every attribute in a relation. For example, Table 1-1 shows the domain definitions for some attributes in the weather relation.
Table 1-1 Relation weather
| Attribute | Meaning | Domain Definition |
|---|---|---|
| city | City name | VARCHAR(80), 80 characters |
| temp_lo | Low temperature | NUMERIC(3,1), range 0.0~99.9 |
| temp_hi | High temperature | NUMERIC(3,1), range 0.0~99.9 |
| prcp | Precipitation | NUMERIC(5,1), range 0.0~9999.9 |
| date | Record date | Date type, format yyyy-mm-dd |
Through domains, users can define the meaning and range of values that attributes can take. Therefore, when the system performs relational operations, more information is available, helping to avoid semantically incorrect operations. For example, comparing a city name with a low temperature is not possible because they belong to different domains. Comparing or subtracting the high temperature from precipitation is meaningless, even though both attributes are defined as numeric domains (though logically, comparison or subtraction operations could still be performed).
Schema
The combination of a relation name and its set of attributes is called the schema of that relation, for example:
weather(city, temp_lo, temp_hi, prcp, date)
In the relational model, a database consists of one or more relations. This collection of relation schemas is called a database schema.
Constraints
The relational model has the ability to constrain the data stored in a database. For example, if a "not null" constraint is applied to the attribute temp_lo in the weather relation, then temp_lo cannot accept null values.
Keys and Foreign Keys
In the relational model, many constraints can be placed on the relations in a database schema, among which keys are the most basic type of constraint. A key consists of a set of attributes in a relation. By defining a key, you can ensure that no two tuples in the relation have the same values for the attribute set that defines the key. For example, using the attribute set city and date as a key on the weather relation, that is:
weather(city, temp_lo, temp_hi, prcp, date)
This ensures that a tuple for the same city on the same day can only appear once.
A foreign key is also a type of constraint, which requires that a value appearing in one relation must also appear as a key in another relation.
Relational Algebra
Relational algebra is an algebra for manipulating data in the relational model. Its atomic operands are:
- Variables representing relations.
- Constants representing finite relations.
And operators such as intersection, union, and difference. For more detailed information about relational algebra, we recommend referring to "Database Systems: The Complete Book", "SQL and Relational Theory", and "Database in Depth".