The Entity Relationship model

 

The Entity/Relationship (E/R) model was developed to give an overall, conceptual view of the organization of data. In these notes, we present the modeling concepts. The E/R model has an associated graphical representation, called E/R diagrams which will be discussed later.

 

Analogies

A Miniworld is a small part of the real world that we are interested in Modeling.

Movie World Example: For a running example we will assume that our Miniworld is the motion picture industry.

 

Entity

An entity is a thing or an object in that world, usually one that physically exists, that is distinguishable from other entities.

 

Attribute

An attribute is a property of an entity.

 

Movie World Example:

Let us assume that we have several "Star" and "Movie" entities.

entity a1 has attributes Name = Merly Streep, Age = 50, HairColour =

{blond, red, brunette}

entity a2 has attributes Name = Robert Redford, Age = 60, HairColour =

blond

entity a3 has attributes Name = Yul Brenner, Age = 60, HairColour =

bald

entity m1 has attributes Name = Sneakers, Cost = $10M, Earning = $40M, 1

Profit = $30M, When-Released = 1995

 

Where a1 and m1 indicates stars and movies respectively.

 

Now we will consider the following observations from the above.

Even among these simple entities we notice that there are several different kinds of attributes. One distinction is simple vs. composite. A simple attribute has an atomic value, while a composite attribute is (naturally) composed of other attributes.

 

MovieWorld Example: We could view a "Star's" Name attribute as a composite attribute, since it is the composition of GivenNames and Surname attributes.

 

Single Valued vs. Multi Valued

Another distinction we can make is single-valued vs. multivalued A single-valued attribute can only be a single value, while a multivalued attribute can be a list or set of values.

MovieWorld Example: The HairColour attribute is multivalued since Meryl Streep's hair colour is three different colours. We will assume that it is three different colours all at the same time!

 

In general, the fact that a single-valued attribute changes value over time (e.g., when a person dyes their hair) does not mean that it is multivalued. A third distinction is stored vs. derived. While the vast majority of attributes will be stored, some attributes can be computed or derived from other attributes.

MovieWorld Example: A movie's Profit is a derived attribute, computable from the Cost and Earnings attributes.

 

 

E-R Diagrams

In an E/R diagram we will represent an attribute using an oval enscribed with the name of the attribute, as follows.

 

At least that is how we will represent a simple, single-valued, stored attribute. A composite attribute will be represented by a hierarchy of ovals, where each oval represents an attribute value within the composite. A multivalued attribute will be represented as an oval within an oval.

 

Finally, a derived attribute will be represented as an attribute with dashed or dotted lines.

 

An aside on null values: One interesting question is what happens when we don't know the value of a particular attribute? When an attribute value is unknown we will use a null value. For the above entites, we have complete information, but in real world databases

 

null values will often be present. We will represent a null value with the special symbol @. For some entites an attribute is inapplicable, which means that the entity does not have a value for that attribute. For instance, the HairColour attribute for Yul Brenner is really inapplicable since he does not have any hair. We will use a @ to represent inapplicable values as well. We have thus overloaded the semantics of @ with two completely disparate meanings. The overloaded semantics however is common in databases since it is in SQL.

 

Symbols used in an E-R Diagrams

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Lecturer-9

Entity type

An entity type is a description of the attributes that a set of possible entities has in common.

 

MovieWorld Example: In our running example, we so far have two entity types: Star and Movie. We will use a third, Studio as well. We will assume that Star has attributes Name, Age, and HairColour. Movie has attributes Name, WhenReleased, Cost, Earnings, and Profit. Finally, Studio has attributes Name and Location. Name is certainly a popular attribute name for these entity types!

 

An entity type is sometimes called an entity set, however, some authors distinguish between the two. More specifically an entity set is a set of actual entities (that is, it is an

extension of an entity type, rather than an entity type itself). We will use the two terms interchangeably. In an E/R diagram an entity type is represented with a rectangular box enscribed with the name of that entity type.

 

Key attributes

Key attributes (or just keys) are a set of attributes, which have distinct values for any possible entity. There may be several keys for a particular entity type.

 

MovieWorld Example: By convention, two movies with the same name cannot be released during the same year. So the attributes Name and WhenReleased form a perfectly reasonable key for the Movie entity type.

 

Relationship

A relationship is an association between two or more entites.

MovieWorld Example: The star Robert Redford "stars in" the movie Sneakers.

 

Relationship type

A relationship type or relationship set is a set of "similar in kind" relationships among one or more entites.

Mathematically, a relationship type, R, among entity types E1, E2, ... En is R E1 E2 ... En In other words a relationship set can be thought of as a subset of the Cartesian product of the participating entity types. The Cartesian product is just the space of all possible associations among the entity types. A relationship type is often also called a role because it describes a role that one entity plays with another.

 

 

 

 

MovieWorld Example: Each star may "star in" one or more movies. So we could have a relationship type StarsIn that captures has all the associations between stars and the movies in which they star.

 

Cardinality Ratio

We will often be interested in the cardinality ratio of a relationship type, that is, how many of each entity type participate in the relationship. Possible cardinality ratios are the following.

One-to-one (1-to-1): Each entity in E1 is associated with 0 or one entity in E2, and vice versa.

 

 

 

MovieWorld Example: Assume that Married is a relationship type between Star and Star, which captures whom is married to whom. It is a 1-1 relationship since each Star is married to at most one other Star .

 

one-to-many :- A one-to-many relationship type (1-N or 1:N) is one in which a single entity of one entity type can be related to several entities of another type, but each entity of the other type is related to at most one entity of the first type.

 

 

 

 

 

 

MovieWorld Example: Assume that Produces is a relationship type between Studio and Movie, which captures which studio produces which movies. It is a 1-N relationship since each Studio may produce several different Movies, but each movie can be produced by at most one Studio (assuming that only one studio can produce a movie, let's not worry too much about collaboration between studios).

 

many-to-many :- A many-to-many relationship type (N-M or N:M) is one in which a single entity of one entity type is related to at most N entities of another type, and vice-versa.

 

 

MovieWorld Example: Assume that StarsIn is a relationship type between Star and Movie, which captures who stars in what movies. It is a N-M relationship since each Star may star in many different Movies, and each Movie may have many different Stars.

 

In an E/R diagram we depict a relationship type as a diagonal box. The cardinality ratio is also shown by adding 1, N, or M to the lines connecting the relationship type to the entity type.

 

Degree of Relationships

Degree. Number of entity types involved in a relationship type. (unary, binary, ternary, ..)

Role. Part played by entities of a unary relationship

 

Weak Entity type

A weak entity type is an entity that needs the key attributes from another entity to uniquely identify tuples. Weak entities lack keys.