02_ relational database

relational database

A relational database (relational Database,rdb) is a database based on a relational model. Relational database system is an important database data model, not only its theory is mature, but also its application scope is much wider than the network and hierarchical database system. At present, the relational database management system has become a popular database system, a variety of implementation methods and optimization methods are more perfect. The design of relational database has a set of perfect normalization theory, which ensures that the database is designed to minimize data redundancy and various operation anomalies.

relational database Concepts

Relational database is a way of data organization using database, and it is the most popular one in modern popular data management system, and also the most efficient way of data organization. The relational database is based on a strong mathematical set theory. Is the application of set theory in the field of data organization. In a relational database, the data in the calendar is organized and managed in terms of the table (the term according to the relationship, the table should be called "relationships").

the internal structure of a relational database

A relational database consists of an association between a data table and a data table. The data table is usually a two-dimensional table consisting of rows and columns, each of which describes a particular aspect or part of the object in the database and its properties. rows in a database table are often called records or tuples . It represents one of many objects that share the same property. a column in a database table is often called a field or property. it represents the shared properties of the object stored in the corresponding database table.

It is clear from the Student information table that the data in the table is specific to the students in the school. Each record in the table represents the complete information for one student. Each field represents the student's information on one hand, thus forming a relative independence. Stand on the Student information table outside of the other data tables. You can add, delete, or modify records for this table, without affecting other data tables in the database at all.

Association of tables in a relational database

In a relational database, the association of tables is a very important component. A table's association is a connection between a data table in a database and a data table using corresponding fields to implement a data table. By using this connection, you no longer have to store the same data multiple times, and this connection is also important when querying multiple tables.

In the item daily schedule table shown, use the owner number column to connect the Project plan table with the owner table. Use the ' Salesperson Number ' column to connect the Project plan table with the marketers table. In this way, when you want to query the project owner's name by project name, you only need to tell the name of the project that the management system needs to query, and then use the "owner number" and "Salesperson number" column to correlate the "Project plan" table, "owner" table can be achieved.

Attention:

All data table names are unique during database design. Therefore, you cannot name different data tables with the same name, but columns of the same name can exist in different tables.

Relational database terminology:

A relational database is characterized by the existence of a table in which each data with the same attribute is independent. For any one table, users can add new. Delete and modify data in a table without affecting other data in the table. Here's a look at some of the underlying terminology in a relational database:

Entities (Entity)

An objective and mutually distinguishable thing is called an entity. An entity can be a collective person, a thing, an object, or an abstract concept or connection. Eg: One student, one department, one elective course, and the working relationship between teachers and faculties are all entities.

Properties (Attribute)

An attribute that an entity has is called a property. An entity can be characterized by several attributes. EG: Student entities may consist of a number, name, gender, date of birth, and faculties.

Solid type (entity type)

Entities with the same attributes are bound to have common characteristics and properties. A collection of entity names and their attribute names is used to abstract and characterize homogeneous entities, called entity types. Eg: Students (student number, name, gender, date of birth, faculty, time of entry) is an entity type.

Entities set (Entity set)

A collection of the same entity is called an entity set. EG: All students are an entity set.

Contact (Relationship)

In the real world, there is a connection between things and things, which are reflected in the information world as relationships between entities (type) and entities (types). Relationships within entities usually refer to the relationships between the attributes that make up the entity. Relationships between entities usually refer to the linkages between different entity sets.

Keys (key)

An important concept in a relational model that identifies a column or columns of rows in a relationship (a set of properties that uniquely identify an entity). Eg: The number is the code of the student entity.

Primary keyword (primary key)

It is selected as a candidate key for the unique identification of the table row, with only one primary key in a table, and the primary key being called the primary key. A primary key can consist of one field, which is called a single-field primary key or a multi-fields primary key.

Candidate Keywords (candidate key)

It is a set of properties that uniquely identifies a row in a table and does not contain extra attributes.

Common keywords (Common key)

In a relational database, a relationship is represented by a compatible or identical attribute or attribute group. If two relationships have a compatible or identical attribute or attribute group, then this attribute or attribute group is called the Common keyword of the two relationships

External keyword (foreign key)

If the public key is the primary key in a relationship, then this common keyword is called the outside keyword of the other relationship. Thus, the outside keyword indicates that the relationship between the two relations outside the keyword is also called a foreign key.

Attention:

The column names of the primary and foreign keys can be different, but they must be required to have the same set of values, that is, the data that appears in the table of the main kin must match the values in the table in which the foreign key is located.

Relational Model Integrity rules

According to relational data theory and the definition of Codd, a language must be able to handle all communication problems with the database, which is sometimes referred to as an integrated data-only language. The language is SQL in the relational database management system. SQL's use of soil is achieved through 3 operations such as data manipulation, data definition, and data management.

the integrity rules of a relational model are constraints on the data . The relational model provides 3 types of integrity Rules: Entity integrity rules. Referential integrity rules and user-defined integrity rules. The entity integrity rules and referential integrity rules are the complete WINS constraints that must be satisfied by the relationship search. Called relationship integrity rules.

Entity integrity

entity integrity refers to the primary attribute of the relationship ( part of the primary key) cannot be a null value . Entities in the real world are distinguishable, and au means that they have some kind of uniqueness identity. Accordingly, the primary key is identified as a uniqueness in the relational model, and the attributes in the primary key are NOT null values ("Unknown" or "meaningless" values). If the main attribute takes a null value, it indicates that there is an entity that is not identifiable, that is, there is an indistinguishable entity, which contradicts the real-world environment, so this entity

Must not be a complete entity.

Referential integrity

If the relationship's foreign key RI matches the primary key in the relationship R2, then each value of the foreign key must be found in the value of the primary key in the relationship R2 or is a null value.

User-defined Integrity

It is a constraint on a specific actual database. It is determined by the application environment and reflects the requirements that the data involved in a particular application must meet. The relational model provides a mechanism for defining and verifying such integrity so that it can be handled in a unified, systematic way without the application having to assume this function.

02_ relational database