Oracle Knowledge Grooming (i) Theory: Basic Concepts and terminology collation

Theory: Basic Concepts and terminology collationI. Relational database

relational database is the most widely used database system, it adopts the relational data model as the organization of data, the relational data model is composed of relational structure , the operation set of relation and The integrity constraints of the relationship are composed of three parts.

Ii. Some basic terms and definitions in the relational database

1. Relational Data Model

Relational data model is based on strict mathematical concepts, consisting of a set of relationships, each relational data structure is a strictly normalized two-dimensional table, simply said the relational model is a two-dimensional table to represent the entity and the relationship between entities data model.

① Two-dimensional table :

The main storage of data in a relational database is a two-dimensional table, a two-dimensional table with strict rule requirements, each column represents a property in a relational database, each column name is a property name, each row represents a record, which corresponds to a physical entity in reality. Where the column is called a property or field, the row is called a tuple or record.

Example: User table

User number	User name	Password	Gender	Age
001	Url123	12345789	Man	34
002	adm336	Abcdefg	Woman	26

② Relationship : A relationship is generally said to refer to a table. A relationship corresponds to a table.

③ tuples : A row in a table becomes a tuple, one row corresponds to a physical entity, and a tuple is the abstraction of a physical entity.

④ Property : A column in a table is a property, and the name of each column is the property's names: A column of user names in the example table.

⑤ Field : That is, the value range of a property, the value range of the property becomes the domain of the attribute, such as: The gender value is male/female; The user number field is a three-digit integer.

⑥ keyword or code (key): Also known as a Code key, a property group in a table (multiple attributes, can also be composed of a single attribute), it can uniquely determine a tuple, the user number in the example table can uniquely determine a user (that is, the entire table does not exist in the same row of data), A relational database is usually called a master code or a primary key. Sometimes you will encounter more than one attribute can uniquely determine a tuple, all the attributes can be uniquely determined a tuple is called the candidate code, and the main code is selected in the candidate code, the candidate code contains the main code.

⑦ Component : The property value of a property in a tuple

