Learn SQL database diagram (petshop) _mssql

I've known for a long time that Microsoft's PetShop is very classic, yesterday took time to learn, at first really did not adapt to, what membership, really do not understand, run PetShop want to learn from the landing, but the user name and password are not known, and later found that there are more registered pages, I registered a page, only to find out from the database. It took so much time to finally go back to the database, but a table in the database of one of the tables can not find the script, nor the database of their own design, there is no clue, and then suddenly remembered that SQL has a database diagram, quickly adapt to the database programmer quickly grasp the relationship between the database tables. So began my trip to Baidu, about the database diagram of the article is really very few, so I began to PetShop, and then set up a database diagram, began to grasp the database diagram. I clicked Mspetshop4services This database, storing the user's information. But the original is no database diagram.

Let's start by ourselves. Create a new database diagram, find the appropriate database (Figure 1), have a database diagram in the first row, and right-click to select New Database Diagram (N), as shown in Figure 2:

(Figure 1) (Figure 2)

This time will jump out of a Add Table dialog box, where we can select the table we want, if you want to select all, this action, the first mouse point and then hold the shift+ mouse point weight last, to complete the operation. The final table is sorted, then ctrl+s save the name of the data diagram, and the database diagram is basically built. The next step is analysis.

(Add Table Dialog block diagram) (Select Table dialog box)

Diagram of the entire mspetshop4services database. The whole picture, is not looking at a table to understand the relationship between the table more comfortable, of course, we still want to understand the meaning of the whole table, first of all, the top is the table name, the following are all fields, some fields left a key, that is the primary key meaning. Having a line between the tables indicates that there is a relationship between the primary key and the foreign key between the two tables, and that half of them have a key that refers to the primary key, an infinity symbol that represents the foreign key. But do not know whether the careful friend has found a problem, such as aspnet_paths and aspnet_personalizationallusers between the lines between the two ends are keys, I also looked for a long time data did not find, so I write code test, What exactly does it mean.

Understand the meaning of the centerline of the diagram through your own code:

I define myself as a student selection diagram:

The SQL script code is as follows:

Copy Code code as follows:

CREATE TABLE Student
(
[Sid] INT not NULL PRIMARY KEY--Student number
Sname VARCHAR (Ten) not NULL-student name
)

CREATE TABLE Course (
[Cid] INT not NULL PRIMARY KEY,--Course number
CName VARCHAR (Ten) Not NULL--course Name
)

CREATE TABLE SC (
[Sid] INT not NULL,
[Cid] INT not NULL,
Grade INT not NULL,--Results
FOREIGN KEY ([SID]) REFERENCES Student2 ([SID]),
FOREIGN KEY ([CID]) REFERENCES COURSE2 ([CID])
)

The diagram comes out as follows:

This data graph is the relationship of the primary key foreign key we know, and it is also in line with our thinking.

Then I changed a line of code inside the code, the diagram changed, the code is as follows:

Copy Code code as follows:

CREATE TABLE Student2
(
[Sid] INT not NULL PRIMARY KEY--Student number
Sname VARCHAR (Ten) not NULL-student name
)

CREATE TABLE Course2 (
[Cid] INT not NULL PRIMARY KEY,--Course number
CName VARCHAR (Ten) Not NULL--course Name
)

CREATE TABLE SC2 (
[Sid] INT not NULL PRIMARY KEY,
[Cid] INT not NULL,
Grade INT not NULL,--Results
FOREIGN KEY ([SID]) REFERENCES Student2 ([SID]),
FOREIGN KEY ([CID]) REFERENCES COURSE2 ([CID])
)

The corresponding diagram is as follows:

I'm sure you saw the two-key line segment. Well, then we should know, we can draw the conclusion that if a table is a primary key, and another table is a common field, which shows the effect is a key to an infinity, the key point to the primary key, infinite Da Cong to the foreign key, but when the foreign key in another table as the primary key, then Infinity has become the key. We can't look at the line at this time. How do you analyze the primary key and the foreign key? Don't look down. Here are two methods, the first comparison, we put the mouse over this line, he will jump out of the line, in the previous table name is the primary key, the following is the foreign key, the last is the name of the relationship (this is my own look at the results of the table), there is a method, but we have to use this method in advance, Know the name of the relationship, and then click on the two tables in any of the table, right-click the select relationship, find the corresponding relationship name, and then choose the right table and column specifications, which contains the main foreign key relationship. Well, it's written here.

　　Summarize:
Encountered difficult we first do not fear, from the most basic start, you can also set up test data to verify the new knowledge encountered.