This is the 28th day of my participation in the August Challenge

A relationship corresponds to a table in a database. A relationship is an abstraction of the concept of a table. It can be thought of as a data structure in which attributes (columns) define the range of values of its fields, and tuples of specific values of attributes correspond to rows in a table, i.e. a record.

The column name, type, and integrity constraints form the Schema of the relationship, namely the entity type of the relationship, and the type of the relationship, also known as metadata. A set of data that satisfies a pattern is also called an instance, that is, a set of data rows or entities.

An introduction to concepts in relational databases

E.f. Codd of IBM proposed the relational data model in 1970. From his paper “A Relational Model of Data for Large Shared Data Banks”, and later proposed the concept of Relational algebra and Relational calculus. These form the most important foundation of a relational database.

Relational data structure

Relational model is mainly divided into two aspects: relational data structure and relational integrity constraints.

This article focuses on relational data structures.

Relation

The concept of a relationship in a relational database can be thought of as a single data structure.

A relationship is a data structure. Entities in the real world and relationships between entities can be represented by a relationship.

Student entities, for example, can be represented by relations; Teacher entity, can be expressed by relation; A relationship can also be expressed as a relationship in which a student attends a particular teacher’s class.

A relationship corresponds to a table in a database. A relationship is an abstraction of the concept of a table.

Relationships can be thought of as tables represented by two-dimensional arrays. Tables are composed of column data structures and row entity data.

Entity

A record in a data table is an entity.

Objects that exist objectively and can be distinguished from each other are called entities, which can be concrete people, things, things or abstract concepts.

Attributes

A property of an entity is called a property. An entity can have several attributes.

Corresponds to a Column in a table.

Value (Value)

The specific content value of an attribute corresponding to an entity.

Domain (Domain)

The concept of a domain is rarely encountered, and therefore most of the time unfamiliar.

A domain is a collection of values of the same data type.

Simply put, a domain is a range of values for attributes, columns (of this type).

Such as:

  • The integer
  • The set of real Numbers
  • An integer between a certain value range (for example, the age attribute of a specified person, which ranges from 0 to 120).
  • A collection of strings of specified length
  • {‘ male ‘, ‘female ‘} (field of “gender”)

Cartesian Product

  • The cartesian product

The Cartesian product comes from a new space constructed by crossing the attribute values of multiple fields.

The space of all arbitrary values in all fields, is the Cartesian product. This corresponds to a Cross JOIN in an SQL statement.

Any combination of all field values. The Cartesian product can be thought of as the “domain” of relations.

The result of a Cross JOIN is also called a Cartesian product.

Given a set of fields D1, D2… , Dn, allowing some of these fields to be the same. D1, D2… , the Cartesian product space of Dn is: D1×D2×… ×Dn = {(d1, d2… Dn) | di∈ di, I = 1,2… , n}

  • Cardinal number

The size of a Cartesian space is expressed in cardinal numbers.

If DiD_iDi (I = 1,2… , n) is a finite set whose cardinality is mim_imi (I = 1,2… N), then D1×D2×… * D_2 * DnD_1… * D2 * D_nD1… The cardinality of the Cartesian product M is the product of the sizes of all fields:


M = ∏ i = 1 n m i M = \prod_{i=1}^n m_i

Or:

  • Cartesian product example

D1= Set of students {Zhang Da, Wang Er}

D2= Set of majors {computer majors, information majors}

The Cartesian product of D1 and D2 is:

D1×D2×D3 = {(Zhang Da, computer major), (Zhang Da, information major), (Wang Er, computer major), (Wang Er, information major)}

The cardinal number is: 2×2=4.

About CROSS JOIN

A cross join returns the Cartesian product of all rows of two tables. The number of rows in the result set returned is equal to the number of eligible rows in the first table multiplied by the number of eligible rows in the second table.

Select * from table_name where table_name = 1; select * from table_name where table_name = 1; select * from table_name where table_name = 1;

CREATE TABLE C1(
  Id int primary key,
  MyCharts varchar(10));CREATE TABLE C2(
  Id int primary key,
  Num int not null,
  OtherCharts varchar(10));insert into C1 values(1.'flute'), (2.'CARDS'), (3.'er product');

