Inheritance parameters of subtype/subclass entities

Question

This is a complex question which I am hoping for a simple answer.

Can a bridging entity inherit PK from a subtype entity that itself has inherited a PK/FK attribute from its parent entity? Conference EDRD Example I have made a scenario to demonstrate what I mean.

So in this example, there is a parent entity that captures information about conferences. There are two possible subtypes for CONFERENCE: INDIVIDUAL and BUSINESS, which have their own "special" attributes and are not shown as not necessary.

There are two bridging entities, meant to capture details of Individuals or Business attending the conference/s. You will see that the bridging entity labelled "A" has the bridging entity that connects directly to CONFERENCE entity. My understanding is that the PK/FK attribute ConferenceNumber in CONFERENCE_BUSINESS is acceptable.

What I am uncertain about, and hoping someone else knows the answer to, is whether the bridging entity beneath label "B" can inherit PK/FK attribute ConferenceNumber from subtype entity INDIVIDUAL, or should it also have a direct relationship to CONFERENCE like the bridging entity labelled "A"?

A better example is when you have books and movies as subtypes and you wish to capture detail about authors and actors. As there can be multiple authors for a book or books and multiple actors for a movie or several movies I want to capture this kind of data using sub type discriminators. I would like to know if anyone knows (and can cite legitimate sources to show) whether the bridging entities in the sample Book and Movie Rental ERD: DVD_ACTOR and BOOK_AUTHOR can inherit PK/FK from the Subtypes I created?

Book and Movie rental ERD example

Cheers.

Answer 1

One approach I've had a lot of success with is:

• use a compound key of ( type_name, identifier )
• use this compound key throughout the data model
• constrain a subtype in a subtype table by putting a constraint on the type_name attribute

In real life, companies and individuals are known as legal persons, a convenient super type for this model. However, a company will more than likely have a different identifier to a person (e.g. a company is registered to a different bureaucracy than, say, the one responsible for issuing social security numbers to individuals) so we need to employ an artificial identifier. We can then push the real life identifier to a subtype table.

Questions for consideration but beyond the scope of this answer:

• Some keys are defined solely for the purpose of foreign keys
• All tables have more than one key: which (if any) should be promoted to primary key? (Hint: it might be the key are defined solely for the purpose of foreign keys!)
• The order of the columns names must be stated when defining a compound key: is this order important?

The following SQL DDL requires ANSI-92 Query Mode but can be recreated using the Access GUI tools. Note that I'm using LocationCountryCode as an attribute common to all types of conference, whereas Slogan and OrganizerPaysExpenses as specific to company and individual subtypes respectively:

CREATE TABLE LegalPersons
( PersonNumber INT NOT NULL UNIQUE,
  PersonType CHAR(10) NOT NULL,
     CHECK ( PersonType IN ( 'Individual', 'Company' ) ),
  UNIQUE ( PersonType, PersonNumber ) );


CREATE TABLE Individuals
( PersonNumber INT NOT NULL UNIQUE,
  PersonType CHAR(10) NOT NULL, 
     CHECK ( PersonType = 'Individual' ),
  UNIQUE ( PersonType, PersonNumber ),
  FOREIGN KEY ( PersonType, PersonNumber )
     REFERENCES LegalPersons ( PersonType, PersonNumber ),
  LastName VARCHAR( 35 ) NOT NULL,
  FirstName VARCHAR( 35 ) NOT NULL );


CREATE TABLE Companies
( PersonNumber INT NOT NULL UNIQUE,
  PersonType CHAR( 10 ) NOT NULL, 
     CHECK ( PersonType = 'Company' ),
  UNIQUE ( PersonType, PersonNumber ),
  FOREIGN KEY ( PersonType, PersonNumber )
     REFERENCES LegalPersons ( PersonType, PersonNumber ),
  CompanyRegisteredNumber CHAR( 8 ) NOT NULL UNIQUE );


CREATE TABLE Conferences
( ConferenceNumber CHAR( 10 ) NOT NULL UNIQUE,
  PersonNumber INT NOT NULL,
  PersonType CHAR(10) NOT NULL,
  FOREIGN KEY ( PersonType, PersonNumber )
     REFERENCES LegalPersons ( PersonType, PersonNumber ), 
  UNIQUE ( PersonType, ConferenceNumber ),
  LocationCountryCode CHAR( 3 ) NOT NULL );


CREATE TABLE BusinessConferences
( ConferenceNumber CHAR( 10 ) NOT NULL UNIQUE,
  PersonType CHAR( 10 ) NOT NULL,
     CHECK ( PersonType = 'Company' ),
  FOREIGN KEY ( PersonType, ConferenceNumber )
     REFERENCES Conferences  ( PersonType, ConferenceNumber ),
  Slogan VARCHAR( 100 ) NOT NULL );


CREATE TABLE IndividualConferences
( ConferenceNumber CHAR( 10 ) NOT NULL UNIQUE,
  PersonType CHAR( 10 ) NOT NULL,
     CHECK ( PersonType = 'Individual' ),
  FOREIGN KEY ( PersonType, ConferenceNumber )
     REFERENCES Conferences  ( PersonType, ConferenceNumber ),
  OrganizerPaysExpenses CHAR( 1 ) DEFAULT 'Y' NOT NULL,
     CHECK ( OrganizerPaysExpenses IN ( 'Y', 'N' ) ) );