Cainiao can also fly: SQL database practice tutorial (3)

Vi. outline design.

After the database analysis is complete, the basics have been completed, and the next step isProgramPreliminary Design. How to design it? If it is a small program, use your mind to think about it, and even if it is finished, but if it is a slightly complex system, I am afraid it will not work. The outline design still needs to use UML diagrams. Do you know how important it is? Let's first design the general structure of the program. We can use the UML package diagram. As a professional cainiao, we need a three-tier architecture to design programs. The three-layer architecture divides programs into three basic layers: the UI Layer (user interface layer), The bll layer (business logic layer), and The Dal layer (data access layer ). The benefits of dividing a program into three layers are self-evident. From then on, you will no longer see the chaos of everything in a form. If you need to modify anything, you no longer need to modify the entire project. Each layer has its own functions. From the UI Layer to the Dal layer, they are down-dependent. Among them, the UI Layer is only responsible for displaying the interface and should not have logic (the validity verification logic is still available). All functions are completed by calling the BLL layer; the bll layer is responsible for all the logical organization of the program. It calls the Dal layer to perform complex operations, and then provides a simple interface for the UI Layer to call, which is equivalent to a large appearance layer, the bll-layer class can be written according to the form. That is to say, each form corresponds to a bll-layer class. Various methods of the class complete all the functions of the form. The Dal layer is responsible for accessing the database, each table in the database has a corresponding operation class in the Dal layer. Through this operation class, you can perform all operations on the corresponding table. The entity class mentioned above serves the three-tier architecture. Generally, data is transmitted between the three layers through the entity class, which is concise and convenient. The entity class is equivalent to a container. Note that there is also a layer that can not be used as a layer of dbhelper. This part is responsible for the lowest level of database interaction.CodeFor example, connecting to the database, inserting data, and querying data. refining the most basic code into a separate layer facilitates code reuse and makes the program more refined. For more information, click Google. Providing a three-tier architecture:

 

 

Let's look at the relationship between databases, Dal operations, and entity classes. This is a bit messy and hard to understand at the beginning. Just think about it:

 

 

With the above analysis, we can use Rational Rose to draw a package diagram. I suggest a little trick. In theory, the rose package should be painted in the package, but there is no way to draw a class, we recommend that you use a package to split a class chart into several parts, and then draw a specific class in each part. In this way, double-click the package and you will see the classes in the package. Based on the three-tier architecture, we can draw the following package diagram:

 

 

The overall architecture is like this. Next, we will take care of the classes in each package.

Sqlhelper package:

Dal package:

Bll package:

Ui package:

 

7. Detailed design.

 

The detailed design must start with the database. As a professional cainiao, we should put all SQL statements in the stored procedure rather than in the program. The advantages of this solution are: easy modification and maintenance, fast execution speed, and reduced data transmission volume. Stored procedures can be simply understood as creating small functions on the SQL server. They have names and parameters. By calling these small functions, we can add, delete, query, and modify tables. It is equivalent to placing SQL statements on the server and compiling them, and the execution speed is fast. In the program, we don't have to write complex SQL statements, just write the stored procedure name directly.

For details about how to use stored procedures, refer to this blog: http://blog.csdn.net/yangyuankp/article/details/7057922

Be sure to study the previous article carefullyArticleBecause this is a classic example of database use. It's okay if I understand it and use the data. I will be too lazy to write it again.

If you encounter some problems, maybe this article will help you: http://blog.csdn.net/yangyuankp/article/details/7208413

In addition, we can see that the class table in this tutorial depends on the grade Table and the student table depends on the class table. Therefore, when deleting a record, you must delete the record in a cascading manner, cascade deletion can be completed in the program, but I prefer to put it in SQL triggers. Trigger usage is similar to the stored procedure, so I won't go into details here. You can use Google.

For detailed design, we still need to use UML diagrams. This time, we usually use time sequence diagrams and flowcharts. Although others are important, they are rarely used. The specific design is Google.

I am sorry to tell you that the tutorial is over now, and the details are far from over. The specific code of the stored procedure and the code at each layer are not written, and there are still many UML diagrams that have not been drawn, however, these are all details. The purpose of this tutorial is to provide macro guidance. This course also requires continuous learning, and the time is very limited. So I can only write so much.

The rest are basically code writing. I don't mean to write anything. The basic process for developing a project is here. I didn't mainly teach you how to write code, instead of telling everyone about the process of a project, how to design a slightly larger program, and share my own experience. Follow this tutorial. It's just about food. It's also a professional cainiao!

The tutorial involves a large number of knowledge points and requires you to consult the materials patiently. If you are not afraid of it, you may not know it. You can use it at the beginning. As you learn more, you can understand the detailed principles. I did not learn these things in a day or two. It was about one and a half years of accumulation. I hope everyone can calm down and study hard.

Finally, I wish you all the best in the IT industry!

 

Appendix:

SQL statement Daquan (Classic collector version) + database-create, create tables, query statements