National Computer technology and software Professional technical qualification (level) exam "Software Evaluator"-Summary of Exam content (iii) Database basics

3. Database Basics 3.1 Database concepts and Patterns 3.1.1 Three modes of the database

Database three-level mode: External mode, mode (conceptual mode), internal mode

(1) Concept mode:

Also known as the "logical Mode", which corresponds to maintaining the conceptual level of database personnel

It is by the Database Designer synthesizes all user's data and constructs the global logical structure according to the unified viewpoint, is the entire data structure and the logic in the database overall description, is the public data view of all users.

(2) External mode:

Also referred to as "sub-mode", which corresponds to the user level of the user or programmer

Outer mode is a subset of schema exports that contains some of the data that a particular user is allowed to use in a pattern.

(3) Internal mode:

Also known as "storage Mode", which corresponds to the physical level of the system programmer

Internal mode is the lowest-level data description in the database, it describes how the data is stored on the storage medium and the physical structure.

(4) Map of level three mode

For a database system, the physical database is objective, it is the base of the database, and the conceptual level database is just a logical and abstract description of the physical database; The user-level database is the interface between the user and the database, which is a subset of the conceptual level database.

The database management system (DBMS) is responsible for the storage of the data, thus simplifying the application

Security control mechanism of 3.1.2 database system

The security mechanisms of a database include the following:

(1) User identification and identification

(2) Access control

(3) Authorization and recycling

(4) Database roles

(5) View mechanism

(6) Audit

(7) Data encryption

The integrity of the data includes:

(1) Correctness of the data: the input value is the same as the data table corresponding field type.

(2) The validity of data: refers to the database of theoretical values to meet the practical application of the data segment.

(3) Consistency of data: the same data should be used by different users.

3.2 Relationship mode and function dependent 3.2.1 function dependency

has a relationship mode R, where x and Y are subsets, and for R's value R, when the values of the attribute components corresponding to x in any of the two tuples are equal, then the values of those attribute components that correspond to Y in U and V are also equal, the "x function determines y" or "Y depends on X", which is recorded as X->y

Classification of function dependencies:

(1) Full function dependency

(2) Partial function dependency

(3) Transfer function dependency

3.2.2 Entity Relationship (ER model)

The Entity Relationship diagram (ER model) refers to the basic structure of the 3 basic concepts of entities, relationships, and attributes to summarize data, thus describing the conceptual pattern of static data structures.

It provides a method of entities, attributes, and connections to describe the conceptual model of the real world.

The basic elements constituting the ER diagram are entity type, attribute, and relation.

The ER model representation method:

(1) Solid Type (entity): represented by a rectangle

(2) attribute (Attribute): represented by an ellipse

(3) Contact (relationship): in diamond notation

3.2.3 Relational Operations

There are 4 types of relational operators:

3.2.3.1 the traditional set operation

(1) and (UNION)

(2) Poor (difference)

(3) Delivery (intersction)

(4) Generalized Cartesian product (Extended Cartesian product)

3.2.3.2-Specific relational operations

(1) Select (Selection)

(2) projection (Projection)

(3) connection (join)

(4) Except (division)

3.2.3.3 comparison Operators

(1) greater than (>)

(2) greater than or equal to (≥)

(3) less than ()

(4) Less than or equal to (≤)

(5) equals (=)

(6) Not equal to (≠)

3.2.3.4 logical operators

(1) and (A)

(2) or (V)

(3) non-(-)

3.3 Relational model and SQL statement 3.3.1 relational database system

Relational database system is a database system supporting relational model

The relational model consists of the following:

(1) Relational data structure

(2) Relationship operation combined

(3) 3 class integrity constraints for relationships

Includes entity integrity, referential integrity, user-defined integrity

3.3.2 Structured Query Language

3.3.2.1 content of Structured Query language

The following 4 sections are included:

(1) Data definition language (DDL): Create, DROP, ALTER

(2) Data Manipulation language (DML): such as INSERT, UPDATE, DELETE

(3) Data Query Language (DQL): such as Select

(4) Data Control Language (DCL): such as Grant, REVOKE, COMMIT, ROLLBACK

3.3.2.2 Grammar Rules

(1) SELECT statement

• Select all columns: SELECT * FROM Test table

• Select some columns and specify their display order

• Change column headings

• Delete duplicate rows

• Limit the number of rows returned

(2) From statement

• Table name as Alias

• Table name aliases

(3) WHERE statement

• Comparison operators:>, >=, =, <, <=, <>,!>,!<

• Range Operator: between ... And ..., not between ... And ...

• List operator: In, not in

• Pattern match: like, not like

• Null-value judgment: Is NULL, not is NULL

• Logical operators: not, and, or

(4) ORDER by statement

3.3.2.3 Common SQL intrinsic functions

(1) Count: Returns the number of rows

(2) SUM: Returns the numeric value of the specified column or expression and

(3) AVG: Returns the numeric mean of the specified column or expression

(4) Min: Returns the numeric minimum value of the specified column or expression

(5) Max: Returns the numeric maximum value of the specified column or expression

3.3.3 Federated queries and connection queries 3.3.3.1 federated queries

Select_statement

Union[all] Selectstatement

[Union[all] selectstatement] [... N

3.3.3.2 Connection Query

(1) Inner connection

• Equivalent connections: use (=) to query results in join conditions

• Unequal connections: Use in connection conditions (>, <, >=, <=,!<,!>) to query results

• Natural connection: use (=) to query results in join conditions, but delete duplicate columns

(2) External connection

(3) Cross connection

3.4 Related concepts of indexing

The role of the index is reflected in:

(1) The uniqueness of each row of data in a database can be ensured by creating a unique epitome

(2) can greatly speed up the retrieval speed of data

(3) Can speed up the connection between table and table

(4) When checking data using grouping and sorting clauses, you can also significantly reduce the time to group and sort in a query

(5) Improve system performance with an optimized stealth device

Increase the disadvantage of the index

(1) Creating and maintaining indexes is time consuming

(2) Index to occupy physical space

(3) Indexes should be maintained dynamically when the data in the table is added, deleted, and modified

As a general rule: You should create an index on the following columns:

(1) On columns that are often searched

(2) on the column as the primary key

(3) On columns that are often used in connections

(4) On columns that often need to be searched by scope

(5) On columns that are often required to be sorted

(6) On columns that are frequently used in the WHERE clause

Columns that should not create an index include the following features

(1) Columns that are seldom used or referenced in the query

(2) Only very little data is worth the list

(3) A column defined as a text,image,bit data type

(4) When the modification performance is far greater than the retrieval performance

National Computer technology and software Professional technical qualification (proficiency) test "software Evaluator"-Summary of Exam content (iii) database basics