Entity-Relationship Model part2

Refining the COMPANY database schema by introducing relationships

By examining the requirements, six relationship types are identified.

All are binary relationships( degree 2)
Listed below with their participating entity types:
WORKS_FOR (between EMPLOYEE, DEPARTMENT)
MANAGES (also between EMPLOYEE, DEPARTMENT)
CONTROLS (between DEPARTMENT, PROJECT)
WORKS_ON (between EMPLOYEE, PROJECT)
SUPERVISION (between EMPLOYEE (as subordinate),
EMPLOYEE (as supervisor))
DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)

ER DIAGRAM – Relationship Types are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

Relationship Types

In the refined design, some attributes from the initial entity types are refined into relationships:

Manager of DEPARTMENT -> MANAGES

Works_on of EMPLOYEE -> WORKS_ON Department of EMPLOYEE -> WORKS_FOR etc

In general, more than one relationship type can exist between the same participating entity types MANAGES and WORKS_FOR are distinct relationship types between EMPLOYEE and DEPARTMENT

Different meanings and different relationship instances.

Recursive Relationship Type

An relationship type whose with the same participating entity type in distinct roles Example: In the SUPERVISION relationship EMPLOYEE participates twice in two distinct roles:

supervisor (or boss) role supervisee (or subordinate) role

Each relationship instance relates two distinct EMPLOYEE entities: One employee in supervisor role

One employee in supervisee role

Weak Entity Types

An entity that does not have a key attribute. A weak entity must participate in an identifying relationship type with an owner or identifying entity type.

Entities are identified by the combination of: A partial key of the weak entity type

The particular entity they are related to in the identifying entity type.

Example:

A DEPENDENT entity is identified by the dependent’s first name, and the specific EMPLOYEE with whom the dependent is related.

Name of DEPENDENT is the partial key. DEPENDENT is a weak entity type.

EMPLOYEE is its identifying entity type via the identifying relationship type DEPENDENT_OF

Constraints on Relationships

Constraints on Relationship Types (Also known as ratio constraints)

Cardinality Ratio (specifies maximum participation)

One-to-one (1:1)

One-to-many (1:N) or Many-to-one (N:1) Many-to-many (M:N)

Existence Dependency Constraint (specifies minimum participation) (also called participation constraint)

zero (optional participation, not existence-dependent)

one or more (mandatory participation, existence-dependent)

Many-to-one (N:1) Relationship

 

Many-to-many (M:N) Relationship

Displaying a recursive relationship

In a recursive relationship type.

Both participations are same entity type in different roles.

For example, SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker). In following figure, first role participation labeled with 1 and second role participation labeled with 2.

In ER diagram, need to display role names to distinguish participations.

A Recursive Relationship Supervision

Recursive Relationship Type is: SUPERVISION (participation role names are shown)

Attributes of Relationship types

A relationship type can have attributes:

For example, HoursPerWeek of WORKS_ON

Its value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT.

A value of HoursPerWeek depends on a particular (employee, project) combination Most relationship attributes are used with M:N relationships.

In 1:N relationships, they can be transferred to the entity type on the N-side of the relationship.

Example Attribute of a Relationship Type: Hours of WORKS_ON

Notation for Constraints on Relationships

Cardinality ratio (of a binary relationship): 1:1,1:N, N:1, or M:N Shown by placing appropriate numbers on the relationship edges.

Participation constraint (on each participating entity type): total (called existence dependency) or partial.

Total shown by double line, partial by single line

Alternative (min, max) notation for relationship structural constraints:

Specified on each participation of an entity type E in a relationship type R Specifies that each entity e in E participates in at least min and at most max relationship instances in R

Default(no constraint): min=0, max=n (signifying no limit) Must have min≤max, min≥0, max ≥1

Derived from the knowledge of mini-world constraints Examples:

A department has exactly one manager and an employee can manage at most one department.

Specify (0,1) for participation of EMPLOYEE in MANAGES Specify (1,1) for participation of DEPARTMENT in MANAGES

An employee can work for exactly one department but a department can have any number of employees.

Specify (1,1) for participation of EMPLOYEE in WORKS_FOR Specify (0,n) for participation of DEPARTMENT in WORKS_FOR

The (min , max) notation for relationship constraints

COMPANY ER Schema Diagram using (min , max) notation

n-ary relationships (n > 2)

In general, 3 binary relationships can represent different information than a single ternary relationship (see Figure 3.17a and b on next slide)

If needed, the binary and n-ary relationships can all be included in the schema design (see Figure 3.17a and b, where all relationships convey different meanings)

In some cases, a ternary relationship can be represented as a weak entity if the data model allows a weak entity type to have multiple identifying relationships (and hence multiple owner entity types) (see Fig 3.17c)

 

Example of a ternary relationship

If a particular binary relationship can be derived from a higher-degree relationship at all times, then it is redundant.

For example, the TAUGHT_DURING binary relationship in Figure 3.18 (see next slide) can be derived from the ternary relationship OFFERS (based on the meaning of the relationships)

Another example of a ternary relationship

Bank Database

There are three basic notations that the E-R Model employs:

1.Entity Sets.

2.Relationship sets.

3.Attributes.

2.2.2 Entities and Entity sets: An Entity is any object of interest to and organization or for the representation in the database.They represent objects in the real world which is distinguishable from all other objects.

For eg: Every person in a college is an entity.

Every room in a college is an entity.

Associated with an entity is a set of properties.These properties are used to distinguish to from one entity to another entity.

For Eg:1.The Attributes of the entity of student are

USN,Name,Address.

2.The Attributes of the Entity Of Vehicle are

Vehicle no,Make,Capacity.

For the purpose of accessing and storing information. Only certain attributes are used.Those attributes which uniquely identify every instance of the entity is termed as primary key.

An Entity which has a set of attributes.Which can uniquely identify all the entities is termed as Strong entity.

An entity whose primary key does not determine all the instance of the entity uniquely termed as weak entity.

A collection of similar entities,Which has certain properties which are common forms an entity set for organization such as a college the object of concern include. Student,Teacher,Rooms,Subjects.The collection of similar entities forms entity set.

2.2.3 Attributes.

An Entity is represented by a set of properties called Attributes.The attributes are useful in describing the properties of each entity in the entity set.

Types of attributes:

1. Simple Attributes: The attributes which cannot be further divided into subparts. Eg; University Seat Number of a student is unique which cannot be further divided.

2. Composite Attributes :The attributes can be further divided into portions. Eg: The attribute name in the Student Database can be further divided into First name,Middle name,Last name.

Name

Firstname Middle name Last name

3. Single valued attributes : The attribute at any instant contains only a specific value at any instant.

for eg The USN is unique

4. Multivalued Attributes; Certain attributes for example the dependent name in the policy database may have set of values assigned to it.There may be more than one dependent for a single policy holder.

5. Stored Attributes:For a person entity,the value of age can be determined from the current date and the value of that person’s birthdate .The Age attribute is hence derived attribute and is said to be derivable from the birthdate attributes,which is called a stored attributes.

6. NULL Attributes: A NULL value attribute is used when an attributes does not have any values.