3. Database paradigm

Source: Internet
Author: User

3. Database paradigm

The database three paradigm is certainly not unfamiliar to everyone. The official explanation is very abstract and difficult for beginners to understand. I forgot to read this knowledge. Whenever someone asks me, you must first look at the teaching materials and review them for a while before you can clarify them.

Why can't the classic Thought of the Three paradigm be immediately presented when others ask questions? After listening to Mr. Mi's "How to learn efficiently" class, I found that I did not integrate myself into the three paradigm, and the three paradigm did not have a relationship with myself. Next, I will use three interesting stories to help you quickly and deeply understand the meaning of the three paradigms.

The first paradigm, the official explanation: the attribute values of each link R in the relational model r are non-exclusive Atomic values. This official explanation is not too difficult. For example, if we want to use "phone number" as a database field, we will divide "phone number" and "landline number" under "phone number ", this violates the first paradigm. In my words, it is"Clear the story". You only say "Phone Number". Do you know whether you mean "mobile phone number" or "landline number "?

Second paradigm, the official explanation: non-primary attributes must rely entirely on the primary relationship key of R. How about it? A little dizzy! For example, in the SCD table, the fields SNO, SN, Age, Dept, MN, CNo, and Score are available. Knowing SNo can determine SN, Age, Dept, MN, and knowing SNo and CNo can determine Score. Better than the bandits, the younger brother SN, Age, Dept, and MN elected SNo as the dashboard, and the Score elected SNo and CNo as the dashboard. The results were inconsistent. The bandits split up and said,"As long as there isIf a person does not agree, this cannot be done.". Big Brother SNo sets up banner SD with younger brother SN, Age, Dept and MN. Big Brother SNo and CNo set up banner SC with younger brother Score.

Third paradigm, the official explanation: each non-primary attribute does not pass the primary relationship key where the function depends on R. How about this? It's totally dizzy! For example, in the SD table, knowing SNo can determine Dept, and knowing Dept can determine MN (department head name ). This story is followed by the Bandit story of the second paradigm. The eldest brother SNo has younger brother SN, Age, Dept, MN, but Dept is especially talented, and MN is recruited as his younger brother, every day, he fooled MN and instilled a reactionary idea into MN. Finally, one day Dept was independent of MN, setting up the banner D. Brother SNo felt that SD was incomplete and changed the banner of bandits to S. After learning this lesson, SNo decided to run the following command:"One person can assume only one role.Otherwise, it is not in line with the three paradigm, resulting in the decomposition of the relationship model.

After this story is over, we will surely write the essence of the three paradigm into our minds.


Database three paradigm

Introduction to several design paradigms of relational databases
1. 1NF)
In any relational database, the first paradigm (1NF) is the basic requirement for the relational model. databases that do not meet the first paradigm (1NF) are not relational databases.
The first paradigm (1NF) means that each column in the database table is an inseparable basic data item. The same Column cannot contain multiple values, that is, an attribute in an object cannot have multiple values or duplicate attributes. If duplicate attributes exist, you may need to define a new object. A new object consists of duplicate attributes. The new object has one-to-multiple relationships with the original object. In the first paradigm (1NF), each row of the table contains only information of one instance. For example, for the employee information table in Figure 3-2, the employee information cannot be displayed in one column or two or more columns in one column; each row in the employee information table only represents the information of one employee. The information of one employee appears only once in the table. In short, the first paradigm is a non-repeated column.

2. 2NF)
The second Paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, to satisfy the second Paradigm (2NF) must satisfy the first paradigm (1NF) first ). The second Paradigm (2NF) requires that each instance or row in the database table be able to be distinguished by a unique region. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. 3-2 The employee ID (emp_id) column is added to the employee information table. Because each employee's employee ID is unique, each employee can be uniquely distinguished. This unique attribute column is called as the primary keyword, primary key, and primary code.
The second Paradigm (2NF) requires that the attributes of an object fully depend on the primary keyword. The so-called full dependency refers to the fact that there cannot be an attribute that only depends on a part of the primary keyword. If so, this attribute and this part of the primary keyword should be separated to form a new entity, the relationship between the new object and the original object is one-to-multiple. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. In short, the second paradigm is that non-primary attributes are not partially dependent on primary keywords.

3. Third Paradigm (3NF)
The third paradigm (3NF) must satisfy the second Paradigm (2NF) first ). In short, the third paradigm (3NF) requires that a database table do not contain information about non-primary keywords already contained in other tables. For example, there is a department information table, where each department has a department ID (dept_id), department name, Department profile, and other information. After listing the Department numbers in the employee information table in Figure 3-2, you cannot add the Department name, Department profile, and other information related to the department to the employee information table. If the department information table does not exist, it should also be constructed based on the third paradigm (3NF), otherwise there will be a large amount of data redundancy. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

Database three paradigm

A general understanding of the three paradigms is of great benefit to database design. In database design, in order to better apply the three paradigms, the three paradigms must be understood in a general sense (the general understanding is sufficient, not the most scientific and accurate understanding ):

1 paradigm: 1NF is an atomic constraint on attributes. Attributes must be atomic and cannot be decomposed;

Second paradigm: 2NF is the uniqueness constraint on the record and requires that the record have a unique identifier, that is, the uniqueness of the object;

Third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields, and it requires that the fields are not redundant.

No redundant database can be designed. However, databases without redundancy may not be the best. Sometimes, to improve operational efficiency, we must lower the paradigm standard and properly retain redundant data. The specific approach is to follow the third paradigm in conceptual data model design, and to reduce the paradigm standard to be considered in physical data model design. Reducing the paradigm is to add fields and allow redundancy.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.