The second normal form must satisfy all the conditions of the first normal form. In addition, it must satisfy the condition that all non-primary key columns must be dependent on the entire primary key.
In the previous table, notice that Origin depends only on the superhero, that is, Name. It doesn't matter which Power we are talking about. So, Origin is not entirely dependent on the composite primary key — Name and Power.
Let's extract just the origin information into a separate table called Origin, as shown here:
Name* |
Origin |
Blitz |
Alien |
Hexa |
Scientist |
Traveller |
Billionaire |
Now our Sightings table updated to be compliant to the second normal form looks as follows:
Name* |
Power* ... |