Database principle and application-relational data model of data model

2018-02-04 23:03:28

I. Relational data Model

The basic data structure of the relational model is only one: Table (relation). in the relational data model, the relationships of entities and entities in the real world are expressed in tables , while the hierarchical data model uses records to represent entities, PCR represents relationships, and the mesh data types are represented by records to represent relationships, It is a big difference that the relational data model uses a table to unify the two.

Characteristics of the relational model:

• Based on the knowledge of set theory, there is a higher level of abstraction
• Block out the underlying implementation algorithm, easy to understand
• Introduction of Relational algebra system
• Introducing a Structured Query language
• Soft link, a soft link, replaces the pointer by establishing a table

1. Attributes (Attributes) and domains (domain)

In the real world, the description of a thing, often described by some of its characteristics, these types are called attributes, the value range of the property is called the domain.

In the traditional relational data model, there is also a restriction on the domain, that is, all domains are atomic data , that is, only the basic data types, the table can not appear in the case of the table. null values are allowed. This is called a paradigm .

2. Relationship (Relation) and tuple (tuple)

A real-world entity or association can create a table or multiple tables.

A relationship can be seen as a multivariate relationship defined on all its domains, which is called the pattern of relationships , and N is the number of attributes, also known as the purpose of the relationship.

An instance of a relationship can be defined like this:

3. Primary key (Primary key)

A primary key is a set of property sets. A primary key (set) can uniquely determine a tuple, and any subset of it cannot do this. If the latter is not met, we call it a super key. For example, the ID can uniquely determine a person, {Id,age} can also uniquely determine a tuple, but its subset ID can also uniquely determine a tuple, so this is called a super-key.

If there is more than one primary key in a table, you can choose one as the primary key and the other as the alternate key (alternate key).

If the primary key consists of all attributes, it is called a full key (all key).

4. Foreign key (Foreign key) and referential integrity

A foreign key is a set of properties. A foreign key (set) is used to refer to tuples in other tables, and this property is the primary key in the referenced table. is actually a soft connection.

Referential integrity: If you use a foreign key as a logical pointer, the tuple it points to must be present and not empty.

5. Other integrity constraints

1) Domain integrity constraints (Domain integrity constraint): range to satisfy constraints

2) Entity integrity constraint (Entity integrity constraint): The value of the primary key is not allowed to be empty

Second, relational algebra

Relational algebra is the theoretical basis of relational data model.

The table used in the following example:

• Projection

The projection operation is to pick the columns that are needed in the table and remove the unwanted columns. The projection operation should be de-duplicated (removing duplicate tuples) because it is meaningless if all the attribute values of the two-tuple are equal. However, in practical applications, it is generally not automatically de-weighed.

• Selection

Select all tuples that meet the criteria.

• Union, intersection, set-difference

Collection of the and -out operations, these operations that perform the collection need to meet and compatibility conditions : The same number of properties and the same type of property.

• Cross-product

The Cartesian product is the concatenation of two tables, the process is 22 combinations between tuples, if you encounter the case of the name of the attribute, you need to rename the property.

• Join

The simple Cartesian product is meaningless, and what we really want is join, which is the connection operation.

1) Conditional connection (Condition join): First Cartesian product, then select operation (sometimes called a theta-join)

2) equivalent connection (equi-join): C in conditional connection has only equal conditions and will satisfy the filter of equality of property values.

3) Natural Connection (Natural join): It is most commonly used to make equivalent connections on all common properties on the Cartesian product.

• Division

Although not a basic operation, it is often used. The main application is to answer similar questions: Find sailors who has reserved all boats.

• Outer Joins

An outer join is an extension of a natural connection. You first make a natural connection, and then you leave the null value of the non-conforming condition. The outer connection is divided into left outer connection, right outer connection and full outer connection.

• Outer Unions

External and operation is to merge two unsatisfied and compatible tables. The set of properties of the result is the set of properties of the two tables, and the tuple is the element of each table plus the non-existent attribute tween value.

Third, the relationship calculus

In addition to using relational algebra to represent relational operations, predicate calculus can be used to express the operation of a relationship, which is called relational calculus. Using relational algebra to express relational operations requires an indication of the order of operations, so relational algebra is a procedural language . It is a non-procedural language to use relational calculus to represent the operation of a relationship, as long as it shows the results that need to be obtained without indicating the process of the operation. The current SQL language is based on relational calculus.

The relationship calculus can be divided into two types as the variables are used, and the essence of the two is the same:

1) Tuple relationship calculus (tuple relational calculus,trc)

2) domain relationship calculus (domains relational CALCULUS,DRC)

• Domain Relational calculus

The so-called domain relational calculus is that variables are the properties of a table, and the result is a table of tuples that make predicate logic true.

In addition, it is worth mentioning that when using predicate calculus to prevent unsafe queries, unsafe queries refer to the result tuple is an infinite number of queries. For example:

is to query all the elements that are not in the sailors table, where the query result is an infinite number, is an unsafe query.

• Tuple relationship Calculus

The tuple relationship calculus is essentially the same as the domain relational calculus, except that there is a little difference in the choice of variables, and the tuple relationship calculus, as the name implies, is the tuple.

Iv. Diagram of ER

E-r ChartAlso known as the entity-contact graph (Entity Relationship Diagram), provides methods for representing entity types, attributes, and connections to describe the conceptual model of the real world. It is an effective way to describe the conceptual model of real-world relations. is a way to represent a conceptual relationship model. Use "Rectangle box" to represent the entity type, the name of the entity is indicated in the rectangle box, the attribute of the entity is represented by "ellipse", and "solid line" is used to connect it with the "entity type" of the corresponding relation; The type (1:1,1:n or m:n) that is connected to the entity type, respectively, and on the "solid segment" side label.

1) Cardinality ratio constraint

2) Participation constraints

The minimum maximum number of times each entity participates in a contact can be further constrained as a participation constraint. can be given in parentheses.

V. Disadvantages of traditional data model

We refer to hierarchical, mesh, and relational data models as traditional data models, with the drawbacks and limitations of traditional data models:

• Application for OLTP (on-line Transaction processing) online transaction processing, is a kind of structured data processing
• Record-based, not very good for users and applications
• Lack of basic semantic information, it is not possible to visually see the links between the various tables

Vi. Other data models

1. logic-based data Model, logic-based models: supports recursive queries and improves the reasoning ability of DBMS;
2. Temporal data model, temporal models: Timeline information
3. Spatial data Model: a better representation of the relationship of space objects
4. XML Data Model

Database principle and application-relational data model of data model