{ "cells": [ { "attachments": {}, "cell_type": "markdown", "id": "fa65e521-713f-4c6b-8e27-17d2b489b0cf", "metadata": {}, "source": [ "## Lecture 5 - Normalization\n", "\n", "### Announcements\n", "\n", "1. Lecture Exercise 5 due at **midnight** today.\n", "2. Lecture Exercise 6 is out later today, due on thursday at 2pm.\n", "3. Homework \\#1 due Today at **midnight** today.\n", "4. Homework \\#2 out on Thursday, due next week thursday.\n", "5. If you have accommodations, please reserve an exam slow with the test center for Exam #1 on October 1st. See: https://provost.rpi.edu/faculty-resources/testing-center \n", " - Note the exam will be 110 minutes. \n", " - For 50% extra time, reserve a 3 hour block\n", " - For 100% extra time, reserve a 4 hour block\n", " - Ideally your block should start at 2pm, but if you cannot do this, please notify me.\n", "\n", "### Today's topics\n", "\n", "- Functional Dependencies\n", "- Functional Dependency Inferences\n", "- Keys, superkeys and prime attributers\n", "- Closure of a set of attributes\n", "- Closure of a set of functional dependencies\n", "- Boyce-Codd Normal Form\n", "- Third Normal Form\n", "\n" ] }, { "cell_type": "markdown", "id": "b2b783a9-fbbf-42d7-b259-9d4edffe38e9", "metadata": {}, "source": [ "**Functional Dependency (fd):** Given a relation R and a set of attributes X and Y of R, a fd is an expression of the form **X -> Y** which means if two tuples in R have the same values for X, then they must have the same values for Y.\n", "\n", "--- \n", " \n", "Students(RIN, Name, RCS, Class, Major, Advisor, Phone) \n", "A student can have many majors \n", "RIN -> RIN Name RCS Class \n", "RIN Major -> RIN Major Advisor \n", "\n", "123456 Joe doej 2026 CSCI Adali 8033 \n", "123456 Joe doej 2026 CSYS Julius 8033 \n", "123456 Joe doej 2026 CSCI Adali 9322 \n", "123456 Joe doej 2026 CSYS Julius 9322 \n", "\n", "---\n", "\n", "**Functional Dependency Inference:** Given a relation R and a set of functional dependencies F, we can find new fds implied by F by using any of the possible inference rules.\n", "\n", "1. Trivial fd: If $Y \\subseteq X$, then X -> Y\n", "(always true for all relations)\n", "\n", "---\n", " \n", "A -> A \n", "AB -> AB \n", "AB -> B \n", "\n", "--- \n", " \n", "2. Transivitity: `If X->Y and Y->Z, then X->Z.`\n", "\n", "3. Decomposition: `If X->YZ then X->Y and X->Z`\n", "\n", "RIN -> RIN Name RCS Class \n", "RIN -> RIN \n", "RIN -> Name \n", "RIN -> RCS \n", "RIN -> Class \n", " \n", "4. Combining: `If X->Y and X->Z, then X->YZ`\n", "\n", "---\n", " \n", "RIN -> Name \n", "RIN -> RCS \n", "RIN -> Class \n", "\n", "RIN -> Name RCS Class \n", "\n", "---\n", " \n", "5. Augmentation: `If X->Y then XZ->YZ`\n", "\n", "**Closure of a set of functional dependencies:** Given a relation R and a set F of functional dependencies, then F+ is the closure of F over R, the set of all functional dependencies implied by F.\n", " - The closure of F, F+ is the set of all fd that can be obtained by F. \n", "\n", "R(A, B, C) F = {A->B, B->C} \n", "\n", "F+ = {A->B, B->C, A->A, B->B, C->C, AB->A, AB->B, AB->AB, BC->B, BC->C, BC->BC, AC->A, AC->C, AC->AC, ABC->A, ABC->B, ABC->C, ABC->A, ABC->B, ABC->C, ABC->AB, ABC->AC, ABC->BC, ABC->ABC, A->C,AC->BC, AB-> AC, AB->BC, A->BC, A->ABC}\n", "\n", " \n", "A->C (transitivity) \n", "AC->BC (augmentation)\n", "\n", "**Implication:** Given a relation R and a set F of functional dependencies, a functional dependency X->Y is implied by F\n", " 1. I can obtain X->Y from F using inference rules, or\n", " 2. X->Y is in F+ (1 and 2 are the same thing)\n", "\n", "Remember, $$F \\subseteq F+$$\n", "\n", "**Closure of a set of attributes:** Given a relation R and a set of functional dependencies F, the closure of a set of attributes X, given by X+ is the set of all attributes implied by X.\n", "\n", " - It is then the case that X->X+ is implied by F.\n", "\n", "Algorithm:\n", "\n", " 1. Initialize, X+ = X\n", " Repeat until no new attributes are added:\n", " 2. Given a functional dependency Y->Z, if \n", " $$Y \\subseteq X+$$, then add Z to X+.\n", " \n", "\n", " R(A,B,C,D,E,F,G) \n", " F = {A->BC, BD->EF, CF->GB}\n", "\n", " 1. AC+ = {A,C}\n", " 2. AC+ = {A,B,C} (A->BC)\n", "\n", " CDF+ = {B,C,D,E,G,F}\n", " \n", " CDF -> BCDEFG is implied by F\n", "\n", " CF -> GB (given)\n", " CDF -> BDG (augmentation)\n", " CDF -> BD (decomposition) BD->EF \n", " CDF -> EF (transitivity)\n", " CDF -> CDF (trivial)\n", " CDF -> BCDEFG (combining rule!)\n", " \n", " F = {A->BC, BD->EF, CF->GB}\n", " \n", " ACD+ = {A,B,C,D,E,F,G}\n", " AD+ = {A,B,C,D,E,F,G} \n", " A+ = {A,B,C}\n", " D+ = {D}\n", "\n", " \n", "**Superkey:** Given a relation R and a set of functional dependencies F, a superkey is a set of attributes X such that \n", " - X implies all the attributes in R according to F.\n", " - Alternatively, X-> Y is implied by F where Y is the set of all attributes in R. \n", "\n", " R(A,B,C,D,E,F,G) \n", " F = {A->BC, BD->EF, CF->GB}\n", "\n", " Superkeys: ACD, AD, ABD, ABCD, ... \n", " \n", "- **Key**: Given a relation R and a set of functional dependencies F, a key is a superkey X that is also minimal, i.e. no subset of X is a set superkey.\n", "\n", " Key: AD\n", "\n", "- **Prime attributes:** Given a relation R and a set of functional dependencies F, a prime attribute is any attribute appearing in a key. \n", " Prime attributes: A and D\n" ] }, { "cell_type": "markdown", "id": "db08ad57-8979-44a4-bf9c-7148f07ea489", "metadata": {}, "source": [ "\n", "---\n", "\n", "R0(A,B) F = {}\n", "\n", "Key: AB (StudentHobbies(RIN, Hobby), Key: RIN, Hobby)\n", "\n", "---\n", "\n", "\n", "R00(A,B,C,D,E) F={A->D, B->C, AB->E}\n", "\n", "Key: AB\n", "\n", "A+ AD\n", "B+ BC\n", "\n", "AB+ ABDCE\n", "\n", "---\n", "\n", "\n", "R1(A,B,C,D,E,F) F = {AB->CDE, ABC->EF}\n", "\n", "Key: AB\n", "\n", "---\n", "\n", "\n", "R2(A,B,C,D,E,F,G) F = {AB->CDEF}\n", "\n", "Key: ABG\n", "\n", "---\n", "\n", "\n", "R3(A,B,C,D,E,F,G) F = {AB->CD, CE->AF, AE->G}\n", "\n", "Key: ABE, BCE\n", "\n", "---\n", "\n", "\n", "\n", "R4(A,B,C,D,E,F,G) F = {ABC->DEF, AD->G}\n", "\n", "Key: ABC\n", "\n", "---\n", "\n", "\n", "R5(A,B,C,D,E,F,G) F = {ABC->DE, DB->F, FG->A}\n", "\n", "Key: ABCG, BCFG, BCDG\n", "\n", "---\n" ] }, { "cell_type": "markdown", "id": "b91d7a9c-84ad-4b20-91c4-ba0d44a387c0", "metadata": {}, "source": [ "**Boyce-Codd Normal Form (BCNF):** Given a relation R and a set of functional dependencies F, the relation R is said to be in Boyce-Codd Normal Form (BCNF) iff for all functional dependencies X->Y in R, one of the following is true:\n", " 1. X-> Y is trivial, or\n", " 2. X is a superkey of R\n", "\n", "In other words, if there is a single violation of BCNF, where X->Y in F, and X->Y is not trivial and X is not a superkey, then R is not in BCNF.\n", "\n", "\n", "\n", "**Third Normal Form (3NF):** Given a relation R and a set of functional dependencies F, the relation R is said to be in Third Normal Form (3NF) iff for all functional dependencies X->Y in R, one of the following is true:\n", " 1. X-> Y is trivial, or\n", " 2. X is a superkey of R, or\n", " 3. Y is contains only prime attributes.\n", " \n", "Everything in BCNF is also in 3NF, but 3NF allows additional relations." ] }, { "cell_type": "markdown", "id": "43caac7b-fc7b-4802-a06e-565a16bdd76a", "metadata": {}, "source": [ "---\n", "R0(A,B) F = {} Key: AB\n", "\n", "in BCNF and in 3NF\n", "\n", "---\n", "\n", "\n", "R00(A,B,C,D,E) F={A->D, B->C, AB->E} Key: AB\n", "\n", "\n", "A->D not trivial, A is not a superkey, violates BCNF\n", "B->C not trivial, B is not a superkey, violates BCNF\n", "AB->E not trivial, AB is a superkey, does not violate BCNF\n", "\n", "Not in BCNF\n", "\n", "A->D not trivial, A is not a superkey, D is not prime, violates 3NF\n", "B->C not trivial, B is not a superkey, C is not prime, violates 3NF\n", "AB->E not trivial, AB is a superkey, does not violate BCNF/3NF\n", "\n", "Not in 3NF \n", "\n", "---\n", "\n", "\n", "R1(A,B,C,D,E,F) F = {AB->CDE, ABC->EF}\n", "\n", "Key: AB\n", "\n", "AB->CDE, not trivial, AB is a superkey ok\n", "ABC->EF, not trivial, ABC superkey, ok\n", "\n", "in BCNF and in 3NF\n", "\n", "---\n", "\n", "\n", "R2(A,B,C,D,E,F,G) F = {AB->CDEF}\n", "\n", "Key: ABG\n", "\n", "Not in BCNF, AB is not a superkey\n", "\n", "Not in 3NF because CDEF are not prime\n", "\n", "---\n", "\n", "\n", "R3(A,B,C,D,E,F,G) F = {AB->CD, CE->AF, AE->G}\n", "\n", "Key: ABE, BCE\n", "\n", "Prime attributes: A,B,C,E\n", "\n", "Not in BCNF, all fds violate BCNF\n", "\n", "AB->CD, AB is not a superkey, C is prime, but D is not prime, violates 3NF\n", "\n", "CE->AF, violates 3NF because CE is not a superkey and F is not prime\n", "\n", "AE->G, violates 3NF because AE is not a superkey and G is not prime\n", "\n", "not in 3NF\n", "\n", "---\n", "\n", "\n", "R4(A,B,C,D,E,F,G) F = {ABC->DEF, AD->G}\n", "\n", "Key: ABC\n", "\n", "Not in BCNF, AD is not a superkey\n", "\n", "Not in 3NF because G is not a prime\n", "\n", "---\n", "\n", "\n", "R5(A,B,C,D,E,F,G) F = {ABC->DE, DB->F, FG->A}\n", "\n", "Key: ABCG, BCFG, BCDG\n", "\n", "Prime attributes: A,B,C,D,F,G\n", "\n", "\n", "Not in BCNF, all fds violate it\n", "\n", "\n", "Not in 3NF becaues ABC->DE and ABC is not a superkey and E is not a prime attribute\n", "\n", "\n", "---\n", "\n", "\n", "\n", "R6(A,B,C,D,E,F,G) F = {ABC->DEFG, BF->A}\n", "\n", "Keys: ABC, BCF\n", "prime attributes: ABCF \n", "\n", "Not in BCNF because BF is not a superkey\n", " \n", "in 3NF because \n", "\n", "ABC->DEFG ok, ABC is a superkey\n", "\n", "BF->A ok because A is a prime attribute\n", "\n", " \n", "---\n" ] }, { "cell_type": "code", "execution_count": null, "id": "d99c41ab-bc08-42ef-bde8-4070726f6a75", "metadata": {}, "outputs": [], "source": [ "\n", "Faculty(RIN, DeptCode, DeptName)\n", "\n", "F={RIN-> DeptCode, DeptCode-> DeptName}\n", " \n", "F = {A->B, B->C}\n", "\n", "\n", "\n", "Key: A\n", "\n", "B is not a superkey, not in BCNF\n", "\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.5" } }, "nbformat": 4, "nbformat_minor": 5 }