Three paradigms of database design

Database design Paradigm

What is a paradigm: in short, database design has a great relationship to the data storage performance, as well as the developer's ability to manipulate the data. So the establishment of a scientific, normative database is needed to meet some

To optimize the way data data is stored. In a relational database, these specifications can be called paradigms.

What are the three main paradigms:

The first paradigm: when all the properties of the relational mode R are not decomposed into more basic units of data, the R is satisfied with the first paradigm, and précis-writers is 1NF. Satisfying the first paradigm is the minimum to normalize the relationship pattern

Otherwise, there will be a lot of basic operations that cannot be achieved in such a relational pattern.

The second paradigm: if the relationship pattern R satisfies the first paradigm, and all non-principal properties of R are completely dependent on each candidate key attribute of R, the R satisfies the second normal form and précis-writers is 2NF.

The third paradigm: set R is a relational pattern satisfying the first paradigm condition, X is any set of properties of R, if x non-transitive relies on any one of R's candidate keywords, and R satisfies the third normal form, précis-writers is 3NF.

Note: A relationship is essentially a two-dimensional table in which each row is a tuple, and each column is an attribute

Understanding the three main paradigms

First Paradigm

1. Each column property is an attribute value that cannot be re-divided, ensuring the atomicity of each column

2. The properties of the two columns are similar or similar, and the same columns are merged as possible to ensure that no redundant data is generated.

　　

　　

If the demand knows the province and classifies it, then it is clear that the first table is not easy to meet the requirements and does not conform to the first paradigm.

　　

　　

It is clear that the first table structure not only satisfies the requirements of a sufficient number of items, but also creates redundancy in the case of an item. Also does not conform to the first paradigm.

Second Paradigm

The data for each row can only be related to one of the columns, that is, a single row of data does one thing. As long as there is data duplication in the data column, you need to split the table.

A person to order a few rooms at the same time, will come out an order number of data, this way contacts are duplicated, resulting in data redundancy. We should tear him apart.

This enables a single piece of data to do one thing without complex relational logic. Update maintenance of table data is also easier to operate.

Third Paradigm

Data cannot have a transitive relationship, that is, no attribute has a direct relationship with the primary key, not an indirect one. Like: A-->b-->c attributes have such relationships that do not conform to the third paradigm.

such as Student table (school number, name, age, gender, institution, institution address, college phone)

Such a table structure, there is the above relationship. Study number------(university address, college phone)

Such a table structure, we should take apart, as follows.

(Student number, name, age, gender, institution)--(institution, institution address, school phone)

At last:

The three paradigms are only the basic concept of general design database, and can build a database with smaller redundancy and reasonable structure. If there is a special case, of course, to special treatment, database design is most important to look at requirements and performance, requirements > Performance > table structure. So we can't blindly pursue the paradigm to build a database.

BC Paradigm

Set the relationship mode R<U,F>∈1NF, if each function of R depends on X→y, if Y is not X, then x must contain candidate code, then r∈bcnf.

Explain: For relational mode R, if all non-trivial, complete function dependencies in R are determined by the code, then r belongs to BCNF.

If R∈BCNF

Each decision attribute set (factor) contains (candidate) code

All properties in R (primary, non-primary) are fully functional dependent on the code

R∈3NF (certificate)

If R∈3NF R is not necessarily ∈bcnf

In relational mode STJ (S,T,J), S represents the student, T represents the teacher, and J represents the course.

Each teacher teaches only one course. Each course is taught by a teacher, and a student chooses a course, and a fixed teacher is identified. A student takes a teacher's class to determine the name of the selected course: (s,j) →t, (s,t) →j,t→j

The definition of the relationship pattern can be concluded, if R belongs to Bcnf, then R is:

1. All non-primary properties are fully functional dependent on each code.

2. All main attributes are fully functional dependent on each code that does not contain it.

3. No attribute full function relies on any set of properties that are not code.

Because of r∈bcnf, by definition excludes any attribute-to-code transitive dependencies and partial dependencies, so r∈3nf. But if R∈3NF, then R may not belong to BCNF.

1. First Paradigm: The database field is a single attribute and cannot be divided.
Explain:

• Cannot be a composite attribute, if present, should be split into multiple properties
• Cannot be a multivalued attribute, if one exists, an entity should be established, and there is a 1-to-many relationship to this property
• Cannot be a repeating property

2. Second paradigm: Any non-critical field cannot be partially dependent on any of the candidate keywords (i.e. must be fully dependent)。
Explain:

• There must be a candidate keyword in the table, that is, each row differs from any other row, and is the only one that distinguishes
• Any non-critical fields cannot be dependent on part of the candidate keyword

3. Third paradigm: Any non-critical fields cannot be passed depending on any of the candidate keywords
Explain:

• Non-keyword fields must be directly dependent on any of the following keywords
• Non-critical field C cannot rely on non-candidate keyword B, because the sample will form a transitive dependency: The candidate keyword a=>b=>c, because B is often a foreign key, that is, the other table's primary key, that is, the table can not contain the other table's non-main properties

4.BC paradigm: Any field cannot be passed depending on any of the candidate keywords
Explain:

• Compared to the third paradigm, one is "any non-critical field cannot", one is "any field cannot", obviously more stringent
• The candidate keyword or some of its fields cannot be passed depending on other candidate key keywords

Comments:
Candidate Keywords: Also called candidate code, the only identification of a row of data, its true subset can not be a candidate keyword, a table may exist multiple candidate keywords, such as the user table Username,userid
PRIMARY key word: Also called the primary key, the main code, selected to distinguish the other rows of the candidate keyword, a table has only one primary key
Partially dependent: (A, B)->c,d, such as A->c, the C part relies on A
transitive Dependency: a->b->c, C pass dependent A

Note the point:

1. Database connectivity can result in a fraction of the performance penalty
2. Not the higher the database paradigm, the better.
3. Sometimes there is a tradeoff between data redundancy and paradigm, and in the actual database development process, a portion of data redundancy is often allowed to reduce database connectivity.

Reference 1:http://www.cnblogs.com/knowledgesea/p/3667395.html

Reference 2:http://m.blog.csdn.net/blog/qq_20042935/42609609

Three paradigms of database design