"Go" database Paradigm (1NF 2NF 3NF BCNF)

Paradigm Judgment Flowchart

1. Relationship between four paradigms

2. The second paradigm, the third paradigm, the BCNF distinction:

2NF: Between the non-primary key column and the primary key column, whether it is dependent on the primary key or a part of the primary key (only dependent on a primary key);

3NF: There is no dependency between the non-primary key columns, only the primary key is directly dependent.

BCNF: There is no dependency between the primary key columns.

The general relational database satisfies the first paradigm and first determines that it is a few primary key attributes.

First paradigm : columns cannot be divided

Second Paradigm : non-primary key properties all depend on primary key properties

Third Normal: No dependency between non-primary key attributes

The IV Paradigm: No dependency between primary key attributes

3. First paradigm: Columns are not divided. Each column is an indivisible basic data item.

A. Counter-example:

Studyno	Name	Sex	Contact
20040901	John	Male	Email:[email protected],phone:222456
20040902	Mary	Famale	Email:[email protected] phone:123455

The contact field can be re-divided and does not conform to the first paradigm.

B. Positive solution:

Studyno	Name	Sex	Email	Phone
20040901	John	Male	Email:[email protected]	222456
20040902	Mary	Famale	Email:[email protected]	123455

4. Second paradigm: On the basis of the first paradigm, for a multi-key table, a non-primary attribute cannot be partially dependent on the primary key (eg: only one primary key); For a single-key table, there is no partial dependency (only one primary key, all dependent), fully compliant.

Better eg:

Order schedule: "OrderDetail" (orderid,productid,unitprice,discount,quantity,productname).
Because we know we can order a variety of products in one order, so a single OrderID is not enough to be the primary key, the primary key should be (Orderid,productid). Obviously Discount (discount), Quantity (quantity) depends entirely on (depending on) Yu Si (Oderid,productid), and unitprice,productname only depends on ProductID. Therefore, the OrderDetail table does not conform to 2NF. Designs that do not conform to 2NF tend to produce redundant data.
You can split the "orderdetail" table into "OrderDetail" (orderid,productid,discount,quantity) and "Product" (Productid,unitprice, ProductName) to eliminate multiple repetitions of unitprice,productname in the original order table.

A. Counter-example:

Studyno	Name	Sex	Email	Phone	Classno	Classaddress
20040901	John	Male	Email:[email protected]	222456	200401	#12A
20040902	Mary	Famale	Email:[email protected]	123455	200402	#8A

The primary keys are Studyno and Classno. The classaddress part relies on the primary key Classno and needs to become two tables.

B. Positive solution:

Student table

Studyno	Name	Sex	Email	Phone
20040901	John	Male	Email:[email protected]	222456
20040902	Mary	Famale	Email:[email protected]	123455

Classroom table

Classno	Classaddress
200401	#12A
200402	#8A

C. Eliminate data redundancy and increase, delete and change anomalies.

(1) Data redundancy:

The same course by N students elective, "credit" repeated n-1 times, the same student elective m courses, name and age repeated m-1 times.

(2) Update exception:

If the credit of a course is adjusted, the "credits" value of all the rows in the data sheet should be updated, otherwise the same course credit will be different.

(3) Insert exception:

Suppose a new course is to be opened and no one has yet been enrolled. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword.

(4) Delete exception:

Assuming that a group of students has completed elective courses, these elective records should be removed from the database table. At the same time, however, the course name and credit information were also removed. Obviously, this can also lead to an insertion exception.

5. The third paradigm: on the basis of the second paradigm, a non-critical field cannot pass a function dependency on either primary key. The non-primary key column must be directly dependent on the primary key and cannot be passed on. That cannot exist: non-primary key column A relies on non-primary key column B, and non-primary key column B depends on the primary key. In summary, there can be no dependency between non-primary key columns.

Better eg:

The Order Table "order" (orderid,orderdate,customerid,customername,customeraddr,customercity) primary key is (OrderID).
The non-primary key columns, such as orderdate,customerid,customername,customeraddr,customercity, are completely dependent on the primary key (OrderID), so it conforms to 2NF. However, the problem is that customername,customeraddr,customercity is directly dependent on the CustomerID (non-primary key column), rather than directly relying on the primary key, it is passed through to rely on the primary key, so it does not conform to 3NF.
By splitting "order" to "order" (Orderid,orderdate,customerid) and "Customer" (CUSTOMERID,CUSTOMERNAME,CUSTOMERADDR, customercity) thus reaching 3NF.

A. Counter-example:

Studyno	Name	Sex	Email	Phone	Bounslevel	Bouns
20040901	John	Male	Email:[email protected]	222456	Excellent	￥1200
20040902	Mary	Famale	Email:[email protected]	123455	Liang	￥800

The primary key is Studyno, with only one primary key Studyno, and conforms to the second paradigm. However, there is a dependency between the non-primary key columns Bounslevel and Bouns.

B. Positive solution:

Student table

Email:[email protected]

TD valign= "Top" >

Studyno	name	< p>sex	Email	Phone	Bounsno
20040901	john	male	222456	1
20040902	Mary	famale	Email:[email protected]	123455	2

Scholarship Level table

Bounsno	Bounslevel	Bouns
1	Excellent	￥1200
2	Liang	￥800

C. Eliminate data redundancy and increase, delete and change anomalies.

6. Boyce-Christie's Paradigm (BCNF): On the basis of the third paradigm, if no field exists in the database table, the transfer function dependency on either of the candidate key fields conforms to the third normal form. That is, there is no key field that determines the key field.

A. Counter example: Storehousemanager

Storehouseid (Warehouse ID)	GOODSID (Product ID)	ManagerID (Administrator ID)	Goodsnum (number of items)
001	20130104	1	200

The primary key is

(warehouse ID, item id) → (Administrator id, quantity) or

(admin ID, item id) → (warehouse ID, quantity),

(warehouse ID, item ID) and (Administrator ID, item ID) are candidate keywords for storehousemanage, the only non-critical fields in the table are the number

Meet the third paradigm. However, there are key fields that determine key field conditions.

(warehouse id) → (Administrator id)

(Administrator id) → (warehouse id)

B. Positive solution:

Warehouse Management Table

Storehouseid (Warehouse ID)	GOODSID (Product ID)	Goodsnum (number of items)
001	20130104	200

Warehouse Table

Storehouseid (Warehouse ID)	ManagerID (Administrator ID)
001	1

C. Elimination of increase, deletion, modification of anomalies.

(1) Delete exception:

When the repository is emptied, all "store item ID" and "quantity" information is deleted, and the "Warehouse ID" and "Administrator ID" information are also deleted.

(2) Insert exception:

You cannot assign an administrator to a warehouse when no items are stored in the warehouse.

(3) Update exception:

If the warehouse has been replaced by an administrator, the administrator ID for all rows in the table is modified.  

The higher the application's paradigm level, the more tables. There are many problems with the table:
1 query to join multiple tables, increase the complexity of the query
2 query needs to connect multiple tables, reducing database query performance
In the current situation, disk space costs are negligible, so the problem caused by data redundancy is not the reason to apply the database paradigm.
Therefore, it is not the case that the higher the application paradigm, the better, depends on the actual situation. The third paradigm has largely reduced data redundancy and reduced the insertion exception, update exception, and deletion exception. Therefore, most of the cases applied to the third paradigm are sufficient, under certain circumstances the second paradigm is also possible.

"Go" database Paradigm (1NF 2NF 3NF BCNF)