What Is a Relational Model?

The relationship is actually the state or content of the relationship model at a certain moment. That is, the relational pattern is type, and the relation is its value. The relationship model is static and stable, while the relationship is dynamic and constantly changing over time, because the relationship operation is constantly updating the data in the database. In practice, however, the relational model and relational system are often referred to as relations, and readers can distinguish them from the context.

Chinese name: Relational model
Foreign name: Relational model

Application: database
Belong to: Logical model

History of relationship model invention

Mesh databases and hierarchical databases have solved the problem of data concentration and sharing, but there is still a lot of deficiencies in the level of data independence and abstraction. When users access these two databases, they still need to clarify the data storage structure and indicate the access path. The later emerged relational databases solved these problems better. Relational database theory emerged from the late 1960s to the early 1970s. The relational data model provides the characteristics and functional requirements of relational operations, but does not give specific syntax requirements for the language of the DBMS. The operation of the relational database is highly non-procedural, users do not need to point out special access paths, and the path selection is done by the optimization mechanism of the DBMS.

In 1970, IBM researcher Dr. EFCodd published the article "Relationship Models of Large Shared Data Banks", which introduced the concept of relational models, discussed the paradigm theory and 12 criteria for measuring relational systems. The functional correlation of the data defines the third normal form of the relationship, thus pioneering the study of the relational method of the database and the theory of data normalization. He won the Turing Award in 1981 for this.

Codd later published several articles, laying the foundation for relational databases. The relational model has a strict mathematical foundation, a high level of abstraction, and is simple and clear, easy to understand and use. However, at the time, some people thought that the relational model was an idealized data model. It was unrealistic to implement a DBMS. In particular, they were worried that the performance of the relational database would be unacceptable. . In order to promote understanding of the issue, ACM led a seminar in 1974, during which a debate was held between the support and opposition relation databases led by Codd and Bachman, respectively. This famous debate has driven the development of relational databases, making them the mainstream of modern database products.

Since then, many people have turned their research direction to relational methods, and relational database systems have gradually appeared. ^[1]

Relational model definition

The relational data model was developed based on the relational concept in set theory. In the relationship model, whether it is an entity or a relationship between entities is represented by a single structure type-the relationship. Relationships in actual relational databases are also called tables. A relational database consists of several tables.

A relational model is a data model that represents entities and the relationships between entities in the form of a two-dimensional table.

Relational Model Basic Terminology

There are thirteen basic concepts and basic terms of the relational model. They are:

(1) Relation: A relation corresponds to a two-dimensional table, and the two-dimensional table is the relation name.

(2) Tuple: A row in a two-dimensional table, called a tuple.
(3) Attribute: A column in a two-dimensional table, called an attribute. The number of attributes is called the element or degree of the relationship. The value of a column is called an attribute value;

(4) (Domain): The value range of the attribute value is the value range.

(5) Component: the attribute value of the column corresponding to each row, that is, one attribute value in the tuple. ^[2]

(6) Relation mode: The row definition in the two-dimensional table, that is, the description of the relationship is called the relationship mode. Generally expressed as (attribute 1, attribute 2, ..., attribute n), such as the teacher's relationship model can be represented as a teacher (teacher number, name, gender, age, title, department).

(7) Key (code): If an attribute or set of attributes that uniquely identifies an entity exists in a relationship, it is called an entity's key, that is, two tuples in any relationship state of the relationship, in the attribute The combinations of values are different.

(8) Candidate key (candidate code): If the value of an attribute in the relationship can uniquely identify a tuple, if any attribute cannot be removed in a key of the relationship, otherwise it is not the key of the relationship, then The specified candidate key is the candidate key or candidate code for the relationship.

For example, the "student number" or "book identification number" in the following student table can uniquely identify a tuple, then the "student number" and "book identification number" can uniquely identify a tuple, then the "student number" and " "Book ID" can be used as a candidate key for student relations.

student ID	Name	gender	age	Library card number	Department
S3001	Zhang Ming	male	twenty two	B20050101	foreign language
S3002	Li Jing	Female	twenty one	B20050102	foreign language
S4001	Zhao Li	Female	twenty one	B20050301	management

In the course selection table, only the attribute groups "student number" and "course number" can uniquely identify a tuple, and the candidate key is (student number, course number).

student ID	Course number
S3001	C1
S3001	C2
S3002	C1
S4001	C3

(8) Primary key (primary code): Specify a tuple to uniquely identify the relationship among several candidate keys of a relationship, then the designated candidate key is called a primary key, or primary key or keyword for short , The main code. Every relationship has one and only one primary key, usually with a small combination of attributes as the primary key. For example, in the student table, "student number" is selected as the basis for data operation, and "student number" is the primary key. In the course selection table, the primary key is (student number, course number).

