Database Systems, Chapter 14

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

1. Semantics of the attributes 2. Reducing the redundant information in tuples. 3. reducing the null values in tuples. 4. Disallowing the possibility of generating spurious tuples.

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

2NF removes all partial dependencies of nonprime attributes A in R on key and ensure that all nonprime attributes are fully functionally dependent on the key of R.

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

3NF removes all transitive dependencies on key of R and ensures that no non prime attribute is transitively dependent of key

14.15. Define fourth normal form. When is it violated?

4NF is violated when if the relation is having the multivalued dependencies which are used to identify and decompose the relations in the relational schema R. Conditions for applying fourth normal form: A relation can be in fourth form, if the relation is in third normal form. For every non trivial depencies X onto Y where X is a superkey for R

14.6. Why can we not infer a functional dependency automatically from a particular relation state?

Certain Functional dependencies can be specified without referring to a specific relation, but as a property of those attributes given there generally understood meaning.

14.18. Why do practical database designs typically aim for BCNF and not aim for higher normal forms?

BCNF is simpler than 3NF, it reduces redundancy, and the data model can be easily understood, it also improves the performance.

14.11. In what way do the generalized definitions of 2NF and 3NF extend the definitions beyond primary keys?

By taking in consideration all the candidate keys of a relation

Functional Dependencies

A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table. For any relation R, attribute Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of Y. This relationship is indicated by the representation below : X ———-> Y The left side of the above FD diagram is called the determinant, and the right side is the dependent. Here are a few examples. In the first example, below, SIN determines Name, Address and Birthdate. Given SIN, we can determine any of the other attributes within the table. SIN ———-> Name, Address, Birthdate

14.5. What is a functional dependency? What are the possible sources of the information that defines the functional dependencies that hold among the attributes of a relation schema?

A functional dependency describes the relationship between the attributes in a table. The functional dependency between two attributes is said to exist if one attribute determines the other attribute uniquely.

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

A relation is said to be boyce codd normal form if and only if every determinant is a candidate key. In the functional dependency XY if the attribute Y is fully functionally dependant on X then X is said to be a determinat. BCNF is a stronger form of 3NF. A relation that is in BCNF will also be in third normal form

14.17. Why is 5NF also called project-join normal form (PJNF)?

A relation schema is said to be in fifth normal form if it is in the fourth normal form and with set of the functional and join dependencies. The fifth normal form is defined with join dependencies. If there is any decomposition of the Relational Schema R there will be lossless decomposition in Join dependency. So, the 5NF is called PJNF

14.8. Define first, second, and third normal forms when only primary keys are considered. How do the general definitions of 2NF and 3NF, which consider all keys of a relation, differ from those that consider only primary keys?

FNF: States that the domain of an attribute must include only atomic values and that the values of any attribute in a tuple must be a single value from the domain of that attribute. In other words first normal form does not allow relations with in relation as attribute values within tuples. 2NF: It is based on a concept of full functional dependency. A dependency is full functional dependency if after removing any attribute A from X dependency does not hold any more. Else it is called partial dependency. A relation schema R is in 2NF if every non-prime Attribute A in R is not partially dependant on any key of R. 3NF: A schema is said to be 3NF if whenever a nontrivial functional dependency X holds in R, either (a) X is a superkey of R or (b) A is a prime attribute of R A functional dependency is X onto Y trivial if X is a superset of Y else the dependency is non trivial

14.14. Does a relation with two or more columns always have an MVD?

In a relation when one relation has multiple values referring to another attribute, then it indicates that there is a multivalued dependency.

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

Insertion anomoly: a situation where it is not possible to enter data into the database without entering data of other attributes. Deletion anomoly refers to the situation where data of certain attributes are lost because the deletion of some of the attributes. Moidfication anomoly occurs refers to the situation where partial update of redundant data leads to inconsistency

14.16. Define join dependency and fifth normal form.

It is a constraint which is specified on the relation schema which is denoted by JD (R1, R2, R3,..Rn) A join depency is said to be trivial join dependcy if join depency specified on the relation schema is equal to R. 5NF is a database normalization technique which is used to reduce the redundancy or duplicate values of the relational databases recording multi-valued facts. The table should be the standard for the fourth normal form. It is also called project join normal form because if there is any decomposition of the relational schema R there will be lossless decomposition of the Relational Schema R there will be lossless decomposition in join dependency. The fifth normal form is defined with the join dependencies.

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

It is defined as a full constraint between two different sets of Attributes in a relation. This does not allow having a set of values in a tuple. The tuples should be presented in a relation. Occurence: The relation will have constarints that cannot be specifed as the functional dependency, then the multivalued dependency arises. It will also occur when there is occurence of one or more tuples on the same table in a database.

14.7 What does the term unnormalized relation refer to? How did the normal forms develop historically from first normal form up to Boyce Codd normal form?

It refers to a relation which does not meet any normal form condition. Normalization takes a relation schema through a series of tests, to certify whether it satisfies a certain normal form.

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.

NULLs should be avoided to avoid wasted memory space at the storage level. When aggregate functions are performed then the results will be incorrect. Also JOINs may be unpredictable Spurious tuples are generated as the result of bad design or improper decomposition of the base table. Spurious tuples can be avoided taking extra care when designing relation schemas

14.1. Discuss attribute semantics as an informal measure of goodness for a relation schema.

Whenever attributes are grouped together to form a relational schema, it is assumed that attributes belonging to one relation have certain real-world meaning, and a proper interprentation associated with them. The semantics of a relation refer to the interpretation of attribute values in a tuple. The ease with which the meaning of a relations attributes can be explained is an informal measure of how well the relation is designed.