Functional Dependency and Normalization for Relational Databases

introduction:
relational database design ultimately produces a set of relations. the implicit goals of the design activity are: informationpreservation and minimum redundancy.
informal design guidelines for relation schemas
four informal guidelines that may be used as measures todetermine the quality of relation schema design:
??making sure that the semantics of the attributes is clear in the schema
??reducing the redundant information in tuples
??reducing the null values in tuples
??disallowing the possibility of generating spurious tuples
imparting clear semantics to attributes in relations
the semantics of a relation refers to its meaning resulting from the interpretation of attribute values in a tuple. the relational schema design should have a clear meaning.
guideline 1
1. design a relation schema so that it is easy to explain.
2. do not combine attributes from multiple entity types and relationship types into a single relation.
redundant information in tuples and updating anomalies
one goal of schema design is to minimize the storage space usedby the base relations (and hence the corresponding files). grouping attributes into relation schemas has a significant effect on storage space storing natural joins of base relations leads to an additional problem referred to as updating anomalies. these are: insertion anomalies, deletion anomalies, and modification anomalies. insertion anomalies happen:
• when insertion of a new tuple is not done properly and will therefore can make the database become inconsistent.
• when the insertion of a new tuple introduces a null value (for example a department in which no employee works as of yet). this will violate the integrity constraint of the table since essn is a primary key for the table. the problem of deletion anomalies is related to the second insertion anomaly situation just discussed.
example: if we deleting from emp_dept an employee tuple that happens to represent the last employee working for a particular department, the information concerning that department is lost from the database.
modification anomalies happen if we fail to updating all tuples as a result in the change in a single one.
example: if the manager changes for a department, all employees who work for that department must be updatingd in all the tables. it is easy to see that these three anomalies are undesirable and cause difficulties to maintain consistency of data as well as require unnecessary updatings that can be avoided hence
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, note them clearly and make sure that the programs that updating the database will operate correctly. the second guideline is consistent with and, in a way, a null values in tuples
fat relations: a relation in which too many attributes are grouped. if many of the attributes do not apply to all tuples in the relation, we end up with many nulls in those tuples. this can waste space at the storage level and may also lead to problems with understanding the meaning of the attributes and with specifying join operations at the logical level.
another problem with nulls is how to account for them when aggregate operations such as count or sum are applied.



select and join operations involve comparisons if null values are present, the results may become unpredictable. moreover, nulls can have multiple interpretations, such as the following:
??the attribute does not apply to this tuple. for example, visa_status may not apply to u.s. students.
??the attribute value for this tuple is unknown. for example, the date_of_birth may be unknown for an employee.
??the value is known but absent that is, it has not been recorded yet. for example, the home_phone_number for an employee may exist but may not be available and recorded yet.
having the same representation for all nulls compromises the different meanings they may have. therefore, we may state another guideline.

guideline 3
as much 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.
for example, if only 15 percent of employees have individual offices, there is little justification for including an attribute office_number in the employee relation rather, a relation emp_offices(essn, office_number) can be created

generation of spurious tuples
often, we may elect to split a “fat” relation into two relations, with
the intention of joining them together if needed. however, applying a natural join may not yield the desired effect. on the contrary, it will generate many more tuples and we cannot recover the original table.

guideline 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) combinations because joining on such attributes may produce spurious tuples.

summary and discussion of design guidelines
we proposed informal guidelines for a good relational design.
the problems we pointed out, which can be detected without additional tools of analysis, are as follows:
??anomalies that cause redundant work to be done during insertion into and modification of a relation, and that may cause accidental loss of information during a deletion from a relation
??waste of storage space due to nulls and the difficulty of performing selections, aggregation operations, and joins due to null values
??generation of invalid and spurious data during joins on base relations with matched attributes that may not represent a proper (foreign key, primary key) relationship
the strategy for achieving a good design is to decompose a badly designed relation appropriately.