Skip to main content
Engineering LibreTexts

5.1: Relationships

  • Page ID
    93658
  • \( \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}\)

    One important theory developed for the entity relational (ER) model involves the notion of functional dependency (FD).  The aim of studying this is to improve your understanding of relationships among data and to gain enough formalism to assist with practical database design.

    Like constraints, FDs are drawn from the semantics of the application domain. Essentially,  functional dependencies describe how individual attributes are related. FDs are a kind of constraint among attributes within a relation and contribute to a good relational schema design. In this chapter, we will look at:

    • The basic theory and definition of functional dependency
    • The methodology for improving schema designs, also called normalization

    Relational Design and Redundancy

    Generally, a good relational database design must capture all of the necessary attributes and associations. The design should do this with a minimal amount of stored information and no redundant data.

    In database design, redundancy is generally undesirable because it causes problems maintaining consistency after updates. However, redundancy can sometimes lead to performance improvements; for example, when redundancy can be used in place of a join to connect data. A join is used when you need to obtain information based on two related tables.

    Consider Figure \(\PageIndex{1}\)  customer 1313131 is displayed twice, once for account no. A-101 and again for account A-102. In this case, the customer number is not redundant, although there are deletion anomalies with the table. Having a separate customer table would solve this problem. However, if a branch address were to change, it would have to be updated in multiple places. If the customer number was left in the table as is, then you wouldn’t need a branch table and no join would be required, and performance is improved . 

    Bank-Accounts-1-300x197
    Figure \(\PageIndex{1}\): An example of redundancy used with bank accounts and branches.

    Insertion Anomaly

    An insertion anomaly occurs when you are inserting inconsistent information into a table. When we insert a new record, such as account no. A-306 in Figure \(\PageIndex{2}\), we need to check that the branch data is consistent with existing rows.

    Insertion-Anomaly-Banking-Accounts-300x222
    Figure \(\PageIndex{2}\): Example of an insertion anomaly.

    Update Anomaly

    If a branch changes address, such as the Round Hill branch in Figure \(\PageIndex{3}\), we need to update all rows referring to that branch. Changing existing information incorrectly is called an update anomaly.

    Update-Anomaly-Bank-Accounts-300x198
    Figure \(\PageIndex{3}\): Example of an update anomaly.

    Deletion Anomaly

    deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted. For instance, if we remove information about the last account at a branch, such as account A-101 at the Downtown branch in Figure \(\PageIndex{4}\), all of the branch information disappears.

    Deletion-anomaly-Bank-Account-300x195
    Figure \(\PageIndex{4}\): Example of a deletion anomaly.

    The problem with deleting the A-101 row is we don’t know where the Downtown branch is located and we lose all information regarding customer 1313131. To avoid these kinds of update or deletion problems, we need to decompose the original table into several smaller tables where each table has minimal overlap with other tables.

    Each bank account table must contain information about one entity only, such as the  Branch or Customer, as displayed in Figure \(\PageIndex{5}\).

    Ch-10-Branch-to-Customer-ERD-300x117
    Figure \(\PageIndex{5}\): Examples of bank account tables that contain one entity each, by A. Watt.

    Following this practice will ensure that when branch information is added or updated it will only affect one record. So, when customer information is added or deleted, the branch information will not be accidentally modified or incorrectly recorded.

    Example: employee project table and anomalies

    Figure \(\PageIndex{6}\) shows an example of an employee project table. From this table, we can assume that:

    1. EmpID and ProjectID are a composite PK.
    2. Project ID determines Budget (i.e., Project P1 has a budget of 32 hours).
    Ch-10-ProjectEmp-table
    Figure \(\PageIndex{6}\): Example of an employee project table, by A. Watt.

    Next, let’s look at some possible anomalies that might occur with this table during the following steps.

    1. Action: Add row {S85,35,P1,9}
    2. Problem: There are two tuples with conflicting budgets
    3. Action: Delete tuple {S79, 27, P3, 1}
    4. Problem: Step #3 deletes the budget for project P3
    5. Action: Update tuple {S75, 32, P1, 7} to {S75, 35, P1, 7}
    6. Problem: Step #5 creates two tuples with different values for project P1’s budget
    7. Solution: Create a separate table, each, for Projects and Employees, as shown in Figure \(\PageIndex{7}\).
    Ch-10-Project-to-Emp-ERD-300x114
    Figure \(\PageIndex{7}\): Solution: separate tables for Project and Employee, by A. Watt.

    How to Avoid Anomalies

    The best approach to creating tables without anomalies is to ensure that the tables are normalized, and that’s accomplished by understanding functional dependencies. FD ensures that all attributes in a table belong to that table. In other words, it will eliminate redundancies and anomalies.

    Example: separate Project and Employee tables

    Ch-10-Project-and-Emp-tables-300x89
    Figure \(\PageIndex{8}\):Separate Project and Employee tables with data, by A. Watt.

    By keeping data separate using individual Project and Employee tables:

    1. No anomalies will be created if a budget is changed.
    2. No dummy values are needed for projects that have no employees assigned.
    3. If an employee’s contribution is deleted, no important data is lost.
    4. No anomalies are created if an employee’s contribution is added.

     

    Key Terms

    deletion anomaly: occurs when you delete a record that may contain attributes that shouldn’t be deleted

    functional dependency (FD): describes how individual attributes are related

    insertion anomaly: occurs when you are inserting inconsistent information into a table

    join: used when you need to obtain information based on two related tables

    update anomaly: changing existing information incorrectly

     

     

    Exercises

     

    1. Normalize Figure \(\PageIndex{9}\).
      Ch10-Exercises -Fig10-1
      Figure \(\PageIndex{9}\): Table for question 1, by A. Watt.
    2. Create a logical ERD for an online movie rental service (no many to many relationships). Use the following description of operations on which your business rules must be based:The online movie rental service classifies movie titles according to their type: comedy, western, classical, science fiction, cartoon, action, musical, and new release. Each type contains many possible titles, and most titles within a type are available in multiple copies. For example, note the following summary:TYPE TITLE
      Musical My Fair Lady (Copy 1)
      My Fair Lady (Copy 2)
      Oklahoma (Copy 1)
      Oklahoma (Copy 2)
      Oklahoma (Copy 3)
      etc. 
    3. What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?

    Also see  Appendix B: Sample ERD Exercises


    5.1: Relationships is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?