Recognises rolled-up subtypes losing conditional constraints

K-606 · Skill knobit · Activity: Design the physical data model · Competence: Data modelling

Supertype and subtypes in one table. Columns that are mandatory for one subtype become nullable for all, so the database accepts a row missing a value its subtype requires, and accepts values on rows they do not apply to.

Can AI do this?

AI does this partly Some of the tested AI models master this; others do not show it. 4 of 10 models master it.

Mastered means a probability of at least 90% that the model has this knobit, estimated from its graded answers. “Not shown” means the answers do not settle it either way. How the models are tested.

What it looks like in practice

Expected result: The columns whose subtype obligation is lost, and the discriminator or check needed

How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: account holds both loan accounts and current accounts; loan_maturity_date nullable for all rows, so a loan account without a maturity date loads, and so does a current account with one Acceptable: CHECK ((account_type_code = 'LOAN' AND loan_maturity_date IS NOT NULL) OR (account_type_code <> 'LOAN' AND loan_maturity_date IS NULL)) citing the rule; on platforms that do not enforce CHECK, a DQ rule. Each subtype-only column's comment says which subtype it applies to

Also known as: Subtypes rolled up, conditional constraints lost (physical model)

Related knobits

Builds on

• K-516 Recognises attributes inapplicable to some instances
• K-652 Explains the implementation options for subtypes

All 40 knobits of Design the physical data model.

Source: Review checklist 06-physical, row 6; E&N 9.2; S&W 11.3; Codd 1979 §2.3