[Turn] three database design paradigm

2011-03-28  来源:本站原创  分类:Database  人气:73 

from: http://news.csdn.net/n/20061230/100207.html

1 popular understanding of the three paradigms

Popular understanding of the three paradigms, for a lot of good database design. In database design, in order to better application of three paradigms, we must understand the three popular paradigms (popular understanding is enough to understand, is not the most scientific, most accurate understanding):

The first paradigm: 1NF is the property of atomic constraints, required attribute is atomic, no further decomposition;

The second paradigm: 2NF is the only constraint records, required to record a unique identifier, that is, the uniqueness of the entity;

The third paradigm: 3NF field redundancy is bound, that any field can not be derived from other fields, it requires no redundant field.

No redundant database design can be done. However, there is no redundancy of the database may not be the best database, sometimes in order to improve operational efficiency, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. Specifically: the conceptual data model designed to comply with the third paradigm, to reduce the standard paradigm of work into the physical data model design considerations. Reduction paradigm is to increase the field to allow redundancy.

The first paradigm (1NF): database table fields are a single property, can not be divided. This constitutes a single attribute from the basic types, including integer, real, character, logical, date and so on.

For example, the following database tables is in line with the first paradigm:

Field 1 Field 2 Field 3 Field 4

And such a database table is not consistent with the first paradigm:

Field 1 Field 2 Field 3 Field 4
Field 3.1 Field 3.2

Obviously, in any current relational database management system (DBMS), the fool can not make the database do not meet the first paradigm, because these DBMS do not allow you to form a database and then divided into two or more columns. So, you want to design the existing DBMS do not meet the first paradigm of the database is impossible.

The second paradigm (2NF): database tables does not exist any non-key fields of the keyword section of part of the candidate functional dependencies (functional dependency refers to some combination of keywords in the presence of certain fields in the decision of non-key field case), that all non-key fields are totally dependent on any set of candidate keywords.

Assuming elective relationship table SelectCourse (student number, name, age, course name, score, credit), combination of keywords for the keyword (student number, course name), because of the following decisions relationship:

(Student number, course name) → (name, age, achievements, credits)

The database table does not meet the second paradigm, the relationship because of the following decisions:

(Course name) → (credits)

(School) → (name, age)

Combination of keywords that exist in the case of non-key fields in the decision.

Do not meet the 2NF, this elective relationship table has the following problems:

(1) Data redundancy:

N the same course by the student elective, "credits" on repeat n-1 times; m with a student enrolled in courses, name and age on repeated m-1 times.

(2) updated exceptions:

If the adjustment of a certain course credits, table of all rows of the "credit" value is updated, otherwise it will appear the same course credit in different situations.

(3) into the anomaly:

Suppose you want to set a new course, being no one elective. In this way, because you have not "study" keyword, course name and credit can not be recorded into the database.

(4) Delete Exception:

Suppose a group of students have already completed the course of the elective, the elective records from the database table should be deleted. However, at the same time, the course name and credit information has been deleted. Obviously, this will lead into the exception.

Relationship between the elective to the following table SelectCourse three tables:

Student: Student (Student ID, name, age);

Course: Course (course name, credits);

Elective relationship: SelectCourse (student number, course name, results).

This database table is in line with the second paradigm, eliminating data redundancy, update anomalies, insertion anomaly and deletion anomaly.

In addition, all single keyword database tables are in line with the second paradigm, because there can be no combination of keywords.

Third Normal Form (3NF): In the second paradigm, based on the data table if there is no non-key field for any candidate key segments of the transfer function dependent on the line with the third paradigm. The so-called transfer function dependence means that if there is "A → B → C" decision between the transfer function C depends on A. Therefore, to meet the third paradigm of database tables should not exist as dependencies:

Non-key field key field → x → y of non-key fields

Students assume the relationship table Student (Student ID, name, age, where the College , College location, college phone), the keyword for the single keyword "study" the relationship because of the following decisions:

(School) → (name, age, where the College , College location, college phone)

The database is in line with 2NF, but does not meet the 3NF, because of the following decisions relationship:

(School) → (where the College ) → ( College location, college phone)

Namely, the existence of non-key fields, " College Point "," School Phone "section on the keyword" Student Number "of the transfer function depends.

