Introduction

On Monday, we were describing the Entity-Relationship (E/R) model and E/R diagrams as a high level data model.
We said this data model was useful in figuring out how to organize our data.
The idea was once we have an E/R Diagram for our database, we then map it in procedural manner to the relational model and from there do some final sanity checks like is it in 3NF before implementing the result in a database.
We went over how to draw Entity Sets, Attributes, and Binary and Multiway Relationships.
We discussed many-one and one-one relationships and how to indicate these in our diagrams.
We discussed how a relationship might have more than one copy of the same entity and how to indicate this in a diagram.
We said how to convert multiway relationships into aset of binary relations involving a newly added entity set.
We then looked at a common extension to the basic E/R model to allow subclassing of entity sets.
Given the basic building blocks of the E/R diagrams, we finally described techniques for creating diagrams and avoiding anomalies with E/R diagrams.

Constraints in the E/R Model

We now look at how the E/R model expresses constraints.
It can express both key constraint diagrammatically as well as referential constraints...

A key for an entity set `E` is a set `K` of one or more attributes such that, given any two elements `e_1` and `e_2` of `E`, `e_1` and `e_2` cannot have identical values for each of the attributes in `K`.
Every entity set must have a key.
In some cases, we will allow the key to live in a different entity set. For example, we might do this with entities arising in isa relationships.
There will typically be more than one possible key for an Entity set, in which cases, we will usually privilege one as the "primary key".
When an entity set is in an isa-hierarchy, we require that the root entity set for this tree to have all the attributes needed for a key.
We use underlines on attributes in an E/R diagram to indicate the primary key of an entity set. For example, title year in Movies in the diagram above.

The arrow notation we have used to indicate many-one and one-one relations, says if that a entity is involved in a most one tuple, but does not require all members of the many side to be involved with any entity of the one side.
For example, we said that Owns(Movies, Studies) was many one to Studios.
This says that any movie can be own by at most one studio, but not that every movie must be owned at all.
If we want to say that every movie is referenced at least once as well, we use a rounded arrow like above.
This is called a referential constraint.

We can also indicate the cardinality of the participation in a relationship using an inequality on the edge as in the above.
The above diagram says that at most 10 stars can appear in single movie.
This kind of constraint is called a degree constraint.

It is possible for an entity set's key to be composed of attributes, some or all of which belong to another entity set.
Such an entity set is called a weak entity set and it is represented by a double rectangle.
The relationship connecting it to the entity which has its key attributes, its supporting entity set, is drawn with a double diamond. It is called the supporting relationship.
There are two common situations when weak entity sets arise:
1. Sometimes entity sets fall into a hierarchy based on classification unrelated to the "isa hierarchy" we've previously considered.
  - For example, in taxonomy, a species like humans, is designated by its genus and species name. We could imagine genus as an entity set of which Homo might be a member, species could then be a weak entity set of it. We don't always know the species just from the name. We need the key from genus as well.
  - In the above diagram Crews is a weak entity set, knowing the number of a film crew doesn't tell us which crew it is unless we also know the studio.
2. Sometimes weak entity sets arise when we try to eliminate multiway relationships.
  - For example, when we eliminate the 3-way Contracts relationship we had on Monday, the key for the new Contracts entity set come from combining the keys of Star, Studios, and Movies.

Come up with an example of your own for each of the two common situations where a Weak Entity arises.
Post your examples to the Oct. 16 In-Class Exercise Thread.

Conditions on the Weak Entity Set's Key
1. Zero or more of the entity set's own attributes belong to the key.
2. There must be at least one key attribute not belonging to the weak entity set itself.
3. Additional key attributes are reached by many-one relationships (supporting relationships).
Conditions on Supporting Relationship `R`
1. `R` must be a binary, many one relationship from the weak entity set ``E to the supporting relationship `F`.
2. `R` must have referential integrity from `E` to `F`.
3. The attributes that `F` supplies for the key of `E` must be key attributes of `F`.
4. If `F` itself is weak, then some or all of the key attributes of `F` supplied to `E` will be key attributes of one or more entity sets `G` to which `F` is connected by a supporting relationship. This holds recursively if `G` too is weak.