Data Modeling and Database Design.PPTVIP

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Attribute 0 of pfid 14 is size and the attribute value 1 is Grande and 2 is Tall and 3 is Short Web-based Build-To-Order Application Data Model for Build-To-Order Application * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Non-loss Decomposition Normalization is a reduction (decomposition) process that replaces a relation by suitable projections. Each of the projection is a new relation that is in a further normalized form than the original relation. The collection of projections is equivalent to the original relation. The original relation can always be recovered by taking the natural join of these projections. Any information that can be derived from the original relation can also be derived from the further normalized relations. The converse is not true. The process is reversible because no information is loss in the reduction process. Transitive Dependency In a relation R, if R.A =R.B and R.B = R.C then attribute C is said to be transitively dependent on attribute A. Second Normal Form (2NF) A relation R is in the second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key. Normalization Process: Remove Transitive Dependencies If R is in 2NF and has two non-key attributes A1 and A2 where A2 is functionally dependent on A1 (A1 = A2). The A2 should be removed from R and a new relation will be created with A1 as the primary key and A2 as a non-key attribute. Example Supplier relation has attributes supplier#, city, distance, where supplier# is the key and distance to a supplier can be determined by the city of the supplier. SUPPLIER (supplier#, city, distance, quality_level) ò SUPPLIER (Supplier#, city, quality_level) CITY-DISTANCE (city, distance) Third Normal Form (3NF) A relation R is in the third normal form (3NF) if and only if the non-key a