Schema refinement in database design:
Good schema design improves decomposition. One way to do this is to eliminate redundancy. Redundancy causes several problems: storage is repeated, updates may not make changes to all in a consistent manner, insertion may be dependent on other items and deletion may not be possible without losing other information as well.
Several normal forms have been proposed for relations and if a schema conforms to one of these, it can avoid certain kinds of problems. A property of decomposition is lossless-join which enables us to recover any instance of the decomposed relation from corresponding instances of the smaller relations. Another property is dependency-preservation which enables us to enforce any constraint on the original relation by simply enforcing some constraints on each of the smaller relations.
A good designer will ask if a relation is in a normal form or if the decomposition is dependency preserving. Relations will often have functional dependencies. A functional dependency is where different attributes will keep the same dependency for every pair of tuples so for example, if t1.X = t2.X, then t1.Y = t2.Y
A primary key constraint is a special case of an FD.
Constraints can be defined on an Entity set. For example, the SSN can be a key to the tuples with attributes name, lot, rating, hourly_wages, hours_worked etc.
Constraints on a relationship set. These constraints can eliminate redundancies that ER design may not catch. For example, a contractor with contract id may involve parts, suppliers and Departments, then it maybe better to split the relations as CQSD and SDP.
Functional Dependencies also help with identifying attributes of entities and particularly find attributes that are on the wrong entity set. For example, employees can work in at most one department. So we can decompose this into two entities Workers and Departments with attributes as workers(ssn, name, did, since) and Departments(did, dname, budget, lot).
Closure of a set of functional dependencies is defined as the set of FDs implied by a given set F of FDs and is denoted as F+. Armstrong's axioms helps find closures of a set of FDs. The axiom suggests
1. Reflexivity if X is a proper subset of Y, then X --> Y
2. Augmentation if X-->Y, then XZ-->YZ for any Z
3. Transitivity if X-->Y and Y-->Z, then X-->Z
It is convenient to use additional rules while reasoning about F+
Union if X -->Y and X-->Z, then X-->YZ
Decomposition if X-->YZ, then X-->Y and X-->Z
These additional rules are not essential, their soundness can be proved using Armstrong's axioms.
The attribute closure of attribute set X is the set of attributes A such that X --> can be inferred using Armstrong Axioms.
Good schema design improves decomposition. One way to do this is to eliminate redundancy. Redundancy causes several problems: storage is repeated, updates may not make changes to all in a consistent manner, insertion may be dependent on other items and deletion may not be possible without losing other information as well.
Several normal forms have been proposed for relations and if a schema conforms to one of these, it can avoid certain kinds of problems. A property of decomposition is lossless-join which enables us to recover any instance of the decomposed relation from corresponding instances of the smaller relations. Another property is dependency-preservation which enables us to enforce any constraint on the original relation by simply enforcing some constraints on each of the smaller relations.
A good designer will ask if a relation is in a normal form or if the decomposition is dependency preserving. Relations will often have functional dependencies. A functional dependency is where different attributes will keep the same dependency for every pair of tuples so for example, if t1.X = t2.X, then t1.Y = t2.Y
A primary key constraint is a special case of an FD.
Constraints can be defined on an Entity set. For example, the SSN can be a key to the tuples with attributes name, lot, rating, hourly_wages, hours_worked etc.
Constraints on a relationship set. These constraints can eliminate redundancies that ER design may not catch. For example, a contractor with contract id may involve parts, suppliers and Departments, then it maybe better to split the relations as CQSD and SDP.
Functional Dependencies also help with identifying attributes of entities and particularly find attributes that are on the wrong entity set. For example, employees can work in at most one department. So we can decompose this into two entities Workers and Departments with attributes as workers(ssn, name, did, since) and Departments(did, dname, budget, lot).
Closure of a set of functional dependencies is defined as the set of FDs implied by a given set F of FDs and is denoted as F+. Armstrong's axioms helps find closures of a set of FDs. The axiom suggests
1. Reflexivity if X is a proper subset of Y, then X --> Y
2. Augmentation if X-->Y, then XZ-->YZ for any Z
3. Transitivity if X-->Y and Y-->Z, then X-->Z
It is convenient to use additional rules while reasoning about F+
Union if X -->Y and X-->Z, then X-->YZ
Decomposition if X-->YZ, then X-->Y and X-->Z
These additional rules are not essential, their soundness can be proved using Armstrong's axioms.
The attribute closure of attribute set X is the set of attributes A such that X --> can be inferred using Armstrong Axioms.
No comments:
Post a Comment