関係データベースの 第三正規化の形式的検証 Formally Verifying the Third Normalization of Relational Databases

1. 関係データベースの第三正規化の形式的検証Formally Verifyingthe Third Normalization ofRelational Databases 産総研　平井洋一AIST, Yoichi Hirai 2013-11-22, Nagano (TPP 2013)

2. ACID properties of the database systems • AtomicityChanges are applied in “all or nothing” manner. Partial changes must be rolled back. • ConsistencyChanges on valid states result in valid states. • IsolationEven concurrent changes simulate a temporally serial execution. • DurabilityOnce changes are applied, they remain forever unless overwritten.

3. Anomalities: failures of consistency • Update anomality Tried to change the title, but failed to change all occurrences. Consistency is violated.

4. Anomalities: failures of consistency • Deletion anomality Just removed a course, but removed a faculty as a result.

5. Codd’s first normal form 1st normal form excludes repetition of the same attributes.

6. Functional dependencies {titleID} → {Title, Author}{titleID, Library} → {titleID, Title, Author, Library}

7. Functional dependencies {FacultyID} → {Faculty name, Faculty hire date}{FacultyID, Course name} → {Course day, Course time} {FacultyID, Course name} → {FacultyID, Faculty name, Faculty hire date, Course name, Course day, Course time}

8. Armstrong’s laws • Mizar has formalization, soundness and completeness with respect to the relational semantics • Reflexivity: Y ⊆ X implies X → Y • Augmentation: Z ⊆ W and X → Y implyX ∪ W → Y ∪ Z • Transitivity: X → Y and Y → Z imply X → Zsound and complete with respect to the relational semantics

9. Codd’s second normal form • Excludes this Because of these conditions {FacultyID, course name} is a minimal set X with functional dependencyX → {FacultyID, faculty name, faculty hire date, course name, course day, course time} ({Faculty ID, couse name} is a candidate key). Faculty hire date is not contained in any candidate key (faculty hire date is non-prime attribute) Faculty hire date is dependent on {FacultyID}, which is a proper subset of a candidate key {FacultyID, couse name}.

10. The third normal form • Excludes this (example from Wikipedia) Because a non-prime attribute “Winner Date of Birth” is transitively dependent on a candidate key. Concretely, “Winner Date of Birth” is a non-prime attribute {Tournament, Year} is a candidate key {Tournament, Year} → Winner holds Winner → {Tournament, Year} does not hold Winner → {Winner Date of Birth} holds “Winner Date of Birth” is not in {Tournament, Year} “Winner Date of Birth” is not in {Winner}

11. Obtaining the third normal form:the input and output • Input: a finite set of functional dependencies Tournament Winner Winner Date of Birth Year • Output: a finite set of relations and their keys (in 3NF) Winner Tournament Year Winner Winner Date of Birth

12. Bernstein’s algorithm 1[Bernstein, 1976] Obtained after two earlier erroneous attempts!

13. Bernstein’s algorithm 1, step 1Eliminating extraneous attributes. Tournament Year Winner Winner Date of Birth Place Tournament Winner Winner Date of Birth Year Smaller, but equivalent (after taking closure of Armstrong’s laws) Place

14. Bernstein’s algorithm 1, step 2Finding nonredundant covering • A set of functional dependencies is nonredundant when no element can be inferred from the others using Armstrong’s laws. • Step 2 removes functional dependencies until the whole set becomes nonredundant.

15. Bernstein’s algorithm 1, step 3Partition Tournament Winner Winner Date of Birth Year These two functional dependencies share the left hand side. Place

16. Bernstein’s algorithm 1, step 4Construct Relations Tournament Winner Winner Date of Birth Year Relation 1{Winner, Winner Date of Birth} Place Relation 2{Tournament, Year, Place, Winner} Underlined attributes are keys. These relations are in the third normal form. Why?

17. Formalization Strategies • Never mention the relational semantics • Attributes are just elements of a type (with equalities) • A functional dependency is a pair of sequents of attributes • Derivations based on armstrong’s laws are defined in an inductive manner.

18. Termination of algorithms.(coq computes only total, terminating functions) • Termination of closure (on Armstrong’s laws) • Sizes converge because increasing and bounded • When sizes converge, the closure converges • Termination of Bernstein’s algorithm 1 • This is easier because all steps are simplification in some case. • Repeat simplifying something until it cannot simplified further.

19. Proving Preservation Properties • Each step preserves the closure of functional dependencies! • This property holds entirely without exception, so very easy to formalize and to prove (straightforward divide and conquer).

20. Proving 3NF • Mostly followed the text(first, I omitted step 1 then the proof attempt failed) • Changed a little to allow easier formalization. • Some proof steps not understood entirely • Refactoring should bring enlightenments.

21. Some changes on Bernstein’s original proof. Removed this graphical reasoning The root cause of such graphical objects “If there exists a (graphical) derivation using a functional dependency g,” A reformulation “If all (graphical) derivation uses a functional dependency g,”

22. Amount of code

23. Still to be seen: Bernstein’s algorithm 2 • The number of relations produced by Bernstein’s algorithm 1 is not optimal • Bernstein’s algorithm 2 gives optimal (= smallest) number of relations, answering Codd’s challenge. • We just formalized the algorithm 2. • And multi-dependencies, normal forms 4 and 5.