DB C5 Entity-Relationship Model

Requirements

绘制E-R图
将E-R图转化为模型

ref Database System Design and Concepts P241-294

The entity-relationship (E-R) data model was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database.

The real world can be modeled as:

A collection of entities (实体)
Relationships (联系) among entities

Entity Sets

entity: a “thing” or “object” in the real world that is distinguishable from all other objects.

An entity has a set of properties/attributes, and the values for some set of properties must uniquely identify an entity.
An entity is represented by a set of attributes.
An entity may be concrete or abstract.

entity set: a set of entities of the same type that share the same properties, or attributes.

attributes: descriptive properties possessed by each member of an entity set.

The designation of an attribute for an entity set expresses that the database stores similar information concerning each entity
in the entity set.
Attribute types:
- Simple and composite attributes
- Single-valued and multi-valued attributes(单值和多值属性).
- Derived attributes (派生属性). versus base attributes or stored attributes

Domain (value set): the set of permitted values for each attribute.

Relationship Sets

relationship: an association among several entities.

relationship set: a set of relationship of the same type. Formally a mathematical relation among $n \ge 2$ entities, each taken from entity sets

\{(e_1,e_2, …, e_n) | e_1 \in E_1, e_2 \in E_2, \cdots, e_n \in E_n\}

where $(e_1,e_2, …, e_n)$ is a relationship, $E_i$ is an entity set.

An attribute can also be the property of a relationship set rather than an entity!

e.g depositor(customer-name, account-number, access-date)

Degree

Degree of Relationship Set: number of entity sets that participate in a relationship set.

Binary Relationship set/Relationship set of degree 2:Relationship set involving two entity sets.

Relationship sets may involve more than two entity sets. E.g suppose employees of a bank may have jobs at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets (employee, job, branch).

But Relationships between more than two entity sets are rare. Most relationships are binary.

Binary vs. Non-Binary Relationships

Some relationships that appear to be non-binary may be better represented using binary relationships:

allows partial information.
reduces duplication.

But how? by creating an artificial entity set!

Mapping Cardinalities

Mapping Cardinalities: the number of entities to which another entity can be associated via a relationship set.

Most useful in describing binary relationship sets. For a binary relationship set the mapping cardinality must be one of the following types:
1. One to one (1 : 1)
2. One to many (1 : n)
3. Many to one (n : 1)
4. Many to many (n : m)

Keys

Weak Entity Sets

Weak Entity Set: entity set that does not have a primary key.

The discriminator or partial key (分辨符或部分码) of a weak entity set is the set of attributes that distinguishes among all those entities in a weak entity set that depend on one particular strong entity

The existence of a weak entity set depends on the existence of a identifying entity set or owner entity set

ØIt must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set.

qThe related relationship is called identifying relationship

The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator

E-R Diagram

		sp format	sp meaning
Rectangles	entity sets	Double rectangles	weak entity set
Diamonds	relationship sets	double diamonds	identifying relationship
Lines	attributes to entity sets
	attributes to relationship sets
	entity sets to relationship sets	Directed line	one
		Undirected line	many
		Double line	total participation
Ellipses	attributes	Double ellipses	multivalued attributes
		Dashed ellipses	derived attributes
		Underline attributes	primary key
		Dashed underline attributes	discriminator

Recursive relationship set
Entity sets of a relationship need not be distinct.

Role: the function that an entity plays in a relationship, e.g., the labels “manager” and “worker” are called roles; they specify how employee entities interact via the works-for relationship set.

qRole labels are optional, and are used to clarify semantics of the relationship.

Participation

Total participation: every entity in the entity set participates in at least one relationship in the relationship set.

e.g participation of loan in borrower is total cause every loan must have a customer associated to it via borrower.
Partial participation: some entities may not participate in any relationship in the relationship set.

e.gparticipation of customer in borrower is partial.

Extended E-R Features

Specialization

Top-down design process: we designate subgroupings within an entity set that are distinctive from other entities in the set.

•These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set.

•Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.