Skip to main content
Engineering LibreTexts

10.6: Summary

  • Page ID
    15556
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)

    We have discussed functional dependencies, candidate keys, 1NF and BCNF. BCNF is the usual objective of the database designer.

    When a relation is not BCNF then one or more of the following will be the source of redundancy in a relation:

    • partial dependencies
    • transitive dependencies
    • functional dependencies amongst key attributes.

    2NF involves the concepts of candidate key and non-key attributes. A relation is considered to be in 2NF if it is in 1NF, and every non-key attribute is fully dependent on each candidate key. In Example 2 we mentioned that stuNum \(\rightarrow \) birthdate was considered a partial functional dependency as stuNum is a subset of a candidate key. A 2NF relation does not contain partial dependencies.

    3NF involves the concepts of candidate key and non-key attributes. We say a relation is in 3NF if the relation is in 1NF and all determinants of non-key attributes are candidate keys. In Example 3 we mentioned that courseId \(\rightarrow \) lastName was considered a transitive dependency; lastName is dependent on teacherId which is not a candidate key. A 3NF relation does not have partial dependencies nor transitive dependencies.

    The definition of BCNF concerns FDs and CKs – there is no mention of non-key attributes. Hence, BCNF is a stronger form than 2NF or 3NF (a BCNF relation will be in 2NF and 3NF).

    A database designer may decide to not normalize completely to BCNF. This is sometimes done to ensure that certain data can be retrieved without having to join relations in a query – when a join is avoided the data is typically retrieved more quickly from the database. This is often done in a data warehouse environment (outside the scope of these notes).

    Exercises

    In each of these exercises, consider the relation, CKs, and FDs. Determine if the relation is in BCNF, and if not in BCNF give a non-loss decomposition into BCNF relations. The last 5 questions are abstract and give no context for the relation nor attributes.

    Exercise \(\PageIndex{1}\)

    Consider a relation Player which has information about players for some sports league. Player has attributes id, first, last, gender. id is the only CK and the FDs are:

    id \(\rightarrow \) first

    id \(\rightarrow \) last

    id \(\rightarrow \) gender

    Player - sample data
    id first last gender
    1 Jim Jones Male
    2 Betty Smith Female
    3 Jim Smith Male
    4 Lee Mann Male
    5 Samantha McDonald Female

    Exercise \(\PageIndex{2}\)

    Consider a relation Employee which has information about employees in some company. Employee has attributes id, first, last, sin (social insurance number) where id and sin are the only CKs, and the FDs are:

    id \(\rightarrow \) first

    id \(\rightarrow \) last

    sin \(\rightarrow \) first

    sin \(\rightarrow \) last

    id \(\rightarrow \) sin

    sin \(\rightarrow \) id

    Employee - sample data
    id first last sin
    1 Jim Jones 111222333
    2 Betty Smith 333333333
    3 Jim Smith 456789012
    4 Lee Mann 123456789
    5 Samantha McDonald 987654321

    Exercise \(\PageIndex{3}\)

    Consider a relation Player which contains information about players and their teams. Player has attributes playerId, first, last, gender, teamId, teamName, teamCity where playerId is the only CK and the FDs are:

    playerId \(\rightarrow \) first

    playerId \(\rightarrow \) last

    playerId \(\rightarrow \) gender

    playerId \(\rightarrow \) teamId

    playerId \(\rightarrow \) teamName

    playerId \(\rightarrow \) teamCity

    teamId \(\rightarrow \) teamName

    teamId \(\rightarrow \) teamCity

    Player - sample data
    playerId first last gender teamId teamName teamCity
    1 Jim Jones M 1 Flyers Winnipeg
    2 Betty Smith F 5 OilKings Calgary
    3 Jim Smith M 10 Oilers Edmonton
    4 Lee Mann M 1 Flyers Winnipeg
    5 Samantha McDonald F 5 OilKings Calgary
    6 Jimmy Jasper M 99 OilKings Winnipeg

    Exercise \(\PageIndex{4}\)

    Consider a relation Building which has information about buildings and floors. Building has attributes buildingCode, floor, numRooms, campus where {buildingCode,floor} is the only CK and the FDs are:

    {buildingCode,floor} \(\rightarrow \) numRooms

    buildingCode \(\rightarrow \) campus

    Building - sample data
    buildingCode floor numRooms campus
    D 3 15 Downtown
    C 2 5 Downtown
    RP 1 20 Selkirk
    D 2 5 Downtown
    D 1 20 Downtown

    Exercise \(\PageIndex{5}\)

    Consider a relation Course which contains information about courses. Course has attributes deptCode, deptName, courseNum, creditHours where {deptCode,courseNum} and {deptName,courseNum} are the only CKs, and the FDs are:

    {deptCode,courseNum} \(\rightarrow\) creditHours

    {deptName,courseNum} \(\rightarrow\) creditHours

    deptCode \(\rightarrow\) deptName

    deptName \(\rightarrow\) deptCode

    Course - sample data
    deptCode deptName courseNum creditHours
    Math Mathematics 2101 3
    Stat Statistics 2102 3
    Math Mathematics 2102 1
    Stat Statistics 4001 6
    Math Mathematics 4001 6

    Exercise \(\PageIndex{6}\)

    Consider the relation Student Performance below which describes student performance in courses. The value stored in the gradePoint column is the grade point that corresponds to the grade received in a course. Assume that students are identified by their student number, and that courses are identified by their course id. Assume each student can take a course only once and so each row is uniquely identified by {stuNum, courseId}. Each student’s overall gpa is stored – gpa is the average of gradePoint for all courses taken by a student.

    Student Performance sample data

    stuNum

    courseId

    grade

    gradePoint

    gpa

    111 3030 C 2.0 2.0
    113 3030 C 2.0 2.5
    113 4040 B 3.0 2.5
    118 2222 C 2.0 2.25
    118 4040 C+ 2.5 2.25
    202 1188 B 3.0 3.0

    Exercise \(\PageIndex{7}\)

    Consider Example 4. Is there another decomposition of ProvinceLanguageStatistics that leads to BCNF relations?

    Exercise \(\PageIndex{8}\)

    Consider a relation R with attributes X, Y, W, Z where X is the only CK, and where there are FDs:

    X \(\rightarrow \) Y

    X \(\rightarrow \) W

    X \(\rightarrow \) Z

    Exercise \(\PageIndex{9}\)

    Consider a relation R with attributes X, Y, W, V where X and V are the only CKs, and where there are FDs:

    X \(\rightarrow \) Y

    X \(\rightarrow \) W

    V \(\rightarrow \) Y

    V \(\rightarrow \) W

    X \(\rightarrow \) V

    V \(\rightarrow \) X

    Exercise \(\PageIndex{10}\)

    Consider a relation R with attributes X, Y, W, V, Z where X is the only CK, and where there are FDs:

    X \(\rightarrow \) Y

    X \(\rightarrow \) W

    W \(\rightarrow \) Z

    W \(\rightarrow \) V

    Exercise \(\PageIndex{11}\)

    Consider a relation R with attributes A, B, C, D, E, F where {A,B} is the only CK, and where there are FDs:

    {A,B} \(\rightarrow \) C

    {A,B} \(\rightarrow \) D

    A \(\rightarrow \) E

    A \(\rightarrow \) F

    Exercise \(\PageIndex{12}\)

    Consider a relation R with attributes A, B, C, D, E where {A,C} and {B,C} are the only CKs, and where there are FDs:

    {A,C} \(\rightarrow \) D

    {B,C} \(\rightarrow \) D

    {A,C} \(\rightarrow \) E

    {B,C} \(\rightarrow \) E

    A \(\rightarrow \) B

    B \(\rightarrow \) A


    This page titled 10.6: Summary is shared under a CC BY-NC-SA license and was authored, remixed, and/or curated by Ron McFadyen.

    • Was this article helpful?