FUNCTIONAL DEPENDENCY

Q- WHAT IS FUNCTIONAL DEPENDENCY ?
ans-In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database.

Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Yvalue; R is then said to satisfy the functional dependency X → Y. Equivalently, the projection  is a function, i.e. Y is a function of X.^[1][2] In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y thedependent set. A functional dependency FD: X → Y is called trivial if Y is a subset of X.

Q what is armstrong AXIOMS?

Armstrong's Axioms

If F is a 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 a set of rules, that when applied repeatedly, generates a 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 holds. a → b is called as a functionally that determines b.

Trivial Functional Dependency

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

Q-what is the need of normalization ?

 ans- normalization is used to   different   anomalies  in update, delete and insert operations soo 

normalization is used to remove those anomalies..

Q what is anomalies ?

Tables which meet the minimum requirement for a relation may not have an effective or appropriate structure. Changing the data in such tables can have undesirable consequences, called modification anomalies. 

Anomalies can be eliminated by redefining the relation into two or more relations. These redefined relations, or normalized, relations are preferred.

Q what  are different anomalies arises in the database design:?

 {  

ans-  there are mainly 3 types of anomalies in the database design .

  A- insertion anomalies 

     if  it is impossible to insert any data due to in sufficient details .  suppose in a school db enrollment of a student without class no is not possible and a student dont have class no  now ..

B- deletion anomalies -  this type of anomalie arises  when due to delete of  one tupple some  other data lost .. suppose an employee consist   a key no of its already covered project , now if  the details of that employee deleted than entire details of its covered project lost ..

C- update anomalies- update anomalies arises  a problem in the update process , ex-

  need to update same details at different  places...

}

Q what is a loss less join ?

ans-a decomposition  is said to be loss less if the we do natural join than data must remain same , 

Q what is dependency preservation ?

Dependency Preservation

1. Another desirable property in database design is dependency preservation.
   • We would like to check easily that updates to the database do not result in illegal relations being created.
   • It would be nice if our design allowed us to check updates without having to compute natural joins.
   • To know whether joins must be computed, we need to determine what functional dependencies may be tested by checking each relation individually.
   • Let F be a set of functional dependencies on schema R.
   • Let  be a decomposition of R.
   • The restriction of F to  is the set of all functional dependencies in  that include only attributes of  .
   • Functional dependencies in a restriction can be tested in one relation, as they involve attributes in one relation schema.
   • The set of restrictions  is the set of dependencies that can be checked efficiently.
   • We need to know whether testing only the restrictions is sufficient.
   • Let  .
   • F' is a set of functional dependencies on schema R, but in general,  .
   • However, it may be that  .
   • If this is so, then every functional dependency in F is implied by F', and if F' is satisfied, then F must also be satisfied.
   • A decomposition having the property that  is a dependency-preserving decomposition.

Normalization

If a database design is not perfect, it may contain anomalies, which are like a bad dream for any database administrator. 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 it could lead to strange situations. For example, when we try to update one data item having its copies scattered over several places, a few instances get updated properly while a few others are left with old values. Such instances leave the database in an inconsistent state.
• Deletion anomalies − We tried to delete a record, but parts of it was 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.

Normalization is a method to remove all these anomalies and bring the database to a consistent state.

First Normal Form

First Normal Form is defined in the definition of relations (tables) itself. This rule defines that all the attributes in a relation must have atomic domains. The values in an atomic domain are indivisible units.

We re-arrange the relation (table) as below, to convert it to First Normal Form.

Each attribute must contain only a single value from its pre-defined domain.

Second Normal Form

Before we learn about the second normal form, we need to understand the following −

• Prime attribute − An attribute, which is a part of the prime-key, is known as a prime attribute.
• Non-prime attribute − An attribute, which is not a part of the prime-key, is said to be a non-prime attribute.

If we follow second normal form, then every non-prime attribute should be fully functionally dependent on prime key attribute. That is, if X → A holds, then there should not be any proper subset Y of X, for which Y → A also holds true.

We see here in Student_Project relation that the prime key attributes are Stu_ID and Proj_ID. According to the rule, non-key attributes, i.e. Stu_Name and Proj_Name must be dependent upon both and not on any of the prime key attribute individually. But we find that Stu_Name can be identified by Stu_ID and Proj_Name can be identified by Proj_ID independently. This is called partial dependency, which is not allowed in Second Normal Form.

We broke the relation in two as depicted in the above picture. So there exists no partial dependency.

Third Normal Form

For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy −

• No non-prime attribute is transitively dependent on prime key attribute.
• For any non-trivial functional dependency, X → A, then either −
  X is a superkey or,
  • A is prime attribute.

We find that in the above Student_detail relation, Stu_ID is the key and only prime key attribute. We find that City can be identified by Stu_ID as well as Zip itself. Neither Zip is a superkey nor is City a prime attribute. Additionally, Stu_ID → Zip → City, so there existstransitive dependency.

To bring this relation into third normal form, we break the relation into two relations as follows −

Boyce-Codd Normal Form

Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms. BCNF states that −

• For any non-trivial functional dependency, X → A, X must be a super-key.

In the above image, Stu_ID is the super-key in the relation Student_Detail and Zip is the super-key in the relation ZipCodes. So,

Stu_ID → Stu_Name, Zip

and

Zip → City

Which confirms that both the relations are in BCNF.