Subtyping is a powerful communication tool on the logical data model, because it allows the modeler to represent the similarities that can exist between distinct business concepts to improve integration and data quality. As an example, refer to the following subtype structure where the supertype Course contains the common data elements and relationships for the Lecture and Workshop subtypes:
Briefly walking through this model, we learn that a Course such as Data Modeling 101 can be a Lecture or a Workshop. Data Modeling 101 can be taught at one or more Locations. A Learning Track such as the Data Track must consist of one or more Lectures yet only one Workshop. A Workshop such as the Advanced Design Workshop can require certain Courses as a prerequisite such as Data Modeling 101 and Data Modeling 101 Workshop.
This is a logical data model (with data elements hidden to keep this example manageable). On the physical data model we can replace this subtype symbol with one of three options:
Rolling down: Remove the supertype entity and copy all of the data elements and relationships from the supertype to each of the subtypes.
Rolling up: Remove the subtypes and copy all of the data elements and relationships from each subtype to the supertype. Also add a type code to distinguish the subtypes.
Identity: Convert the subtype symbol into a series of one-to-one relationships, connecting the supertype to each of the subtypes.
For this Challenge, using this Course structure as example, what are the pros and cons with each of these three options?