Summary of knowledge points of database principle

Noun accumulation:

Database: "Treasury" for storing and providing data

Data: The base 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: Contains database, DBMS, application system, database Administrator (DBA)

Primary KEY (Primary key): A collection of properties or properties that are used to uniquely identify a record in a table.

Foreign key (Foreign key): Used to associate with another table, foreign key is also a primary key of a table

Super key: A Super key is a collection of properties or properties that uniquely differentiate a tuple

Key (Candidate key): Removes extra attributes from the super-key, and still ensures that different tuples are distinguished.

Schema: A descriptive narrative of a database that contains a database structure, data types, and constraints.

Instance (instance/state): The real data stored at a time in a database. (instance is a detailed, instanced schema at a given moment.)

Data manipulation language (Dml:data manipulation Language): Delete and change

Data definition language (ddl:data definition Language): defining, deleting, and altering objects in a database

Data Control Language (Dcl:data control Language): Used to control user permissions on database manipulation

Data model: The abstraction of real-world data features to define how data is organized and how the data is related

and compatibility (Union compatibility): Two relationships with and compatibility need to be guaranteed to have the same number of attributes, and for each attribute has the same domain

View: A view is a virtual table, not a physical stored data. Instead, the data is derived from a base table or other view. The update to the view is actually converted to an update to the actual base table.

Data Model:

Basic concepts: The abstraction of real-world data features to define how data is organized and how the data is related.

Level:

1. Conceptual model (conceptual): Modeling data and information by user's point of view

2. Logic/Implementation Model (LOGICAL/IMPLEMENTATION): Hierarchical model, mesh model, relational model

3. Physical model (physical): How data is physically stored in a detailed DBMS product

The three-level schema structure of the database system:

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

2. Conceptual mode (conceptual schema): (also known as the Global schema.) ) sometimes referred to as "mode". is a descriptive narrative of the logical structure and characteristics of all data in the database

3. External mode (External schemas): (also known as sub-mode or user mode.) descriptive narration of the logical structure and characteristics of local data that can be seen and used by database users

Two-level mapping of database systems and physical and logical independence:

Level Two mapping:

1. Conceptual mode/Internal mode mapping

2. External mode/Conceptual mode mapping

Physical independence of the data:

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

Logical independence of data:

The mapping between conceptual and external schemas provides logical independence of the data. When the overall logical structure of the data changes, it is only necessary to change the mapping between the various external and conceptual patterns to ensure that the application is unaffected.

Constraints on data: integrity constraints

1. Domain constraints: Constraints on the value range of a property

2. Key constraints: Each relationship must have a primary key, and each primary key has to be not the same

3. Non-NULL Constraint: Property value cannot be null

4. Entity integrity constraint: Primary key value cannot be null

5. The integrity constraint: The foreign key can take a null value, but it cannot be null if the foreign key has a relationship primary key.

6. User-defined Integrity

Integrity constraints that may be violated by various data operations

Insert operations: Domain constraints, key constraints, non-null constraints, Entity integrity constraints, and the participation integrity constraints

Delete operation: License integrity constraint

Update operations: Domain constraints, key constraints, non-null constraints, Entity integrity constraints, and the participation integrity constraints

SQL statement Run Order:

1. The FROM clause assembles data from different data sources

2. Where clause filters records based on specified criteria

3. GROUP BY clause divides data into multiple groupings

4. Using aggregation functions for calculations

5. Filtering groupings using the HAVING clause

6. Calculate all the expressions

7. Use order by to sort the result set

The difference between control redundancy (controlled redundancy) and non-control redundancy (uncontrolled redundancy):

Uncontrolled data storage redundancy can cause problems such as the following:

1. Repetitive work when updating data

2. Wasted Space

3. Data may be inconsistent

Therefore, ideally, we should design a database without redundancy, but sometimes we need to improve the efficiency of the query, so we introduce control redundancy (controlled redundancy)

Like what:

We store the student name and course number redundancy in the Grade_report table, because we need to check the student's name and the course number at the same time.

Relationship (Relation) differs from file (files) or table (tables):

The relationship looks like a two-dimensional table

The domain of the relationship (the value range of the property) is a set of atomic values (non-re-cut values)

Tuples in a relationship must be different

Relational algebra:

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

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

Types of internal connections:

1. Equivalent connection

2. Unequal connections

3. Natural connection

SQL statements:

Replication of table structures (excluding relationships between tables)

SELECT * into copy_department from DEPARTMENT WHERE 1=0;

Three-valued predicate logic:

1. TRUE

2. FALSE

3. UNKNOWN

It is only true that there is a comparison of the results, e.g. (true and unknown are unknown, this tuple will not be present in the results)

The basic process of database application system design:

Phases of database design andimplementation process

Phase 1:requirements Collections and analysis (requirements collection and analyses)

Phase 2:conceptual Database Design (conceptual structure)

Phase 3:choice of a DBMS (select the appropriate DBMS)

Phase 4:data Model Mapping (Logical) (Logical Structure design)

Phase 5:physical Database Design (physical structure)

Phase 6:database System Implementation (Database implementation)

Phase 7:database System operation and maintenance (Database execution and maintenance)

ER diagram symbol Interpretation:

To map an ER model into a logical model:

1. Map strong entity type

2. Map weak entity type

3. Mapping 1:12 Yuan Contact type

4. Mapping 1:n two Yuan contact type

5. Mapping m:n two Yuan contact type

6. Mapping multi-valued properties

7. Map N-ary Contact

Database paradigm:

1NF (First Normal Form): If and only if all fields include only atomic values, that is, each component is a data item that is not re-divided, then the entity E is said to satisfy the primary paradigm

2NF (Second Normal Form): satisfies the second normal mode when and only if the entity E satisfies the first paradigm, and each non-key attribute completely relies on the primary key

3NF (Third Normal Form): satisfies the third paradigm when and only if the entity E is the second normal (2NF) and no non-primary attribute transitive dependency in E

Summary of knowledge points of database principle