⑧ Relationship Pattern : When a general specific written representation is expressed, a relational pattern is represented as: the relationship name (attribute 1, attribute 2, property 3 ...). ), précis-writers is: R (U) or R (A1,a2,a3 ... ）

R (U,d, Dom, F), R is the relationship name (table name), and U is the collection of properties that make up the relationship, D is the domain of the attribute, and the DOM is the set of attributes to the domain's image.

Terminology comparison

Relationship terminology	Table title
Relationship Name	Table name
Relationship	Two-dimensional table (a specific one)
Meta-group	A row in a record/table
Property	Columns in the table
Property name	Column Name
Property value	Values in a column
Component	column values for a column in a row
Non-canonical relations	Table table (Nesting of tables)

2. Characteristics of relational Data model

① relationships must be normalized: each relationship in a relational pattern must meet certain requirements.

② model Concept is single: the advantage of relational data model is that the model concept is single, regardless of the specific entity or the relationship between entities, are described and represented by a simple two-dimensional table, the data retrieval and operation results are also relational (table), its data structure is simple and clear, easy to understand and operate.

③ Operation Collection: In the relational data schema, the object and result of the operation are a set of tuples, i.e. relationships (tables)

Summarize:

The relational data pattern is based on strict mathematical concepts, and the concept of its data model is single (i.e. table). The access path of the relational data model is transparent to the user, so it has higher data independence, better security secrecy, and simplifies the development and establishment of professionals.

3. Relationship operation

The object and result of a relational operation is a collection, called a one-time-fits-all approach, rather than a record-by-data operation of a relational data model. Common data operations in a relational database include query and insert operations (insert), delete operations (delete), and modification operations . See details:

The above relational operations are logical theory operations, in the actual database system, in order to achieve these relationship operations, we can use a relational algebra and relational calculus between the language SQL (Structured Query language), SQL has rich query, data definition, data control functions. Sql*plus is used primarily in Oracle.

4, the integrity of the relationship

① Entity Integrity : A cannot be empty if the attribute (one or a group) A is the primary attribute of the relationship B. The simple explanation is that the primary key in a table cannot be null, where the primary key includes the primary key non-null for a single attribute, and the attribute group in the combined primary key cannot be null.

Entity integrity is for basic relationships, where tuples in a table correspond to physical entities in reality, and physical entities in reality are distinguishable, each independent and unique. The primary key (main attribute) is the entire unique identity, and the primary key is empty, which means that there are non-identifiable entities, which contradicts the theory, so the primary key cannot be empty.

② Referential Integrity : If the attribute (or attribute group) F is a foreign key of the basic relationship R, it corresponds to the primary key K of the basic relationship s (that is, the primary key of the relationship R reference relationship s is a foreign key, where S and r are not necessarily different relationships), The value for each tuple on F in R must be one of the following two cases: null (all properties in F are empty), equal to the primary key of a tuple in S.

Foreign keys: In the real world, there is often a connection between entities, which are described in relational databases in relation to relationships, and there is a reference between relationships and relationships: if f is one or a set of properties of the basic relationship r, but not the primary key of the R relationship, and K is the primary key of the basic relationship, if f corresponds to K, It is said that F is the foreign Key of R (Foreign key), and that the basic relation R is the reference relation (referencing Relation), and the basic relation S is referred to as the reference relation or the target relation.

Relationship S

User name (primary key)	Password	Age
GMM123	123456	35
SST789	Admin22	45

Relationship R

Id (primary key)	User name	Duties
001	GMM123	Teacher
002	SST789	Teacher

For example, in the above two tables, the user name in the relationship S is the primary key of the relationship s, and the user name attribute in R corresponds to the foreign key of the relationship R.

③ user-defined integrity : Entity integrity and referential integrity are required by any relational database, and in addition, depending on the situation and problem, additional constraints are often required, specifically defined by the user when defining the relationship.

5. Normalization theory in relational database

Function dependent: Set R (U) is a relationship pattern on the attribute set U, x, Y is a subset of u, if there is no possible relationship to R (U) r,r two tuples on the X attribute value is equal and the property value on Y is unequal, then the X function determines that y or y depends on X and is recorded as X→y.

Classification of function dependencies:

Trivial function dependence and non-trivial function dependence

Full function dependency and partial function dependency

transitive function dependencies

Paradigm Theory:

① first normal form (1NF): If each attribute value in the relationship mode R is an irreducible data item, it is said that the relational pattern satisfies the first normal pattern and is recorded as R∈1NF. (Indivisible meaning is a single attribute, the attribute value must be "atomic")

② second Paradigm (2NF): In satisfying the first paradigm (i.e., R∈1NF), and all its non-primary properties are fully functional dependent on any candidate key of R, the relationship is said to satisfy the second paradigm, namely R∈2NF.

③ third Normal form (3NF): In the premise of satisfying the R∈2NF, and all non-primary properties, none of the transfer function depends on any candidate key, R satisfies the third normal form, that is R∈3NF.

④bcnf Paradigm (BCNF): For a relational pattern R, if for each function depends on x→y, where the determinant x contains the key, then the relationship pattern R satisfies the BCNF paradigm, namely r∈bcnf.

It is generally considered that the BC Paradigm is a modification and extension of the third paradigm, and the essential meaning of bcnf is that each of these determinants is a primary key. Alternatively, there is no other nontrivial function dependency in bcnf except that the candidate key determines all of its properties or the primary key.

Paradigm in the database design occupies a large proportion, through it can optimize the database design, avoid some problems, such as: Reduce data redundancy, improve the overall organization of the database, enhance the consistency of data, increase the flexibility of database design. Therefore, the database design should be based on the actual situation, so that the relational model to meet the appropriate paradigm, so that the database design more standardized

Third, the database design

1. Entity-Relationship model (E-R)

In actual concrete design, we need to abstract real life objects into mathematical models and then transform them into relational models.

① entity: is the basic object of the E-R model and the abstraction of various transactions in the real world. The simple point is that it corresponds to the real life of the business, such as: Zhang San, John Doe specific person, can also be students of the reality of the group.

② Property (Attribute): A number of attributes of an entity, that is, an attribute of an entity. For example, in real life students have the characteristics of the student number, which translates into the learning number attribute in the relationship.

③ Contact (Relationship): There will be a variety of relationships and connections between entities, such as teachers and students have a teacher-student relationship, a language teacher may be a class of all students in the common language teacher, and for a specific student, they have only one language teacher, that is, a pair of more, and a.

One-on-one contact (1:1): For entity sets A and B, any entity, a, or a in a is contacted by up to an entity B, and vice versa, A and B.

One-to-many contact (1:N): There are N entities in each entity, a, and B in entity set A, and for each entity B in entity set B, there is at most one entity a associated with it in entity set a, and a is a pair of pairs.

Many-to-many connections (M:N): If there are N entities in each entity, a, and B in entity set A, and the M entities in each of the entities in b,a correspond to each other, then A and B are many-to-many links.

2, E-r diagram drawing

Oracle Knowledge Grooming (i) Theory: Basic Concepts and terminology collation