【正文】 io, movie} ? {budget} 3. {studio} ? {studio_city} 4. studio_city is not a part of a key 5. studio_city functionally depends on studio which is a proper subset of the key Second Normal Form (2NF) 1. If a data item is fully functionally dependent on only a part of the primary key, move that data item and that part of the primary key to a new table. 2. If other data items are functionally dependent on the same part of the key, place them in the new table also 3. Make the partial primary key copied from the original table the primary key for the new table. Place all items that appear in the repeating group in a new table Example 1 (Convert to 2NF) Old Scheme ? {Title, PubId, AuId, Price, AuAddress} New Scheme ? {Title, PubId, AuId, Price} New Scheme ? {AuId, AuAddress} 2NF Deposition Example 2 (Convert to 2NF) Old Scheme ? {Studio, Movie, Budget, StudioCity} New Scheme ? {Movie, Studio, Budget} New Scheme ? {Studio, City} Example 3 (Convert to 2NF) Old Scheme ? {City, Street, HouseNumber, HouseColor, CityPopulation} New Scheme ? {City, Street, HouseNumber, HouseColor} New Scheme ? {City, CityPopulation} 2NF Deposition ? Concepts of Database Management – Chapter 5 Problems 9 amp。 MovieTitle Violates BCNF Example 3 Consulting (Not in BCNF) Scheme ? {Client, Problem, Consultant} 1. Key1 ? {Client, Problem} 2. Key2 ? {Client, Consultant} 3. No nonkey attribute hence 3NF 4. {Client, Problem} ? {Consultant} 5. {Client, Consultant} ? {Problem} 6. Dependency between attributess belonging to keys violates BCNF Boyce Codd Normal Form (BCNF) 1. Place the two candidate primary keys in separate entities 2. Place each of the remaining data items in one of the resulting entities according to its dependency on the primary key. Example 1 (Convert to BCNF) Old Scheme ? {City, Street, ZipCode } New Scheme1 ? {ZipCode, Street} New Scheme2 ? {City, Street} ? Loss of relation {ZipCode} ? {City} Alternate New Scheme1 ? {ZipCode, Street } Alternate New Scheme2 ? {ZipCode, City} BCNF Deposition 1. If deposition does not cause any loss of information it is called a lossless deposition. 2. If a deposition does not cause any dependencies to be lost it is called a dependencypreserving deposition. 3. Any table scheme can be deposed in a lossless way into a collection of smaller schemas that are in BCNF form. However the dependency preservation is not guaranteed. 4. Any table can be deposed in a lossless way into 3rd normal form that also preserves the dependencies. ? 3NF may be better than BCNF in some cases Deposition – Loss of Information Use your own judgment when deposing schemas Example 2 (Convert to BCNF) Old Scheme ? {MovieTitle, MovieID, PersonName, Role, Payment } New Scheme ? {MovieID, PersonName, Role, Payment} New Scheme ? {MovieTitle, PersonName} ? Loss of relation {MovieID} ? {MovieTitle} New Scheme ? {MovieID, PersonName, Role, Payment} New Scheme ? {MovieID, MovieTitle} ? We got the {MovieID} ? {MovieTitle} relationship back Example 3 (Convert to BCNF) Old Scheme ? {Client, Problem, Consultant} New Scheme ? {Client, Consultant} New Scheme ? {Client, Problem} BCNF Deposition ? Fourth normal form eliminates independent manytoone