A four-way Relationship

1
A four-way Relationship

• The relation Contracts involves two studios, a
  star, and a movie.
• The intent is that one studio, having a certain
  star under contract, may further contract with a
  second studio to allow that star to act in a
  particular movie.
• We can visualize the relationship with the
  following 4-tuple
• (studio1, studio2, star, movie)

2
About the Arrows

• Given a star, a movie, and a studio producing the
  movie, there can be only one studio that owns
  the star.
• Similarly, given a star, a movie, and the stars
  studio, we can determine a unique producing
  studio.
• Given a star, the stars studio, and a producing
  studio, there could be several different
  contracts allowing the star to act in several
  movies.
• Similarly, a producing studio might contract with
  some other studio to use more than one of their
  stars in one movie.
• Not all of the other entities are needed to
  uniquely identify the remaining entity.
• E.g. only moviestar is needed to find the prod.
  stud. or the owner studio.

3
Converting Multiway Rel. to Binary

• For a variety of reasons, like translating to the
  UML, we might want to have only binary
  relationships.
• Notably, any multiway relationship can be
  converted to a set of binary many-one
  relationships.
• When removing a multiway relationship, we
  introduce a corresponding new connecting entity
  set, which has many-one relationships with the
  participating entity sets in the original
  relationship.
• The tuples (triples, quadruplets, etc) of the
  original relationship become entities of the new
  entity set.

4
Subclasses

• Often, a class contains certain objects that have
  special properties not associated with all
  members of the class.
• If so, we find it useful to organize the class
  into subclasses, each subclass having its own
  special attributes and/or relationships.
• We relate the parent with child entity sets by a
  special (1-1) relationship called isa.

5
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.

6
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.

7
Design Principles Faithfulness

• That is, entity sets and their attributes should
  reflect reality.
• Can't attach an attribute number-of-cylinders
  to Stars
• 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?

8
Redundancy or Right Relationships

• Say everything once only!
• well, not always obvious.
• Suppose we decided that we needed the three-way
  relationship Contracts. Do we still need the
  two-ways relationships Owns and Stars-In?

9
Redundancy or Right Relationships II

• 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.

10
Redundancy or Right Relationships II

• 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.
11
Multiway relationships vs. Connecting Entity Sets

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

Better, because we can represent the fact that a
contract can involve not more than one star and
one movie, but many movies.
12
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.
• It is allowed for two entities to agree on some,
  but not all, of the key attributes.
• We must designate a key for every entity set.
• Underline the key attribute(s).
• In an ISA hierarchy, only the root entity set has
  a key, and it must serve as the key for all
  entities in the hierarchy.

13
Keys, Example

• 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.

14
Keys, Example (Continued)

• For the entity set Studios
• Its reasonable to assume that there are no
  studios with the same name.
• So, we will enforce name to serve as a key.
• For the entity set Stars
• We may think that the name cannot serve to
  distinguish two people, but
• Yes! For stars the name distinguish them since
  they traditionally choose stage names.
• So, again here, we will enforce name to serve as
  a key.
• Note. In entity set hierarchies the key at root
  is key for all. Movie titleyear is also key for
  Cartoons.

15
Example name is Key for Beers
16
Example a Multi-attribute Key

• Note that hours and room could also serve as a
• key, but we must select only one key.

17
Surrogate Keys

• We might think that it is difficult to find keys
  or to be sure that a set of attributes forms a
  key. In practice the matter is usually simpler.
• In the real world, people introduce attributes
  whose role is to serve as a key for classes.
• For example companies generally assign employee
  IDs to all employees, and this IDs are
  carefully chosen to be unique numbers.
• In Canada everyone has a SIN.
• There is nothing wrong with there being several
  choices of key for a class.
• The idea of creating an attribute whose purpose
  is to serve as a key is widespread
• Students IDs in universities
• Driver license numbers
• Automobile registration numbers

18
Entity Sets Versus Attributes I

• Example Bad design

This design repeats the manufacturers address
once for each beer Loses the address if there
are temporarily no beers for a manufacturer.
19
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.

20
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.

21
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.

22
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.