Database Management Systems (dbms)

Constraints on Relationship Types

• One To One ( 1: 1)

Employee entity
Manages relationship
.m1
.m2
.m3
.
.
.mn
.e1
.e2
.e3
.
.
.en

. d1
. d2
. d3
.
.
.dn
Department entity
EMPLOYEE
DEPATMENT
manages
1
1
An employee can manage one department and a department is managed by only one employee.

Constraints on Relationship Types..

• One To Many (1:N)
• Eg: An employee works for one department and a department has many employees.

Employee entity
Works for relationship
.w1
.w2
.w3
.
.
.wn
.e1
.e2
.e3
.
.
.en

. d1
. d2
. d3
.
.
.dn
Department entity
M
1
EMPLOYEE
DEPATMENT
Works for

Constraints on Relationship Types..

• Many – To – Many (M:N)
• Eg: Consider two entities employee and the project. An employee can work on several projects and a project has many employees.

Employee entity
Works for relationship
Project entity
M
N
EMPLOYEE
PROJECT
Works
.w1
.w2
.w3
.
.
.wn
.e1
.e2
.e3
.
.
.en

. P1
. P2
. P3
.
.
.Pn

Participation Constraint (Membership class)

• Participation constraint specifies the existence of an entity depends upon it being related to another entity through the relationship.
• There are two types of participation constraints.
• Total ( obligatory)
• Each and every entity in the entity set MUST BE related to the other entity set via the relationship.
• Partial (non–obligator, optional)
• Some or parts of the entity set are related to the other entity set but not necessarily all.

Participation Constraint Examples

• Total participation

Every employee must work for a department

EMPLOYEE
DEPATMENT
Works for
.w1
.w2
.w3
.
.
.e1
.e2
.e3
.
.
. d1
. d2
. d3
.

Participation Constraint Examples

• Partial participation

Some employees manage a department but not all.

EMPLOYEE
DEPATMENT
manages
.w1
.w2
.
.
.

.e1
.e2
.e3
.
.


. d1
. d2
. d3
.

Attributes of relationship types

• Relationship types can also have attributes similar to those of entity type.

Eg: Employee works on project

If we need to record the number of hours per week that an employee works on project we can include an attribute hours for the works on relationship type.

EMPLOYEE
PROJECT
Works on
hours

Weak Entity Type

• Weak entity
• An entity type that is existence-dependent on some other entity type.
• Identifying Owner
• The entity type on which the weak entity type depends

E.g. Employee is the Owner of Dependant.

• Identifying Relationship
• The relationship between a weak entity type and it’s owner

Weak Entity Type

• Can not exist without it’s identifying owner
• Weak entity always has total participation with it’s identifying owner.

B
Weak entity
Total
Identifying relationship
Identifying owner
A

Strong Entity Type

• Strong entity
• An entity type that is not existence-dependent on some other entity type is called strong entity.
• Eg: employee works on project

EMPLOYEE
PROJECT
Works on
Conceptual Design – Notations

Entity Type
Weak Entity
Relationship
Identifying Relationship

Conceptual Design – Notations

Attribute
Key Attribute
Multi valued Attribute
Derived Attribute

Conceptual Design – Notations

Composite Attribute
Total Participation of E2 in R
Cardinality ratio 1:N for E 1: E 2 in R

E2
E1
R
R
E2
E 1
1
N

Example - A company Database

• A company database keeps track of a company’s employees, departments and projects. The requirements are as follows:
• The company is organized into departments. Each department has a unique name, a unique number and a particular employee who manages the department. A department may have several locations.

Example - A company Database

• A department controls a number of projects, each of which has a unique name, a unique number and a single location

Example - A company Database..

• We store each employee’s name, i.d. number , address, salary , sex, and birth date . An employee is assigned to one department but may work on several project, which are not necessarily controlled by the same department. Employee may or may not supervise another employee and we need to keep track of the direct supervisor of each employee.

Example - A company Database..

• We want to keep track of the dependants of each employee for insurance purposes. We keep each dependant’s name , sex, birth date and relationship to the employee.

Solution - A company Database..

• Step One
• Identify entity types, attribute on each entity.
• Step Two
• Identify relationship types
• Step Three
• ER Diagram for the company database

Solution - A company Database..

• Step One
• What are the entity type in your company database?
• What are the attributes belongs to each entity?
• Sketch

Solution - A company Database..

• Step Two
• Identify the relationship types in your company database?
• Sketch
• Write assumptions

Solution - A company Database..

• Step Three
• Draw the ER Diagram.