Entity-Relationship Model Design Principles, Keys, Subclasses, Exercises

1
Entity-Relationship ModelDesign Principles,
Keys, Subclasses, Exercises
2
Design Principles Faithfulness

• Whatever relationships are asserted should make
  sense given what we know about the part of the
  real world being modeled.
• If we define a relationship Stars-in between
  Stars and Movies, it should be a many-many
  relationship.
• Not always obvious.
• E.g. Courses and Instructors Whats the
  multiplicity of a relationship Teaches?
• In such cases, please state your assumptions.

3
Redundancy or Right Relationships

• Should we have a relationship Works-for between
  Stars and Studios?

It depends. We need it if a star might work for
a studio in a manner unrelated to a movie.
4
Redundancy or Right Relationships

• Say everything once only! well, not always
  obvious.
• Do we still need the two-ways relationships
  Owns and Stars-In?

5
Redundancy or Right Relationships

• Can it be possible to deduce the relationship
  Owns from Contracts?
• If for every movie, there is at least one
  contract involving
• that movie,
• its owning studio, and
• some star for that movie,
• then we can dispense with Owns.
• However, if there is the possibility that a
  studio owns a movie, yet has no stars under
  contract for that movie, or no such contract is
  known to our database, then we must retain Owns.

6
Multiway relationships vs. Connecting Entity Sets

• Lets suppose that contracts involve one star,
  one movie, but any set of studios.

With this we can represent the fact that a
contract can involve not more than one star and
one movie, but many studios.
7
Keys

• A key is a set of attributes for one entity set
  such that no two entities in this set agree on
  all the attributes of the key.
• Its allowed for two entities to agree on some,
  but not all, of the key attributes.
• We must designate a key for every entity set.
• In E/R, we underline the key attribute(s).

8
Key for Movies

• Lets consider the entity set Movie.
• We might assume that the attribute title is a
  key. However, there can be different movies with
  the same name
• Godzilla has several different versions
  (Japanese, American etc.).
• If we enforce in the database a key constraint on
  attribute title of Movie class, then the DBMS
  will not allow us to insert information about
  different Godzillas.
• A better choice is to take the set title, year
  of attributes as a key.
• We still run the risk that there are two movies
  made in the same year, with the same title, but
  thats very unlikely.

9
Keys for Studios and Stars

• For Studios
• Reasonable to assume that there are no two
  studios having the same name.
• So, we will enforce name to serve as a key.
• For Stars
• We may think that the name cant serve to
  distinguish two people, but
• Yes! For stars the name distinguishes them since
  traditionally they choose stage names.
• So, again here, we will enforce name to serve as
  a key.

10
Surrogate Keys

• Often, people introduce attributes whose role is
  to serve as a key for classes.
• Companies assign employee IDs to all employees,
  and these IDs are carefully chosen to be unique
  numbers.
• In Canada everyone has a SIN.
• Students IDs in universities
• Driver license numbers
• Automobile registration numbers

11
Entity Sets Versus Attributes I

• Example Bad design

1. This design repeats the manufacturers address
once for each beer 2. Loses the address if
there are temporarily no beers for a manufacturer.
12
Entity Sets Versus Attributes II

• An entity set should satisfy at least one of the
  following conditions
• It is more than the name of something it has at
  least one nonkey attribute.
• or
• It is the many in a many-one or many-many
  relationship.
• Example Good

Manfs deserves to be an entity set because of the
nonkey attribute addr. Beers deserves to be an
entity set because it is the many of the
many-one relationship ManfBy.
13
Subclasses

• Often, a class contains certain objects that have
  special properties not associated with all
  members of the class.
• If so, organize the class into subclasses, each
  subclass having its own special attributes and/or
  relationships.

14
Inheritance in the E/R Model

• In the object-oriented world, objects are in one
  class only.
• Subclasses inherit properties from superclasses.
• In contrast, E/R entities have components in all
  subclasses to which they belong.
• The entity has whatever attributes any of its
  components has, and it participates in whatever
  relationships its components participate in.

15
Components Example

• Take the movie Roger Rabit, which is both a
  cartoon and murder-mystery.
• It will have components in all three entity sets
  Movies, Cartoons, and Murder-Mysteries.
• The three components are connected together into
  one entity by the isa relationships.
• Roger Rabit will have all four attributes of
  Movies, the attribute weapon, and finally will
  participate in the relationship voices.

16
Keys for entity set hierarchies
In entity set hierarchies the key at root is key
for all. title,year is the key for Movies,
Cartoons and Murder-Mysteries.
17
Exercises I

• Exercise 2.1.1 Let us design a database for a
  bank, including information about customers and
  their accounts. Information about a customer
  includes their name, address, phone, and Social
  Security number. Accounts have numbers, types
  (e.g., savings, checking) and balances. We also
  need to record the customer(s) who own an
  account. Draw the E/R diagram for this database.
• Exercise 2.1.2 Modify your solution to Exercise
  2.1.1 as follows
• a) Change your diagram so an account can have
  only one customer.
• b) Further change your diagram so a customer can
  have only one account.
• ! c) Change your original diagram of Exercise
  2.1.1 so that a customer can have a set of
  addresses (which are street-city-state triples)
  and a set of phones. Remember that we do not
  allow attributes to have nonatomic types, such as
  sets, in the E/R model.
• ! d) Further modify your diagram so that
  customers can have a set of addresses, and at
  each address there is a set of phones.

18
Exercises II

• Exercise 2.1.3 Give an E/R diagram for a
  database recording information about teams,
  players, and their fans, including
• For each team, its name, its players, its team
  captain (one of its players), and the colors of
  its uniform.
• For each player, his/her name.
• For each fan, his/her name, favorite teams,
  favorite players, and favorite color.
• Exercise 2.1.4 Suppose we wish to add to the
  schema of Exercise 2.1.3 a relationship Led-by
  among two players and a team. The intention is
  that this relationship set consists of triples
  (player1, player2, team) such that player 1
  played on the team at a time when some other
  player 2 was the team captain.
• a) Draw the modification to the E/R diagram.
• Exercise 2.1.5 Modify Exercise 2.1.3 to record
  for each player the history of teams on which
  they have played, including the start date and
  ending date (if they were traded) for each such
  team.

19
Exercises III

• ! Exercise 2.1.6 Suppose we wish to keep a
  genealogy. We shall have one entity set, Person.
  The information we wish to record about persons
  includes their name (an attribute) and the
  following relationships mother, father, and
  children. Give an E/R diagram involving the
  Person entity set and all the relationships in
  which it is involved. Include relationships for
  mother, father, and children. Do not forget to
  indicate roles when an entity set is used more
  than once in a relationship.
• ! Exercise 2.1.7 Modify your people" database
  design of Exercise 2.1.6 to include the following
  special types of people
• Females.
• Males.
• People who are parents.
• You may wish to distinguish certain other kinds
  of people as well, so relationships connect
  appropriate subclasses of people.