CH 14 - Basics of Functional Dependencies and Normalization for Relational Databases

Question 1

14.3. Why should NULLs in a relation be avoided as much as possible? Discuss the problem of spurious tuples and how we may prevent it.

Answer 1

• We recommended limiting NULL values, which cause prob- lems during SELECT, JOIN, and aggregation operations.
• This can waste space at the storage level
• 5 Another problem with NULLs is how to account for them when aggregate operations such as COUNT or SUM are applie
• SELECT and JOIN operations involve comparisons; if NULL values are present, the results may become unpredictable.6
• Moreover, NULLs can have multiple interpreta- tions,
• This produces a particularly bad schema design because we cannot recover the information that was originally in EMP_PROJ from EMP_PROJ1 and EMP_LOCS. If we attempt a NATURAL JOIN operation on EMP_PROJ1 and EMP_LOCS, the result produces many more tuples than the original set of tuples in EMP_PROJ. In Figure 14.6, the result of applying the join to only the tuples for employee with Ssn = “123456789” is shown (to reduce the size of the resulting rela- tion). Additional tuples that were not in EMP_PROJ are called spurious tuples because they represent spurious information that is not valid. The spurious tuples are marked by asterisks (*) in Figure 14.6. It is left to the reade s

Question 2

14.5. What is a functional dependency?

Answer 2

• which is the basic tool for ana- lyzing relational schemas, a
• s. Functional dependencies specify semantic constraints among the attributes of a relation schema. N
• Definition. A functional dependency, denoted by X → Y, between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples that can form a relation state r of R. The constraint is that, for any two tuples t1 and t2 in r that have t1[X] = t2[X], they must also have t1[Y] = t2[Y].
• If a constraint on R states that there cannot be more than one tuple with a given X-value in any relation instance r(R)—that is, X is a candidate key of R—this implies that X → Y for any subset of attributes Y of R (because the key constraint implies that no two tuples in any legal state r(R) will have the same value of X). If X is a candidate key of R, then X → R.
  ■ If X → Y in R, this does not say whether or not Y → X in R.

Question 3

desirable properties of a relational decomposition

Answer 3

as. We discuss desirable properties of relational decomposition— nonadditive join property and functional dependency preservation property. A gen

Question 4

14.1. Discuss attribute semantics as an informal measure of goodness for a rela- tion schema.

Answer 4

• l, the easier it is to explain the semantics of the relation—or in other words, what a relation exactly means and stands for—the better the relation schema design will be. T
• Do not combine attributes from multiple entity types and relationship types into a sin- gle relation

Question 5

14.2. Discuss insertion, deletion, and modification anomalies. Why are they con- sidered bad? Illustrate with examples.

Answer 5

• Storing natural joins of base relations leads to an additional problem referred to as update anomalies.
• Insertion: ple, to insert a new tuple for an employee who works in department number 5, we must enter all the attribute values of department 5 correctly so that they are con- sistent wit
• Deletion: . If we delete from EMP_DEPT an employee tuple that happens to represent the last employee working for a particular depart- ment, the information concerning that department is lost inadvertently from the database.
• Modification: if we change the value of one of the attri- butes of a particular department—say, the manager of department 5—we must update the tuples of all employees who

Question 6

14.4. State the informal guidelines for relation schema design that we discussed. Illustrate how violation of these guidelines may be harmful.

Answer 6

• Do not combine attributes from multiple entity types and relationship types into a sin- gle relation
• Guideline 2. Design the base relation schemas so that no insertion, deletion, or modification anomalies are present in the relations. If any anomalies are present,4
• ne 3. As far as possible, avoid placing attributes in a base relation whose values may frequently be NULL. If NULLs are unavoidable, make sure that they apply in exceptional cases only and do not apply to a majority of tuples in the relation.
• ideline 4. Design relation schemas so that they can be joined with equality conditions on attributes that are appropriately related (primary key, foreign key) pairs in a way that guarantees that no spurious tuples are generated. Avoid relations that contain matching attributes that are not (foreign key, primary key) combina- tions because joining on such attributes may produce spurious tuples.

Question 7

