Prior to the Midterm, 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.
Attributes
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
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.
E/R Diagrams
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.
Quiz
Which of the following is true?
Any table in BCNF will have the dependency preservation property.
The 3NF synthesis algorithm won't produce relations in 2NF.
Any FD is an MVD.
Instances of an E/R Diagram
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:
A set of entities `{e_{1}, ..., e_{m}}` for each entity set `E` in the diagram.
Each of the entities `e_{i}`'s above has particular values for each of its attributes.
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.
Multiplicity of Binary E/R Relationships
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 Relationships
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.
Roles in Relationships
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:
Attributes on Relationships
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.
Converting Multiway Relationships to Binary
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:
Subclasses in the E/R Model
Given an entity set there might be a large number of entities in it that share additional properties besides the attributes all members shares.
If so, it often makes sense to create a sub-class of the entity set with these additional properties.
We connect the subclass entity set with the original entity set using an isa relationship as we do in the above figure. (Sometimes this is diagrammed by just having a `in` symbol on the edge).
Isa relationships are always one-one even though we usually don't put the arrows at the end of the edges.
In the above, Cartoons would have all the attributes that a Movies entity does, but would also participate in the Voices relationship which says star voices the voice of a character.
We do allow the creation of sub-subclasses using isa relationships, we require the subgraph of E/R relationships to forms a tree. I.e., we don't allow multiple inheritance.
The book doesn't distinguish our basic E/R model from the model with sub-classes, but many books refer to the model allowing sub-classes as the Extended E/R model.
Design Principles for E/R Diagrams
We now have many of the basic components of E/R-diagrams.
Over the next several slides we consider design pricniples which tend to yield better E/R diagrams...
Faithfulness
The design should be faithful to the underlying concepts of the data.
Entity sets and attributes should reflect the real-world.
So a Stars entity set shouldn't have a number-of-cylinders attribute, but an Automobile entity set might.
If we have a relationship, it should make sense in terms of the real-world entities involved.
For example, StarsIn should be a many-many relationship because in the real world, a star can be in many movies and a movie can have many stars.
Avoiding Redundancy
The diagram should only express each item it represents once.
This will avoid the redundancy anomalies we have already considered.
There are several new mechanisms for redundancy in E/R diagrams.
For example:
If we have an Owns relationship between Movies and Studios.
We might also give Movies a studioName attribute.
This could lead to redundancy because:
It is a repetition of a fact already represented by the Own relationship, and this repetition will persist after we run our E/R to relational model algorithm that we discuss later.
One could also have an update anomaly where we change the Owns relation but not this attribute or vice versa.
Simplicity Counts
It is important to try to keep the elements of the design to the smallest number necessary.
For example, consider the E/R diagram:
The two relationships Represents, Owns as well as the entity Holdings replace the single relationship owns we had before.
The fact that Represents is one-one means we could determine from a movie the unique studio that own it, but this is more complicated than just having a single relationship, and would tend to be prone to error.
Choosing the Right Relationship
In creating an E/R model, we often first try to figure out the entity sets and then the relationships between then.
If we had `n` entity sets, we could have as many as `(n(n-1))/2` possible binary relationships between them.
Most of these would probably not be useful and even even be derivable from other existing relationships.
So we want to pick the right relationships to avoid redundancy anomalies.
For example, last day we introduced the Contracts 3-way relationship between movies, stars, and studios.
If we have this relationship, do we still need a StarsIn and a Owns relationship?
Each may or may not be redundant depending on the intent of the people wishing to store the data.
I.e., A StarsIn pair can be deduce from a contract for a star to appear in a movie of a studio. On the other hand, it might be possible for a star to appear in a movie without there having been a known contract. Do the people who will use the DB need to model this possibility?
Picking the Right Kind of Element
Attributes are simpler to implement than entities sets which can be simpler than relationships.
We may have a choice how to represent things as one of these three types.
Our default should be choose the simpler element as long as it doesn't lead to anomalies.
For example, suppose we got rid of the Studios entity set and and just added studioName and studioAddress to Movies.
This might cause problems because we repeat the studio address for each movie made by a studio. This could cause both redundancy and update issues.
On the other hand, if the people who are going to use the DB don't need the studio address recorded, then it might be safe just to avoid the Studios entity set.
Replacing an Entity Set with Attributes
Suppose `E` is an entity set.
If `E` satisfies the following, it may be safely replaced with attributes belonging to other relations:
All relationships in which `E` is involved must have arrows entering `E`.
If `E` has more than one attribute, then no attribute depends on the other attribute. That is, the only key for `E` is all its attributes
No relationship involves `E` more than once.
If these conditions are met, we can replace an Entity with attributes belonging to another relation as follows:
If there is a many-one relationship from `R(F,E)`, then remove `R` and make the attributes of `E` attributes of `F`, renaming as needed if there are conflicts with existing attributes.
If there is a multiway relationship `R` with an arror to `E`, make the attributes of `E` attributes of `R` instead and delete `E` and the arrow to `E`.
