What are the basic knowledge points of database principles?

Basic knowledge points on database principles include: 1. Two-level mapping and physical and logical independence of the database system; 2. The difference between controlled redundancy and non-controlled redundancy; 3. The difference between relationships and files or tables ; 4. Relational algebra; 5. Database paradigm, etc.

##More related free learning recommendations: mysql tutorial(Video)

The basic knowledge points of database principles are:

Noun accumulation:

Database (Database): the "warehouse" that stores and provides data

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

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

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

Primary Key: used to uniquely identify a record in the table An attribute or collection of attributes.

Foreign Key: Used to associate with another table. The foreign key is the primary key of another table.

Super Key: A super key can uniquely distinguish Attributes of tuples or collections of attributes

keys (candidate keys): redundant attributes in super keys are removed, and different tuples can still be distinguished.

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

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

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 the database

Data Control Language (DCL: Data Control Language): Used to control user permissions to manipulate the database

Data Model (Data Model): An abstraction of real-world data characteristics, used to define how data is organized and how the relationships between data are

Union Compatibility (Union Compatibility): Two relationships must have the same attributes to be compatible. Number, and the same domain for each attribute

View (VIEW): A view is a virtual table, not physically stored data. Rather, it is data derived from underlying tables or other views. Updates to the view are actually translated into updates to the actual base tables.

Data Model:

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

Level:

1. Conceptual model (Conceptual): Model data and information from the user’s perspective

2. Logical / Implementation model (Logical / Implementation) : Hierarchical model, network model,

Relational model

3. Physical model (Physical): The physical storage method of data in specific DBMS products

Database The three-level schema structure of the system:

1. Internal Schema: (also called storage schema.) Description of the physical structure and storage method of data, It is the way data is represented inside the database

2. Conceptual Schema: (Also called global schema.) Sometimes referred to as "schema". It is a

description of the logical structure and characteristics of all data in the database

3. External Schema

s

): (also called sub-schema or user schema. )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

2.External schema/conceptual schema mapping

Physical independence of data:

The mapping between the internal schema and the 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 the conceptual schema and the external schema provides the logical independence of the data. When the overall logical structure of the data changes, only the mapping between each external schema and the conceptual schema needs to be modified to ensure that the application is not affected.

Data constraints: integrity constraints

1. Domain constraints: constraints on attribute value range

2. Key constraints: each 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 null

5. Referential integrity constraints: The foreign key can take a NULL value, but if the foreign key is the primary key of another relationship, it cannot be NULL.

6. User-defined integrity

Various data operations may violate's integrity constraints

Insertion operations: domain constraints, key constraints, Non-null constraints, entity integrity constraints, referential integrity constraints

Delete operations: referential integrity constraints

Update operations: domain constraints, key constraints, non-null constraints, entity integrity constraints, Referential integrity constraints

SQL statement execution sequence:

1. FROM clause assembles data from different data sources

2. WHERE clause Filter records based on specified conditions

3. GROUP BY clause divides data into multiple groups

4. Use aggregate functions for calculation

5. Use HAVING Clause filter grouping

6. Calculate all expressions

7. Use ORDER BY to sort the result set

Controlled Redundancy and The difference between Uncontrolled Redundancy:

Uncontrolled data storage redundancy will cause the following problems:

1. Duplicate work when updating data

2. Waste of space

3. Data may be inconsistent

So, ideally, we should design a database without redundancy, but sometimes we need to improve the query Efficiency, so we introduced Controlled Redundancy

For example:

We redundantly store student names and course numbers in the GRADE_REPORT table, because when querying scores we need to query them at the same time Student name and course number.

The difference between Relation and files or tables:

Relationship looks like a two-dimensional table

Relationship The domain (the value range of the attribute) is a set of atomic values ​​(non-redivisible values)

The tuples in the relationship must be different

Relational algebra:

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

Relational algebra interpreter: Relational algebra interpreter (simulating relational algebra)

Inner join Types:

1. Equivalent join

2. Unequal join

3. Natural join

SQL statement:

Copy of table structure (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 determined to be true if the comparison result is TRUE, e.g. ( The intersection of TRUE and UNKNOWN is UNKNOWN, and this tuple will not appear in the result)

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:

Steps to map ER model into logical model:

1. Mapping strong entity type

2. Mapping weak entity type

3. Mapping 1: 1 binary relationship type

4. Mapping 1: N binary relationship type

5. Mapping M : N-binary relationship

6. Mapping multi-valued attributes

7. Mapping N-ary relationship

Database paradigm:

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

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

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

The above is the detailed content of What are the basic knowledge points of database principles?. For more information, please follow other related articles on the PHP Chinese website!