Inheritance in Data Models

1
Inheritance in Data Models

• Background The most complex aspect of UML for
  data models, so it was skipped at the beginning.
• Objective Explain how something so complex as
  inheritance and wind its way into something so
  simple as a relational schema

2
UML Subtypes - Standard Inheritance Notation
3
Subtype Entities

• Subtype entity is an entity that represents a
  special case of another entity, called supertype
• Sometimes called an IS-A relationship
• Entities with an IS-A relationship should have
  the same identifier

// inheritance
4
How can subtypes become tables?

• In Java, new subclass is a new class,
• all the details are hidden from you.
• just use the name of the subclass
• Data modeling, same behavior,
• the UML tool will worry the details
• details wont be hidden

5
Example Subtype Entities
6
Inheritance as a Foreign Keythis is what
Rational Rose does
1
0..1
1
fk clientId
pk clientId
0..1
fk clientId

• Advantage Collecting information common to
  subtypes requires mentioning just one table.
• e.g. for all clients, find total amount due.

7
Inheritance as a Foreign Key
1
0..1
1
fk clientId
pk clientId
0..1
fk clientId
Disadvantage accessing all attributes of a an
object requires a foreign key join.
Solution Use views, createview
INDIVIDUAL-CLIENTview as select from CLIENT c,
INDIVIDUAL-CLIENT ic where c.clientId
ci.clientId A real solution?
8
One more SQL DDL Views defintions
1
0..1
1
fk clientId
pk clientId
0..1
fk clientId

• The result of a query, logically, is a table,
• view name can be used anywhere a table name is
  used.
• Rose does not generate the view definitions. (it
  should)

createview INDIVIDUAL-CLIENTview as select
from CLIENT c, INDIVIDUAL-CLIENT ic where
c.clientId ci.clientId A real solution?
9
Second Method Flattening
create table CLIENT ClientNumber numeric,
ClientName varchar(99), AmountDue numeric
create table INDIVIDUAL-CLIENT ClientNumber
numeric, ClientName varchar(99), AmountDue
numeric, Address varchar(99), SocialSecurityNumb
er varchar(9)
create table CORPORATE-CLIENT ClientNumber
numeric, ClientName varchar(99), AmountDue
numeric, ContactPerson varchar(99), Phone
varchar(10), TaxIdentificationNumber varchar(9)

• Advantage Contents of each subtype clearly
  identifiable.
• e.g. tableName.attribute
• Disadvantage Collecting information common to
  subtypes requires mentioning each, every table
• e.g. for all clients, find total amount due.