section I, Related Concepts 1. Data: A basic object stored in a database and a symbolic record describing things. 2. Database: Databases are a collection of large amounts of data that are stored in a computer, organized, and shareable over a long period of time. 3. DBMS: A database management system, a layer of data management software located between the user and the operating system for the scientific organization, storage and management of data, and efficient access to and maintenance of data. 4. DBS: Database system, which refers to the introduction of databases in computer systems, is generally composed of databases, database management systems, application Systems, database Administrators (DBAs). 5. Data model: is a tool used to abstract, represent and deal with the data and information in the real world, is the simulation of the real world, is the core and foundation of the database system, and its constituent elements are structure, data operation and integrality constraints. 6. Conceptual model: Also known as information Model, is based on the user's point of view of data and information modeling, mainly for database design. 7. Logical model: The data is modeled according to the viewpoint of the computer system and used for DBMS implementation. 8. Physical model: is the lowest level of data abstraction, describes the data within the system representation and access methods, on the disk or tape storage methods and access method, is oriented to the computer system. 9. Entities and attributes: objectively and mutually distinguishable things are called entities. An attribute that an entity has is called a property. 10.e-r diagram: The entity-Relationship diagram, which is used to describe the real-world objects and their interrelationships, is the main tool of database conceptual model design. 11. Relationship mode: From the user's point of view, the relationship pattern consists of a set of relationships, and the data structure of each relationship is a normalized two-dimensional table. 12. Type/value: Type is a description of the structure and properties of a class of data; A value is a specific assignment of a type, an instance of a type. 13. Database Schema: is a description of the logical structure of the entire data in the database (name, type, value range, etc.) and characteristics (the connection between data and the security and completeness requirements of the data). 14. Database three-level system architecture: External mode, mode, and internal mode. 15. In-database mode: Also known as storage mode, is a description of the physical structure and storage method of the database, which is the representation of the data inside the database. A database has only one internal mode. 16. Out-of-database mode: Also known as sub-schema or user mode, it is a description of the logical structure and characteristics of local data that the database user can see and use, and is the data view of the database user. is typically a subset of patterns. A database can have multiple external modes. 17. Two-level image of the database: External mode/mode image, Mode/internal mode image. Second, the key points of knowledge 1. The database system is composed of database, database management system, application system and database administrator. 2. The components of the data model are: data structures, operations, integrity constraints. 3. The relationship between the entities is divided into a pair of one or one-to-many and many pairsThree more types. 4. Common data models include: relationships, hierarchies, meshes, object-oriented, object-relational mappings, and so on. 5. The integrity constraints of the relational model include: Entity integrity, referential integrity, and user-defined integrity. 6. The meaning and function of the database level three model and the two level image are expounded. The database level three pattern reflects the three levels of abstraction of the data: the schema is a description of the logical structure and characteristics of the entire data in the database. Internal mode, also known as storage mode, is a description of the physical structure and storage method of the database. External mode, also known as sub-mode or user mode, is a description of the logical structure and characteristics of local data related to a particular database user. The database level three pattern implements the connection and transformation of these three levels of abstraction through a two-level image within the DBMS. The external mode is oriented to the application, through the external mode/pattern image and the logical mode to establish the connection, realizes the data logical independence. A one-to-one mapping between model/internal mode mappings and internal patterns to achieve physical independence of data. Section II, related concepts 1. Primary key: A property or attribute group that uniquely identifies a tuple is called a key or candidate key for a relationship. If there are multiple candidate keys for a relationship, choose one as the primary key (Primary key). 2. Foreign key: If one or a group of properties of a relationship reference (reference) the primary key of another relationship, this or the set of attributes is called a foreign code or foreign key (Foreign key). 3. Relational database: a database established according to a relational model is called a relational database. It is a collection of all relationships in an application domain. 4. Relationship mode: Simply put, the relationship pattern is the definition of the relationship, including the attribute composition of the relationship, the data type of each attribute, the dependency between the attributes, the tuple semantics and the integrity constraints. The relationship is the state or content of the relationship pattern at a certain moment, the relationship model is the type, the relationship is the value, the relationship model is static and stable, and the relationship is dynamic and changing with time, because the relational operation is constantly updating the data in the database. 5.. Entity integrity: Used to identify the uniqueness of an entity. It requires that the basic relationship must have a primary key that identifies the uniqueness of the tuple, that the primary key cannot be empty, and that duplicate values cannot be taken. 6. Referential integrity: Used to maintain referential relationships between entities. It requires that the foreign key of a relationship be either empty or take the primary key value corresponding to the referenced relationship, that is, the foreign key value must be a value that already exists in the primary key. 7. User-defined integrity: It is the semantic constraints that must be met for a particular application's data. Includes three cases of non-null, unique, and Boolean conditional constraints. Ii. important points of knowledge 1. Relational database language is divided into three categories: relational algebra, relational calculus and Structured Query language. 2. The 5 basic operations of the relationship are selection, projection, and, difference, Cartesian product. 3. The relationship pattern is a description of the relationship, and the five tuple is formally represented as: R (u,d,dom,f), where the r--relationship name U--The set of attribute names that make up the relationship d--the domain dom--property from which the property is derived from the attribute group, the collection of data dependencies between the domain's image collection f--attributes 4. The Cartesian product, selection and projection operations are as follows Section IIII. Related Concepts 1. SQL: The short name of a structured query language is the standard language for relational databases. SQL is a common and powerful relational database language, which is the standard interface for relational data access and the basis of interoperability between different database systems. Set data query, data manipulation, data definition, and data control functions in one. 2. Data definition: Data definition functions include definition of schema, table definition, view, and index. 3. Nested query: A query that nests one query block in the condition of another query block's WHERE clause or having phrase. Ii. important points of knowledge 1. SQL data definition statements manipulate objects: schemas, tables, views, and indexes. 2. The command verb for the SQL data definition statement is: CREATE, DROP, and ALTER. 3. Indexes in RDBMS are generally implemented with B + tree or HASH. 4. Indexes can be divided into unique indexes, non-unique indexes, and clustered indexes of three types6.SQL The general format for creating table statements isCREATE table < table name > (< column name > < data type >[< column-level integrity constraints >][,< Column name > < data type >[< column-level integrity constraints;]] ... [,< table-level integrity constraints >]) where < data type > can be a variety of data types supported by the database system, including length and precision. Column-level integrity constraints are for integrity constraints on a single column (this column), including PRIMARY KEY, REFERENCES table name (column name), UNIQUE, not NULL, and so on. A table-level integrity constraint can be a constraint based on multiple columns in a table, including PRIMARY key (column list), FOREIGN key REFERENCES table name (column name), and so on. 7. The general format of SQL CREATE INDEX statement is create [unique] [CLUSTER] Index < index name >on < table name > (< column list >), where unique: represents the creation of a unique index, the default is a non-unique index; CLUSTER: Indicates the creation of a clustered index, the default is the nonclustered index;< column Name list;: one or more comma-separated column names, followed by ASC or DESC for each column name, ascending/descending, by default. Multiple columns are sorted by multilevel. 8. The general format of SQL query statements is SELECT [All|distinct] < arithmetic expression list > from < table name or view list >[WHERE < conditional expressions 1> [GROUP by < property list 1 > [Having < conditional expression 2 >] [ORDER by < attribute list 2> [Asc|desc]]; where all/distinct: All is the default, which lists all query result records, including duplicate records. DISTINCT lists only one entry for duplicate records. Arithmetic expression list: One or more comma-delimited arithmetic expressions that consist of constants (including numbers and strings), column names, functions, and arithmetic operators. Each expression can also be followed by an alias. Also available * represents all columns in the query table. < table name or view name list;: one or more comma-delimited table or view names. The name of the table or view can be followed by an alias. Conditional expression 1: An expression that contains a relationship or logical operator that represents a query condition. Conditional expression 2: An expression that contains a relationship or logical operator that represents a grouping condition.< attribute list 1>: one or more comma-separated column names. < attribute list 2>: one or more comma-separated column names, followed by ASC or DESC for each column name, ascending/descending, by default. The knowledge about SQL statements here first as a brief introduction, the specific wording of the next will be specially come up with an article to narrate. Fourth I. Related concepts and knowledge 1. A trigger is a special event-driven process that a user defines on a base table. Automatically activated by the server to perform more complex checks and operations with finer and more powerful data control capabilities. Use the Create TRIGGER command to create a trigger. 2. There are three types of security problems in computer system, such as technical security, management security and policy law. 3. The TCSEC/TDI standard consists of four aspects of security policy, responsibility, warranty and documentation. 4. Common access control methods include both autonomous access control (DAC) and forced access control (MAC). 5. The SQL statement for the discretionary access control (DAC) includes GRANT and REVOKE two. User rights are composed of two parts: the data object and the operation type. 6. Common SQL autonomic Permission control commands and examples. 1) Grant all users all permissions to the Student and Course tables. Grant all priviliges in table Student,course to public; 2) grant the user U4 the right to query and name modification to the Student table. Grant Select,update (Sname) on table Student to U4; 3) grants the INSERT permission to the SC table to the U5 user and allows him to propagate the permission. Grant Insert on table SC-U5 with GRANT OPTION; 4) The user U5 the INSERT permission on the SC table and withdraws the authorization that was transmitted by him. REVOKE INSERT on table SC from U5 CASCADE; 5) Create a role R1 and have data query and update permissions on the Student table. The CREATE ROLE r1;grant select,update on table Student to r1;6) audits the operation to modify the Student table structure. I. Related concepts and knowledge points 1. Data dependence: The constraint relationship between attributes and attributes in a relationship is an abstraction between real-world attributes and the intrinsic nature and semantics of the data. 2. Normalization theory: It is the basic theory used to design a good relationship model. It eliminates inappropriate data dependencies by decomposing relational patterns to address insert exceptions, delete exceptions, update exceptions, and data redundancy issues. 3. Function dependency: Simply stated, for the two subset of properties of the relational pattern x and Y, if any of the x values can uniquely determine the value of Y, then the Y function is said to be dependent on X, which is recorded as X→y. 4. Non-trivial function dependency: two attribute subsets for relational schemas X and Y, if x→y, but y!? X, the x→y is called non-trivial function dependent; if x→y, but Y? X, the x→y is called a non-trivial function dependency. 5. Full function dependency: for the two subset of attributes for the relational pattern x and Y, if x→y, and for any one true subset X ' of x, there is no X ' →y, then the Y pair is called the X full function dependent. 6. Paradigm: Refers to a collection of relational patterns that conform to a certain level. When designing a relational database, different paradigms are defined according to the differing definitions that satisfy the dependency requirements. 7. Normalization: Refers to a low-level paradigm of the relationship model, through the transformation of the pattern decomposition into a number of high-level paradigm of the relationship between the set of patterns of the process. 8.1NF: If all properties of the relational pattern are non-divided, the relational patternBelongs to 1NF. 9.2NF:1NF Relationship mode if the full function of each non-principal property is dependent on the code at the same time, the relational pattern belongs to 2NF. 3NF: If each non-primary attribute of the relational pattern is neither dependent on the code nor passed by code, then the relational pattern is 3NF. BCNF: If each determinant of a relational pattern contains a code, the relational pattern belongs to BCNF. 12. Database design: Refers to a given application environment, constructs the optimized database logical mode and the physical structure, and establishes the database and its application system accordingly, enables it to effectively store and manage the data, satisfies the various user's application demand, including the information management request and the data operation request. 13.6 Basic steps of database design: Requirements analysis, conceptual structure design, logical structure design, physical structure design, database implementation, database operation and maintenance. 14. Conceptual structure Design: The process of abstracting the user requirements from demand analysis into the information structure, the conceptual model. It is to form a conceptual model independent of the specific DBMS by synthesizing, summarizing and abstracting the user's needs. 15. Logical Structure Design: Transform the conceptual structure model (basic E-R diagram) into a logical structure that conforms to the data model supported by a DBMS product and optimize it. 16. Physical Structure design: refers to the process of selecting a physical structure that best suits the application environment for a given logical data model. This includes the storage structure and access methods of the design database. 17. Abstract: Refers to the actual people, things, things and concepts of human treatment, extraction of the common characteristics of concern, ignoring the non-essential details, and these characteristics with a variety of concepts accurately described, these concepts constitute a model. 18. Database design must adhere to the principle of combination of structural design and behavioral design. 19. The data dictionary consists of five parts: data item, data structure, data stream, data storage and processing process. 20. Three commonly used abstract methods are classification, aggregation, and generalization. 21. The conflicts between local e-r graphs are mainly manifested in three aspects: attribute conflict, naming conflict and structure conflict. 22. The database commonly used access methods include index method, clustering method and HASH method three kinds. 23. The key factors to consider in determining the location and storage structure of data are: Access time, storage space utilization, and maintenance costs. Ii. database three Paradigms 2.1 first paradigm (1NF) non-repeating column in the first normal form (1NF) each column of a database table is an indivisible base data item cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties. In short, the first paradigm is a column with no duplicates. In any relational database, the first paradigm (1NF) is the basic requirement for relational schemas, and a database that does not meet the first normal form (1NF) is not a relational database. 2.2 Second Normal form (2NF) propertiesFully dependent on the primary key [eliminate partial child function dependency] satisfies the second normal form (2NF) must first satisfy the first normal form (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. It is often necessary to add a column to the table to store the unique identity of each instance. The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary key. The so-called full dependency is the inability to have a property that depends only on the primary key, and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. It is often necessary to add a column to the table to store the unique identity of each instance. In short, the second paradigm is that properties depend entirely on the primary key. 2.3 The third normal form (3NF) property does not depend on other non-principal properties [elimination of transitive dependencies] satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables. For example, there is a departmental information table, where each department has a department number (dept_id), a department name, a department profile, and so on. Then the department number is listed in the Employee Information table, the department name, department profile and other departments related information can no longer be added to the Employee Information table. If there is no departmental information table, it should be built according to the third paradigm (3NF), otherwise there will be a lot of data redundancy. In short, the third paradigm is that properties do not depend on other non-principal properties. 2.4 Concrete Example analysis The following is an example of a school student system to show the application of several paradigms. Before designing the database table structure, let's make sure that the content to be designed includes those. School number, student name, age, gender, course, course credit, department, academic results, office address, Department of telephone and other information. For the sake of simplicity we only consider these field information for the time being. There are several aspects to this information that we say are concerned about. 1) Students have those basic information 2) Students choose those lessons, what is the score 3) the number of credits per lesson 4) students belong to that department, the basic information of the Department is what. First Paradigm (1NF): The fields in a database table are single attributes and cannot be divided. This single attribute consists of a basic type, including Integer, real, character, logical, date, and so on. In any current relational database management system (DBMS), you are not allowed to divide one column of a database table into two or more columns, so you are making a database that conforms to the first paradigm. We will consider the second paradigm, and put all this information into a table (student number, name, age, gender, course, course credit, department, academic results, office address, Office Phone) The following dependencies exist. 1) → (name, age, gender, department, office address, Department phone) 2) (course name) → (Credit) 3) (School number, class) → (academic results) depending on the relationship, we can change the selectcourse to the following three tables: Student: Student (school number, name, age, gender, department, office address, Office Phone); Course: Course (course name, credits) : Selectcourse (School number, course name, grade). In fact, in contrast to the requirements of the second paradigm, this is the database table that satisfies the second paradigm, which, if not satisfied with the second paradigm, produces the following problem data redundancy: the same course by N students elective, "credit" repeated n-1 times; the same student took the M course, and the name and age were repeated m-1 times. Update exception: 1 if the credit of a course is adjusted, the "credit" value of all the rows in the data table should be updated, otherwise the same course credit will be different. 2) Suppose that a new course is to be opened and no one has yet been enrolled. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword. Delete exception: Assuming that a group of students have completed elective courses, these elective records should be removed from the database table. At the same time, however, the course name and credit information were also removed. Obviously, this can also lead to an insertion exception. We will consider how to change it to meet the third normal form of the database table, and then look at the above student table Student (school number, name, age, gender, department, office address, Office Phone), the keyword is a single keyword "study number", because there are the following decision relationship: (student number) → (name, age, gender, department, Office address, Office phone) But there are also the following decision relationship (school number) → (school location, college phone) that is, there is a non-critical field "College location", "College phone" to the key field "study number" of the transfer function dependency. It can also have data redundancy, update exceptions, insert exceptions, and delete exceptions (not specifically analyzed here, as referenced in the second paradigm). According to the third paradigm, the student relationship table can be divided into the following two tables to meet the third paradigm: Students: (school number, name, age, gender, department); Department: (Department, Office address, Office phone). This section is a summary of some of the topics that C + + programmers will be asked during an interview. From the basic written interview books, there may be some older, but this is the basis
Database-related concepts