(9) Primary attribute and non-primary attribute: Attributes contained in any candidate key in the relationship are called primary attributes, and attributes not contained in any candidate key are non-primary attributes.

(10) Full key or full code: A collection of all attributes in a relational schema.

(11) Foreign key or foreign code: Although an attribute in a relationship is not the primary key of the relationship, or is only the primary key, but it is the primary key of another relationship, it is called a foreign key or foreign code.

(12) Super key or super code: If an attribute is removed from a key in a relation, it is still the key of the relation, then such key is called the super key or super code of the relation.

(13) Reference relationship and referenced relationship: Two relationships that are related to each other by foreign keys and can be transformed into each other.

Relational Model 2D Table

In a relational model , fields are called attributes, field values are called attribute values, and record types are called relational models . The relational schema name is R. Records are called tuples, and collections of tuples are called relationships or instances. Generally use uppercase letters A, B, C, ... to represent a single attribute, and lowercase letters to represent the attribute value. The number of attributes in a relationship is called a "tuple", and the number of tuples is called a "base." The example has a relational element number of 5 and a cardinality of 2. It is sometimes called a relation, a tuple is a row, and an attribute is a column.

Relational model key

Keys, also known as codes, consist of one or more attributes and are divided into the following types:

a. Superkey: If an attribute is removed from a key in a relationship and it is still the key of the relationship, such a key becomes a superkey.

b. Candidate keys: Superkeys without redundant attributes are called candidate keys. That is, in the candidate key, if you want to delete the attribute, it is not a super key.

c. Primary key: A candidate key selected by the user as the tuple identifier is called the primary key. In general, the key is the primary key.

Definition and properties of relationship models

A relation is a collection of tuples with K (K> = 1).

A relationship is a normalized form. It has the following restrictions:

a. Each attribute value in a relationship is indecomposable.

b. The same tuple is not allowed in the relationship.

c. The order between tuples is not considered in the relationship.

d. The attributes in the tuple are also out of order.

Relational mode, relational submode, and storage mode

In the relational model , the conceptual pattern is a collection of relational patterns, the outer pattern is a collection of relational sub-patterns, and the inner pattern is a collection of storage patterns.

Relationship model

The relational pattern is actually the record type, including: schema name, attribute name, value domain name, and the primary key of the schema. He does not involve a description of physical storage, but a description of the characteristics of the data.

2. Relational Sub-pattern

A sub-pattern is a description of the part of the data used by the user. In addition to pointing out user data, the correspondence between patterns and sub-patterns should also be indicated.

3. Storage mode

The basic organization of relational storage is a file, and tuples are records in a file. Because the relational schema has keys, storing a relation can be done using a hash method or an index method.

Three types of integrity rules for relational models

Entity integrity rule

Tuples in this rule need not have null values on the attributes that make up the primary key. If there is a null value, then the primary key value cannot play the role of uniquely identifying the tuple.

2. referential integrity rules

If the attribute set K is the primary key of the relational pattern R1, and K is also the foreign key of the relational pattern R2, then in the relation of R2, the value of K can only allow two kinds of possibilities, or null, or equal to one of the relations in R1 Primary key value.

Pay attention when using:

a. The foreign key and the corresponding primary key can have different names, as long as they are defined on the same value range.

b. R1 and R2 can also be the same relationship model, which indicates the relationship between attributes.

c. Whether foreign key values are allowed to be empty depends on the specific problem.

3. User-defined integrity rules

This is a constraint on specific data, depending on the application environment.

Formal definition of the relational model

One or three components: data structure, data manipulation, and integrity rules.

1. The basic data structure of a relational model is a relation.

2. Relational operations are divided into relational algebra and relational calculus.

3. Three types of integrity rules for relational models .

Relational algebra

Relational database data operations are divided into two types of query and update. Query statements are used for various retrieval operations, and update operations are used for operations such as insert, delete, and modify.

Relational query languages are divided into two categories based on their theoretical foundation:

1. Relational algebra language: The query operation is a DML language based on set operations.

2. Relational calculus language: The query operation is a DML language based on predicate calculus.

Five basic operations of relational algebra

Relational algebra is a collection of advanced operations whose relation is the object of operation. A relationship is defined as a collection of tuples with the same number of tuples. The elements in a set are tuples, and operations in relational algebra can be divided into two categories:

Traditional set operations: union, difference, intersection, Cartesian product.

Extended relational operations: projection, selection, join, and natural join, except.

1. and

Two relations R and S are set to have the same relation pattern. The combination of R and S is a set of tuples belonging to R and S, which is denoted as RS.

Note: The arity of R and S is the same.

Poor

Two relations R and S are set to have the same relation pattern. The difference between R and S is a set consisting of tuples that belong to R but not S, and are denoted as R-S.

Note: The arity of R and S is the same.

3. Cartesian product

