Shulou(Shulou.com)05/31 Report--

How to describe the SQL Server statement category, database paradigm and system database composition, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

Finally wait until this day, I want to start to study the database system again, about the database this, no accident, every day will regularly update an article 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 our data finally comes from, of course, we must first establish tables, so it includes CREATE, ALTER, DROP tables.

(2) Data Manipulation Language (DML), we need to do what operations on the data, of course, nothing more than add, delete, change, so it includes SELECT, INSERT, UPDATE, Delete, including TRUNCATE, MERGE.

(3) Data Control Language (DCL), we will grant different permissions to different users when operating the database.

database paradigm

Paradigm is what things, it means standardized rules, easy to understand is the definition of norms, rules, we need to abide by, so why set this set of rules? On the contrary, we think that our predecessors must have encountered this set of rules. If we do not define this set of rules, there will be one kind of problem or another. In order to avoid the emergence of such problems, this set of rules is mainly used to solve the following two problems.

(1) Avoid anomalies during data modification.

(2) Keep data redundancy to a minimum.

The most basic paradigms of database paradigms are the first normal form (1NF), the second normal form (2NF), the third normal form (3NF), and higher-level normal forms, but they are too complicated for us to discuss. Most books say so, and we will understand them in this way.

First Normal Form (1NF)

Definition: rows in relational tables must be unique and attributes atomic.

Too obscure, too abstract, do not understand, we analyze one by one, we look at the above definition in the focus on the line [unique], attribute [atomic].

So under what circumstances is it unique?

First: since it is unique, then a certain identifier in the row must be known and not unknown, that is, it cannot be empty.

Number two: the only one that means you can't repeat the promise.

Third, how to ensure that the line is unique? Unique rows are implemented by defining a unique key.

So what exactly is atomicity?

The first time you see the word atom, will you immediately associate it with the atomic bomb explosion independently developed by China, or the first chemical formula 2H2 +O2= 2H2O encountered in chemistry class? Two hydrogen atoms, i.e. four hydrogen atoms, and one oxygen atom, i.e. two oxygen atoms, combine to form a water molecule. Oh, look back. Molecules are composed of atoms. Atoms are composed of atomic nuclei and extra-nuclear electrons. Atomic nuclei are composed of protons and neutrons. There are also quarks and the like. Whether it is a program language or a database, atomicity has always been talked about. Why don't we talk about proton and quark? Because atoms are already relatively small, atomicity has always been used to emphasize and divide them. For convenience of understanding, we can think like this. The latter need not be described. If we haven't understood it yet, it is equivalent to atoms constituting molecules. If atoms are regarded as the smallest granularity, they cannot be divided again. Then we think about atomicity of attributes in turn, that is, attributes cannot be divided again. What does this mean? For example, there is an address attribute in the table. If we save (Hunan Province, Yueyang City, Huarong County), it violates the first paradigm. This attribute can still be divided into province, city and county.

Here we can summarize the conditions satisfied by the first normal form:

(1) Unique identification key

(2) Key cannot be empty

(3) Key cannot be repeated

(4) Attributes cannot be subdivided

Second Normal Form (2NF)

Definition: Under the premise of satisfying the first normal form, every non-key attribute must satisfy the complete functional dependence on the whole candidate key, that is, the non-key attribute cannot be a complete functional dependence on some part of the candidate key.

Well, let's continue with explaining the first paradigm above to explain the arcane definition of the second paradigm. Let's look at the following table

The candidate key defined above refers to the primary key of. OrderId and ProductId in the table given above are both candidate keys (primary keys), but at this time we can get OrderDate, CustomerId and CompanyName columns through some candidate keys (primary keys) such as OrderId. At this time, it is only a partial dependence on OderId rather than a complete dependence on both OderId and ProductId. In this case, in order to show complete dependence on candidate keys, it should be divided into the following two tables.

Therefore, the above definition is popularly said: attributes should be completely dependent on the primary key rather than partially dependent, otherwise it violates the second normal form.

Third Normal Form (3NF)

Similarly, the third normal form is viewed under the premise that the first and second normal forms are satisfied.

Definition: All non-key attributes must depend on non-transitive candidate keys, that is, non-key attributes must be independent of each other, and further, non-key attributes cannot form dependencies.

Let's look at the above two tables that have been modified to meet the second normal form. In this case, OrderId is the primary key in the order table, and CustomerId and CompanyName are completely dependent on OrderId. We can obtain Customer Id through Order Id, or Company Name through Order Id, and we can also obtain Customer Company Name through Customer Id. That is to say, CustomerId and CompanyName are a transitive relationship, rather than independent of each other. In this case, if the third normal form needs to be satisfied, it should be expressed as follows:

We can see that the third normal form emphasizes the independence between non-key attributes and non-key attributes, while the second normal form emphasizes the complete dependence of non-key attributes on candidate primary keys. So the second normal form and the third normal form are unified: non-key attributes must be dependent on the key, not on each other, and on the whole key.

System Database Composition

When you open the database, there will be a system database under the database by default, and the contents are as follows:

master

The master database stores instance-wide 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 definitions for all system objects.

model

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

tempdb

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

msdb

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

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.