Classification of Dependencies in DBMS –

S.NO Classification of Dependencies Which Normal Form Remove these Dependencies
1 Partial Dependencies Second Normal Form (2NF)
2 Transitive Dependencies Third Normal Form (3NF)
3 Multivalued Dependencies Fourth Normal Form (4NF)
4 Join Dependencies Fifth Normal Form (5NF)
5 Inclusion Dependency (Dependencies among the Relations/Tables or Databases)

Partial dependencies and Transitive Dependencies are types of Functional Dependencies.

Functional Dependency

A Functional dependency is a relationship between attributes.

For example, if we know the value of customer account number, we can obtain customer address, balance etc. By this, we say that customer address and balance is functionally dependent on customer account number.

In general terms, attribute Y(customer address and balance) is functionally dependent on the attribute X(customer account number), if the value of X determines the value of Y.

For More about Functional Dependency – Click Here

Partial Functional Dependency – 

A Functional Dependency in which one or more non key attributes are functionally depending on a part of the primary key is called partial functional dependency. or

where the determinant consists of key attributes, but not the entire primary key, and the determined consist of non-key attributes.

For example, Consider a Relation R(A,B,C,D,E) having 
FD : AB → CDE where PK is AB.

Then, { A → C; A → D; A → E; B → C; B → D; B → E } 
        all are Partial Dependencies.

To know more about Partial Dependency – Click Here

Transitive Dependency –

Given a relation R(A,B,C)  then dependency like A–>B, B–>C   is a transitive dependency, since   A–>C is implied .

In the above Fig 1, 
SSN --> DMGRSSN is a transitive FD 
                {since SSN --> DNUMBER and DNUMBER --> DMGRSSN hold}

SSN --> ENAME is non-transitive FD since there is no set of attributes X 
                where SSN --> X and X --> ENAME.

To know more about Transitive Dependency – Click Here

Multivalued Dependency 

Consider a relation Faculty (FID, Course, Book) which consists of two multivalued attributes (Course and Book). The two multivalued attributes are independent of each other.

FID Course Book
1 C1/C2 B1/B2
2 C1 B1

 

 

FID Course Book
1 C1 B1
1 C1 B2
1 C2 B1
1 C2 B2
2 C1 B1

it is clear that there are multiple copies of the information about Course and Book. This is an example of a multivalued dependency which occurs when a relation has more than one independent, multivalued attribute.

A multivalued dependency occurs when a relation R has attributes A(FID), B(Course), and C(Book) such that

  • A determines a set of values for B
  • A determines a set of values for C and
  • B and C are independent of each other. (No relation between Course and Book)

These multivalued dependencies can be indicated as follows :

  • (FID → →  Course)
  • (FID → → Book)

To know more about Multivalued Dependency – Click Here

Join Dependency 

Let R be a relation. Let A, B, …, Z be arbitrary subsets of R’s attributes. R satisfies the JD
* ( A, B, …, Z )
if and only if R is equal to the join of its projections on A, B, …, Z.

A join dependency JD(R1, R2, …, Rn) specified on relation schema R, is a trivial JD, if one of the relation schemas Ri in JD(R1, R2, ….,Rn) is equal to R.

Inclusion Dependencies

An inclusion dependency (shortly called as INDs) are the dependencies which exists when some columns of a relation are contained in other columns (usually of a second relation). The example of Inclusion dependency is a foreign key constraint or Referential Integrity Constraint as it states that the referring column(s) in one relation must be contained in the primary key column(s) of the referenced relation.

Inclusion Dependencies - Classification of Dependencies in DBMS

Objective of Inclusion Dependencies:

To formalize two types of interrelational constraints which cannot be expressed using F.D.s or MVDs:

  • Referential integrity constraints
  • Class/subclass relationships

Inclusion dependencies are mostly key-based, i.e. which involve only keys. Referential Integrity or Foreign key constraints are a good example of key-based inclusion dependencies. An ERD(er diagram) that involves ISA hierarchies also leads to key-based inclusion dependencies.
If all inclusion dependencies are key-based then we rarely have to worry about splitting attribute groups that participate in inclusions, since decompositions usually do not split the primary key.

Note that going from 3NF to BCNF always involves splitting some key, hopefully not the primary key, since the dependency guiding the split is of the form X → A where A is part of a key.

     

Incoming search terms:

  • inclusion dependencies in dbms
  • inclusion dependency
  • inclusion dependencies
  • inclusion dependency in dbms with example
  • inclusion dependence
  • multivalued nd join functional dependency in dbms
  • key dependency in dbms
  • types of dependency in dbms with example notes
  • partial and transitive dependency
  • inclusion dependency in dbms

Leave a Reply