It will also present data redundancy, update anomalies, insertion anomaly and deletion anomaly of the situation, readers are free to analyze that.

The students are divided into the following two tables of relational tables:

Student: (Student ID, name, age, where the College );

School : ( School , location, phone).

This database table is in line with the third paradigm, eliminating data redundancy, update anomalies, insertion anomaly and deletion anomaly.

Paradigm Application

We gradually get to a forum's database, the following information:

(1) User: user name, email, homepage, phone, address

(2) Posts: post title, post content, respond to the title, your reply

We will first design for the mere existence of the database table:

User name email Home Phone Address Post Title Post content Reply Title Replies content

The database table consistent with the first paradigm, but there is no set of candidate keywords can determine the entire database table row, the only key fields the user name can not be completely determined by the tuple. We need to increase the "Post ID", "Reply ID" field, the table will change as follows:

User name email Home Phone Address Post ID Post Title Post content Reply ID Reply Title Replies content

This data keyword in the table (user name, posting ID, reply ID) to determine the entire line:

(User name, posting ID, reply ID) → (email, homepage, phone, address, post title, post content, respond to the title, your reply)

However, this design does not meet the second paradigm, the relationship because of the following decisions:

(User name) → (email, homepage, phone, address)

(Post ID) → (post title, post content)

(Reply ID) → (Reply title, your reply)

That is part of the function of non-key field depends on the candidate key field, it is clear that this design will lead to a lot of data redundancy and operational anomalies.

We will be decomposed into a database table (as keywords underlined):

(1) user information: user name, email, homepage, phone, address

(2) Post Information: post ID, title, content

(3) reply to a message: reply ID, title, content

(4) Last: user name, posting ID

(5) Re: post ID, reply ID

This section is designed to meet the requirements of 1,2,3 paradigm and BCNF normal form, but this design is not the best?

Not necessarily.

Observe that the first four "post" in the "User Name" and "Post ID" is between 1: N relationship, so we can "post" into the first two of the "Post Information" ; No. 5, "reply" in the "Post ID" and "Reply ID" is between 1: N relationship, so we can "reply" to merge three of the "reply message" in the. This will reduce the quantity of data redundancy, the new design:

(1) user information: user name, email, homepage, phone, address

(2) Post information: user name, posting ID, title, content

(3) the reply message: post ID, reply ID, title, content

Database Table 1 clearly meet all the requirements of the paradigm;

Database table in paragraph 2 in the presence of non-keyword "title", "content" section of the keywords "Post ID" part of the functional dependency, which does not meet the requirements of the second paradigm, but this design does not lead to data redundancy and abnormal operation;

Database table 3, there are also non-key fields "title", "content" section of the keywords "Reply ID" part of the functional dependency, not to meet the requirements of the second paradigm, but the database is similar to Table 2, the design operation will not lead to data redundancy and anomalies.

It can be seen, not necessarily forced to meet the requirements of the paradigm, for 1: N relationship, when one side into the side after N, N side of the second paradigm is no longer satisfied, but this design but better! [Here 1: N users should refer to the above - post, the N 1 to the side into the side: the user name inside the post (1) into N (Post Information)]

For M: N relationship, can not M or N side of the side into the other side, this will lead to the paradigm does not meet the requirements, leading to abnormal operation and data redundancy.
For the 1:1 relationship, we can on the left or the right of the 1 1 into the other side, by design does not meet the requirements of the paradigm, but does not cause operational anomalies and data redundancy.

Conclusion

Paradigm designed to meet the requirements of the database structure is clear, while avoiding data redundancy and operational anomalies. This does not mean that the design must meet the requirements of the paradigm is wrong, there is a 1:1 in a database table or 1: N relationship of this rather special case, the combined result of the paradigm does not meet the requirements but is reasonable.

In our design the database, we must always consider the requirements of the paradigm.

相关文章
  • [Turn] three database design paradigm 2011-03-28

    from: http://news.csdn.net/n/20061230/100207.html 1 popular understanding of the three paradigms Popular understanding of the three paradigms, for a lot of good database design. In database design, in order to better application of three paradigms, w

  • Database design paradigm in layman's language 2010-10-20

    Data dependencies: 1 data dependent Data refers to is dependent on a relationship between attributes are equal or not reflected in the relationships between the data, the most important thing is functional dependency and multivalued dependency. 2 FD

  • Database design paradigm 2011-05-12

    1NF: attribute can not be divided 2NF: 1NF part to eliminate reliance 3NF: elimination of the transfer depends 2NF Database structure must follow certain rules. In a relational database, this rule is the paradigm. Paradigm is in line with a certain l

  • 3 large database design paradigm 2010-03-07

    Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation exception. O

  • [Change] database design paradigm in layman's language 2010-07-08

    Original Address http://www.cnblogs.com/Bowen80/archive/2009/01/08/1371903.html When relational database design is to observe certain rules. In particular, database design paradigm is briefly 1NF (first normal form), 2NF (second paradigm), 3NF (third

  • Understanding of database design paradigm 2010-09-10

    What <br /> Paradigm Paradigm is in the relationship between a particular level set mode. The relationship between the relational database must meet certain requirements, that meet the different paradigms. Currently there are six relational database

  • Database design paradigm (example) 2010-12-13

    When relational database design is to observe certain rules. In particular, the database design paradigm is briefly 1NF (first normal form), 2NF (second normal form), 3NF (third normal form) and BCNF, while the fourth and fifth paradigm paradigm left

  • Relational database design paradigm 2011-01-20

    Should first be sure: the database design is a science. We usually do a small project, the database will be used, because the data is less reason, we do not feel good or bad database design. However, when the large amount of data when, for example, t

  • Several relational database design paradigm 2011-02-03

    The first paradigm (1NF) In any relational database, the first normal form (1NF) is the relationship between the pattern of the basic requirements, not satisfied with the first normal form (1NF) database is not a relational database. The so-called fi

  • 2011.2.26 database design paradigm Summary 2011-02-26

    And ready to start looking for work, based on past experience, the database paradigm is more common interview questions, re-turned the next few days ago information, brief summary tonight. In database design, we encourage the design to meet certain r

  • Turn: On database design skills 2011-08-06

    Speaking of databases, I think can not but talk about data structures. In 1996, I first entered college to learn computer programming, when the teacher told us that: a computer program = data structures + algorithms. Although the process of program d

  • Relational database design paradigm introduced 2010-12-22

    The relationship between the relational database must meet certain requirements, which meet different paradigm. Currently there are six relational database paradigm: the first paradigm (1NF), second normal form (2NF), third normal form (3NF), fourth

  • Summary of database design paradigm 2010-10-18

    A, 1NF: atomic (not further divided) 1NF is the column for each database table the basic data items are indivisible, the same column can not have multiple values, that is, an entity can not have more than one value or property can not have duplicate

  • Three summary database design paradigm 2010-10-20

    For the table, each of which is a grid value or is empty, if not more consistent with the first paradigm. That we should be very easy to understand. The second paradigm requires that each non-primary property must be entirely dependent on the primary

  • [Turn] the database three classic example of paradigm analysis 2010-10-18

    Life-long Learning Learning is a happy thing [Turn] the database three classic example of paradigm analysis Database of the three paradigms 1N: relation R attributes are inseparable items. 2N: In the 1N, based on the function of each non-primary attr

  • Three paradigms paradigm of database design and anti-reflection (rpm) 2011-07-09

    A person to grow into the position of project manager to understand the database design principles, although a lot of things that are relatively strong theoretical things; when we get a new demand, we need to figure out from beginning to end, you beg

  • Database design criteria (the first. Second. The third paradigm description) 2010-06-03

    I, Relational Database Design Paradigm of 1.1 The first paradigm (1NF) no repeat of the column The so-called first normal form (1NF) is the database table for each column of basic data items are indivisible, the same column can not have more than one

  • Database design analysis of the three major paradigms application example - turn 2011-08-01

    Introduction Database design paradigm is needed to meet database design specifications to meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation e

  • Simple. Clear! Database design, analysis of the three major paradigms Application 2010-03-12

    Introduction Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation

  • Application database design analysis of the three major paradigms 2010-03-19

    Introduction Database design paradigm is needed to meet database design specifications that meet these specifications database is simple, clear structure, at the same time, does not occur insert (insert), delete (delete) and update (update) operation