Definition of super-key, primary key, foreign key, etc. in database

This article mainly introduces the specific definition and usefulness of super-key, candidate key, primary key, main attribute, foreign key in MySQL database. You can also refer to the topic.alibabacloud.com MySQL video tutorial and MySQL manual。

First look at the definitions of the various keys:

Super key: An attribute set that uniquely identifies a tuple in a relationship is called a hyper-key of a relational pattern

Candidate key (candidate Key): A super-key that does not contain extra attributes is called a candidate key

Primary key (primary key): A candidate key program primary key that the user selected as a tuple identity

Foreign key (foreign key) if the set of properties in the relational schema R1 is not the primary key of the R1, but the primary key of the other relationship R2, the property set is the foreign key of the relational schema R1.

Specific explanations of the examples:

Suppose you have the following two tables:

Student (school number, name, gender, ID number, teacher number) teacher (teacher number, name, salary)

Super key:

The definition of a super-key indicates that any combination of the student's number or a social Security number is a super-key for the table. such as: (School Number), (school number, name), (Social Security number, gender) and so on.

Candidate Key:

A candidate key is a super-key, which is the smallest super-key, meaning that if you remove any of the candidate keys, it is no longer a super-key. The candidate keys in the student table are: (Student number), (Social Security number).

Primary key:

The primary key is one of the candidate keys, is a man-made rule, for example, in the student table, we usually let the "learning number" key, teachers table let "teacher number" key.

FOREIGN key:

Foreign keys are relatively simple, and the foreign keys in the student table are "teacher numbers". A foreign key is primarily used to describe the relationship of two tables.

Main properties and primary keys

Strictly speaking main attribute: refers to the primary key column, which is a primary key defined by a column: a property or property set that uniquely identifies a tuple, which can consist of multiple columns.
In teaching, most instances are the primary key consists of a column, so you can also simply say that the main attribute and the primary key is no different.

First Paradigm (1NF)

The first paradigm is that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties. That is the basic concept of the first paradigm.

What do you mean, for example, there is a "phone" field in a table with a value that has both a cell phone number and a landline number. So that does not conform to the requirements of the first paradigm. At this point, the "phone" should be split into "phone" and "landline" so it becomes a data table that conforms to the first paradigm.

Summary: Fields can no longer be divided.
Second Paradigm (2NF)
The second paradigm is established on the basis of the first paradigm, that is, satisfying the second paradigm must first satisfy the first paradigm. The second paradigm requires that each instance or row in a database table must be uniquely differentiated. For the implementation of the distinction, it is often necessary to add a column to the table to store the unique identity of each instance, requiring that the entity's properties depend entirely on the primary key. To discuss a table character that does not conform to the second normal form, the primary key of the table is the combined primary key, and if it is not a combined primary key, then the character does not conform to the second normal.
Summary: cannot be partially relied upon, meaning that when a table has a combined primary key, the other non-primary key fields must be completely dependent on the primary key.
Third Paradigm (3NF)

The third paradigm is to conform to the second paradigm, and the third paradigm is that a data table cannot have non-primary key fields in other tables, that is, if there are fields in other tables, it must be the primary key of that table.

If a table has a B C three fields, A->b b->c. Then C and a are transitive dependencies that do not conform to the third paradigm. And whether a can directly determine that C is a transitive dependency, only B and C do not depend on any field other than a to conform to the third paradigm.

Summary: There can be no transitive dependencies, which means that fields other than the primary key must depend on the primary key and cannot depend on other fields.