Let the arity of the relations R and S be r and s, respectively. The Cartesian product of R and S is a set of (r + s) tuples. The first r components (attribute values) of each tuple are from a tuple of R, and the last s components are from a tuple of S. , Denoted as R × S.

If R has M tuples and S has n tuples, then R × S has m × n tuples.

4. Select

Finding all tuples from a relationship that satisfy a given condition is called selection. The condition is given by a logical expression, and a tuple whose value is true is selected. This is an operation from the perspective of the row, that is, tuples are extracted horizontally. The result obtained through the selection operation can form a new relationship. The relationship mode remains the same, but the number of tuples is less than or equal to the number of tuples in the original relationship. He is a subset of the original relationship.

Let it be: F (R) {t? T belongs to RF (t) = true}

5. Projection

A new relationship made up of several attributes from a relationship is called a projection. This is done from a column perspective. A new relationship can be obtained through the projection operation. The relationship often contains fewer attributes than the original relationship, or the order of the attributes is different. If the new relationship contains duplicate tuples, delete the duplicate tuples.

Let it be: A (R) = {t [A]? T belongs to R} A is the attribute column in R.

For example: 3,1 (R)

Four Combination Operations of Relational Algebra

1. turn over

The intersection of the relations R and S is a set consisting of tuples belonging to R and S, which is denoted as RS. R and S requirements are defined on the same relationship model.

RS {t? T belongs to Rt belongs to S}, and R and S have the same arity.

2. Join

There are two types of joins: joins and F joins ( is an arithmetic comparator and F is a formula).

join

join is a tuple whose attribute value satisfies a certain operation from the Cartesian product of the relations R and S, which is written as:

R? × i j? S, where i and j are the numbers of the i-th and j-th attributes in the relations R and S, respectively.

R? × i j? S i (r + j) (R × S)

If is the equal sign "=", the join operation is called "equivalence join".

F connection

The F join operation selects a tuple whose attribute value satisfies a certain formula F from the Cartesian product of the relations R and S, and is written as:

R? × F? S, where F is a formula of the form F1F2 ... Fn, each f is a formula of the form i j, and i and j are the i-th in the relationship R and S, respectively The sequence number of the jth attribute.

3. Natural connection

The natural connection of the two relations R and S is represented by R? ×? S. The specific calculation process is as follows:

Calculate R × S

Let the common attributes of R and S be A1, ..., Ak, and select those tuples in R × S that satisfy R .A1 = S.A1, ..., R.Ak = S.Ak

Remove these columns of S.A1, ..., S.Ak.

If there are no common attributes in the two relations, then the natural join is transformed into a Cartesian product operation.

4. Division

Given the relations R (X, Y) and S (Y, Z), where X, Y, Z are attribute groups. Y in R and Y in S can have different attribute names, but they must come from the same domain set. The division operation of R and S yields a new relationship P (X), where P is the projection of the tuple in R on the X attribute that meets the following conditions: the tuple on the image set X of component value x contains S on Y Collection of projections.

Relational Algebra Expressions and Application Examples

In relational algebra operations, the expressions that are compounded by five basic operations through a finite number of times are called relational algebra expressions. The result of this expression is still a relationship. Can use relational algebra expressions to represent various data query operations.

Example: Let's set up three relationships in the teaching library:

