Difference between bcnf and 3nf

Source: Internet
Author: User
Bcnf: If all attributes (including primary and non-primary attributes) of the relational mode R (u, f) do not pass any candidate keywords dependent on R, therefore, relational R belongs to bcnf. Or relational mode R. If each deciding factor contains a keyword (rather than a keyword), then bcnf's relational mode.
For example, the component management relationship model WPE (wno, PNO, Eno, qnt) lists the Warehouse number, accessory number, employee number, and quantity respectively. The following conditions are met:
A. a warehouse has multiple employees.
B. One employee only works in one warehouse.
C. Special personnel are responsible for one type of accessories in each warehouse, but one person can manage several types of accessories.
D. Parts of the same model can be placed in several warehouses.
Analysis: qnt cannot be determined by the above PNO, and is determined by the combination attribute (wno, PNO). function dependencies (wno, PNO)-> Eno exist. A person is responsible for one type of accessories in each warehouse, and one person can manage several types of accessories. Therefore, the owner can be determined only by the combination attribute (wno, PNO) -> Eno. Because an employee only works in one warehouse, there is ENO-> wno. As a type of accessories in each warehouse is the responsibility of a person, while an employee only works in one warehouse, there are (Eno, PNO)-> qnt.
Find the candidate keyword, because (wno, PNO)-> qnt, (wno, PNO)-> Eno, so (wno, PNO) can determine the entire tuples, is a candidate keyword. Based on ENO-> wno, (Eno, PNO)-> qnt, the entire tuples (Eno, PNO) can also be determined as another candidate keyword. The Eno, wno, and PNO attributes are both primary attributes and only one non-primary attribute qnt. It fully depends on any candidate keyword and is directly dependent on the function. Therefore, the relational mode is 3nf.
Analyze the main attributes. Because ENO-> wno, the main attribute Eno is the deciding factor of wno, but it is not a keyword, but only a part of the combination keyword. This results in the dependency of the master attribute wno on another candidate keyword (Eno, PNO), because (Eno, PNO)-> Eno, but in turn does not exist, p-> wno, so (Eno, PNO)-> wno is also a transfer dependency.
Although there is no dependency on the transfer of non-primary attributes to candidate key Liao, the transmission dependency of primary attributes on candidate keywords can also be troublesome. For example, a new employee is assigned to a warehouse but is currently in the internship phase and is not independently responsible for the management of certain accessories. The link cannot be inserted because a portion of PNO is missing. If an individual changes to safety regardless of accessories, the employee will also be deleted when the accessories are deleted.
Solution: divide it into the management EP (Eno, PNO, qnt), the keyword is (Eno, PNO), the job EW (Eno, wno), and the keyword is Eno
Disadvantage: the persistence of function dependencies after decomposition is poor. In this example, due to decomposition, the function dependency (wno, PNO)-> Eno is lost, and thus the original semantics is damaged. It does not show that a part in each warehouse is under the responsibility of a specialist. A component may be managed by two or more people at the same time. Therefore, the decomposed link mode reduces some integrity constraints.

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.