Database Interview Common topics

0. three paradigms

namely: Property Unique ,    Record Unique ,    Table Unique

First Normal (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.

The second paradigm (2NF): A partial function dependency of a non-critical field on any of the candidate key fields is not present in the database table (some of the function dependencies refer to situations where some fields in the composite keyword determine non-critical fields), or all non-critical fields are completely dependent on any set of candidate keywords.     
The third paradigm (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 paradigm. The so-called transfer function dependence, refers to if there is"A→b→c"the decision-making relationship, thenCthe transfer function relies onA. Therefore, a database table that satisfies the third paradigm should not have the following dependencies: key Fields→non-critical fieldsx→non-critical fieldsy

1. Super key, candidate key, primary key, foreign key

Hyper-Key: a property set that uniquely identifies a tuple in a relationship is called a hyper-key of a relational pattern. A property can be used as a super-key, and multiple properties can be combined together as a super-key. The super-key contains the candidate and primary keys.

Candidate Key: Is the least-key, that is, a super-key without redundant elements.

PRIMARY key: A combination of data columns or properties in a database table that uniquely and fully identifies the stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be a null value (null).

FOREIGN key: The foreign key of this table is called by the primary key of another table that exists in one table.

2. What is a transaction? What is a lock?

transactions: are bound together as a logical unit of work SQL statements are grouped, and if any one statement fails then the entire operation fails, and the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass ACID testing, that is, atomicity, consistency, isolation, and persistence.

Lock: In so the In a DBMS, a lock is the key to implementing a transaction, which guarantees the integrity and concurrency of the transaction. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

3. Four characteristics and meanings of database transactions

atomicity: All operations in the entire transaction, either complete or complete, are not likely to stall in the middle of the process. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.

Consistency: The integrity constraints of the database are not compromised until the transaction begins and after the transaction has ended.

isolation: The isolated state performs transactions so that they appear to be the only operations that the system performs within a given time. If there are two transactions that run at the same time and perform the same function, the isolation of the transaction will ensure that every transaction in the system considers only that the transaction is in use by the system. This attribute is sometimes called serialization, and in order to prevent confusion between transactional operations, serialization or serialization must be requested so that only one request is used for the same data at a time.

Persistence: After the transaction is completed, changes made to the database by the firm are persisted in the database and are not rolled back.

4. What is a view?

A view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is usually a subset of rows or columns that have a table or multiple tables. Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries.

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.

Note: This view is created in the database Instead of being created with code.

5, the role of triggers?

A trigger is a special stored procedure that is executed primarily by triggering an event. It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes. Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

6, maintain the integrity and consistency of the database, do you like to use triggers or self-writing business logic? Why?

It is most efficient and convenient to use constraints such as check, primary key, foreign key, non-null field, and so on. The second is the use of triggers, which ensures that the data is intact and consistent regardless of the business system access to the database. The final consideration is self-writing business logic, but this is cumbersome, programming complex, inefficient.

7, the role of the index? And what are the pros and cons of it?

Database index is a sort of data structure in the database management system, which helps to quickly query and update data in database tables. The implementation of an index typically uses a B-tree and its variants, plus trees.

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.

there is a cost to indexing a table: one is to increase the storage space for the database, and the second is to spend more time inserting and modifying data (Because the index also has to be changed).

8. The difference between Drop,delete and truncate

Drop deletes the table directly.
Truncate deletes the data in the table, and then inserts the self-growth ID from 1 onwards.
Delete Deletes the data in the table and optionally adds a WHERE clause.

Database Interview Common topics