Extended Entity Relationship Model

Summary

• Entity Types and Entity Surrogates
• Single Valued Properties
• Identifying Properties
• Composite Properties
• Multi Valued Property Types
• Relationship Types
• Recursive relationship type
• Supertypes and Subtypes
• Union Entity Types
• Relationship Objectification
• What can EER do?
• What about Queries?
• Relational Model
  • Data Structures
  • Tables
  • Constraints

Entity Types and Entity Surrogates

Entity type names must be unique.

Entities are represented by rectangles.

Properties are represented by ovals.

Single Valued Properties

Single Valued Properties only have the property name in the oval.

Proptery values are:

• Lexical
• Visible
• Audible
• Things that name other things

Identifying Properties

An underlined property type identifies the entity

This is the primary key.

Every entity must be uniquely referenceable.

Composite Properties

The value of some properties may be composed of other properties.

Multi Valued Property Types

Modeled by double oval.

An entity can have multiple multi-valued properties.

For example, a user can have multiple interests.

Relationship Types

Represented by diamonds, cardinality is represented by numbers on the edges.

A partial function is a mapping between entities that may not include every instance of a given entity.

Having N instead of a number means many

Mandatory relationships have a bold line and are called a total function.

You can also have N-M relationship types.

Relationships can be between more than two entities.

Many ternary relationship types cannot be reduced to a conjunction of binary relationship types.

Imagine how the below is different from the above.

Entities can be identified by their relationships, or a combination of their relationshiops and their properties.

In the above, Status cannot:

• Exist without User;
• Be identified without User;

DateAndTime and Email identify Status.

Status is therefore a weak entity type and DateAndTime would be a partial identifier.

Note, DateAndTime should be a regular oval with a dashed underline, but idk how to do that in graphviz.

Recursive relationship type

Recursive relationship types relate an entity back to itself.

Supertypes and Subtypes

A subtype is-a supertype.

For instance, every male user is-a user.

The is-a relationship is modeled with circle and either a d if it is disjoint or a o if it allows for overlap.

Properties are inherited similar to OOP.

Union Entity Types

Union entity types are composed of multiple different entities where an instance of the union has to be a instance of 1 and only 1 subset entity.

Relationship Objectification

In order to turn a relationship into an entity, you split the relationship into two and add an entity between them.

So this:

Becomes this:

What can EER do?

Classification is handled by entity types.

Generalization is handled by Super/Sub types.

Aggregation is not explicitly handled by EER.

Great for fixing and representing a perception of reality.

What about Queries?

Queries do not have a type in EER.

The closest thing is a list of properties.

This is why DBMS’s don’t rely on EER and instead use relational data models.

Relational Model

• Data Structures
• Constraints
• Operations
  • Algebra
  • Calculus
    • Tuple Calculus (SQL)
    • Domain Calculus (QBE)

Data Structures

The only structure is relations.

Domain := a set of atomic values

Relation := a subset of the set of ordered n-tuples in a domain.

$R \subset {<d_1, d_2, …, d_n> | d_i \in D_i, i=1, …, n}$

Attribute := a unique name given to a domain in a relation helping us interpret domain values.

Tables

We illustrate relations by tables.

RegularUser:

Email varchar(50)	BirthDate datetime	CurrentCity varchar(50)	Hometown varchar(50)	Salary integer
[email protected]	1985-11-07	Seattle	Atlanta	10,000

• Relation name
  • RegularUser
• Attribute names
  • Email
  • BirthDate
  • CurrentCity
  • Hometown
  • Salary
• Domains
  • varchar(50)
  • datetime
  • integer
• Tuples
  • Rows
• Degree
  • Number of columns
• Cardinality
  • Number of rows

Order of attributes and tuples are idenpendent of relation

Constraints

• Keys
  • Entity Integrity
• Primary Keys
  • Referntial Integrity

If an attribute is a key of a table, it can’t be null or duplicated.

If an attribute references a key of another table, it must be a subset of the values of that key.