Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator

<< Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes

Steps in the Study of system >>

Database Management System (CS403)

Lecture No. 11

Reading Material

"Database Systems Principles, Design and Implementation"

written by Catherine Ricardo, Maxwell Macmillan.

Overview of Lecture

o Inheritance

o Super type

o Subtypes

o Constraints

o Completeness

o Disjointness

o Subtype Discrimination

According to the Microsoft Dictionary of Computing

Inheritance Is

The transfer of the characteristics of a class in object-oriented programming to other

classes derived from it. For example, if "vegetable" is a class, the classes "legume" and

"root" can be derived from it, and each will inherit the properties of the "vegetable" class:

name, growing season, and so on2. Transfer of certain properties such as open files, from

a parent program or process to another program or process that the parent causes to run.

Inheritance in the paradigm of database systems we mean the transfer of properties of one

entity to some derived entities, which have been derived from the same entities.

100

Database Management System (CS403)

Super types and Subtypes

Subtypes hold all the properties of their corresponding super-types. Means all those

subtypes which are connected to a specific supertype will have all the properties of their

supertype.

EmpName

EmpAddress

EmpId

EmpPhNo

EMPLOYEE

NoOfHrs

Grade

SALARIED

HOURLY

AnnualSal

HourlyRate

Fig-1 a

The Figure:1 above shows that the supertype and subtype relation between the

SALARIED and HOURLY employees with the supertype entity EMPLOYEE, we can

see that the attributes which are specific to the subtype entities are not shown with the

supertype entity. Only those attributes are shown on the supertype entity which are to be

inherited to the subtypes and are common to all the subtype entities associated with this

supertype.

The example shows that there is a major entity or entity supertype name EMPLOYEE

and has a number of attributes. Now that in a certain organization there can be a number

of employees being paid on different payment criteria.

101

Database Management System (CS403)

P_Name

P_Address

P_Id

P_PhNo

PERSON

Qual

C_Name

STD

FAC

CGPA

Grade

Fig 1 b

The second example is that of student and the Faculty members who are at the super level

same type of entities. Both the entities at the super level belong to the same entity of type

Person. The distinct attributes of the student and faculty members are added later to he

sub entities student and fac.

Supertype / subtype Relationship:

The use of supertype and subtype for the entities is very useful because it allows us to

create hierarchy of the entities according to the attributes they have and we need not to

write all the attributes again and again. We can group similar types of entities and the

attributes associated with those entities at certain levels.

This also adds clarity to the definitions of the entities as it is not necessary to write the

attribute again and again for all the entities.

Moreover it also eases the operation of removing or adding attributes from the entities,

here it is worth noting that adding an attribute at the super entity level will add the

102

Database Management System (CS403)

attribute to the below listed or derived sub entities and removing the attribute will remove

the attribute from the entities at sublevels in the same way.

The process of identifying supertype and creating different type of sub entities is

supported by the general knowledge of the designer about the organization and also based

of the attributes of the entities which are entities existing in the system..

Specifying Constraints

Once there has been established a super/sub entity relationship there are a number of

constraints which can be specified for this relationship for specifying further restrictions

on the relationship.

Completeness Constraint

There are two types of completeness constraints, partial completeness constraints and

total completeness constraints.

Total Completeness:

Total Completeness constraint exist only if we have a super type and some subtypes

associated with that supertype, and the following situation exists between the super type

and subtype.

All the instances of the supertype entity must be present in at one of the subtype entities,

i.e.--there should be not instance of the supertype entity which does not belong to any of

the subtype entity.

This is a specific situation when the supertype entities are very carefully analyzed for

their associated subtype entities and no sub type entity is ignored when deriving sub

entities from the supertype entity.

Partial Completeness Constraint:

This type of completeness constraint exists when it is not necessary for any supertype

entity to have its entire instance set to be associated with any of the subtype entity.

103

Database Management System (CS403)

This type of situation exists when we do not identify all subtype entities associated with a

supertype entity, or ignore any subtype entity due to less importance of least usage in a

specific scenario.

Disjointness Constraint

This rule or constraint defines the existence of a supertype entity in a subtype entity.

There exist type types of disjoint rules.

o Disjointness rule

o Overlap rule

Disjoint constraint:

This constraint restricts the existence of one instance of any supertype entity to exactly

one instance of any of the subtype entities.

Considering the example given in Fig 1a it is seen that there can be two types of

employees, one which are fixed salary employees and the others are hourly paid

employees. Now the disjoint rule tells that at a certain type an employee will be either

hourly paid employee or salaried employee, he can not be placed in both the categories in

parallel.

Overlap Rule:

This rule is in contrast with the disjoint rule, and tells that for one instance of any

supertype entity there can be multiple instances existences of the of the instance for more

then one subtype entities. Again taking the same example of the employee in an

organization we can say that one employee who is working in an organization can be

allowed to work for the company at hourly rates also once he has completed his duty as a

