From E/R Diagrams to Relational Designs

From E/R Diagrams to Relational Designs

We now consider the process by which a new database, such as our movie database, is created. We start with a design phase, in which we address and answer questions about what information will be stored, how information elements will be related to one another, what constraints such as keys or referential integrity may be supposed, and so on. This phase may last for a long time, while options are estimated and opinions are reconciled.

The design phase is followed by an implementation phase using a real database system. Since the majority of commercial database systems use the relational model, we might suppose that the design phase should use this model too, rather than the E/R model or another model oriented toward design.

On the other hand, in practice it is sometimes easier to start with a model like E/R, make our design, and then convert it to the relational model. The main reason for doing so is that the relational model, having only one concept - the relation - rather than several complementary concepts (e.g., entity sets and relationships in the E/R model) has certain inflexibilities that are best handled after a design has been selected.

To a first approximation, converting an E/R design to a relational database schema is straightforward:

●  Turn each entity set into a relation with the same set of attributes, and

●  Replace a relationship by a relation whose attributes are the keys for the connected entity sets.

While these two rules cover much of the ground, there are also some special situations that we need to deal with, including:

1. Weak entity sets cannot be translated straightforwardly to relations.

2. "Isa" relationships and subclasses need careful treatment.

3. Often, we do well to join two relations, particularly the relation for an entity set E and the relation that comes from a many-one relationship from E to some other entity set.

Schemas and Instances

We should not forget the main difference between the schema of a relation and an instance of that relation. The schema is the name and attributes for the relation and is relatively unchangeable. An example is a set of tuples for that relation, and the instance may change frequently.

The schema/instance difference is common in data modeling. For instance, entity set and relationship descriptions are the E/R model's way of describing a schema, while sets of entities and relationship sets form an instance of an E/R schema. Remember, however, that when designing a database, a database instance is not part of the design. We only imagine what typical instances would look like, as we develop our design.