Data Center reconstruction-Database Design (2)

Data Center reconstruction-Database Design (2)

After completing the database requirements analysis, erdiagram, and relational model phases of the data room charging system, we should design the database based on the relational model. Below is a summary of this phase.

This relational model includes 10 users, students, cards, basic data, computers, bills, work records, recharge, card return, and computers, these 10 relational models are used to design database tables (Computer NameSystem Time system date). It is not necessary to design the relationship separately. Other data must be converted to data tables. After determining which relational models need to be converted to Relational Tables, the fields of the data table that need to be analyzed are clearly defined and the three paradigm norms of the data table are determined.

Let's review the three major paradigms of Database Design:

(1) Each field in a data table cannot have multiple values or duplicate attributes, which is atomic.

(2) requires that the attributes of an object fully depend on the primary keyword. Full dependency is an attribute that does not depend only on a part of the primary key.

(3) On the basis of 1NF, any non-primary attribute does not depend on other non-primary attributes [eliminate the transfer dependency on 2NF].

Analyze an old data table:

 

Like this table OnLine_Info, the primary key CardNo, and other fields include: cardtype, studentname, Department, sex, etc. The information is exactly the same as the content in Student_Info, So, you can associate two tables by using the primary and Foreign keys, so that these repeated fields do not need to be repeated in different tables.

Let's look at another table, Studetn_Info:

 

At that time, the table name was Student_Info. After careful analysis, it was found that this was the superposition of student information and card information. In principle, the previous card corresponds to a person, but when the card information was modified or deleted, the student information is also at risk of being modified. Because this table contains too much information, this table is required to query the student information, and the query card information needs this table, this table is also required to check whether the table is checked out, which seriously violates the "single responsibility" Principle in the object-oriented thinking. Therefore, this design must remove these shortcomings.

 

Research on Data Types yesterday when we mapped the relational model to the data table, we had to consider the types of data, such as numeric, numeric, time, date, and cash ......, If the most frequently used character type is char (n), nchar (n), varchar (n), and nvarchar (n), a simple summary is given.

 

This is an image made in OneNote after reading the data. In short, if it is a Unicode data type (that is, a combination of Chinese and English), select varchar or nvarchar, select nchar or nvarchar. for example, after the user name is defined as char (10) When logging on to the form, Trim (UserID) must be used in the code to input the database to avoid spaces, of course, this requires that the user name cannot contain spaces, but spaces may be involved in the password field. We do not recommend using nchar here. It is best to use char (n ).

Second, all the fields related to the money type are represented by the money type. It is okay to use the decimal (m, n) data type in the code.

Data Tables involved in display

Database Name: Restructurecharge

A total of 9 tables:

(1) User_Info

 

(2) Card_Info

 

(3) Student_Info

 

(4) Recharge_Info

 

(5) Online_Info

 

(6) LogoffCard_Info

 

(7) Worklog_Info

 

(8) BasicDate_Info

 

(9) Bill_Info

 

This is the nine tables designed this time, two fewer than the previous 11 tables, and the redundant tables with more than 10 fields are removed from this table. during the design process, whether or not CancelCard_Info needs to be used after the ideological struggle is added. After all, if there are not many tables, the pressure on Card_Info is relatively high. It is better to ensure "single responsibility, several tables, such as Worklog_Info and Bill_Info, still need to add serial numbers to facilitate query.

In this way, the database design is complete. Although there are still some areas that do not conform to the second and third paradigms, it is much better than the previous one. in the implementation phase of the Code, do you still have to look back at your summary of the data type design and character length.