Three-paradigm Review

At the beginning of the first semester, the database system was designed.
Mr. Xiong reviewed the following three paradigms. Although he had learned it, he forgot about it.
Let's review it now. To learn the story, Zhixin can be the teacher ye.

A paradigm is a set of relational patterns at a certain level. The relationship in the relational database must meet certain requirements. The first paradigm, or 1nf for short, meets the minimum requirements. The second paradigm, or 2nf for short, further satisfies some requirements on the basis of the first paradigm. There are currently six other paradigms: 1nf, 2nf, 3nf, bcnf, 4nf, and 5nf.

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 appear, you may need to define a new object. A new object consists of duplicate attributes, and 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 second Paradigm (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 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, a department information table contains the Department ID (dept_id), department name, and department description. 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.

BC Paradigm (bcnf)

For example, in the Relationship Mode STJ (S, T, J), s indicates the student, t indicates the teacher, and J indicates the course. Assume that each teacher only teaches one course. A course is composed of multiple teachers. A student selects a course and determines a fixed teacher. Therefore, the following function dependencies are available:
(S, j) → T, (S, T) → J, t → J
Obviously, both (S, J) and (S, T) can be used as candidate codes. This relational mode does not have any dependency or partial dependency on code passing for non-primary attributes. Therefore, STJ is 3nf. On the other hand, t → J, that is, t determines the attribute set, but t is only the primary attribute. It is neither a candidate key nor a candidate key.

If the relational mode is rε bcnf, we can see from the definition that there is no property transfer in R dependent on or partially dependent on any candidate key, so there must be rε 3nf. However, if R is 3nf, R may not be bcnf.
3nf and bcnf measure the degree of normalization of the relational model based on function dependency.
If all the relational modes in a relational database belong to 3nf, the insertion and deletion exceptions have been largely eliminated, however, the dependency between the primary attribute and the candidate code may be partially dependent on and transmitted. Therefore, the separation of the link mode is incomplete.
If all the relational modes in a relational database belong to bcnf, in the scope of function dependency, it has completely decomposed the modes, reaching the highest degree of standardization, the insertion and deletion exceptions are eliminated.

Multi-value dependency and 4nf)

Previously, we discussed the paradigm of the relational model in the scope of function dependencies. If you only consider the function dependency as a data dependency, The Relationship Mode of bcnf is perfect, but if you consider other data dependencies, such as multi-value dependency, there is still a problem with the relationship pattern that belongs to bcnf.

This example will help you better understand

There is a relational mode R (athlete ID, competition item, score, competition type, competition supervisor). If it is specified that each athlete participates in a competition item, there is only one score; each competition item belongs to only one competition category; each competition category has only one competition supervisor.

Please answer the following questions:

(1) write the basic FD and key code of model r according to the above rules

(2) describe why R is not 2nf, and decompose R into 2nf mode sets.

(3) further decomposed into 3nf mode Sets

Solution:

(1) There are three basic FD types:

(Athlete ID, Competition Project) → score; Competition Project → competition category; competition category → competition Supervisor

The key code of the relationship R (that is, the candidate Code) is: (athlete number, Competition Project)

(2) Two such FD in R:

(Athlete ID, Competition Project) → (Competition category, competition supervisor)

Competition Project → (Competition category, competition supervisor)

Some function dependencies of non-primary attributes on primary attributes exist, so R is not 2nf.

R should be divided into: r1 (Competition Project, Competition category, competition supervisor)

R2 (Athlete ID, competition item, Score)

In this case, R1 and R2 are 2nf

(3) R2 is already 3nf, but R1 has two FD:

Competition item → competition category;

Competition category → competition Supervisor

There is a function dependency between non-primary attributes and primary attributes, so R is not 3nf.

R1 is divided into R11 (Competition Project, competition category)

R12 (Competition category, competition supervisor)

Follow these steps to normalize the link mode:
(1) project the 1nf relationship to eliminate some function dependencies between non-primary attributes and codes in the original relationship, and convert the 1nf relationship into several 2nf relationships.
(2) The 2nf relationship is projected to eliminate the function dependency of non-primary attributes on codes in the original relationship, resulting in a group of 3nf
(3) project the 3nf relationship to eliminate some function dependencies of the primary attribute on codes in the original relationship and transmit function dependencies, and obtain a group of bcnf relationships.
(4) projected the bcnf relationship to eliminate the multi-value dependency of non-trivial functions in the original relationship and generate a set of 4nf