14.6. Why can we not infer a functional dependency automatically from a partic- ular relation state?

Answer 7

• A functional dependency is a property of the relation schema R, not of a particular legal relation state r of R. Therefore, an FD cannot be inferred automatically from a given relation extension r but must be defined explicitly by someone who knows the semantics of the attributes of R.
• we cannot determine which FDs hold and which do not unless we know the meaning of and the relationships among the attribut

Question 8

14.8. Define first, second, and third normal forms when only primary keys are considered.

Answer 8

• 1NF It states that the domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute. Hence, 1NF disallows having a set of values, a tuple of values, o. All of the key attributes are defined. All attributes are dependent on the primary key.
• Definition. A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R.
• Definition. According to Codd’s original definition, a relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key.

Question 9

14.8. How do the general definitions of 2NF and 3NF, which consider all keys of a relation, differ from those that consider only primary keys?

Answer 9

• . Notice that this does not affect the definition of 1NF since it is independent of keys and functional depen- dencies.
• general definitions of 2NF and 3NF that take all candidate keys of a relation into account
• Definition. A relation schema R is in second normal form (2NF) if every nonprime attribute A in R is not partially dependent on any key of R.12
• . A relation schema R is in third normal form (3NF) if, whenever a nontrivial functional dependency X → A holds in R, either (a) X is a superkey of R, or (b) A is a prime attribute of R.13
• This general definition can be applied directly to test whether a relation schema is in 3NF; it does not have to go through 2NF first. In other words, if a relation passes the general 3NF test, then it automatically passes the 2NF test

Question 10

Prime attribute

Answer 10

• s a general definition of prime attribute, an attribute that is part of any candidate key will be considered as prime. P

Question 11

14.9. What undesirable dependencies are avoided when a relation is in 2NF?

Answer 11

A proper subset of a key of R functionally determines a nonprime attribute. Here we have a partial dependency that violates 2NF.

Question 12

14.10. What undesirable dependencies are avoided when a relation is in 3NF?

Answer 12

A nonprime attribute determines another nonprime attribute. Here we typi- cally have a transitive dependency that violates 3NF.

Question 13

fully functionally dependent

Answer 13

A functional dependency X → Y is a full functional dependency if removal of any attribute A from X means that the dependency does not hold anymore

Question 14

1. Reflexive Rule (IR1)

Answer 14

In the reflexive rule, if Y is a subset of X, then X determines Y.

If X ⊇ Y then X → Y

https://www.javatpoint.com/dbms-inference-rule

Question 15

14.12. Define Boyce-Codd normal form. How does it differ from 3NF? Why is it considered a stronger form of 3NF?

Answer 15

• Definition. A relation schema R is in BCNF if whenever a nontrivial functional dependency X → A holds in R, then X is a superkey of R.
• clause (b) in the 3NF definition, BCNF disallows them and hence is a stricter defini- tion of a normal form.
• is, every relation in BCNF is also in 3NF; however, a relation in 3NF is not necessarily in BCNF.

Question 16

What is a a nontrivial functional dependency?

Answer 16

• In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant.
  i. e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.
• roll_no → name
• https://www.geeksforgeeks.org/types-of-functional-dependencies-in-dbms/

Question 17

Trivial functional dependency

Answer 17

• In Trivial Functional Dependency, a dependent is always a subset of the determinant.
  i. e. If X → Y and Y is the subset of X, then it is called trivial functional dependency
• {roll_no, name} → name

Question 18

Attribute Closure

Answer 18

Attribute closure of an attribute set can be defined as set of attributes which can be functionally determined from it.

Question 19

How to find attribute closure of an attribute set?

Answer 19

• Add elements of attribute set to the result set.

- Recursively add elements to the result set which can be functionally determined from the elements of the result set.

Question 20

14.13. What is multivalued dependency? When does it arise?

Answer 20

• , some relations have constraints that cannot be specified as functional dependencies and hence are not in violation of BCNF. To address this situation, the concept of multivalued dependency

Question 21

attribute is transitively dependent on the primary key

Answer 21

Note – If A->B and B->C are two FDs then A->C is called transitive dependency.