Introduction

At the end of last Wednesday, we introduced a work-flow from data modeling to database implementation.
We said before we create our relational database schema, we usually model things in a high level model that is closer to real-world situations, then map the result to relations.
We said we were going to look at several high level models, starting with the Entity-Relationship (E/R) Model.
We said the E/R model had three components: Entity Sets, Attributes, and Relationships.
We start today by looking at each of these components in detail.

Entity Sets

An entity is an abstract object of some sort, and a collection of similar entities forms an entity set.
As an intuition, entities are similar to objects in an OO-language, and entity sets are similar to class definitions.
Unlike objects and classes, however, entities and entity sets describe the structure of the data (more like a C-struct) and not the operations on the data.
For example, in a movie database, each movie would be an entity, and the set of all movies, an entity set. Similarly, each star is an entity and the set of all stars an entity set, etc.

Entity sets have attributes. An attribute is a property that all entities in an entity set have.
For example, the entity set Movies could have attributes title and length.
There are several variants of the E/R model. In the variant described in the book, all attributes are primitive types such as int's, or string's.
Some variants allow attributes to be structs as well, and may even allow, in addition, sets of struct objects or sets of primitive objects.

Relationships are connections between two or more entity sets.
For example, between the entity sets Movies and Stars, we could have a relationship, StarsIn, which expresses that a Star `s` is in a Movie `m`.
Binary relationships, relationships involving two entity sets, are the most common.
We do allow relationships, however, among more than two entity sets. For example, you could imagine a LoversTriangle ternary relationship which involves three People entities.

An E/R Diagram is a graph representing entity sets, attributes, and relationships.
The shape of nodes in this kind of graph indicate the kind of object from one of these three types as follows:
- Entity sets are represented by rectangles.
- Attributes are represented by ovals.
- Relationships are represented by diamonds.
Edges connect an entity to its attributes and also connect a relationship to its entity sets.
For example, in the above, name and address are attributes of the Studios entity sets and the Studios and Movies entity sets participate in the Owns relationship.
The arrow between Owns and Studios is to indicate the uniqueness constraint that a movie can be owned by only one studio.

Which of the following is true?

Our BCNF decomposition algorithm was dependency preserving.
Our algorithm for converting into 3NF took existing tables and split them in two to restore the 3NF property.
If for all relations in a schema, if one has a nontrivial MVD on a given relation, the left hand side attributes of the MVD form a superkey, then the schema will be in 4NF.

E/R Diagrams are used for describing database schemas.
Given such a description, we may imagine a database described by such a schema containing particular data, i.e., to be an "instance" of this database.
More formally, an instance of an E/R diagram, consists of:
1. A set of entities `{e_{1}, ..., e_{m}}` for each entity set `E` in the diagram.
2. Each of the entities `e_{i}`'s above has particular values for each of its attributes.
3. A set of tuples `{(e_{11}, ..., e_{1n}), ...,(e_{k1}, ..., e_{kn})}` for each `n`-way relationship `R(E_1, ..., E_n)` in the diagram.
For example, an instance of the relationship StarsIn might be:
```
Movies         | Stars
-----------------------------
Basic Instinct | Sharon Stone 
Total Recall   | Arnold Schwarzenegger 
Total Recall   | Sharon Stone
```
where a particular row, such as "Basic Instinct | Sharon Stone", represents a tuple, in this case, (Basic Instinct, Sharon Stone), in the relationship. A value like "Basic Instinct" represents a Movies entity which in turn has attributes.

For binary relationships in E/R diagrams it is common to indicate the multiplicity of participation by each of the two entities involved.
Suppose `R` is a relationship connecting entity sets `E` and `F`. Then:
- If each member of `E` participates in `R` with at most one member of `F`, then we say `R` is many-one from `E` to `F`. This would be diagrammed by having an arrow on the edge of `R` going into F.
- If `R` is both many-one from `E` to `F` and many-one from `F` to `E`, then we say `R` is one-one. This is diagrammed by having an arrow into each entity set from `R` in the diagram. For example,
- If `R` is neither many-one from `E` to `F` or from `F` to `E`, then we say `R` is many-many.

Multiway relationship are relationships between more than two entity sets.
Although rare, they can sometimes be useful to reflect what is going on in the data.
Such as relation is draw by having an edge from the relationship to each of the entity sets that participate in it. For example,

is a three-way (fancy word is ternary) relationship between Stars, Movies, and Studios, representing that a star can have a contract to perform in a movie of some studio.
The arrow indicate, for a particular star and movie, the studio is unique.

As we mentioned with our earlier LoversTriangle relationship, it is possible that one entity set appears two or more times in a single relationship.
If this happens, then we usually label the edges between the entity set and the relationship with the roles of the participation in the relationship.
For example,

could be used to model the relationship of a movie being a sequel of another.
It is also possible to have a multi-way relationship with one entity set appearing more than once:

Sometimes it is useful to allow our relationships to have attributes.
For example, consider the diagram:
In the above, the salary attribute of contracts indicates how much a star was paid for the movie by the studio.
It wouldn't work to put this attribute in any of the current entity sets in the relationship.
We could imagine involving a fourth entity set with contract details, but this is more complicated then just allowing adding an attribute to the relationship.

Some data models, such as UML, don't allow for multi-way relationships, so it is useful to be able to have techniques for removing them.
One way to remove a multi-way relationship is to introduce a new entity set and then have a binary relationship between this new entity set and each of the participants in the original relationship.
As an example, we could convert the four-way relationship Contracts we had earlier into: