Skilirium

Derive the logical data model

Activity 5 of 8 in Data modelling · Occupation: Data modeller

The outcome: derives a normalised logical data model and traces anomalies to their causes.

Builds on: Build the conceptual model and taxonomies.

Which AI model is best at derive the logical data model?

Best result for derive the logical data model: gpt-6-astra (89% test score). Best value: gemini-4-argon (85%, within 5 points of the best at $0.062 per run instead of $0.45).

ModelProviderTierTest scoreKnobits mastered (expected)Cost per test run
gemini-4-argonGoogleFlagship85% 95%$0.062
gemini-3.1-pro-previewGoogleFlagship79% 94%$0.20
gpt-6-astraOpenAIFlagship89% 91%$0.45
claude-fable-5-1AnthropicFlagship88% 94%$0.85
gemini-3.8-flashGoogleMid tier69% 94%$0.045
gpt-6.1-solOpenAIMid tier89% 89%$0.077
claude-sonnet-5-5AnthropicMid tier88% 95%$0.23
gemini-3.5-flash-liteGoogleLow cost60% 66%$0.008
gpt-6-lunaOpenAILow cost68% 92%$0.005
claude-haiku-5-5AnthropicLow cost83% 94%$0.018

Test score: mean rubric score on the test tasks for this activity. Knobits mastered: expected share of the activity's knobits the model masters. Cost: mean API cost of one test run at list prices. Method and caveats.

The 56 knobits of this activity

29 that AI does well, 1 it does partly, and 3 where people still lead.

IDKnobitTypeAI exposure (models mastering)
K-501Recognises an insertion anomaly in a logical data modelSkillPeople still lead 0/10
K-502Recognises an update anomaly in a logical data modelSkillAI does this well 10/10
K-503Recognises a deletion anomaly in a logical data modelSkillPeople still lead 0/10
K-504Recognises a repeating group in an entitySkillAI does this well 9/10
K-505Recognises an attribute holding several factsSkillAI does this well 9/10
K-506Recognises an attribute with several meaningsSkillAI does this well 10/10
K-507Recognises attributes held as name-value rowsSkillNot tested yet
K-508Recognises a partial dependency on a composite keySkillNot tested yet
K-509Recognises a transitive dependency between non-key attributesSkillAI does this well 9/10
K-510Recognises a stored derivable attributeSkillAI does this well 10/10
K-511Recognises a non-superkey determinant in a 3NF entitySkillNot tested yet
K-512Recognises independent multi-valued facts in one entitySkillNot tested yet
K-513Recognises a join dependency kept in one entitySkillNot tested yet
K-514Recognises a lossy decompositionSkillNot tested yet
K-515Recognises a functional dependency lost in a decompositionSkillNot tested yet
K-516Recognises attributes inapplicable to some instancesSkillAI does this well 9/10
K-517Recognises an unstable or meaningful primary keySkillAI does this well 10/10
K-518Recognises an entity identified only by a system-assigned keySkillAI does this partly 6/10
K-519Recognises undeclared subtype constraintsSkillAI does this well 7/10
K-520Recognises independently changing histories in one entitySkillNot tested yet
K-521Recognises an unresolved many-to-many relationshipSkillAI does this well 9/10
K-522Recognises a polymorphic associationSkillNot tested yet
K-523Recognises a connection trapSkillNot tested yet
K-524Recognises a fan trapSkillNot tested yet
K-525Recognises a chasm trapSkillNot tested yet
K-526Recognises a redundant relationshipSkillAI does this well 8/10
K-527Recognises overlapping entitiesSkillNot tested yet
K-528Recognises one entity split by value into severalSkillNot tested yet
K-529Recognises a relationship contradicting its data ruleSkillAI does this well 8/10
K-530Recognises a constraint implied by neither keys nor domainsSkillNot tested yet
K-531Traces a logical-model anomaly to its structural causeSkillPeople still lead 0/10
K-532Records the functional dependencies of an entitySkillAI does this well 9/10
K-533Decomposes an entity along a functional dependency without lossSkillAI does this well 9/10
K-534Resolves a many-to-many relationship into an associative entitySkillAI does this well 9/10
K-535Replaces a repeating group with a dependent entitySkillAI does this well 9/10
K-536Introduces subtypes for attributes applying to only some instancesSkillAI does this well 7/10
K-551Explains functional dependencyKnowledgeAI does this well 10/10
K-552Explains candidate key and prime attributeKnowledgeAI does this well 10/10
K-553Explains the insertion, update and deletion anomaliesKnowledgeAI does this well 10/10
K-554Explains first normal formKnowledgeAI does this well 9/10
K-555Explains second normal formKnowledgeNot tested yet
K-556Explains third normal formKnowledgeAI does this well 10/10
K-557Explains Boyce-Codd normal formKnowledgeNot tested yet
K-558Explains multivalued dependency and fourth normal formKnowledgeNot tested yet
K-559Explains join dependency and fifth normal formKnowledgeNot tested yet
K-560Explains sixth normal form for temporal dataKnowledgeNot tested yet
K-561Explains domain-key normal formKnowledgeNot tested yet
K-562Explains attribute domainsKnowledgeAI does this well 10/10
K-563Explains lossless-join decompositionKnowledgeAI does this well 9/10
K-564Explains dependency preservationKnowledgeAI does this well 9/10
K-565Explains derived data and point-in-time snapshotsKnowledgeAI does this well 10/10
K-566Explains supertype and subtype constraintsKnowledgeAI does this well 9/10
K-567Explains relationship cardinality and optionalityKnowledgeAI does this well 9/10
K-568Explains natural keys, surrogate keys and key stabilityKnowledgeAI does this well 10/10
K-569Explains the connection, fan and chasm trapsKnowledgeNot tested yet
K-570Explains an entity type as the realisation of one business conceptKnowledgeNot tested yet