Brief Introduction to SQL Server statement categories database paradigm System database composition, SQL paradigm

Brief Introduction to SQL Server statement categories database paradigm System database composition, SQL paradigm

Preface

Finally, on this day, I will start to study the database again. If there is no accident about the database, I will update a new article on a regular basis every day and the content will not contain too much, short content, in-depth understanding.

SQL statement category

SQL statements include the following three categories:

(1) Data Definition Language (DDL): Where does the Data come from? Of course, a table must be created first, so it includes CREATE, ALTER, and DROP tables.

(2) Data Manipulation Language (DML): What operations do we need to perform on Data? Of course, this is nothing more than adding, deleting, modifying, and querying Data, so it includes SELECT, INSERT, UPDATE, and DELETE, it also includes TRUNCATE and MERGE.

(3) Data Control Language (DCL): We grant different permissions to different users when operating the database.

Database paradigm

What is the paradigm? It refers to the standardization rules. In plain terms, it is the defined norms and rules that need to be followed. Why should we set this rule? We thought, on the other hand, it must have been encountered by our predecessors. If we do not define this set of rules, there will be one or another problem. In order to avoid such a problem, this set of rules will come out, the main purpose is to solve the following two problems.

(1) avoid exceptions during data modification.

(2) maintain minimum data redundancy.

The most basic paradigm of the database paradigm is the first paradigm (1NF), the second Paradigm (2NF), and the third paradigm (3NF). There is a higher level paradigm, but we will not discuss it if it is too complicated, most books say this, so we can understand it.

1NF)

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

Too obscure, too abstract, and too difficult to understand. Let's analyze them one by one. Let's take a look at the above definition, which focuses on the row [unique] and attribute [atomicity ].

So under what circumstances is the row unique?

First, since it is unique, a specific identifier in the row must be known rather than unknown and cannot be blank.

Second: Unique, that is, the promise cannot be repeated.

Third, how can we ensure that the row is unique? Define a unique key to implement a unique row.

So what is atomicity?

First, will the word Atom immediately think of the explosion of an independent atomic bomb developed by China, or the first chemical formula 2H2 + O2 = 2H2O encountered in the chemistry class, two hydrogen atoms, that is, four hydrogen atoms and one oxygen, that is, two oxygen atoms, form one water molecule, an atom is composed of an atomic nucleus and an external electron. The nucleus is composed of a proton and a neutron. What else is the quark? It is always atomic in programming languages and databases, why didn't we talk about the proton and Quark properties? Because the atoms are already relatively small, we have been emphasizing and dividing them with atomicity. In order to facilitate understanding, we can think so, we don't need to talk about it later. If we haven't understood it yet, it's like an atomic component. We can use an atom as the smallest granularity, so we can no longer divide it, in turn, we want to think about the atomicity of the attribute, that is, the attribute cannot be further divided. What does this mean? For example, if there is an address attribute in the table, such as (Hunan Province, Yueyang City, HuaRong County) this violates the first paradigm. This attribute can be further divided into provinces, cities, and counties.

Here we can summarize the conditions met by the first paradigm:

(1) unique key

(2) The key cannot be blank.

(3) Duplicate keys

(4) attributes cannot be further divided.

2NF)

Definition: when the first paradigm is met, each non-key attribute must satisfy the full functional dependency on the entire candidate key. That is, the non-key attribute cannot be a full functional dependency on a part of the candidate key.

Now, let's continue to interpret the first paradigm to explain the obscure definition of the second paradigm. Let's look at the following table.

The above-defined candidate keys refer to the primary keys. The OrderId and ProductId in the above table are both used as the candidate key, that is, the primary key. However, we can use some candidate keys (primary keys) such as OrderId to obtain the columns such as OrderDate, mermerid, and CompanyName, in this case, only part of the OderId depends on the OderId and ProductId. In this case, we should divide the full dependency on the candidate key into the following two tables.

In general, the above definition is: the attribute should be completely dependent on the primary key rather than partially dependent; otherwise, the second paradigm is violated.

3NF)

Similarly, the third paradigm is to look at the third paradigm on the premise of satisfying the first and second paradigms.

Definition: all non-key attributes must depend on the non-pass candidate keys, and the non-key attributes must be independent of each other. Further, the dependency between non-key attributes cannot be formed.

Let's take a look at the two tables that have been modified to meet the second paradigm. At this time, OrderId in the order table is the primary key, and the customer Id is mermerid and company name, that is, CompanyName, is completely dependent on OrderId, we can get the customer Id through the order Id, or the company name through the order Id, or we can get the customer company name through the customer Id, that is to say, CustomerId and CompanyName are a transmission relationship, rather than independent of each other. If the third paradigm needs to be satisfied, it should be represented as follows:

We can see that the third paradigm emphasizes the independence between non-key attributes and non-key attributes, while the second paradigm emphasizes the full dependency between non-key attributes and Candidate Primary keys. Therefore, the second and third paradigms are summarized as follows: non-key attributes must be key-dependent, rather than inter-dependent, and depend on the entire key.

System database Composition

When the database is opened, a system database is created under the database by default. The content of the system database is as follows:

Master

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

Resource

Resource Database is a hidden and read-only database that stores the definition of all system objects.

Model

A model database is a template for creating a new database. Each new database created is initialized with a copy of the model.

Tempdb

The tempdb database is the place where SQL Server stores temporary data, such as worksheets, sorting spaces, and row version control information. At the same time, SQL Server allows us to create temporary tables for our own use, and these temporary tables are located in tempdb. However, we need to note that every time we restart the SQL Server instance, the database will be destroyed and created by the model copy.

Msdb

Msdb database is a service used by the SQL Server proxy to store data. The SQL Server proxy is responsible for automatic operations, including jobs, plans, and alarms, as well as Replication Services.

Summary

This section focuses on the composition of SQL statements and the three paradigms of the database. A brief introduction to the composition of the system database belongs to the scope of understanding. Today we will go here first, we will join in the next section.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!