Computer room Reconstruction--Database design (II.)

After the completion of the computer room charge system database requirements analysis, ER diagram, relational model of the phase, it is based on the relational model to design the database, the following is my summary of this stage.

This relationship model has users, students, cards, basic data, computers, bills, work records, recharge, return card, a total of 10, to be the 10 relational model to design the database table, which for the computer ( computer name system time system date) This relationship, there is no need to separate out design, The others need to be converted to a data table, and after determining which relational models need to be converted to relational tables, it is necessary to analyze the data table fields and define the specification of the three paradigms of the data tables.

Let's revisit the three paradigms of database design:

(a) Each field in a data table cannot have more than one value or cannot have duplicate properties, which are atomic in nature.

(ii) require that the attributes of an entity depend entirely on the primary key. A full dependency is a property that cannot exist that relies only on the primary key.

(c) On the basis of 1NF, any non-primary attribute is not dependent on other non-principal properties [Elimination of transitive dependencies on a 2NF basis].

Analyze an old data table:

Like this table Online_info, the primary key Cardno, the other fields are: Cardtype,studentname,department,sex and so on, and this information is completely and student_info content duplicates, so, It is entirely possible to associate two tables with the primary foreign key, so that the duplicated fields do not have to be repeated in a different table.

Look at one more table Studetn_info:

At that time, the table name is Student_info, can be seriously analyzed and found that this is completely the student information and card information overlay, the principle of a card corresponding to a person, but when the card information modified or deleted, the student information also has been modified risk, and because this table contains too much information, Lead to query student information needs this table, query card information needs this table, check out the money need this table, to see whether the bill and so also need this table, serious contrary to the object-oriented thinking of "single duty" principle, so this design must get rid of these deficiencies.

The study of data typesyesterday, when the relational model was mapped to a data table, the corresponding types of data needed to be considered separately, such as character type, numeric type, time date type, Money type ..., the most or the character type, then char (n), nchar (n), varchar (n) and nvarchar (n) for a simple summary.

This is a diagram made in OneNote after reviewing the data, in summary, if it is a Unicode data type (that is, a combination of Chinese or Chinese English), select varchar or nvarchar, if it is necessary to grow longer, Select nchar or nvarchar. For example, when we are logged in to the form, the user name is defined in char (10), and you need to use trim (UserID) in the code to pass in the database, in order to avoid spaces, of course, this requires that the user name cannot contain spaces, but in the password field , it may involve spaces, where nchar is not recommended and it is best to use char (n).

Second, all of the fields that involve money types are all used in the currency type, as long as the data type of decimal (m,n) in the code corresponds to OK.

The data sheet relates to the display

Database name: Restructurecharge

Altogether 9 sheets:

(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 design of the 9 tables, compared to the previous 11 tables less than 2, this time the table also removed those more than 10 fields redundant table, in the design process, for cancelcard_info whether need to continue to use in the ideological struggle or add, after all, not a table words on Card_ Info pressure is larger, or to ensure "single duty" bar, followed by a few tables similar to Worklog_info, bill_info or need to add serial number, convenient to query time.

In this way, the design of the database is completed, although there is not in line with the second to third paradigm, but compared with the last time, in the implementation phase of the code, the design of the data type, the length of the characters, is not to look back to their own summary.

Computer room Reconstruction--Database design (II.)