52 Chapter 7 Database Design and the E-R Model
another relation, unless it references the ID of person. When natural joins
are used in queries, this approach avoids accidental equation of attributes
to some extent, although not always; for example, students and instructors
share attributes ID and name (presumably inherited from a generalization
person), so a query that joins the student and instructor relations would
equate the respective attribute names.
2. Primary keys: one approach to design creates identifier values for every
entity, which are internal to the system and not normally made visible to
end users. These internal values are often declared in SQL as auto increment,
meaning that whenever a tuple is inserted to the relation, a unique value is
given to the attribute automatically.
In contrast, the alternative approach, which we have used in this book,
avoids creating artificial internal identifiers, and instead uses externally
visible attributes as primary key values wherever possible.
As an example, in any university employees and students have externally
visible identifiers. These could be used as the primary keys, or alternatively,
the application can create identifiers that are not externally visible, and use
them as the value for the primary key.
As another example, the section table, which has the combination of (course
id, section id, semester, year) as primary key, could instead have a section
identifier that is unique across all sections as primary key, with the course id,
section
id, semester, year as non-primary key attributes. The difference would
be that the relations that refer to section, namely teaches and takes, would
have a single unique section id attribute as a foreign key referring to section,
and would not need to store course
id, section id, semester, and year.
Considerable emphasis is placed on the construction of tables from E-R dia-
grams. This serves to build intuition for the discussion of the relational model
in the subsequent chapters. It also serves to ground abstract concepts of entities
and relationships into the more concrete concepts of relations. Several other texts
places this material along with the relational data model, rather than in the E-R
model chapter. Our motivation for placing this material here is help students to
appreciate how E-R data models get used in reality, while studying the E-R model
rather than later on.
Exercises
7.14 Explain the distinctions among the terms primary key, candidate key, and
superkey.
Answer: A superkey is a set of one or more attributes that, taken collectively,
allows us to identify uniquely an entity in the entity set. A superkey may
contain extraneous attributes. If K is a superkey, then so is any superset
of K. A superkey for which no proper subset is also a superkey is called
a candidate key. It is possible that several distinct sets of attributes could