9.1.2.5.2: Examples

Last updated
Save as PDF

Page ID: 75266

\( \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}}\)

Example \(\PageIndex{1}\)

Consider the ERD

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

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

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 \(\PageIndex{2}\)

Map the ERD to relations.

Exercise \(\PageIndex{3}\)

Map the ERD to relations.