Basic Concepts in the database

In the same data set, different selection criteria correspond to different output results, and the database is a warehouse that organizes, stores, and manages the data according to the structure. The program or user can use it to make data access and modification, it is the soul of data storage.

Data management has undergone 3 stages of human management, file system and database system. Database is a combination of logical relationship and definite meaning, it can overcome a series of problems produced by traditional file organization, data redundancy is small, because the relational database management system has great flexibility for information query, and the design is simple, so it has been widely used in the actual system development.

• The difference between relational database system and file database system
• What are the features of the SQL language
• What is the difference between an inner connection and an outer connection?
• What is a transaction
• What is a stored procedure it differs from a function in relation to
• What is a primary key and what is a foreign key
• What is a deadlock?
• What is a shared lock what is a mutex
• What is the difference between 1234 paradigms
• Check Constraint
• View

The difference between relational database system and file database system

A relational database is a collection of data items that are organized into a set of formally described tables in which the data can be accessed or relational in different ways without having to reorganize the database tables, which correspond to a collection of all the relationships in a relational model. For example, the relationship between teachers, students, curricula, and relationships is an educational system. File database system is the operation of files, including storage, query and so on.

The difference between a relational database system and a file database system is as follows:
(1) The main characteristic of the relational database system is the structure of the data, and the file database system is the unstructured of the data.
(2) in the relational database, the logical structure that the user sees is a two-dimensional table, and the basic element in the file database system is the file.
(3) File database system can realize multimedia file management, support C/s working mode

What are the features of the SQL language

SQL is an abbreviation for Structured Query Language (structured Query Language), which includes 4 parts: Data query, data manipulation, data definition and data control.

A data query is the most common operation in a database, and the SELECT statement obtains the required information. The data manipulation LANGUAGE,DML of SQL language consists of 3 kinds of statements: inserting data, modifying data and deleting data. The SQL language uses data definition LANGUAGE,DDL to implement data definition functions that define and revoke database users, base tables, views, indexes. Data Control LANGUAGE,DCL is used for unified control of the database to ensure that the data is secure in the case of multi-user sharing.

Basic SQL statements are SELECT, INSERT, UPDATE, delete, create, drop, grant, REVOKE, etc.

For example, there are 3 basic tables in the educational management system:
Student Information table S (Sno,sname,age,sex), whose attributes indicate number, name, age, and gender, respectively

Class Information Table SC (SNO, CNO, Scgrade), whose attributes represent the study number, course number, and grade, respectively

Course Information Form C (Cno,cname,cteacher), whose attributes represent the course number, the course name, and the name of the instructor.

(1) Insert the average score for each course in the SC table into another existing table Sc_c (Cno,cname,avg_grade) Zhongmu where Avg_grade represents the average score for each course.

insert into SC_C( CNO, CNAME,AVG_GRADE)select SC.CNO, C.NAME, AVG(SCGRADE) FROM SC, C where SC.CNO = C.CNO

(2) Remove he Haoyuan's female student selection record from SC table

delete from SC,S,C where SC.SNO=S.SNOAND SC.CNO = C.CNO  AND S.SEXAND C.CTEACHEAR = ‘何昊’

(3) to stipulate that female students should take he Haoyuan Teacher's course result should be above 80 points (including 80 points)

alter table SC,S,C add constrant GRADE CHECK( GRADE>80 )where SC.CNO = C.CNO and SC.SNO = S.SNO AND C.CTEACHER = ‘何昊‘

(4) Find out the names of all students who have not been enrolled in the ' He Haoyuan ' teacher course

SELECT SNAME FROM SWHERE NOT EXISTS( SELECT * FROM SC,C WHERE SC.CNO= C.CNO AND CNAME = ‘何昊‘ AND SC.SNO = S.SNO )

(5) List of the names of students with two or more (including two) failed courses (with a score of less than 60) and their average scores

SELECT S.SNO,S.SNAME,AVG_SCGRADE = AVG(SC.SCGRADE)FROM S,SC,( SELECT SNO FROM SC WHERE SCGRADE<60 GROUP BY SNO HACING COUNT(DISTINCT CNO)>=2)A WHERE S.SNO = A.SNO AND SC.SNO = A.SNO GROUP BY S.SNO,S.SNAME

What is the difference between an inner connection and an outer connection?

An inner join is also known as a natural connection, and only two rows matching the table can appear in the result set. The result set returned is all matching data in two tables, discarding unmatched data. Because an inner join removes all rows from the result table that do not match the rows in the other join tables, the internal connection may cause information loss

