Creating Subtype Cluster Relationships - ER/Studio Data Architect
A database designer has a problem with her supertype-subtype database design , and An "empty" subtype occurs in a supertype-subtype relationship in which the . The second way to implement the overlapping-subtype discriminator is to. Introduce the concept of supertype/subtype relationships, and prepare the For reinforcement, have the students work Problem 7 (Problems and Exercises) in class. a subtype. j specialization. d anchor object. f action. l subtype discriminator. Problem 13PE: Add a subtype discriminator (named Person Type) to the diagram . Construct the supertype/subtype relationship with different types of notation.
Modeling Supertypes and Subtypes: Part 3
Select the subtype cluster type, Complete or Incomplete. A subtype cluster is complete when all possible subtype entities are included in the subtype cluster. An example of a complete subtype cluster is one in which Person is the supertype and Male and Female are the subtypes.
Select if all possible subtype entities will not included be in the cluster. For example, a subtype cluster is made up of several entities, Employee, StoreManager, SalesPerson and StockPerson, each of which stores information about employees. In this example, Employee is the supertype and the remaining entities can be grouped in a subtype.
This is probably an incomplete subtype cluster because other employee types cannot be represented in the cluster, such as security guards and cashiers. Select Inclusive or Exclusive subtype. Select if each supertype that has subtypes can be more than one subtype at a time. Select if each supertype that has subtypes can be only one subtype at a time. In IE notation, an exclusive subtype is represented with an exclusive OR symbol.
Select a discriminator from the list. A discriminator is an attribute that distinguishes each of the subtype entities from one another. This reinforces our conclusion in the split key section: Our actions in support of A, however, are not so straightforward, and in practice may often be overlooked. As we already noted, there is nothing in the physical design diagram--or logical or conceptual for that matter--that implies this rule.
In our data modeling, relationship participation from sources manager and engineer to the target mangineer is optional, as it must be in this all or nothing scenario. We document and convey the A requirement to the developers, but rather than hope for the best, we should be proactive.
We can't enforce A in the triggering subsystem we're designing because of timing, but we can detect and log transactional violations on a background thread. As the contrapositive of A, condition A' essentially says the same thing, but the wording more strongly suggests the rule on the deletion side.
Unsupported SSL/TLS Version
For safety, we can enforce in our triggering mechanism that the mangineer can't be directly deleted--that it in fact must be deleted through deletion of its corresponding engineer or manager row only. Supporting the Discriminator If we are to add back the discriminator column to the employee table, then it must be dynamically updated as its child tables are modified.
As with the split foreign key and Mangineer rule, for safety we don't leave the implementation up to the growing body of stored procedure code but rather want it in the emerging triggering subsystem. Because there is overhead involved and room for error, we redundantly store this system-implicit data only if query patterns warrant it.
Let's assume they do.
Or break it into two bit columns--we won't consider this. We'll add these constraints on the employee table in part to ensure new rows are in the nil state: A column default having value 3 A check to keep the value in the range An insert trigger to ensure the initial value is 3 You can find these in the sample SupertypeSubtype database.
An implicit fact is one that can be gotten by a query on a table sand as such, we never allow its value to be set by user code; to do so would risk inconsistency.
Because the discriminator value depends on insert and delete operations done on child tables, only triggers on those tables should set it.
To ensure this is so, we also need an AFTER update trigger on the employee table; here is the relevant snippet: We'll see how the context is set in the triggering mechanism following, but we already have the first trigger and other protective measures in place. And yes--setting the context to the current SPID is not the safest strategy--demonstration only.
The Triggering Subsystem We're concerned with enforcing complex business rules when insert and delete actions occur on the tables we generated from the supertype and subtype entities. Let's start with insertion. All triggers for the manager and engineer tables are essentially the same, so I'll just show code for the engineer.
After the triggers package the EmployeeID primary keys from the inserted or deleted logical table into a user-defined table type and invoke the procedure, the procedure code first sets the connection context value so that the update trigger on the employee table will allow the discriminator modification. As before, using the current session ID is not safe--for demonstration only. If the discriminator is in a correct state, it will go to the next correct state--which is why we took pains to ensure the correct insertion start state value 3 in the section on supporting the discriminator above.
Assuming the implementation is correct, convince yourself that for manual! Unlike the insert triggers, the delete triggers for the employee, manager, and engineer must be instead of versions because along with setting the discriminator, they must also implement cascade delete, and do so in child to parent order to prevent foreign key violations. As you recall, we must do our own cascade delete because of multiple inheritance, as was described in the section on the split foreign key problem above.
The employee table trigger is simplest, implementing only cascade delete: ID; END The delete triggers on the manager and engineer will handle the deletion of the mangineer rows, if any: We first delete the child mangineers, which can no longer exist if either owner is missing--condition B'--followed by the manager or engineer rows themselves, and after all deletions we can update the discriminator if deletions did not start at the root employee.
As we did with the code for updating the discriminator, we put the code for deleting the mangineers into a shared module: Recall that as per business rule A' in the section describing the Mangineer Rule above, without the mangineer row we can't have both manager and engineer rows, so we allow deletion through manager and engineer delete triggers only as above.
In the sample code, you see I've again set the context. In any case, this snippet from the final trigger--an after delete trigger--on the mangineer table uses the context to prevent direct deletion: See the instead of delete triggers on hr.
But in fact we have choices as to which entities become tables. We can merge tables in different ways: We justify merging as a way to ease querying or simplify the design or lend better performance, but in practice we don't necessarily get any of these benefits; as always, it takes careful analysis of current and predicted usage patterns.
And as always, we must still enforce all the business rules, and as without merging, triggers are often involved. A corollary of the business rule enforcement statement above is that we must ensure that the data is the same as it would have been without merging.
For our running example, the viable options are these: We'll also examine rolling down the employee into the manager and engineer tables, but we'll need to change the rules. The Rollup In this rollup design, we have merged all three subtype-based tables into the supertype--hr. Where the columns were mostly non-null in the subtype tables, they must all be null in the rollup table.
The yellow tables were in relationships with the subtypes before rolling up, and their relationships remain at the supertype level. For our sample problem, the rollup on balance is a big gain in reducing the plumbing that supports the multiply derived mangineer. As for the discriminator: Although the pre-existing relationships of the employee table are unchanged, its new relationships gotten from the subtype tables must be examined to see whether foreign keys alone still support the rules.
The Discriminator Revisited The discriminator remains an implicit fact, so as we did before we want to block user code from modifying it. When we discussed supporting the discriminator for the pre-rollup design, we put in place miscellaneous constraints plus part of the triggering subsystem, which involved placing triggers on several tables, writing a shared code module, and setting and testing the connection context.
But because all the information we need is now contained in each employee row, we just make the discriminator a computed, persisted column and done.
Because rolled up table hr. If Budget is null, is the employee a manager or not? Admittedly this is a rare case because most subtypes have mostly not null columns. For remaining discussions, we'll assume that manager has at least one not null column, making the computed discriminator valid. The Nullability Rules for Rollup As was stated, all rolled-up columns become nullable.
When rolling up, we must ensure the same. The First Nullability Rule for Rollup. For any row in the rollup table, over all columns from a single rolled-up subtype, all values must be null or if any value is set, then all values for columns that were not nullable in the subtype must be set. This rule can be enforced in a check constraint; here is the sample check constraint for the rolled-up mangineer columns: As the TV ads say, wait there's more!
Pre-rollup, a row in a subtype must have a row in every ancestor in the path leading to the rollup table.
After all, relationships do become foreign keys. The Second Nullability Rule for Rollup. For each row that would have been in a rolled-up subtype table, in the rollup table each of its ancestor subtypes must have values in non-null columns in the rollup table.
We of course enforce the rule in a separate check constraint: BonusPay is null OR EngrDisciplineID is not null If the engineer table had more than one non-null column, we still would only need to test one of its columns: The Mangineer Rule in Rollup Going with our assumption that the manager table has at least one not null column, we have a valid, computed discriminator, and can use it to enforce the mangineer rule in its biconditional form: Clearly the rollup is a winner here.
For the original non-rollup model, we stated that we couldn't enforce condition A--that we could only log violations on a background thread. Condition A requires a mangineer row to exist whenever matching manager and engineer rows do. Enforcement of the other conditions required extensive triggering. Now we just enforced the whole rule with simple Boolean logic.
The series of checks we've build so far can replace the entire triggering subsystem because we are now doing table modifications in one table, not several.
But we will need triggers. Relationship Generalization In the rollup diagram, the white tables are unaffected by the rollup.
Enhanced E-R Model
This is as expected: The referential integrity for the yellow tables, though, which originally connected the subtype tables, has changed: Where participation by hr. The same applies to the mangineer's relationship with the perk plan table. Again this is what we expect, because only some employees are engineers and mangineers, and we achieved this by making all the rollup columns nullable.
And our check constraints ensure that the referencing columns have values only at appropriate times. We implement the foreign keys as before and done. By contrast, the relationship line formerly connecting skl. The difference is owing to the fact that nullability hasn't changed for the engineer skill referencing table. But a big change does exist, and it is subtle: The foreign key alone can't enforce this; I sense a trigger coming on.