Database Fundamentals Questions

Copyright NOTICE: This article is the original article of Bo Master, reprint annotated source http://blog.csdn.net/u013142781

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?

transaction: is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails, 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 DBMS, the lock is the key to implement the transaction, the lock can guarantee 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, the request must be serialized or sequenced 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, not in 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 other is to spend more time inserting and modifying the data (because the index changes as well).

Creating an index can greatly improve your system's performance (benefits):

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.

Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.

Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Because there are many disadvantages to increasing the index:

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

Indexes are built on top of some columns in a database table. When you create an index, you should consider which columns you can create indexes on and which columns you cannot create indexes on.

in other words, you should create an index on these columns:

(1) In the column that often needs to search, can speed up the search;

(2) on the column that is the primary key, enforce the uniqueness of the column and the arrangement of the data in the organization table;

(3) often used in connected columns, these columns are mainly foreign keys, you can speed up the connection;

(4) Create an index on a column that is often required to search by scope, because the index is sorted and its specified range is continuous;

(5) Create indexes on columns that often need to be sorted, because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time;

(6) Speed up the judgment of the condition by creating an index on the column that is often used in the WHERE clause.

Similarly, you should not create an index for some columns:

First, the index should not be created for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.

Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has very little value.

The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

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.

(1) The DELETE statement performs the deletion by deleting one row from the table at a time and saving the row's delete operation as a transaction record in the log for the rollback operation. TRUNCATE table deletes all the data from the table at once and does not record the individual deletion records in the log, and deleting rows is not recoverable. Delete triggers related to the table are not activated during the removal process. Execution speed is fast.

(2) The space occupied by the table and index. When the table is truncate, the space occupied by the table and index is restored to its original size, and the delete operation does not reduce the space occupied by the table or index. The drop statement frees all the space occupied by the table.

(3) Generally, drop > truncate > Delete

(4) Scope of application. TRUNCATE can only be table;delete to a table and view

(5) TRUNCATE and delete delete data only, and drop deletes the entire table (structure and data).

(6) Truncate and without where Delete: delete data only, without deleting the structure of the table (definition) The DROP statement will delete the structure of the table that is dependent on the constraint (constrain), the trigger (trigger) index (index), and the stored procedure that depends on the table/ The function will be preserved, but its state will change to: invalid.

(7) The DELETE statement is DML (data maintain Language), which is placed in rollback segment and is not valid until the transaction is committed. If there is a corresponding Tigger, the execution time will be triggered.

(8) Truncate, drop is a DLL (data define language), the operation takes effect immediately, the original data is not placed in the rollback segment, can not be rolled back.

(9) In the absence of backup, use the drop and truncate sparingly. To delete some data rows, use Delete and pay attention to where to constrain the extent of the impact. The rollback segment should be large enough. To delete a table with drop, if you want to preserve the table and delete the data in the table, you can do it with truncate if the transaction is irrelevant. If it is related to a transaction, or if the teacher wants to trigger trigger, use Delete.

Truncate table name is fast and efficient because:
TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.

TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

(12) For a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.

Database Fundamentals Questions