Objectives After completing this chapter, you should be able to: Describe advanced concepts of database design
●
Define Subtypes and Supertypes
●
Describe Generalization and Specialization
●
Use Constraints in Supertype
●
Use Constraints in Supertype/Subtype Discriminators
●
Describe Supertype/Subtype Hierarchy
●
Define domains
●
Basic Definitions ●
Subtype: A subgrouping of the entities in an entity type which has attributes that are distinct from those in other subgroupings.
●
Supertype: A generic entity type that has a relationship with one or more subtypes.
●
Inheritance: Subtype entities inherit values of all
attributes of the supertype. An instance of a subtype is also an instance of the supertype.
Basics continued...
Basic notation for supertype/ subtype relationship:
Basics continued...
Employee supertype with three subtypes:
Relationships and Subtypes • Relationships at the supertype level
indicate that all subtypes will participate in the relationship. • The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level.
Relationships continued...
Supertype/subtype relationships in a hospital:
Supertype/ Subtype Notation The hieararchy of the supertype/suntype notation is as follows: ● SUPERTYPE ● SUBTYPE 3 ● SUBTYPE 2 ● SUBTYPE 1 ● Attributes unique to subtype 1 ● Attributes unique to subtype 2 ● Attributes unique to subtype 3 ● Attributes shared by all entities
Generalization ●The process of categorizing entities by their
similarities and differences is known as generalization. ●Generalization hierarchies should be used when: ●A large number of entities appear to be of the same type,
●Attributes are repeated for multiple entities, ●The model is continually evolving.
Rules for Generalization ●Each instance of the supertype entity must appear in at least one subtype. ●Subtypes can be a part of only one generalization hierarchy. ●Subtypes may be the parent entity in a relationship, but not the child.
Example
Specialization
●The process of defining one or more subtypes of the supertype, and forming supertype/subtype relationships.
Constraints in Supertype
●Completeness Constraints
●Disjointness Constraints
Completeness Constraints ● Addresses the question of whether an instance of a
supertype must also be a member of at least one subtype. ● The total specialization rule specifies that each entity
instance of the supertype must be a member of some subtype in the relationship. ● It is denoted by a double line.
● The partial specialization rule specifies that an entity instance of the supertype is allowed to not belong to any subtype. ● It is denoted by a single line.
Constraints continued... Total Specialization Rule
Disjointness Constraints ● Addresses the question of whether an instance of a Super type may simultaneously be a member of two (or more) subtypes. ● The disjoint rule specifies that if an entity instance is a member of one subtype, it cannot simultaneously be a member of any other subtype. ● It is denoted by the letter “d”. ● The overlap rule specifies that an entity instance can simultaneously be a member of two (or more) subtypes.
Constraints continued... Disjoint Rule
Constraints continued... Overlap Rule
Constraints in Discriminators Subtype Discriminator ● It is an attribute of the supertype whose values determine the target subtype(s)”. It is used to direct into which of the subtypes (if any) a new instance of the supertype should be inserted. ● Disjoint – a simple attribute with alternative values to indicate the possible subtypes. ● Overlapping – a composite attribute whose subparts
pertain to different subtypes. Each subpart contains a boolean value to indicate whether or not the instance belongs to the associated subtype
Discriminators continued... Introducing a subtype discriminator (Disjoint Rule)
Discriminators continued... Introducing a subtype discriminator (Overlap Rule)
Supertype/Subtype Hierarchy ● It is “a hierarchical arrangement of supertypes and subtypes, where each subtype has only one supertype”. ● In this hierarchy, attributes are assigned at the highest
logical level that is possible in the hierarchy. ● Subtypes that are lower in the hierarchy inherit attributes not only from their immediate supertype,
but also from all supertypes higher in the hierarchy, up to the root.
Hierarchy continued... Example of supertype/subtype hierarchy
Domain ● A domain is a conceptual pool of values from which one or more attributes draw their actual values. Examples:
DOMAIN AGE RANGE 0-127 ATTRIBUTE EMPLOYEE.AGE 16-65 ATTRIBUTE DEPENDENT.AGE 0-60 ● Two values can only be compared if they come from the same domain.
Defining a Domain Syntax: CREATE { DOMAIN | DATATYPE } [ AS ] domain-name
Domain Integrity Constraints ● Domains are used in the relational model to define the characteristics of the columns of a table. ● The domain specifies its own name, data type and logical size. ● The logical size represents the size as perceived by the user, not how it is implemented internally.
Summary You now should be able to: ●
Describe advanced concepts of database design
●
Define Subtypes and Supertypes
●
Describe Generalization and Specialization
●
Use Constraints in Supertype
●
Use Constraints in Supertype/Subtype Discriminators