What is relationship in database ? Types of relationship, cardinality and degree of relationship in database
Relationship Types
A Relationship Type defines a relationship set among
entities of certain entity types
A relationship type is illustrated in an ERD using a diamond
symbol. Consider the following diagram that shows two entity types (Department
and Instructor) and the concept (a relationship) that instructors work in
departments. This relationship involves two entity types and is referred to as
a binary relationship.
Below is a ternary relationship (a relationship type
involving three entity types).
Relationship types are useful for capturing/expressing
certain business rules.
Relationship Sets
An Relationship Set is a collection of relationships all
belonging to one relationship type.
In the set are instances of relationships. For instance, if
a relationship type is registration then each enrollment of a student in a
course is an instance of registration and appears is in the relationship
set.
Relationships
A Relationship is one instance in a Relationship Set.
Consider a relationship type, registration, where each
instance is the enrollment of a student in a course.
A relationship between the Student Type and Course Type
occurs when a student is registered for a course. In the following diagram we
have three relationships (three relationship instances) since April is taking
two courses and Jim is taking one course.
Cardinality
Cardinality is a constraint on a relationship specifying the
number of entity instances that a specific entity may be relate
When we ask How many employees can work in a single department?
or How many departments can an employee work in? we are asking questions
regarding the cardinality of the relationship.
The three classifications are: one-to-one, one-to-many, and
many-to-many.
Below, the ERD shows a relationship between invoice lines
and products.
The "n" represents an "arbitrary number of
instances", and the "1" represents "at most one
instance". We interpret the cardinality specifications with the following
business rule statements:
• The "n" indicates that the same Product entity
can be specified on "any number of" Invoice Lines.
• The "1" indicates that an Invoice Line entity
specifies "at most one" Product entity.
One-to-One Relationships
One-to-one relationships have 1 specified for both
cardinalities, and do not seem to arise very often. To illustrate a one-to-one,
we require very specific business rules.
Suppose we have People and Vehicles. Assume that we are only
concerned with the current driver of a vehicle, and that we are only concerned
with the current vehicle that a driver is operating. Then, we have a one-to-one
relationship between Vehicle and Person (note the role shown for Person in this
relationship):
One-to-Many Relationships
This type of relationship has 1 and n specified for
cardinalities, and is very common in database designs. Suppose we have
customers and orders and the business rules:
• an order is related to one customer, and
• a customer can have
any number (zero or more) of orders.
We say there is a one-to-many relationship between customer
and order, and we draw this as:
Many-to-Many Relationships
Many-to-many relationships have "many" specified
for both cardinalities, and are also very common. However, should you examine a
data model in some business, there is a good chance you will not see any
many-to-many relationships on the diagram. In those cases, the data modeler has
resolved the many-to-many relationships into two one-tomany relationships.
Suppose we are interested in courses and students and the fact that students
register for courses: Any student may take several courses, A course may be taken
by several students. This situation is represented with a many-to-many relationship between Course and Student:
Degree
The degree of a relationship is the number of entities
participating in the relationship. When we speak of a student registered for a
course, we are discussing a relationship, register, where two entity sets
(Student and Course) are involved; the relationship is of degree 2 because each
instance of register will always involve one student entity and one course
entity.
Relationships of degree 2 are called binary relationships;
relationships of degree 3 are called ternary relationships. In general we speak
of n-ary relationships where n entities participate in a relationship. Most
OLTP systems are designed using binary relationships, and these are mostly what
this set of notes discuss.
Identifying Relationships
An Identifying Relationship is a relationship between a
strong and a weak entity type, where the key of the strong entity type is
required to uniquely identify instances of the weak entity type. The weak
entity type will have a partial key (discriminator) attribute that, in
conjunction with the key of the strong entity type, uniquely identifies weak
entity instances.
Note in the following diagram that the identifying
relationship is drawn with a double line. Since Section is a weak entity it is
shown with a double lined box, and since a Section cannot exist on its own but
in relationship to a Course we shown a mandatory relationship too. Note that
Section has a partial key sectionNo, Course has a key courseNo.
Non-Identifying Relationships
A Non-Identifying Relationship is a relationship between
strong entity types. Each entity type has a key specified; each entity has an
attribute that uniquely identifies it and distinquishes it from any other
instance in the corresponding entity set or extension.
Suppose each University department has one office, but that
the office could be the location for more than one department.
Recursive Relationships
Recursion in a data model is an especially difficult topic.
We say we have a recursive relationship if the same entity type appears more
than once in a relationship. Recursive relationships seem to always occur
somewhere in a data model of decent size. A typical business example arises
when we have a business rule such as "an employee supervises other
employees":
In the above model, why is it necessary that the entity
participation is optional?
Consider other situations such as:
• a team plays a game against another team
• a person is a child of a person
• an organizational unit (e.g. department, division, branch,
...) comprises other units
• a course is a prerequisite for another course
Comments
Post a Comment