Student relations S (S #, SNAME, AGE, SEX)

Learning Relationship SC (S #, C #, GRADE)

Course Relationship C (C #, CNAME, TEACHER)

The following uses a relational algebra expression to express each query

1. Retrieve the student ID and grade of the student whose course number is C2.

2. Retrieve the student ID and name of the student whose course number is C2.

3. Retrieve the student ID and name of the elective course named MATHS.

4. Retrieve the student ID of the elective course number C2 or C4.

5. Search for the student ID of at least the elective course number C2 or C4.

6. Retrieve the names of students who are not studying C2.

7. Retrieve the names of the students who studied the entire course.

1. S #, GRADE (C # = 'C2' (SC))

Or 1,3 (2 = 'C2'

Relational model

A relationship model is a description of a relationship.

R (U, D, dom, F)

R relationship name, U constitute the attribute name set of the relationship, D attribute group U comes from the domain, the dom attribute to the domain's image collection, and F attribute data dependency relationship set. For example: the tutor and graduate student come from the same domain-people, take different attribute names, and define the mapping of attributes to the domain in the model, that is, which domain they are from

dom (SUPERVISOR-PERSON) = dom (POSTGRADUATE-PERSON) = PERSON

The relationship model can usually be abbreviated as:

R (U) or R (A1, A2, ..., An)

R relationship name, A1, A2, ..., An attribute name, Note: The mapping of domain name and attribute to domain is often directly explained as the type and length of the attribute.

A relational database system is a database system that supports a relational model.

The characteristics of the relational model are: a single concept, standardized, and expressed in a two-dimensional table.

Introduction to relational models

The relational model was proposed by EFCodd in 1970.

Compared with hierarchical and mesh models, it has the following characteristics:

1. Simple data structure (two-dimensional table)

2. A solid theoretical foundation.

a. Relational operation theory

b. Relational pattern design theory

The basic assumption of the relational model is that all data are represented as mathematical relationships, that is, a subset of the Cartesian product of n sets. The reasoning about this data is based on the predicate logic of two values (that is, no NULL). To proceed, this means that there are only two possible evaluations of each proposition: either true or false. The data is manipulated by means of relational calculus and relational algebra. A relational model is a data model that uses a two-dimensional table structure to express entity types and the relationships between entities.

Relational models allow designers to build a consistent model of information through a standardized refinement of the database. The access plan and other implementation and operation details are handled by the DBMS engine and should not be reflected in the logical model. This is in contrast to the common practice of SQL DBMS, where performance tuning often requires changes to the logical model.

The basic building blocks of relationships are domains or data types. Tuples are ordered multisets of attributes, and attributes are ordered pairs of fields and values. A relation variable (relvar) is a collection of ordered pairs (order pairs) of domains and names, and it serves as the header of the relationship. A relationship is a collection of tuples. Although these relational concepts are mathematically defined, they can be loosely mapped to traditional database concepts. Tables are the accepted visual representation of relationships; tuples are similar to the concept of rows .

The basic principle of a relational model is the principle of information: all information is represented as data values in a relationship. Therefore, relationship variables are unrelated to each other at design time; instead, the designer uses the same domain in multiple relationship variables. If one attribute depends on another attribute, this dependency is enforced by referential integrity.

Relationship model advantages

(1) Single data structure

In the relational model, whether it is an entity or a connection between entities is represented by a relationship, and the relationship corresponds to a two-dimensional data table, the data structure is simple and clear.

(2) Relations are standardized and based on strict theory

The basic norms that make up a relationship require that each attribute in the relationship be indivisible, and that the relationship be based on a strict mathematical concept with a solid theoretical foundation.

(3) Simple concept and easy operation

The biggest advantage of the relational model is simplicity, which is easy for users to understand and master. A relation is a two-dimensional table, and users can operate the database with a simple query language.

Relationship model composition

Relational model relational data structure

Single data structure-relationship

Real-world entities and the various relationships between entities are represented by relationships. From a user perspective, the logical structure of the data in the relationship model is a two-dimensional table.

Relation model relation operation set

Commonly used relation operations include query operations and insert, delete, and modify operations. Among them, the expressiveness of query operations is the most important, including: selection, projection, connection, division, union, intersection, and difference.

Relational operation capabilities in relational models are often expressed early in the algebraic or logical methods, which are called relational algebra and relational calculus, respectively. Relational algebra is a way to express query requirements with algebraic operations on relations; relational calculus is a way to express query requirements with predicates. There is also a language that is between relational algebra and relational calculus called Structured Query Language, or SQL for short.

Data integrity for relational model relationships

Including: domain integrity, entity integrity, referential integrity, and user-defined integrity.

Domain integrity: refers to the value range of the attribute. For example, the gender value should be male or female.

Entity Integrity rule: If attribute A is the main attribute of the basic relationship R, attribute A cannot take a null value. For example: In the curriculum (course number, course name, teacher, weekly hours, remarks), the "course number" attribute is the primary key, then the "course number" cannot take the same value, nor can it take an empty value.

Referential integrity rules: If the attribute (or attribute group) F is the foreign key of the basic relationship R, it corresponds to the primary key Ks of the basic relationship S (the relationships R and S are not necessarily different relationships). The value of each tuple on attribute F must be:

1. Or take a null value (each attribute value in F is null);

2. Or equal to the primary key value of a tuple in S.

For example: employee (employee number, name, gender, department number, supervisor, salary, commission)

Department (department number, name, location)

Where the employee number is the primary key of the "employee" relationship, the department number is the foreign key, and the department number in the "department" relationship is the primary key, the attribute of the department number of each tuple in the employee relationship can only take the following two types of values:

Category 1: Null value, indicating that the department has not been assigned to the employee;

Type 2: Non-null, but the value must be the department number of a tuple in the department relationship, which means that the employee cannot be assigned to a non-existent department, that is, there must be one in the reference relationship "department" A tuple whose primary key value is equal to the foreign key value in the reference relationship Employee.

Domain integrity, entity integrity, and referential integrity are the integrity constraints that must be met in a relational model. As long as it is a relational database system, it should support domain integrity, entity integrity, and referential integrity. In addition, different relational database systems often require special constraints based on their application environment. User-defined integrity is a constraint on some specific relational databases. For example: course selection table (course number, student number, grade), when defining the relationship course selection table, we can define the constraint that the attribute of grade must be greater than or equal to 0.