Database Common Interview Questions

The original intention for the postgraduate examination, the following is the Bo master himself, Bo Master is a senior undergraduate, can not guarantee that the content is completely correct, please look dialectically, if there is a problem can be in the comment area to point out my amendment. Collation is not easy, I will not hang a QR code please pay for the tourists, if possible, Trouble mercy, click on the lower right corner of the recommendation Bar!

1. Three paradigms

That is: attribute unique, record unique, table unique

Function dependency: Set R (U) is the relationship pattern on the attribute set U, and X, Y is a subset of U. If a possible relationship with R (U) is not possible in the R,r two tuples on the X attribute value is equal, and on Y the value of the property is unequal, then the X function determines that the Y or Y function depends on X, which is recorded as X->y

L First normal form (1NF): Each component must be an irreducible data item

L Second Normal form (2NF): If the relationship pattern belongs to the first paradigm, and each non-principal attribute full function relies on any candidate code, then the relational pattern belongs to the second paradigm. The so-called complete function dependency, refers to if the "Y function depends on X (x->y)", and for any of the x's true subset X ', there is a Y function does not depend on X '.

L Third normal form (3NF): On the basis of the second paradigm, if there is no non-critical field in the data table, the transfer function dependency on either of the candidate key fields conforms to the third normal form. The so-called transfer function dependency, refers to if there is a "a→b→c" decision relationship, the C transfer function depends on A. Therefore, a database table that satisfies the third paradigm should not have the following dependencies: key fields → non-critical fields x→ non-critical fields Y

2. Integrity constraints

The integrity of the database refers to the correctness and compatibility of the data. The correctness of the data is that the data conforms to the real world semantics and reflects the current situation. The compatibility of data means that the data of the same object in different relational tables is logical.

L Entity integrity: If attribute A is the primary attribute of basic relationship B, a cannot take a null value (so-called NULL is a value that does not know, does not exist, and is meaningless)

L Referential Integrity: If the attribute f is the outer code of the basic relationship R, it corresponds to the main code KS of the basic relationship s, then the value for each tuple on F in R must be:

2 or null value (each property value of f is null)

2 or equal to the primary code value of a tuple in s

(A class monitor property can be selected as the monitor, or it can be a tuple in the relationship of the value of the study number)

User-defined integrity: A constraint on a specific relational database that reflects the semantic requirements that the data involved in a particular application must meet (such as a property must take a unique value, a non-primary attribute cannot take a null value)

3. What is the main code? What is a foreign code?

If the value of an attribute group in a relationship can uniquely identify a tuple, and its subset cannot, then the attribute group is called a candidate code, and if a relationship has multiple candidates, one of the main codes is selected.

If f is one or a set of properties of the basic relationship r, but not the code of the relationship R, the KS is the main code of the basic relationship s. If f corresponds to the KS, it is said that F is the outer code of R.

4. Does using index queries improve the performance of queries? Why?

In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

In general, querying data through an index is faster than a full table scan. But we must also pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified. This means that insert,delete,update for each record will pay 4, 5 disk I/Os for this. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. Using index queries does not necessarily improve query performance

5. What is a data model? What is normalization?

A data model is a model that identifies the relationship between an entity type and its entities. Typical data models are mesh models, hierarchical models, and relational models.

From tables in a relational database, the process of removing redundant data is called normalization. Includes: Streamlining the structure of the database, removing redundant columns from the table, identifying all data that depends on other data

6. View technology is useful in database design

A view is a table that is exported from one or more basic tables, which is a virtual table that relies on the actual table of the data source. Only the definition of the view is stored in the database, not the data that corresponds to the view, which is still stored in the original base table. The view is a window for programmers to use the database, which simplifies the user's operation and is a means of keeping the user's data confidential. In order to perform complex processing, increase computation speed and save storage space, the definition depth of the view should not exceed three layers. If the three-tier view is still not enough, you should define a temporary table on the view and then define the view on the temporary table.

The following two scenarios are typically used in the view:

(1) Do not want visitors to get information about the whole table, only to expose some fields to the visitor, so build a virtual table, that is, the view.

(2) The data of the query originates from different tables, and the query person wants to query in a unified way, so it can also set up a view, combine the results of multiple table query, the query only need to get the data directly from the view, do not have to consider the difference of the data from the different tables.

7, the database of the three-level model structure

1) External mode (sub-mode) is a description of the logical structure and characteristics of local data that the database user can see and use, a data view of the database user, and a logical representation of the data related to an application. Outer mode is usually a subset of patterns, and a database can have multiple external schemas (views)

2) mode (logical mode) is a description of the logical structure and characteristics of all the data in the database, and it is a public view of all users, and a database has only one schema. (Basic table)

3) Internal mode (storage mode) is a description of the physical structure and storage of the database, and is the organization of the data within the database. (Save file)

8. Data dictionary: A set of system tables inside a relational database management system that records all the definition information in the database. The relational database management system is actually updating the corresponding information in the data dictionary when it executes the SQL data definition statement.

9. Data Independence

L Logic Independence: When the mode changes, the database administrator changes the image of each external mode/pattern, which can keep the outer mode unchanged (the application is written according to the external mode of the data, so the application does not have to be modified to ensure the logical independence of the data and the program)

L Physical Independence: When the database storage structure changes, the database administrator changes the image of each mode/internal mode, so that the pattern can be kept unchanged, so that the application does not have to change, which guarantees the physical independence of the data and the program.

10. The difference between relationship and relationship model and relational database: relationship is the state or content of a relationship pattern at a certain moment. Relational patterns are static and stable, and relationships are dynamic, changing over time, as relational operations constantly update data in the database. Relational database is a relational model based database, it uses the relationship to describe the real world, a relationship can be used to describe an entity and its attributes, can also be used to describe the relationships between entities.

11. Trigger: Set up (Attach) a set of SQL statements that can be automatically executed by the system on a relationship (base table) and stored in the database after precompilation

12. Brief description of query optimization strategy

2 If a subexpression appears more than once in an expression, the subexpression should be pre-computed to save the result to avoid repeating the calculation

2 Select operations to do as first as possible

2 try to avoid using SELECT *

2 Avoid using calculations on indexed columns. Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index.

2 Avoid using is null and is not NULL on indexed columns

13, transaction: is a user-defined sequence of database operations, these operations are either fully done, or do not do, is an inseparable unit of work. (Atomicity, consistency, isolation, permanence)

14. Blockade Agreement

1) First-class blockade protocol: Transaction T must write the lock before modifying the data R until the end of the transaction is released (avoid loss of modification)

2) Level two block protocol: increase transaction T on the basis of the first level blocking protocol must read the data R before reading the lock, you can release the read lock after reading (to avoid loss of modification and read dirty data)

3) Level three block protocol: increase the transaction T on the basis of the first level blocking protocol the lock must be read before the data R is read, until the end of the transaction is released (avoid loss of modification, read dirty data, non-repeatable read)

4) Two-segment lock protocol: Before reading and writing any data, first request and obtain the blockade of the data

After releasing a lock, the transaction no longer requests and obtains any other blockade

15, non-destructive connection decomposition is the decomposition of a relational pattern into a number of relational patterns, through the natural joins and projections and other operations can still be restored to the original relationship mode

Database Common Interview Questions