Functional Dependency

Fully Functional Dependency (FFD)

Posted on Updated on

USE-FULL FOR FYBSC (IT) & TYBSC (CS) STUDENTS

FULLY FUNCTIONAL DEPENDENCE (FFD)

  • Fully Functional Dependence (FFD) is defined, as Attribute Y is FFD  on attribute” X, if it is FD on X and not FD on any proper subset of X.
  • For example, in relation Supplier, different cities may have the same status. It may be possible that cities like Amritsar, Jalandhar may have the same status 10.
  • So, the City is not FD on Status. But, the combination of Sno, Status can give only one

corresponding City, because Sno” is unique. Thus,

(Sno, Status) -> City

  • It  means  city  is  FD  on  composite  attribute  (Sno,  Status)  however  City  is  not  fully

functional dependent on this composite attribute, which is explained below:

(Sno, Status) -> City

X   Y

  • Here  Y  is  FD  on  X,  but  X  has  two  proper  subsets  Sno  and  Status;  city  is·  FD  .on  one proper subset .of X i.e. Sno Sno -> City
  • According to ‘FFD definition Y must not be FD .on any proper subset of X, but here City is FD in one subset .of X i.e. Sno, so City is not FFD on (Sno, Status)
  •  Consider another case of SP table:

Here, Qty is FD on combination of Sna, Pno.

(Sno, Pno)   ->   Qty

X      Y

Here,  X  has  two  proper  subsets  Sno  and  Pna. Qty  is  not  FD  on  Sno,  because  one  Sna  can supply  mare  than  .one  quantity.  Qty  is  also  not  FD  on  Pno,  because  .one  Pna  may  be supplied many times by different suppliers with different .or same quantities. So, Qty is FFD and composite attribute of (Sno, Pno) -> Qty.

OTHER FUNCTIONAL DEPENDENCIES

  • There are same rather types of functional dependencies, which play a vital role during the process .of normalization of data are

i)  Candidate Functional Dependency -A candidate functional dependency is a functional dependency that includes all attributes of the table. It should also be noted that a well-fanned dependency diagram must have at least one candidate functional dependency, and that there can be more than .one candidate functional dependency for a given dependency diagram.

ii) Primary Functional Dependency A primary functional dependency is a candidate functional dependency that is selected to determine the primary key. The determinant of the primary functional dependency is the primary key of the relational database table. Each dependency diagram must have one and only on primary functional dependency.  If a relational  database  table  has  .only  .one candidate  functional  dependency,  then  it  automatically  becomes  the  primary  functional dependency. Once the primary key has been determined, there will be three possible types of functional dependencies:

iii) Partial functional dependency – is a functional dependency where the determinant consists of key attributes, but not the entire primary key, and the determined consists of

non-key attributes.

iv) Transitive functional dependency is a functional dependency where the determinant consists of non-key attributes and the determined also consists of non-key attributes.

v) Boyce-Codd functional dependency is a functional dependency where the determinant consists of non-key attributes and the determined consists of key attributes.

vi) Multi-Value Dependency (MVD) occurs when two or more independent multi valued facts about the same attribute occur within the same table. It means that if in a relation R having A, Band C as attributes, B and Care multi-value facts about A, which is represented as A ->->B and A ->->C ,then multi value dependency exist only if B and C are independent on each other.

vii) Join Dependency exists if a relation R is equal to the join of the projections X Z. where

X, Y, Z projections of R.

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

Functional Dependency

Posted on Updated on

USEFULL FOR FYBSC (IT) & TYBSC (CS) STUDENTS

 

·         In the process of efficiently storing data, and eliminating redundancy, tables in a database are designed and created to be in one of five possible normal forms.  Each normal form contains and enforces the rules of the previous form, and, in turn, applies some stricter rules on the design of tables.·         A set of tables in a database are initially said to be in 0 normal form. First Normal Form:

  • Tables are said to be in first normal form when:-
  • The table has a primary key.-
  • No single attribute (column) has multiple values.-
  • The non-key attributes (columns) depend on the primary key.
  • Some examples of placing a table in first normal form are:

2

In first normal form the table : –

4

 

Second Normal Form:

  • Tables are said to be in second normal form when:
  • o   The tables meet the criteria for first normal form.
  • If the primary key is a composite of attributes (contains multiple columns), the non key attributes (columns) must depend on the whole key.
  • Third Normal Form:
  •  Tables are said to be in third normal form when:
  • o   The tables meet the criteria for second normal form.
  • o   Each non-key attribute in a row does not depend on the entry in another key column. Fourth
  • Normal Form:
  • Tables are said to be in fourth normal form when:
  • o  The table meets the criteria for third normal form.
  •  Situations where non-key attributes depend on the key column exclusive of other non-key columns are eliminated. Fifth Normal
  • Form:
  • Tables are said to be in fifth normal form when:
  • o The table meets the criteria for fourth normal form.
  • o The table consists of a key attribute and a non-key attribute only.

 

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

 

Functional Dependency

Posted on

USEFULL FOR FYBSC (IT) & TYBSC (CS) STUDENTS

Functional Dependency

Functional dependency (FD) is set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,…, An then those two tuples must have to have same values for attributes B1, B2, …, Bn.

Functional dependency is represented by arrow sign (→) that is X→Y, where X functionally determines Y. The left hand side attributes determines the values of attributes at right hand side.

Armstrong’s Axioms

If F is set of functional dependencies then the closure of F, denoted as F+, is the set of all functional dependencies logically implied by F. Armstrong’s Axioms are set of rules, when applied repeatedly generates closure of functional dependencies.

  • Reflexive rule: If alpha is a set of attributes and beta is_subset_of alpha, then alpha holds beta.
  • Augmentation rule: if a → b holds and y is attribute set, then ay → by also holds. That is adding attributes in dependencies, does not change the basic dependencies.
  • Transitivity rule: Same as transitive rule in algebra, if a → b holds and b → c holds then a → c also hold. a → b is called as a functionally determines b.

Trivial Functional Dependency

  • Trivial: If an FD X → Y holds where Y subset of X, then it is called a trivial FD. Trivial FDs are always hold.
  • Non-trivial: If an FD X → Y holds where Y is not subset of X, then it is called non-trivial FD.
  • Completely non-trivial: If an FD X → Y holds where x intersect Y = Φ, is said to be completely non-trivial FD.

Normalization

If a database design is not perfect it may contain anomalies, which are like a bad dream for database itself. Managing a database with anomalies is next to impossible.

  • Update anomalies: if data items are scattered and are not linked to each other properly, then there may be instances when we try to update one data item that has copies of it scattered at several places, few instances of it get updated properly while few are left with there old values. This leaves database in an inconsistent state.
  • Deletion anomalies: we tried to delete a record, but parts of it left undeleted because of unawareness, the data is also saved somewhere else.
  • Insert anomalies: we tried to insert data in a record that does not exist at all.

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com