Superkey Definition in DBMS: Example Explanation

[momentum]

In my dbms book , definition of superkey is not clear in this example.

 

[jedishrfu]

I think its saying the pair of state + reg# is a superkey ie a unique key in the relational space.

As an example, it's entirely possible that two different states say New York DMV and Texas DMV rely on the same vendor for DMV registration software and hence two people could have the same registration # for their vehicle but when state is added then it definitely becoming unique.

The case where serial # (VIN) is a superkey makes sense too since serial numbers for vehicles should be unique across all vehicles. Adding make defines it better but its not necessary.

https://en.wikipedia.org/wiki/Vehicle_identification_number

The VIN number is standard on vehicles and is stamped on many parts as a means to defeat car theft for parts. It is unique across all car manufacturers by agreement.

 

[momentum]

Okay ...It also said (SerialNo, Make) is a superkey but not key?

why ? what is the difference between key and superkey here ?

 

[jedishrfu]

Because make isn’t necessary serial# is a minimal key by itself. Basically the VIN actually encodes the car manufacturer and make already, if that’s what they mean by serisl# here.

 

[momentum]

Why SerialNo, Make is superkey ? I don't understand this part

 

[jedishrfu]

You’re getting hung up on this concept. Keys are used in tables to select data. Sometimes we need keys that are unique to one and only one row. A car serial #, VIN is a unique key. It identifies a single car out of all the cars in the world.

Creating a key from serial# and make also identifies a single car but make while useful isn’t needed. So a super key has the uniqueness property and a key has the uniqueness property plus there’s no extra fields in the key. Hence serial# by itself is a key whereas serial# plus make is a super key.

I don’t like this description of keys and super keys. It makes a simple concept unnecessarily complicated.

 

[momentum]

you mean ... key is only one stuff but superkey is a combination or a collection of stuff ?

 

[jedishrfu]

Perhaps it’s better if you talk to your teacher, I feel I will only confuse you more. Read the definitions again and look at the examples they give.

I tried to explain it using real world examples where th car serial # is a unique key by itself and table designers would choose it for their primary key because no two cars have the same serial# ie VIN.

However, other designers might choose state plus reg# because again it’s unique and no two cars should have the same combination of values. The types of queries performed on your database schema determine what keys are chosen for tables and why.

Why would someone choose serial# over the state plus reg# because the search for a car would be faster just give me the serial# and boom I have the car. Consider the state plus reg# now you have to ask does the state match yes okay now does the reg# match bingo we found the car.

Perhaps some other PF member has a better way to explain it.

Here’s a stackexchange discussion on the same thing with a database designer piping in that he never heard of super keys before.

https://dba.stackexchange.com/quest...nce-between-primary-key-and-super-key-in-dbms

 

[verty]

As the book explains it, a superkey is a collection of attributes that you can use to search for a particular record. It won't return 2 or more. If you can't remove attributes from the superkey without it still being a superkey, then it is called a key. A key is also called a candidate key. It is a candidate for being the primary key.

"Serialno, Make" can be used to search for one car; it is a superkey. But we can just search on Serialno, so "Serialno, Make" is not a key. And Serialno is one of the candidates for being made primary key for that table.

Does that make sense?

 

[hmmm27]

Why would someone choose serial# over the state plus reg# because the search for a car would be faster just give me the serial# and boom I have the car. Consider the state plus reg# now you have to ask does the state match yes okay now does the reg# match bingo we found the car.

Assuming you mean serial# as VIN and reg# as license-plate...

If you've a database with all vins and reg's, state+reg as an indexed key is a more efficient retrieval, if for no other reason than it's shorter.

Just like "superkey" - while more than a little pretentious - has less syllables than "unique compound-key" (which is what it seems to be).

 

[jedishrfu]

I was thinking of serial number as a vin and reg# as one of those arcane numbers like an account number often found on state forms because they realize that license plates can change due to theft, loss or vanity upgrades.

I’m not an expert in database tuning but I think that the state plus reg# key might not be optimal since most records of cars in a state are registered to that state so most searches will discriminate via the reg# alone. However the VIN seems to be the primary key a lot of query cases for unique records.

In any event, we are speculating here and the write up is more at a theoretical level. The op must bridge the gap to understand the concept rather like seeing the forest instead of the trees. Our examples are mostly the trees.

 

[jim mcnamara]

Unique means one and only one. Never duplicated. There is never ever more than one. A superkey is a unique key. It can have have only one field. It can have two fields. It can have three fields, and so on.
You have to be sure it is never duplicated in the table.

"Superkey" comes from some textbook writer making new terms for no really good reason.

This kind of thing - something that controls what the user is allowed to enter into a data field or column - is called a constraint.

 

[CWatters]

Its not clear if a serial number is the same as the VIN.

For washing machines two companies could issue the same serial number so you need the serial number and make to uniquely identify it.

 

[hmmm27]

I think the book's author is trying to disambiguate from unique keys that are either system generated, or may contain generated suffices (DarVade0077) to avoid duplication.

VIN's , starting in the '80s, embed the manufacturer's code. They're unique.

 

[sysprog]

The example in the Superkey Wikipedia article seems (at least to me) reasonably perspicuous: https://en.wikipedia.org/wiki/Superkey#Example