An inner connection is a guarantee that all rows in two tables meet the conditions of the connection, but not the outer connection. Unlike inner joins, outer joins include not only rows that meet the join criteria, but also all rows of data in the left table (when left outer joins), right table (when right outer joins), or two edge tables (full outer joins). That is, only the rows of one table are restricted, and the rows of the other table are not restricted.
There are 3 types of outer joins of SQL: Left outer connection, the key word is the Ieft OUTER join, right outer join, the key word OUTER join; The use of outer joins is the same as an inner join, except that the INNER JOIN keyword is replaced with the corresponding outer join keyword.

What is a transaction

A transaction is a separate execution unit (unit) in a database that is typically caused by the execution of user programs written by advanced database manipulation languages such as SQL or programming languages such as C + +, Java, and so on. When the data in the database is changed successfully, the data changed in the transaction is committed and no longer changed. Otherwise, the transaction is canceled or rolled back, and the change is not valid. (p243)

A transaction must satisfy 4 properties, namely atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability), or acid 4 properties.

(1) atomicity. The transaction is an indivisible whole, in order to guarantee the overall goal of the transaction, the transaction must be atomic, that is, when the data is modified, it is either fully executed or not executed ... Atomicity requires a transaction to be fully executed
(2) consistency. The database data must remain consistent state before and after a transaction executes. The consistency state of the database should satisfy the constraints specified by the schema, and the database remains in a consistent state after the transaction is fully executed. In order to maintain the integrity of all data, in a relational database, all rules must be applied to the modification of the transaction. The consistency state of a database is assigned by the user and is implemented by the concurrency control mechanism. For example, bank transfer, the sum of two account amounts before and after the transfer should remain unchanged. Data inconsistencies caused by concurrent operations include loss of data modification, reading of "dirty" data, non-repeatable reading, and phantom data generation.
(3) Isolation. Isolation is also known as independence, when two or more transactions concurrently execute, in order to ensure the security of the data, a transaction inside the operation and the operation of the transaction is isolated, not to be seen by other ongoing transactions.
(4) Durability. Persistence is also known as permanence, and after the transaction is complete, the DBMS guarantees that its modifications to the data in the database are permanent and that the modification is persisted when the system or cutoff fails. Persistence is generally guaranteed through database backup and recovery.

In general, by executing a commit or ROLLBACK statement to terminate a transaction, when a commit statement is executed, all changes made to the database since the start of the transaction are permanent, which is written to disk, and when the rollback statement is executed, the All changes made to the database since the start of the transaction are revoked, and the contents of the database are returned to the state they were in before the transaction started. In any case, when the transaction is complete, it is guaranteed to return to a consistent state.

What is a stored procedure? What is the difference between it and the function of contact

When the SQL statement executes, it is compiled before it is executed. In large database systems, in order to improve efficiency, the SQL statement set for specific functions is compiled and optimized and stored in the database server, and the user invokes execution by specifying the name of the stored procedure.

Stored procedures are not equal to functions, although they are essentially no different, but in particular there are several aspects of the difference:
(1) The stored procedure is generally performed as a separate part, and the function can be invoked as part of a query statement. Because a function can return an object, it can be located after the FROM keyword in a query statement.
(2) In general, the function of the stored procedure is more complex, but the function realizes the function to be more specific.
(3) The function needs to enclose the input parameters in parentheses and return only one value or Table object, and the stored procedure can return multiple parameters
(4) functions can be embedded in SQL total use, can be called in Select, the stored procedure does not work.
(5) The function cannot manipulate the entity table directly, only the built-in table
(6) When the stored procedure is re-created, it is compiled on the server and executes more quickly.

What is a primary key? What is a foreign key

The primary key, also known as the Master code, is one or more fields in the database and is a unique identifier for the records in the table. The primary key cannot be empty. There can be only one primary key in a table, and a primary key column is not necessarily a single column, it can be multiple columns. A primary key can uniquely identify a row.

The primary key should follow these guidelines:
(1) Primary key is meaningless to the user
(2) Primary key cannot be empty
(3) The primary key remains unchanged. Because the purpose of the primary key is to uniquely label a row of data
(4) The primary key should not contain dynamically changing timestamps
(5) The primary key should in principle be automatically generated by the computer, not the user specified.

The foreign key is also known as the outer Code, which indicates the connection between the two relationships. When a common keyword is a primary key in a relationship, this common keyword is called a foreign key for another relationship.
A table can have more than one foreign key, or it can be empty

What is a deadlock?

