BOYCE-CODD NORMAL FORM (BCNF) - IBM Mainframe

Database relations are designed so that they have neither partial dependencies nor transitive dependencies, because these types of dependencies result in update anomalies. A functional dependency describes the relationship between attributes in a relation. For example, if 'A' and 'B' are attributes in relation R, 'B' is functionally dependent on 'A' (denoted by A->B), if each value of 'A' is associated with exactly one value of 'B'. For example in the CONTACTS table, we can say that L_NAME, F_NAME and COMPANYJD are functionally dependent on CONTACTJD. These dependencies are expressed as follows:

BOYCE-CODD NORMAL FORM (BCNF)

The left-hand side and the right-hand side of a functional dependency are sometimes called the determinant and dependent respectively. As the definition states, the determinant and the dependent are both, sets of attributes. When the set contains more than one attribute we will use the braces to enclose them as shown above. A functional dependency A-»B is full functional dependency if removal of any attribute from 'A' results in the dependency not being sustained any. A functional dependency A->B is partially dependent if there is some attribute that can be removed from 'A' and the dependency still holds. For example, consider the following functional dependency: {L_NAME, F_NAME, COMPANY_ID} -» CONTACT_ID

It is correct to say that each value of {LNAME, FJslAME and COMPANYJD} is associated with a single value of CONTACTJD. However, it is not full functional dependency because CONTACT ID is also functionally dependent on a subset of {LNAME, FJslAME and COMPANYJD}, namely {L_NAME and F_NAME}.

Transitive dependency, as we have seen, is a condition where A, B and C are attributes of a relation such that if A-»B and B-»C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C). Transitive dependency is a description of a type of functional dependency that occurs when the following functional dependencies hold between A, B and C of a relation:

A-->B and B-->c

Then the transitive dependency A->C exists via attribute B. This condition holds provided that A is not functionally dependent on B or C. For example, consider the following functional dependencies within an EMPLOYEE-DEPARTMENT relationship. The EMPLOYEE relation has attributes like EMP_NO, NAME, ADDRESS, POSITION, SALARY, DEPTJD, etc. The DEPARTMNET relation has attributes like DEPTJD, DEPTNAME, MANAGER and so on. Now consider the following dependencies: EMF NO->DEPT ID and DEPT ID-M3EPT NAME'

Then the transitive dependency EMP_NO->DEPT_NAME exists via DEPTJD attribute. This condition holds, as EMPNO is not functionally dependent on DEPTJD or DEPTJ4AME. One of the major aims of relational database design is to group attributes into relations so as to minimize data redundancy and thereby reduce the file storage space required by the implemented base relations. Relations that have redundant data may have problems called update anomalies, which are classified as insertion, deletion or modification anomalies. These anomalies occur because, when the data in one table is deleted or updated or new data is inserted, the related data is also not correspondingly updated or deleted. Sometimes when a deletion in one table occurs, it will leave meaningless data in other tables. One of the aims of the normalization is to remove the update anomalies.

Boyce-Codd Normal Form (BCNF) is based on functional dependencies that take into account all candidate keys in a relation. For a relation (table) with only one candidate key, third normal form and BCNF are equivalent. A relation is in BCNF if and only if every determinant is a candidate key. To test whether a relation is in BCNF, we identify all the determinants and make sure that they are candidate keys. A determinant is an attribute or a group of attributes on which some other attribute is fully functionally dependent.

The difference between third normal form and BCNF is that for a functional dependency A->B, the third normal form allows this dependency in a relation if 'B' is a primary-key attribute and 'A' is not a candidate key, whereas BCNF insists that for this dependency to remain in a relation, 'A' must be a candidate key. Therefore BCNF is a stronger form of the third normal form, such that every relation in BCNF is also in the third normal form. However, a relation in the third normal form is not necessarily in BCNF.

Consider the following relation INTERVIEW, which contains the details of the arrangements for interviews of candidates by the in-house technical experts of a company. The interviewers are allocated a specific room on the day of the interview. A room can be allocated to several interviewers as required, throughout the day. A candidate is interviewed only once on a given date. The relation is shown below:

INTERVIEW Table

INTERVIEW Table

The above table has three composite candidate keys, which overlap by sharing the common attribute INTDATE as shown below:

  • CANDIDATEJD and INTDATE
  • INTVRJD, INT_DATE and INTJTIME
  • ROOM_NO, INT_DATE and INTJTIME

We make the composite candidate key (CANDIDATEJD, INTDATE) as the primary key. Now the relation has the following functional dependencies:

  • {CANDIDATEJD, INT_DATE} -HINTJIME, INTVRJD, ROOM_NO} — Primary Key
  • {INTVRJD, INT_DATE, INTJTIME} -^CANDIDATE ID — Candidate Key
  • {ROOM_NO, 1NT_DATE, INTJTIME} -^INTVRJD, CANDIDATEJD} — Candidate Key
  • {INTVRJD, 1NT_DATE} ->ROOM_NO

The first three dependencies are all candidate keys for this relation and will not cause any problems for the relation. The only functional dependency that requires discussion is the fourth one—(INTVRJD, INT_DATE}-»ROOM_NO. Even though the {INTVRJD, INT_DATE} is not a candidate key for the relation, this functional dependency is allowed in the third normal form, because ROOMNO is a primary key attribute being part of the candidate key— ROOMNO, INT_DATE and INTJTIME. As there are no partial or transitive dependencies on the primary key (CANDIDATEJD and INTJDATE) and since the functional dependency {INTVRJD, lNTJDATE}->ROOM_NO is allowed, the relation is in the third normal form.

However, this relation is not in BCNF (a stronger form of the third normal form), due to the presence of the {INTVRJD, INT_DATE} determinant, which is not a candidate key for the relation. BCNF requires that all the determinants in a relation must be a candidate key for the relation. As a consequence the INTERVIEW relation may suffer from update anomalies.

For example, to change the room number for Interviewer E001 on 24-May-1999, we need to update two rows. If only one row is updated with new room number, this results in an inconsistent state for the database. Even though it is perfectly possible that the same interviewer can conduct the interview in different rooms it is against our original assumption that an interviewer is assigned a room for the day. So to transform the INTERVIEW relation to BCNF, we must remove the violating functional dependency by creating two new relations INTERVIEW and ROOM as shown below:

INTERVIEW Table

INTERVIEW Table

ROOM Table

ROOM Table

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics