ZeePedia Add to Favourites   |   Contact us

Database Management Systems

<<< Previous Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator Next >>>
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.
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
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
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.
Database Management System (CS403)
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
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.
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
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.
Database Management System (CS403)
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.
Database Management System (CS403)
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
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.
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.
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
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
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
Manufacture 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.
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules