Skip to main content
Engineering LibreTexts

8.2: Examples

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

    Example \(\PageIndex{1}\)

    Consider the ERD

    Example ERD #1.
    Figure \(\PageIndex{1}\): Example ERD #1.

    The mapping rules lead to the relations:

    Table \(\PageIndex{1}\): Book relations.

    Book

    bookId

    title

    Table \(\PageIndex{2}\): Member relations.

    Member

    memberId

    firstName

    lastName

    Table \(\PageIndex{3}\): Borrow relations.

    Borrow

    memberId

    bookId

    dateTimeBorrowed

    dateTimeReturned

    Notes:

    • The Member relation does not have a composite attribute name.
    • Since Borrows is a many-to-many relationship the Borrow relation is defined with a composite primary key {memberId, bookId, dateTimeBorrowed}.
    • memberId in the Borrow relation is a foreign key referencing Member.
    • bookId in the Borrow relation is a foreign key referencing Book.

    Example \(\PageIndex{2}\)

    Consider the ERD

    Example ERD #2.
    Figure \(\PageIndex{2}\): Example ERD #2.

    The mapping rules lead to the relation:

    Table \(\PageIndex{4}\): Employee relations.

    Employee

    empNo

    firstName

    supervisor

    Notes:

    • The attribute supervisor is a foreign key referencing Employee.
    • A foreign key is placed on the ‘many’ side of a relationship and so in this case the foreign key references the employee who is the supervisor (the role name on the ‘one’ side); hence the name supervisor was chosen as the attribute name.

    Example \(\PageIndex{3}\)

    Consider the ERD

    Example ERD #3.
    Figure \(\PageIndex{3}\): Example ERD #3.

    The mapping rules lead to the relations.

    Table \(\PageIndex{5}\): Department relations.

    Department

    deptCode

    deptName

    chair

    Table \(\PageIndex{6}\): Course relations.

    Course

    deptCode

    courseNo

    title

    creditHours

    Notes:

    • deptCode was chosen as the primary key of Department.
    • deptName is a key and so a unique index can be defined to ensure uniqueness.
    • Since Course is a weak entity type and is involved in an identifying relationship, the primary key of Course is composite comprising {deptCode, courseNo}.
    • deptCode in Course is a foreign key referencing Department.

    Exercises

    Exercise \(\PageIndex{1}\)

    Map the ERD to relations.

    Exercise ERD #1.

    Exercise \(\PageIndex{2}\)

    Map the ERD to relations.

    Exercise ERD #2.

    Exercise \(\PageIndex{3}\)

    Map the ERD to relations.

    Exercise ERD #3.

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

    • Was this article helpful?