Outline

• 4NF Algorithm
• E/R Model
• In-Class Exercise

Introduction

• We have been talking what makes a set of relations a "reasonable" relational schema for one's data and have considered a variety of normal forms: 1NF, 2NF, BCNF, 3NF.
• Depending on the normal form, these prevent various kinds data redundancies and anomalies such as insertion, update, deletion anomalies.
• One also looked at important preservation properties usch as the lossless join property (LJP) and the dependency preservation property (DPP).
• We have given a table decomposition algorithm to BCNF which has the LJP and one to 3NF which has both LJP and DPP.
• We then looked at a more general kind of dependency which if left unchecked can cause redundancies in our tables: multivalued dependencies.
• We said an MVD
  `A_1A_2...A_n ↠ B_1...B_m`
  holds on `R` if for each pair of tuples `t` and `u` of relation `R `that agree on all the `A_i`s, we can find in `R` some tuple `v` that agrees:
  1. With both `t` and `u` on the `A_i`s,
  2. With `t` in the `B_j`'s, and
  3. With `u` on all attributes of `R` that are not among the `A_i`s or `B_j`s.
  4. 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.
• 4NF is a normal form that attempts to split tables so as to avoid redundancies caused by such MVDs.
• We begin today by giving a 4NF decomposition algorithm, before talking about heuristic table modeling approaches.

Fourth Normal Form

• We now modify BCNF to get a normal form on MVDs which avoids MVD-style redundancy.
• We say a relation is in fourth normal form (4NF) if whenever
  `A_1A_2...A_n ↠ B_1...B_m`
  is a nontrivial MVD, `{A_1,A_2,...,A_n}` is a superkey.
• In our table earlier `\n\a\m\e` wasn't a superkey, but `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` was non-trivial, so that relation was not in 4NF.
• Since every FD is an MVD, if one is in `4NF` one will be in `BCNF`.

4NF Decomposition Algorithm

We can modify our BCNF decomposition algorithm to get a 4NF decomposition algorithm as follows:

Input:

A relation `R_0` with a set of FDs and MVDs

Output:

A decomposition of `R_0` into relations all of which are in 4NF. The decomposition has the lossless join property.

Method:

Do the following steps, with `R = R_0` and `S= S_0`:

1. Find a 4NF violation in R, say `A_1A_2...A_n ↠ B_1...B_m`, where
   `{A_1,A_2,...,A_n}`
   is not a superkey. If there is none, return; R by itself as a decomposition.
2. If there is such a 4NF violation, break the schema for the relation `R` into two schemas:
   • `R_1`, whose schema is `A_i`s and `B_j`s.
   • `R_2`, whose schema is the `A_i`s together with all the attributes that are not `A_i`'s or `B_j`'s.
3. Find the FDs and MVDs that hold in `R_1` and `R_2`. The book shows how to modify the chase algorithm to do this, but we are going to skip it. Recursively, apply the algorithm to each of `R_1` and `R_2`.

In the case of our earlier example table. The above algorithm would split the table because of `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` and decompose it into two tables `R_1`(name, street, city) and `R_2`(name, title, year).

High Level Database Models

• We are now going to look at the process in which a database such as our movie database might be created.
• This might look like:
  Ideas `->` High-Level Design `->` Relational Database Schema `->` Relational DBMS.
• You might think the design phase should use the relational model as well.
• In practice, it is often easier to start with a higher-level model and then convert the design to the relational model.
• The problem with the relational model is that it has only one concept, the relation, rather than several complementary concepts more closely related to real-world situations.
• We also want to leverage our minds innate ability to process images.
• So we are now going to begin describing several diagramming techniques for modeling databases beginning with one of the oldest, E/R diagrams (Entity/Relationship Diagrams).

Entity-Relationship Model

• In the Entity-relationship model (E/R model), the structure of data is represented graphically as an entity-relationship diagram. This has three principal parts, which we will go over:
  1. Entity Sets
  2. Attributes
  3. Relationships

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.

In-Class Exercise

• Imagine you are trying to come up with the tables for an online shopping business.
• Model this problem using E/R diagrams.
• Then post your solution to the Oct 4 In-Class Exercise Thread.

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

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 a relationship 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: