Shulou(Shulou.com)06/01 Report--

This article mainly introduces the basic knowledge of the principle of database, which has a certain reference value, friends who need can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The basic knowledge points of database principle are: 1, the two-level mapping and physical and logical independence of database system; 2, the difference between control redundancy and non-control redundancy; 3, the difference between relation and file or table; 4, relational algebra; 5, database paradigm and so on.

The basic knowledge points of database principle are as follows:

Noun accumulation:

Database (Database): the "warehouse" where data is stored and provided.

Data (Data): the basic object stored in the database.

Database management system (DBMS): a layer of data management software located between the user and the operating system.

Database system (Database System): including database, DBMS, application system, database administrator (DBA)

Primary key (Primary Key): a collection of attributes or attributes that uniquely identify a record in a table.

Foreign key (Foreign Key): used to associate with another table, and the foreign key is the primary key of another table

Super Key: a superkey is a collection of attributes or attributes that can uniquely distinguish tuples

Key (candidate key): remove the redundant attributes in the superkey and still be able to distinguish between different tuples.

Schema: a description of a database, including database structure, data types, and constraints.

Instance (Instance / State): real data stored in a database at a certain time. (Instance is the materialization and instantiation of Schema at a certain time.)

Data manipulation language (DML:Data Manipulation Language): add, delete, modify and check

Data definition language (DDL:Data Definition Language): define, delete, and modify objects in a database

Data Control language (DCL:Data Control Language): used to control users' permissions on database manipulation

Data model (Data Model): abstraction of real-world data characteristics to define how data is organized and how data relate to each other

Concurrency compatibility (Union Compatibility): it is necessary to ensure that two relationships have the same number of attributes and the same fields for each attribute.

Views (VIEW): views are virtual tables, not physically stored data. Instead, it is data derived from a base table or other view. An update to the view is actually converted to an update to the actual base table.

Data Model (Data Model):

Basic concept: the abstraction of the characteristics of real-world data, which is used to define how the data is organized and the relationship between the data.

Hierarchy:

1. Conceptual Model (Conceptual): modeling data and information from the user's point of view

two。 Logic / implementation model (Logical / Implementation): hierarchical model, mesh model, relational model

3. Physical model (Physical): how data is physically stored in specific DBMS products

The three-level schema structure of the database system:

1. Internal Schema: (also known as storage mode. The description of the physical structure and storage of the data is the representation of the data within the database

two。 Conceptual schema (Conceptual Schema): (also known as global schema. ) sometimes referred to as "pattern". It is a description of the logical structure and characteristics of all data in the database.

3. External Schemas: (also known as submode or user mode. ) description of the logical structure and characteristics of local data that database users can see and use

Two-level mapping and physical and logical independence of the database system:

Two-level mapping:

1. Conceptual schema / internal schema mapping

two。 External schema / conceptual schema mapping

Physical independence of data:

The mapping between internal schema and conceptual schema provides the physical independence of the data. When the physical structure of the data changes, only the mapping between the internal schema and the conceptual schema needs to be modified.

Logical independence of data:

The mapping between conceptual schemas and external schemas provides logical independence of the data. When the overall logical structure of the data changes, you only need to modify the mapping between the external schema and the conceptual schema to ensure that the application is not affected.

Data constraints: integrity constraints

1. Domain constraints: constraints on the range of attribute values

two。 Key constraints: each relationship must have a primary key, and each primary key must be different

3. Non-null constraint: attribute value cannot be NULL

4. Entity integrity constraint: primary key value cannot be empty

5. Referential integrity constraint: the foreign key can take the NULL value, but it cannot be NULL if the foreign key is another relational primary key.

6. User-defined integrity

Integrity constraints that may be violated by various data operations

Insert operations: domain constraints, key constraints, non-empty constraints, entity integrity constraints, referential integrity constraints

Delete operations: referential integrity constraint

Update operations: domain constraints, key constraints, non-empty constraints, entity integrity constraints, referential integrity constraints

The execution order of SQL statements:

1. FROM clause assembles data from different data sources

2. The WHERE clause filters records based on the specified criteria

3. The GROUP BY clause divides the data into multiple groups

4. Use aggregation functions for calculation

5. Use the HAVING clause to filter grouping

6. Evaluate all expressions

7. Use ORDER BY to sort result sets

The difference between control redundancy (Controlled Redundancy) and non-control redundancy (Uncontrolled Redundancy):

Uncontrolled data storage redundancy can cause the following problems:

1. Repetitive work when updating data

two。 Waste of space

3. The data may be inconsistent.

Therefore, ideally, we should design a database without redundancy, but sometimes we need to improve the efficiency of queries, so we introduce control redundancy (Controlled Redundancy).

For example:

We redundant store the student name and course number in the GRADE_ report table, because we need to query the student name and course number at the same time when querying the score.

The difference between a Relation and a file (files) or a table (tables):

The relationship looks like a two-dimensional table

The domain of a relationship (the range of values of an attribute) is a set of atomic values (values that cannot be re-divided)

Tuples in the relationship must be different

Relational algebra:

Five basic operations: Union, difference, Cartesian product, selection, projection

Relational algebra interpreter: relational algebra interpreter (analog relational algebra)

Types of internal connections:

1. Equivalent connection

two。 Unequal connection

3. Natural connection

SQL statement:

Replication of table structure (excluding relationships between tables)

SELECT * INTO COPY_DEPARTMENT FROM DEPARTMENT WHERE 1: 0

Ternary predicate logic:

1. TRUE

2. FALSE

3. UNKNOWN

It is determined to be true only if the comparison result is TRUE, e.g. (TRUE intersects UNKNOWN as UNKNOWN, this tuple will not appear in the result)

The basic process of database application system design:

Phases of Database Design andImplementation Process (the basic process of database design)

Phase 1:Requirements Collections and Analysis (requirements Collection and Analysis)

Phase 2:Conceptual Database Design (Conceptual structure Design)

Phase 3:Choice of a DBMS (choose the appropriate DBMS)

Phase 4:Data Model Mapping (Logical Database Design) (logical structure design)

Phase 5:Physical Database Design (physical structure Design)

Phase 6:Database System Implementation (database implementation)

Phase 7:Database System Operation and Maintenance (database operation and maintenance)

ER diagram symbol explanation:

To map an ER model to a logical model:

1. Mapping strong entity type

two。 Mapping weak entity type

3. Mapping 1: 1 binary connection type

4. Mapping 1: n binary connection type

5. Mapping M: n binary connection type

6. Map multi-valued attributes

7. Mapping N-ary relation

Database paradigm:

1NF (First Normal Form): entity E is said to satisfy the first normal form if and only if all fields contain only atomic values, that is, each component is an indivisible data item.

2NF (Second Normal Form): if and only if entity E satisfies the first normal form and each non-key attribute is completely dependent on the primary key, the second normal form is satisfied.

3NF (Third Normal Form): if and only if entity E is the second normal form (2NF) and there is no non-primary attribute transitive dependency in E, the third normal form is satisfied.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the basic knowledge of database principles. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you have any problems. detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.