patternMajor
Modeling a scenario in which each Music Artist is either a Group or a Solo Performer
Viewed 0 times
eachgroupsolomodelingeitherartistwhichscenariomusicperformer
Problem
I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail below.
Scenario description
-
An Artist has a Name, and must be either a Group or a Solo Performer (but not both).
-
A Group is made up of one or more Solo Performers and has a Number of Members (which should be calculated from the number of Solo Performers making up the Group).
-
A Solo Performer may be a Member of many Groups or of no Group and may play one or more Instruments.
Question
How to construct an ERD to represent such scenario? I’m confused with the ‘or’ part of it.
Scenario description
-
An Artist has a Name, and must be either a Group or a Solo Performer (but not both).
-
A Group is made up of one or more Solo Performers and has a Number of Members (which should be calculated from the number of Solo Performers making up the Group).
-
A Solo Performer may be a Member of many Groups or of no Group and may play one or more Instruments.
Question
How to construct an ERD to represent such scenario? I’m confused with the ‘or’ part of it.
Solution
The part of the scenario that you are confused with can be modeled with a classic construct called supertype-subtype1 structure.
I will (1) introduce some pertinent preliminary ideas, (2) detail how I would delineate —at the conceptual level— the business context under consideration, and (3) provide additional related material —e.g., the corresponding logical-level representation via SQL-DDL declarations— as follows.
Introduction
A structure of this nature takes place when, in a given business environment, there is a cluster of entity types within which the supertype has one or more properties (or attributes) that are shared by the rest of the entity types in the cluster, i.e., the subtypes. Every subtype has, in turn, a particular set of properties that are applicable to itself only.
Supertype-subtype clusters can be of two kinds:
-
Exclusive. Comes about when an instance of the superentity type must always have one and only one subtype counterpart; therefore, the potential subtype occurrences in question are mutually exclusive. This is the kind that concerns to your scenario.
A typical case in which an exclusive supertype-subtype comes about is a business domain where both an Organization and a Person are considered Legal Parties, like in the situation deliberated in this series of posts.
-
Nonexclusive. Presents itself when a supertype instance may be complemented by multiple subtype occurrences, each of which is compelled to be of a different category.
An example of this kind of supertype-subtype is dealt with in these posts.
Notes: It is worth mentioning that supertype-subtype structures —being elements of a conceptual character— do not belong to a specific data management theoretical framework, be it relational, network or hierarchical —each of which offers particular structures to represent conceptual elements—.
It is also opportune to point out that although supertype-subtype clusters bear a certain resemblance to object-oriented application programming (OOP) inheritance and polymorphism, they are in fact distinct devices because they serve different purposes. In a database conceptual model —that must represent real world aspects— one deals with structural features in order to describe informational requirements, whereas in OOP polymorphism and inheritance, among other things, one (a) sketches and (b) implements computational and behavioural characteristics, aspects that decidedly belong to appplication program design and programming.
Apart from that, an individual OOP class —being an application program component—, does not necessarily have to “mirror” the structure of an individual entity type that belongs to the conceptual level of the database at hand. In this respect, an application programmer may typically create, e.g., one single class that “combines” all the properties of two (or more) different conceptual-level entity types, and such a class may as well include computed properties.
Using entity-relationship constructs to represent a conceptual model with supertype-subtype structures
You asked for an entity-relationship diagram (ERD for brevity) but, although being an extraordinary modeling platform, the original method —as introduced by Dr. Peter Pin-Shan Chen2— did not supply enough constructs to represent scenarios of the sort being discussed with the precision that a proper database conceptual model requires.
Consequently, it was necessary to make some extensions to said method, situation that yielded results in the development of an approach that assists in the creation of enhanced entity-relationship diagrams (EERDs) that, naturally, enriched the initial diagramming technique with new expressive characteristics. One of those characteristics is, precisely, the possibility of depicting supertype-subtype structures.
Modeling your context of interest
The illustration shown in Figure 1 is an EERD (using symbols similar to the ones proposed by Ramez A. Elmasri and Shamkant B. Navathe3, who refer to such structures as superclass/subclass) where I modeled the business domain you describe considering all the specifications. It is also available as a PDF that can be downloaded from Dropbox.
As you can see in the aforementioned diagram, both
Diagram description
In order to start the description of the EERD, it is important to point out that your sentence
is related to the disjointness and the completeness aspects of the supertype-subtype cluster at hand.
Disjointness
The disjointness feature is particularly important because it is right here where the “or part” that you mentioned comes into play, due to the fact that an
I will (1) introduce some pertinent preliminary ideas, (2) detail how I would delineate —at the conceptual level— the business context under consideration, and (3) provide additional related material —e.g., the corresponding logical-level representation via SQL-DDL declarations— as follows.
Introduction
A structure of this nature takes place when, in a given business environment, there is a cluster of entity types within which the supertype has one or more properties (or attributes) that are shared by the rest of the entity types in the cluster, i.e., the subtypes. Every subtype has, in turn, a particular set of properties that are applicable to itself only.
Supertype-subtype clusters can be of two kinds:
-
Exclusive. Comes about when an instance of the superentity type must always have one and only one subtype counterpart; therefore, the potential subtype occurrences in question are mutually exclusive. This is the kind that concerns to your scenario.
A typical case in which an exclusive supertype-subtype comes about is a business domain where both an Organization and a Person are considered Legal Parties, like in the situation deliberated in this series of posts.
-
Nonexclusive. Presents itself when a supertype instance may be complemented by multiple subtype occurrences, each of which is compelled to be of a different category.
An example of this kind of supertype-subtype is dealt with in these posts.
Notes: It is worth mentioning that supertype-subtype structures —being elements of a conceptual character— do not belong to a specific data management theoretical framework, be it relational, network or hierarchical —each of which offers particular structures to represent conceptual elements—.
It is also opportune to point out that although supertype-subtype clusters bear a certain resemblance to object-oriented application programming (OOP) inheritance and polymorphism, they are in fact distinct devices because they serve different purposes. In a database conceptual model —that must represent real world aspects— one deals with structural features in order to describe informational requirements, whereas in OOP polymorphism and inheritance, among other things, one (a) sketches and (b) implements computational and behavioural characteristics, aspects that decidedly belong to appplication program design and programming.
Apart from that, an individual OOP class —being an application program component—, does not necessarily have to “mirror” the structure of an individual entity type that belongs to the conceptual level of the database at hand. In this respect, an application programmer may typically create, e.g., one single class that “combines” all the properties of two (or more) different conceptual-level entity types, and such a class may as well include computed properties.
Using entity-relationship constructs to represent a conceptual model with supertype-subtype structures
You asked for an entity-relationship diagram (ERD for brevity) but, although being an extraordinary modeling platform, the original method —as introduced by Dr. Peter Pin-Shan Chen2— did not supply enough constructs to represent scenarios of the sort being discussed with the precision that a proper database conceptual model requires.
Consequently, it was necessary to make some extensions to said method, situation that yielded results in the development of an approach that assists in the creation of enhanced entity-relationship diagrams (EERDs) that, naturally, enriched the initial diagramming technique with new expressive characteristics. One of those characteristics is, precisely, the possibility of depicting supertype-subtype structures.
Modeling your context of interest
The illustration shown in Figure 1 is an EERD (using symbols similar to the ones proposed by Ramez A. Elmasri and Shamkant B. Navathe3, who refer to such structures as superclass/subclass) where I modeled the business domain you describe considering all the specifications. It is also available as a PDF that can be downloaded from Dropbox.
As you can see in the aforementioned diagram, both
Group and SoloPerformer are displayed as exclusive subtypes of the Artist superentity type:Diagram description
In order to start the description of the EERD, it is important to point out that your sentence
- “an Artist must be either a Group or a SoloPerformer (but not both)”
is related to the disjointness and the completeness aspects of the supertype-subtype cluster at hand.
Disjointness
The disjointness feature is particularly important because it is right here where the “or part” that you mentioned comes into play, due to the fact that an
Artist has to be either one subtype instance or the other, which I specified in the EERD through the small circle containing the letter “d”, a construct that receives the name of disjoint rCode Snippets
--
--
CREATE TABLE Artist ( -- Stands for the supertype.
ArtistNumber INT NOT NULL,
Name CHAR(30) NOT NULL,
Type CHAR(1) NOT NULL, -- Holds the discriminator values.
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Artist_PK PRIMARY KEY (ArtistNumber),
CONSTRAINT Artist_AK UNIQUE (Name), -- ALTERNATE KEY.
CONSTRAINT Artist_Type_CK CHECK (Type IN ('G', 'S')) -- Enforces retaining either ‘G’, for ‘Group’, or ‘S’, for ‘SoloPerformer’, only.
);
CREATE TABLE MyGroup ( -- Represents one subtype.
GroupNumber INT NOT NULL, -- To be constrained as PK and FK simultaneously.
FormationDate DATE NOT NULL,
--
CONSTRAINT MyGroup_PK PRIMARY KEY (GroupNumber),
CONSTRAINT MyGroupToArtist_FK FOREIGN KEY (GroupNumber)
REFERENCES Artist (ArtistNumber)
);
CREATE TABLE SoloPerformer ( -- Denotes the other subtype.
SoloPerformerNumber INT NOT NULL, -- To be constrained as PK and FK simultaneously.
BirthDate DATE NOT NULL,
--
CONSTRAINT SoloPerformer_PK PRIMARY KEY (SoloPerformerNumber),
CONSTRAINT SoloPerformerNumberToArtist_FK FOREIGN KEY (SoloPerformerNumber)
REFERENCES Artist (ArtistNumber)
);
CREATE TABLE GroupMember ( -- Stands for a M:N association involving the two subtypes.
MemberNumber INT NOT NULL,
GroupNumber INT NOT NULL,
JoinedDate DATE NOT NULL,
--
CONSTRAINT GroupMember_PK PRIMARY KEY (MemberNumber, GroupNumber), -- Composite PK.
CONSTRAINT GroupMemberToSoloPerformer_FK FOREIGN KEY (MemberNumber)
REFERENCES SoloPerformer (SoloPerformerNumber),
CONSTRAINT GroupMemberToMyGroup_FK FOREIGN KEY (GroupNumber)
REFERENCES MyGroup (GroupNumber)
);
CREATE TABLE Instrument ( -- Represents an independent entity type.
InstrumentNumber INT NOT NULL,
Name CHAR(30) NOT NULL,
--
CONSTRAINT Instrument_PK PRIMARY KEY (InstrumentNumber),
CONSTRAINT Instrument_AK UNIQUE (Name) -- ALTERNATE KEY.
);
CREATE TABLE SoloPerformerInstrument ( -- Denotes another M:N association, in this case between a subtype and an independent entity type.
SoloPerformerNumber INT NOT NULL,
InstrumentNumber INT NOT NULL,
CreatedDate DATE NOT NULL,
--
CONSTRAINT SoloPerformerInstrument_PK PRIMARY KEY (SoloPerformerNumber, InstrumentNumber), -- Composite PK.
CONSTRAINT SoloPerformerInstrumentToSoloPerformer_FK FOREIGN KEY (SoloPerformerNumber)
REFERENCES SoloPerformer (SoloPerformerNumber),
CONSTRAINT SoloPerformerInstrumentToInstrument_FK FOREIGN KEY (InstrumentNumber)
REFERENCES Instrument (InstrumentNumber)
);
--
--CREATE VIEW FullGroup AS
SELECT G.GroupNumber,
A.Name,
A.CreatedDateTime,
G.FormationDate
FROM Artist A
JOIN MyGroup G
ON G.GroupNumber = A.ArtistNumber;CREATE VIEW FullSoloPerformer AS
SELECT SP.SoloPerformerNumber,
A.Name,
A.CreatedDateTime,
SP.BirthDate
FROM Artist A
JOIN SoloPerformer SP
ON SP.SoloPerformerNumber = A.ArtistNumber;Context
StackExchange Database Administrators Q#92065, answer score: 21
Revisions (0)
No revisions yet.