insert into C2 values(1.3.'flute'), (2.2.'CARDS'), (3.1.'er product');

SELECT * FROM C1 CROSS JOIN C2;
Copy the code

The query results are as follows:

Id MyCharts Id Num OtherCharts 1 Flute 1 3 Flute 2 Card 1 3 Flute 1 flute 2 2 Card 2 Card 2 2 Card 1 Flute 3 card 2 Card 3 1 flute 3 card 3 1 flute 3 card 3 3 1 productCopy the code

CROSS connections do not require an ON condition.

Code (Key)

The set of attributes that uniquely identify an entity is called a code, or candidate code. An entity may have multiple codes, and one of them is selected as the primary code (i.e. the primary key in the table)

Entity Type

With entity name and attribute name set, used to abstract and represent the same kind of entity, called entity type.

The translation of personal sense entity is easy to make people not understand semantically. Translated as “entity type,” it’s easy to understand, and it does mean entity type.

Entity Set

A collection of entities of the same type is called an entity set.

Relationship

The idea is to show the connections within and between things in the world.

Can be divided into:

  • Relationships within entities: Relationships between attributes that make up an entity.
  • Relationships between entities: Relationships between different sets of entities.

Physical relationships involve one-to-one (1:1), one-to-many (1:m), and many-to-many (m: N).

The concept of relations, tuples, codes, etc. derived from the Cartesian product

Relation

D1 (D2)… * D_2 * DnD_1… * D2 * D_nD1… The subset ×Dn (Cartesian product) is called in the fields D1, D2… , DnD_1, D_2… , D_nD1, D2… , the relationship on Dn is expressed as:


R ( D 1 . D 2 . … . D n ) R (D_1, D_2… , D_n)

R: name of the relationship n: Degree of the relationship, that is, the number of columns.

Tuples

Each element in the relationship (d1, d2… Dn) (d_1, d_2… , d_n) (d1, d2… Dn), the cartesian product combination of each individual field value, called an N-tuple or tuple for short, usually denoted by t.

Corresponds to a row in the table.

attribute

The different columns in a relationship are called attributes, and each Attribute has a name. A column corresponds to a field.

N order relationships must have n attributes.

code

  • Candidate Key

A set of attributes in a relationship is called a candidate code if its value uniquely identifies a tuple. Also called code.

The simplest case: the candidate code contains only one attribute.

  • All-key

The most extreme case: All attribute groups of the relational schema are candidate codes for the relational schema, called all-keys.

Primary key

If a relationship has multiple candidate codes, select one of the Primary keys.

The main properties

Each attribute of candidate code is called Prime attribute.

Attributes that are not included in any candidate code are called non-prime attributes or non-key attributes.

Unit relation, binary relation, multivariate relation

When n=1, that is, there is only one attribute, the relation is called Unary relation or Unary relation

When n=2, i.e., there are two attributes, the relation is called Binary relation.

If there are n attributes, the relationship is n.

Relation Schema

What are relational patterns

The relational schema, which corresponds to the table schema, is the type (the type of the relationship), mainly the column name and type.

The relational schema is a type, and the relationship is a value (the value of that type), understood from this concept.

  • Relationship patterns are descriptions of relationships
    • Structure of a tuple set (structure of a set of columns)
      • attribute
      • The domain from which the property comes
      • Mapping relationships between properties and domains
    • Integrity constraint

Formal representation of relational patterns

The relational pattern can be formally expressed as:


R ( U . D . D O M . F ) R(U, D, DOM, F)
  • R relationship name
  • U The collection of attribute names that make up the relationship
  • D U the domain from which the property comes
  • A collection of images of DOM attributes to a domain
  • F Set of data dependencies between attributes

Relationship Model and “Relation” of Relationship

The relationship between type and value

Relational mode: describes relationships, which are static and stable

Relationship: Is the state or content of the relationship pattern at a point in time and is dynamic and changing over time. Changes in the data in the table.

Relational patterns and relationships can often be referred to loosely as relationships.

Relational database

The collection of all relationships in a given application domain constitutes a relational database.

A relational database is a collection of relationships (for a specific scenario or requirement, business), that is, a collection of tables.

reference

Main reference from “Introduction to Database System (Basic)”