Differences between paradigms (examples)

As I have recently finished my graduation project, I have strengthened the basic knowledge of database design, mainly the application of paradigm. Make a summary here.

The first is the basic concept, that is, the term:

(1) entity: it is the thing that must be described by data in actual application. It is generally a term.

(2) field (fields): It is a data item, that is, what we usually call "column ".

(3) Record: a set of related data items unique to an instance of an object, that is, what we usually call "rows ".

(4) Key: a field or field set that uniquely identifies a record.

(5) primary key: the key that uniquely identifies a record in a table. Each primary key should have the following features: 1. Unique. 2. Minimal (select the least key combination as much as possible ). 3. Not empty. 4. Non-updatable (cannot be changed at any time)

(6) foreign key (Foreign keys): Copies the primary key fields of the relevant records connecting the Parent and Child tables.

(7) dependent table: weak entity is a child table that must be identified by a parent table.

(8) associative table: A child table of two parent tables in many-to-many relationships.

(9) entity Integrity: Each table must have a valid primary key.

(10) integrity of reference: there are no mismatched foreign key values.

 

Then there is the topic of this article: "paradigm. Paradigm mainly includes 1nf, 2nf, 3nf, bcnf, and 4nf ). The following is a one-to-one explanation based on examples. (The following examples are from database design and development: a visual approach, Raymond Frost, John Day, Craig Van Slyke, and the primary key is underlined)

Example:

Assume that you need to design a database for an online video and entertainment hall to record the consumption time of each member. The entertainment hall must have basic information about its members, including their email addresses, passwords for accessing the online entertainment hall, their names, and phone numbers. Each time a Member accesses the entertainment hall, a process ID is created, and the start time and end time of the process must be recorded.

Is a standardized design. The following shows an nonstandard design.

So what are the advantages and disadvantages of nonstandard design?

Advantage: Obviously, there is no foreign key in the nonstandard design. This simplifies some related operations.

Disadvantage: in fact, potential problems are fatal.

1. Update problems. When two primary keys (IDS) are different, but other data with identical information is updated, one of them is updated, but the other is not updated, resulting in inconsistent data.

2. Insert problems. When a piece of data is inserted, the insert information is only part of the table attribute. For example, in the above example, only one basic member information (email, password, fname, INAME, phone) is inserted, which is not allowed in the nonstandard design because the data information is incomplete.

These problems lead to the emergence of some rules when constructing relational databases. These are the paradigm.

 

Definition of the first paradigm: all fields in the table only contain single values.

Apparently, the second row of Data violates the first paradigm. Because the phone field has two values. PS: Khan, How Do Americans use passwords as weapons ???

Solutions:

Before discussing a more advanced paradigm, you must understand the concept of a factor.

Determinant: a field or a group of fields that control or determine the values of other fields.

 

Second paradigm definition: each non-key field in the table is determined by the entire primary key, and cannot be determined by a part of the primary key itself. Therefore, the 2nf violation only appears in the table where the primary key is composed of more than one field.

In the student_enroll table above, Id Determines fname, INAME, dorm, phone, and Section $ call_no determines fname, INAME, dorm, phone, and grade. If the second paradigm is violated, Jim Green cannot be updated because Jim Green has two phone numbers.

Solutions:

Third paradigm definition: The table does not have non-key fields that can be identified by other non-keywords.

3nf violations may occur in tables with multiple non-key fields.

In membervisit, the primary key (ID) determines the email, password, fname, INAME, and phone. Email determines password, fname, INAME, and phone. This violates the third paradigm. This causes Luce to store two passwords .. This is obviously incorrect.

Solution:

Bcnf definition: Each definite factor is a key. This paradigm is especially used to deal with the primary key portion of a non-key field.

In the preceding quarterly_bonus table, a non-key field SSN determines the employee_id, which is part of the key. In fact, SSN and employee_id are mutually determined. Therefore, it violates the bcnf paradigm. Employee 8857 has two different SSNs in the figure.

Solution:

Fourth paradigm definition: In a full key table, a part of a key can determine multiple values of at most one other field. 4nf is only used for full-key tables.

To understand the fourth paradigm, we need to introduce the concept of multi-value dependencies (MVPs): one or a group of fields that can control or determine multiple values of another field.

In the above skill table, email is multi-value dependent. This violates the fourth paradigm. If we delete Luce's German identity. We also lost his favorite information about tennis.

Solution:

The two tables are not associated. Each table contains only one MVPs.

 

Detection paradigm violation:

1. 1nf violation occurs when multiple values are stored in one unit.

2. 2nf violations only occur in tables with associated keys, and non-key fields only depend on a portion of the primary key.

3. 3nf violation occurs when one non-key field determines another non-key field, the table may have a key of any size.

4. bcnf violation occurs when a part of the primary key is determined by a non-key field. These violations can only occur in tables where the primary key is composed of associated keys.

5. The 4nf violation occurs when the table's primary key is connected by at least three keys without non-key fields. In addition, a part of the key determines multiple values of the other part of the key.

 

The solution to the paradigm violation is to split the compound determining factor into a single determining factor.