SQL server-Statement category, database paradigm, System database composition

Objective

Finally until this day, I will start to re-system learning database, about the database this piece, no accident, every day will be updated regularly and the content will not contain too much, short content, in-depth understanding, always to review the basics.

SQL statement Category

The SQL statement consists of the following three categories

(1) Data definnition Language is the DDL, where our data ultimately comes from, of course, we must first establish a table, so it includes create, ALTER, drop table.

(2) Data manipulation Language, that is, DML, what we need to do with the data, of course, is nothing more than delete and change, so it includes Select, INSERT, UPDATE, DELETE, It also includes truncate, MERGE.

(3) Data Control Language (Language) is the DCL, we operate the database for different users will be granted different permissions.

Database paradigm

What is the paradigm, it means the rules of normalization, easy to understand a little bit is the definition of norms, rules, we need to abide by, then why set this set of rules? We think, in turn, that the predecessors have encountered, if not define this set of rules, then there is such a problem, in order to avoid the emergence of such problems, this set of rules, mainly to solve the following two points.

(1) Avoid anomalies in the data modification process.

(2) Maintain minimal redundancy of the data.

The most basic paradigm of the database paradigm is the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), and a higher-level paradigm, but too complex for us to discuss, and most books say so, and so we know.

First Paradigm (1NF)

Definition: Rows in a relational table must be unique and attributes are atomic.

Too obscure, too abstract, not too understanding, we hit analysis, we look at the above definition is focused on the line "unique", attribute "atomicity".

So what's the only way to be the only one?

First: Since it is the only one, then one of the identities in the line must be known and not unknown and cannot be empty

Second: The only thing that means no repetition.

Third: How to guarantee the only thing? Implement a unique row by defining a unique key.

So what exactly is atomicity?

The first to see the word atom is not immediately associated with China's independent development of the atomic bomb outbreak, or the first chemical class encountered in the chemical 2h2+o2=2h2o,2 hydrogen is 4 hydrogen atoms and 1 oxygen is 2 oxygen atoms set to generate 1 water molecules, oh, review, the molecule is composed of atoms, Atoms are composed of nuclei and nuclear electrons, the nucleus is made up of protons and neutrons, and what quarks, whether it is the language of the program or the database is always talking about atomicity, why not speak of proton and Quark, because the atom is relatively small, so has been used to emphasize and divide the atom, in order to facilitate understanding, Can think so, the following will not be in the narrative, to here still do not understand, is equivalent to the atomic composition of molecules, the atom as the smallest particle size can not be divided, then we think of the properties of the atomic property is not re-division, this is what the meaning, such as in the table has an address attribute, if we exist (Hunan province Yueyang, Huarong County) This violates the first paradigm, which can then be divided into provinces, cities, and counties.

Here we can make a summary of the conditions that the first paradigm satisfies:

(1) Uniquely identified key

(2) The key cannot be empty

(3) key cannot be duplicated

(4) attribute cannot be re-divided

Second Paradigm (2NF)

Definition: In satisfying the first paradigm, each non-key attribute must satisfy the complete function dependency on the entire candidate key, that is, the non-key attribute cannot be a complete function dependency on a part of the candidate key.

Well, let's continue into the first paradigm explained above to explain the obscure definition of the second paradigm. Let's look at the following table

The above definition candidate key refers to the primary key. The OrderID and ProductID in the table above are the candidate keys and the primary key, but at this point we can get OrderDate, CustomerID and CompanyName by some candidate keys (primary key) such as OrderID. This is simply a partial dependency of oderid rather than a complete reliance on both Oderid and ProductID. In order to show that the full dependence of the candidate keys should be divided into the following two tables.

So the definition of the above-mentioned is: the attribute to the primary key should be fully dependent rather than partially dependent, otherwise violate the second normal form.

Third Paradigm (3NF)

The same third paradigm is the third paradigm in terms of satisfying the first and second paradigms.

Definition: All non-key attributes must depend on non-transitive candidate keys, that is, non-key attributes must be independent of each other, further speaking, non-key attributes can not form a dependency relationship.

Let's take a look at the above two tables that have been modified to meet the second paradigm, when the order table OrderID The primary key, the customer ID is CustomerID and the company name CompanyName to OrderID is completely dependent, we can get the customer ID by the order ID, You can also get the company name through the order ID, and we can also get the customer company name through the customer ID, which means that CustomerID and CompanyName are a transitive relationship, not independent of each other. If you need to satisfy the third normal form, the following should be said:

We can see that the third paradigm emphasizes the independence between non-key attributes and Nonkey attributes, while the second paradigm emphasizes the full dependency of non-key attributes and candidate primary keys. So we generalize the second and third paradigms as follows: non-key attributes must be dependent on the key, not on each other, and on the entire key.

System Database Composition

When the database is opened in the database by default there will be a system database, the contents are as follows:

Master

The master database stores the metadata information for the instance scope, the server configuration, all the database information in the instance, and initialization information.

Resource

The resource database is a hidden, read-only database that stores definitions of all system objects.

Model

The model database is the template that creates the new database, and each new database created is created with the model copy initialized.

Tempdb

The tempdb database is where SQL Server stores temporary data, such as worksheets, sort spaces, row versioning information. At the same time, SQL Server allows us to create temporary tables that we use ourselves, and the locations of these temporary tables are tempdb, but we need to be aware that whenever the instance of SQL Server is restarted, the database will be destroyed and created by the model copy.

Msdb

The msdb database is where SQL Server Agent's Services store data, and SQL Server Agent is responsible for automating operations, including jobs, schedules, and alerts, as well as replication services, and so on.

Summarize

In this section we focus on the composition of SQL statements and the database of three paradigms, the composition of the system database is briefly introduced, belongs to the scope of understanding it, today we first come here, we will see you next.

SQL server-Statement category, database paradigm, System database composition