salaried employee. In such a situation the employee instance record for this employee

will be stored in both the sub entity types.

104

Database Management System (CS403)

Ph_Id

P_Name

AdmDate

P_Id

RESPONSIBLE

PATIENT

PHYSICIAN

WardNo

Prescription

OUT DOOR

IN DOOR

PATIENT

DateDischarge

Fig 2-a

In the example the completeness of the relation is shown between the supertype entity

and the subtype entity, it shows that for the data of patients we can have only two type of

patients and one patient can be either an outdoor patient or indoor patient. In it we can see

that we have identified all possible subtypes of the supertype patient. This implies a

completeness constraint. One more thing to note here is the linked entity physician to the

patient entity. And all the relationships associated with the supertype entity are inherited

to subtype entities of the concerned supertype.

105

Database Management System (CS403)

Veh_Id

Model

Price

Registration

VEHICLE

NoOfDoors

CAR

TRUCK

Pessengers

Fig 2-b

The Figure2b shows the supertype and subtype relationship among different type of

vehicles. Here we can see that the Vehicle has only two subtypes, known as Truck and

Car, As it is normal to have a number of other vehicles in the company of a certain type

but when we have noted just a limited number of vehicles then it means that we are not

interested in storing information for all the vehicles as separate entities. They may be

stored in the vehicle entity type itself and distinct vehicle may be stored in the subtypes

car and truck of the Vehicle.

This is a scenario where we have the freedom to store several entities and neglect others,

and it is called as partial completeness constraint rule.

After the discussion of the Total Completeness and Partial completeness let us move to

the next constraint that is disjointness and check for its examples.

Again in the Figure 2-a. we have the environment where patient entity type has two

subtypes indoor and outdoor patient. To represent disjointness we place the letter "D" in

the circle which is splitting the super entity type into two sub entity types. Suppose that

the hospital has placed a restriction on the patient to be either a n indoor patient or

106

Database Management System (CS403)

outdoor patient, in such a case there exists disjointness which specifies that the patients

data can not be place in the database in both the subtype entities. It will be wither indoor

or outdoor.

PartName

Part_No

PART

Sup_Id

MANUFAC-

PURCHASED

TURED

Sup_address

Fig- 3

The figure 3 above shows the second type of disjoint constraint which tells that the entity

subtype instance can be repeated for any single entity supertype instance. We can see the

relationship of a certain hardware company for the parts provided by the company to its

clients. Now there may exist an overlapping situation for a certain part which is to be

provided to a certain firm, but the manufactured quantity of that part is not enough to

meet the specific order, In this situation the company purchases the remaining the

deficient number of parts form the other suppliers. We can easily say that the data for that

specific part is to be placed in both the entity subtypes. Because it belongs to both the

subtype entities, this is an overlapping situation and expresses disjointness with

overlapping. Another important thing which is to be noted here that the purchased part

subtype entity has a relationship with another entity where the data for the suppliers is

stored from whom the parts are bought. Now this relation does not have nay interaction

with the manufactured parts relation as it is not connected with its supertype i.e.--parts

supertype entity.

107

Database Management System (CS403)

Considering the above discussed we can have four different types of combination existing

for the supertype and subtype entities.

Complete Disjoint

Complete Overlapping

Partial Disjoint

Partial overlapping

Subtype Discriminator

This is a tool or a technique which provides us a methodology to determine that to which

subtype one instance of a supertype belongs.

To determine the relation we place an attribute in the entity supertype which can specify

through its value, that to which entity subtype it belongs.

For example we consider the example

There can be two different situations which specify the placement or relationship of a

supertype entity instance in a subtype entity instance. First situation is that of disjoint

situation where one supertype entity instance can be placed only in one subtype of that

supertype. Let us consider the example of vehicles above in Figure-2-b it show that there

can be two different vehicles car and truck associated with the supertype vehicle now if

we place an attribute named Vehicle_type in the supertype we can easily determine the

type of the associated subtype by placing a C for car and a T for truck instance of the

vehicle.

The other situation where the Subtype discriminator is required the overlapping

constraint; it is the situation where one supertype attribute can be placed in more than one

subtype entities.

Considering again the part example shown in Figure 3, which has an overlapping

constraint; In this situation we can have many solution one common solution is to place

two attribute in the supertype one for manufactured and other one for purchased. We can

combine them as a composite attribute, when we place Y for manufacture and N for

108

Database Management System (CS403)

Purchased then it means the part is manufactured by the company, and similarly the

following situation will give us further information

Attribute

Manufacture

Purchased

Result

Manufacture Purchased

Manufactured

Purchased.

Significance of Subtype Discriminator:

Existence of subtype discriminator helps us a lot in finding the corresponding subtype

entities, although we can find a subtype entity instance without having a subtype

discriminator in the supertype but that involves lots of efforts and might consume a huge

time in worst case situations.

This concludes out discussion of The ER Model in the course.

109

Table of Contents: