Learn SQL database relational diagram (Petshop)

A long time ago, I knew that Microsoft's Petshop was very classic. I took the time to study yesterday. At the beginning, I was not very familiar with what kind of membership is, run petshop to learn from login, but the user name and password do not know, and later found a more registered page, registered a page, only to find that you have to go from the database. It took so much time to finally return to the database, but a table in the database could not find the script, nor was it a self-designed database. There was no clue at all, later, I suddenly remembered that SQL had a database relationship diagram, which quickly became suitable for database programmers to quickly master the relationship between database tables. So I started my journey to Baidu, and there were very few articles on the relational graph of the database. So I started to build a relational graph of the Database Based on petshop and started to master the relational graph of the database. I click the database MSPetShop4Services to store user information. However, the original version does not have a database relationship diagram.

First, let's create a new database relationship diagram. Find the corresponding database (figure 1). There is a database relationship diagram in the first row. Right-click and choose create database relationship diagram (N ), 2:

(Figure 1) (figure 2)

In this case, the Add Table dialog box is displayed. You can select the expected table. If you want to select all tables, first, place the cursor over the first one, and then press shift + the mouse over the last one to complete the operation. Finally, sort the table and press ctrl + s to save the table. The data Relation Diagram name is basically set up. The next step is the analysis.

(Add Table dialog box) (Select Table dialog box)

The relationship diagram of the whole MSPetShop4Services database. The whole figure shows whether the relationship between tables is much more comfortable. Of course, we still need to understand the meaning of the whole table. First, the top table name is used, and the following fields are used, some fields have a key on the left, which means a primary key. If there is a line connection between tables, it means that there is a relationship between the primary key and the foreign key between the two tables. Half of the tables have a key and refer to the primary key, A foreign key expressed by an infinite symbol. But I don't know if any of my friends have found a problem. For example, the two ends of the lines between the aspnet_Paths and aspnet_PersonalizationAllUsers tables are keys. I have not found any information for a long time, so I wrote my own code and tested what it meant.

Use your own code to understand the meaning of the midline graph:

I define myself as the relationship diagram of a student's course selection:

The SQL script code is as follows:

Copy codeThe Code is as follows: create table Student
(
[Sid] int not null primary key, -- Student ID
SNAme VARCHAR (10) not null -- Student name
)

Create table Course (
[Cid] int not null primary key, -- course number
CName VARCHAR (10) not null -- Course name
)

Create table SC (
[Sid] int not null,
[Cid] int not null,
Grade int not null, -- score
Foreign key ([SID]) REFERENCES Student2 ([Sid]),
Foreign key ([CID]) REFERENCES Course2 ([Cid])
)

The diagram is as follows:

This Data graph shows the relationship between the foreign key of the primary key, which is also in line with our thinking.

Then I changed a line of code in the code, and the figure changed. The Code is as follows:

Copy codeThe Code is as follows: create table Student2
(
[Sid] int not null primary key, -- Student ID
SNAme VARCHAR (10) not null -- Student name
)

Create table Course2 (
[Cid] int not null primary key, -- course number
CName VARCHAR (10) not null -- Course name
)

Create table SC2 (
[Sid] int not null primary key,
[Cid] int not null,
Grade int not null, -- score
Foreign key ([SID]) REFERENCES Student2 ([Sid]),
Foreign key ([CID]) REFERENCES Course2 ([Cid])
)

The corresponding relationship diagram is as follows:

I believe you can see the line segments of the two keys. Well, we should have understood it. We can come to the conclusion that if one table is a primary key while the other table is a common field, the result is a key and an infinite number, the key points to the primary key, and the Infinity points to the foreign key. However, when the foreign key is also used as the primary key in another table, the Infinity also changes to the key. In this case, we cannot check the line. How can we analyze the primary key and foreign key? Don't worry. Here we will introduce two methods. The first one is a simple answer. If we place the mouse over this line, a prompt will pop out, with the table name in the front being the primary key and the foreign key following it, the last is the name of the Link (this is the result of my own table view). There is another method, but we also need to use this method in advance to know the name of the link, click any table in the two tables, right-click and select the link, find the corresponding Link name, and select the table and column specification on the right. The relationship between the primary and Foreign keys is displayed. Now, I will write it here.

　　Summary:
In case of difficulties, we should not be afraid to start from the most basic aspects. We can also establish test data to verify the new knowledge we encounter.