1

I have the following table. The PK is model_ID. Assuming that the modelName field is unique(that is, no two brands can have an identical modelName), there is a transitive dependency between modelName and brand.

What is the optimal solution for the table to comply with 3NF, without having the modelName field as part of the PK?

table

migrated from programmers.stackexchange.com Jan 28 '15 at 19:16

This question came from our site for professionals, academics, and students working within the systems development life cycle.

  • 1
    @MikePartridge - A database developer would would be equally qualified. I typically don't go to the dba site because I'm rarely involved with backups, disk drives, files structures, security settings, installation, etc. – JeffO Jan 28 '15 at 15:05
  • I question your assumption that modelName is unique. They can change and are outside your control. Why not just stick with a primary key? – kevinsky Jan 28 '15 at 15:08
  • @JeffO The dba.se site is not only about database administration (despite it's name - that was a poor choice and has been discussed endlessly in meta). I have no idea if the question is on topic here but it's certainly on topic in dba as well. I say one of the 2 Qs should be closed (by the OP or mods.) – ypercubeᵀᴹ Jan 28 '15 at 15:29
  • Why don't you want to make model name a part of pk if it represents a natural identity anyway? – mkalkov Jan 28 '15 at 16:22
1

To answer the question about transitive dependency:

If modelName is unique then it is a candidate key, so there is no transitive dependency. The table is at least in 3NF.

1

Model name does not need to be part of the PK. The problem with this table is brand. "Scott" is repeated. Model relies on Brand, but brand does not rely on model.

Create a brand table. Turn the Model.brand to Brand_FK.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.