"Personal computer room reconfiguration"--Creating a Database Trilogy

Has carried on the foundation three layer thought the edification, immediately entered the Personal computer room reconstruction stage, feels oneself this rookie in the rookie, any heavy and the way far. To build tall buildings, there must be cement and bricks. Database is a container for managing data resources, the following is the process of building my own table, if there is inappropriate place, please correct me!

first, "Three paradigms" to clear the chest

Benefits: The specification of relational databases, in order to reduce data redundancy. Meet the three normal forms, indicating that the database is relatively sound, data redundancy is small, later maintenance is convenient.

Detailed content:

first normal form (1NF): The fields in a database table are single attributes and cannot be divided to ensure the atomicity of each column.

For example, the address will be dismantled into a provincial city until it can be demolished.

second Paradigm (2NF): The upgrade of the first paradigm-the goal is to ensure that each column in the table is related to the primary key.

For example, to design a student Test score table (table 1), the joint primary key is the number and the course. School number as the primary key, the credit is only related to the course. This violates the design principle of the second paradigm.

So, in this case, we should split the table, separate the student information into a table (table 2.0), and separate the course information into another table (2.2).

What happens if I don't split it?

Data redundancy: if 1 students take n courses, then the student information will be repeated n-1 times (table 3 of 1 is the data redundancy place), n students take 1 courses, courses and credits will be repeated n-1 times.

Update Exception: If you need to update the credit of a course, all the credits in the table should be updated, otherwise there will be different credits in the same course.

If you add a course, there is no student elective, no primary key: study number, you can not deposit the course information in the database.

Delete exception: If a group of students graduate, to delete records, the course name, credit will be deleted, it is difficult to wait for the school, come in a batch of new students to fill in???

The third paradigm (3NF): One more layer on the basis of the second paradigm. Make sure that each column is directly related to the primary key column, not indirectly.

For example: A table of Student information (table 4), the existence of a decision relationship: Student number--name, age, gender, there are also the following decision relationship

Learn the number----the balance, the state, the balance, the status of the transfer dependence on the number, is indirectly related. Violates the principle of the third paradigm.

So splitting it into tables 5 and 6 is perfect!

Of course, the third paradigm will also have data redundancy, update exceptions, delete exceptions, and details similar to the second paradigm.

second, "E-r map" analysis tool

E-r (enitity Relationship Diagram): Provides methods for representing entities, relationships, and attributes to describe the conceptual model of the macroscopic world.

This is the E-r diagram I drew. The process of drawing is to clarify the process of thinking. At the beginning of the recharge, return card and other tables will only use, know to determine a table has card number, operator ID and other information. At that time still think ID quite redundant, but now found it and card number form a joint primary key, play a role is not small ~

third, "SQL Build Table" falls to the implementpersonal feeling, this place of the establishment of the table, the statement is not difficult to build a place, the focus should pay attention to the following points:
1. Why should the name be standardized? no rules inadequate surrounding area. Naming is a manifestation of professional quality, and the naming of norms is also convenient for us to revise and maintain later.
2. is the data type char or varchar? char is fixed-length, when the input character is less than the specified number, char (8), and the input character is less than 8, it will fill the empty value later. If it is greater than 8, the exceeded characters are truncated. varchar is a variable-length, non-Unicode character data with a length of n bytes. N is a numeric value between 1-8000. Relative storage space savings. because char fixed length, so in processing speed is much faster than varchar, but relatively cost storage space, so the storage is not small, but the speed of the requirements can use the char type, the reverse can be used with varchar.
The data types of other fields should also be selected appropriately, not all of the char types.
3. What do I do if I do not allow changes to fields after the table is completed? in SQL SERVER2008, new tables cannot modify field names and add field names. You can choose the tools in the menu-Options--designers--Table Designer and Database Designer, remove the tick.




Iv. Summaryeach time to the end of a little bit of their own feelings, in order to record my dedication to the computer business of the mind course (here there should be applauseO (∩_∩) o~). The biggest feeling of building a table is: 1. Don't be afraid of not knowing, afraid don't know. The three paradigms, the E-r diagram began and did not study well, just know that it is very useful to build a table. Although I do not know, but when used to know, immediately can learn, saving a lot of detours time. 2. Write things are their own look, the big deal disgusting others ~ Others say better, their own according to the operation of others, it is not as good as their own write a blog impressive. 3. Time management is really Jiukujiunan reincarnation Ah! Since being forced by the teacher, seriously learned a few days of management, found that the head does not hurt, the eye does not spend, the director has the focus, every day is very fulfilling, very happy. Not a few days, self-feeling is good, adhere to, the little rookie will someday become a big Bird, Roar ~