A brief description of SQL Server statement category database paradigm System database composition _mssql

Objective

Finally wait until this day, I want to start to restart the system learning database, about the database, no accident, every day will update a regular and content will not contain too much, short content, in-depth understanding.

SQL statement Category

The SQL statement includes the following three categories

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

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

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

Database paradigm

What is a paradigm, it means the standardization of rules, easy to understand a point is the definition of norms, rules, we need to comply with, then why should we set this set of rules? We think in the opposite way, it must have been met before, if we do not define this set of rules, there will be such or such a problem, in order to avoid such a problem arises this set of rules, mainly to solve the following two points.

(1) Avoid exceptions in the process of data modification.

(2) Maintain the minimum data redundancy.

The most basic paradigm of the database paradigm is the first normal form (1NF), the second normal form (2NF), the third normal form (3NF), and the higher level paradigm, but it is too complicated. We don't do the discussion, most books say so, so we understand.

First Paradigm (1NF)

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

Too obscure, too abstract, not very understanding, we Yi Yilai analysis, we look at the above definition to focus on row "unique", attribute "atomicity".

So what is the only way to be a line?

First: Since it is unique, then an identity in the line must be known, not unknown, cannot be empty

Second: The only means can not repeat Connaught

Third: How to guarantee the only line? A unique row is implemented by defining a unique key.

So what exactly is atomic?

The first to see the word atoms is not immediately associated with China's independent development of the atomic bomb, or the first chemical class that comes up with chemistry, 2h2+o2=2h2o,2 hydrogen, 4 hydrogen atoms, and 1 oxygen, or 2 oxygen atoms, to produce 1 water molecules, oh, to recap, molecules are made of atoms, Atoms consist of atomic nuclei and nuclear electrons, and the nucleus is made up of protons and neutrons, what other quarks, whether in the language of the program or in the database have been talking about atomicity, why not talk about proton and quark, because the atom is relatively small, so has been used to emphasize and divide atoms, in order to facilitate understanding, Can think so, the back is not in the narrative, to here still don't understand, the equivalent of atoms forming molecules, the smallest particle size is no longer divided, then we think of the property of the atomicity is the attribute can not be divided, what does this mean, such as in the table has an address attribute, if we save as (Hunan Province, Yueyang, Huarong County) This violates the first paradigm, this attribute can be divided into provinces, cities, counties.

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

(1) A key that is uniquely identified

(2) The key cannot be null

(3) key cannot repeat

(4) attribute can not be divided

Second Normal form (2NF)

Definition: In the premise of satisfying the first normal form, each Non-key attribute must satisfy the full 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 move on to the above explanation of the first paradigm 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. OrderID and ProductID in the table above are both as candidate keys and primary keys, but at this point we can get OrderDate, CustomerID and CompanyName columns through some candidate keys (primary key) such as OrderID. This is simply a partial dependency of the Oderid rather than a complete reliance on both Oderid and ProductID. The complete reliance on candidate keys at this point should be divided into the following two tables.

So the popular definition of the above is: the attribute to the primary key should be completely dependent rather than partial dependence, otherwise violate the second normal form.

Third Paradigm (3NF)

The third normal form is the third normal form in the premise of satisfying the first and second normal forms.

Definition: All Non-key attributes must depend on the candidate key that is not passed, i.e. the Non-key attributes must be independent of each other, further speaking, the non-key attribute cannot form a dependency relationship.

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

We can see that the third paradigm emphasizes the independence of Non-key and Non-key attributes, while the second normal form emphasizes the absolute dependence of non-key attributes and candidate primary keys. So the second and third paradigms are unified as follows: Non-key attributes must be dependent on the keys, not on each other, and on the entire key.

System Database Composition

When you open the database, there is a system database by default under the database, which reads as follows:

Master

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

Resource

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

Model

The model database is the template for creating a new database, and each new database created is initialized with a copy of model.

Tempdb

The tempdb database is where SQL Server stores temporary data, such as worksheets, sort spaces, and row versioning information. While SQL Server allows us to create temporary tables of our own use, and the location of these temporary tables is tempdb, we need to be aware that whenever the instance of SQL Server is restarted, the database is destroyed and created by the copy of the model.

Msdb

The msdb database is where the SQL Server Agent's Services store data, and the 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 the SQL statement and the three paradigm of the database, a brief introduction to the composition of the system database, belong to the scope of understanding, we come here today, we'll see you next day.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!