There are several programs in the OS concurrent execution, they continue to apply for, release resources, in the process, because of contention for resources and in an indefinite wait state, resulting in the program can not continue to execute, if no external force, they will not be able to advance, it is said that the system is in a deadlock state or the system generated a deadlock. At this point, you can only break this state by external forces.

The cause of the deadlock is 3 points.
(1) Insufficient system resources, often in the system of multiple processes sharing resources, such as printers, these resources at the same time can only be used by one process.
(2) The process runs in the wrong order, the process is asynchronous in the run, when the process advance sequence is not at that time, resulting in deadlock.
(3) Improper allocation of resources, if the system resources are sufficient, the process of resource requests can be met, the likelihood of deadlock will be greatly reduced, and the process advance sequence and speed is different, may also produce deadlock.

The 4 necessary conditions for creating a deadlock:
(1) Mutual exclusion, each resource can only be used by one process at a time
(2) Request and hold wait, when a process is blocked by requesting a resource, keep the acquired resources
(3) Inalienable, the resources acquired by the process cannot be forcibly deprived until it is exhausted
(4) Loop wait, a number of processes formed between the end-to-end waiting for the resource relationship.

So, in order to prevent deadlocks, it is necessary to hit one or more of the 4 conditions of the deadlock, therefore, we need to maximize the system resources, arrange the sequence of the process reasonably and determine the reasonable allocation of the resources of the algorithm.

To avoid deadlock is in the dynamic light bulb process of resources, to take an effective way to prevent the system from entering the unsafe state, to prevent deadlock, the most representative method is the banker algorithm.

What is a shared lock? What are mutex locks

In the database, the lock is mainly a kind of protection mechanism to read/write data, from the point of view of database system, the lock can be divided into shared lock and mutual exclusion lock.

Shared locks are called S-locks, also known as read locks. For operations that do not change or do not update data (read-only), such as a SELECT statement. If transaction T adds a shared lock to data A, the other transaction can only have a plus shared lock, and cannot add a mutex. A shared lock prevents other concurrently running programs from obtaining overlapping exclusive locks, but allows the program to obtain overlapping shared locks. Other users can acquire a shared lock-locked resource, but cannot modify the shared lock. When you execute a SELECT command, SQL Server typically shares the lock on the object. Shared locks are also known as read locks. the shared lock is released immediately after the data page with the shared lock is read.

A mutex is referred to as an X-lock, also called an exclusive lock, for data modification operations such as INSERT, UPDATE, or delete. Make sure that you do not make multiple updates to the same resource at the same time. In order to ensure the integrity of data operation, mutual exclusion lock is introduced. A mutex is used to ensure that only one thread can access an object at any time. If the transaction t is X-lock on data D, then no other transaction can add any type of lock to D, until T releases the X lock on D, and the general requirement is to add a mutex to the data before modifying the data, so the mutex is also called a write lock.
For the use of locks, there are certain restrictions, there are two things to follow:
A, after the first lock operation
B. Must be unlocked after the transaction ends

What is the difference between 1234 paradigms

The correct design of the table is called "Database Normalization", and its purpose is to reduce data redundancy in the database, thus increasing the consistency of data.

Paradigms are a refinement of the data elements, relationships in the database, and the definition of the required tables and items in each table after the initial work. Common paradigms are 1NF, 2NF, 3NF, BCNF, and 4NF.

1NF, the first paradigm. The first paradigm is that each column in a database table is an indivisible base data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties. If duplicate attributes are present, you may need to define a new entity, which consists of duplicate attributes and a one-to-many relationship between the new entity and the original entity. The pattern of the first paradigm requires that attribute values be no longer split into smaller parts, that is, property items cannot be attribute combinations or have group properties.
In short, the first paradigm is a column with no duplicates.

Check Constraint

A check constraint is one that restricts the acceptable data values or data formats in a column or column in a table, and is used to limit the range of values for a column, in the form of a check (constraint expression). If you are defining a check constraint on a single column, it allows only specific values, and if you define a check constraint on a table, the constraint restricts the values in a particular column. For example, for an employee information table, a constraint was added to the employee's age attribute, that is, the age must be greater than 0 and less than or equal to 120, then the user must obey the constraint when entering age, enter a negative number, or 121 cannot enter

View

A view is a logical window of data that is picked up from a base table in a database, unlike a base table. It is a virtual table, in the database, only the definition of the view is stored, not the data items that the view contains, these items are still stored in the original basic table structure.

The role of the view:
(1) Can simplify the data query statement
(2) enables users to view the same data from multiple angles
(3) Through the introduction of views, can improve the security of data
(4) The view provides some degree of logical independence.

Basic Concepts in the database