Shulou(Shulou.com)06/01 Report--

This article will explain in detail the definition of hyperkeys, primary keys and foreign keys in the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

First, look at the definitions of the various keys:

Super key: a set of attributes that uniquely identify tuples in a relationship is called a hyperkey in a relational schema

Candidate key (candidate key): a superkey that does not contain redundant attributes is called a candidate key

Primary key (primary key): a candidate key selected by the user as a tuple identity

Foreign key (foreign key) A set of attributes in relational schema R1 is a foreign key of relational schema R1 if it is not the primary key of R1, but the primary key of another relational R2.

Combined with the specific explanation of an example:

Suppose you have the following two tables:

Student (student number, name, gender, ID card number, teacher number) teacher (teacher number, name, salary)

Super key:

According to the definition of superkey, any combination of student number or ID number in the student table is the superkey of this table. Such as: (student number), (student number, name), (ID card number, gender), etc.

Candidate key:

The candidate key belongs to the superkey, which is the smallest superkey, that is, if any attribute of the candidate key is removed, it is no longer a superkey. The candidate keys in the student table are: (student number), (ID number).

Primary key:

The primary key is one of the candidate keys, which is artificially specified. For example, in the student table, we usually use the "student number" as the primary key and the teacher number as the primary key in the teacher's table.

Foreign key:

The foreign key is relatively simple, and the foreign key in the student table is "teacher number". Foreign keys are mainly used to describe the relationship between two tables.

Primary attributes and primary keys

Strictly speaking, primary attribute: refers to the primary key column, that is, the primary key is defined by one column: an attribute or set of attributes that uniquely identifies a tuple, that is, it can be composed of multiple columns.

In teaching, most instances are made up of a column of primary keys, so it can simply be said that there is no difference between primary attributes and primary keys.

The first normal form (1NF)

The first paradigm means that each column of a database table is an inseparable basic data item, and there can not be multiple values in the same column, that is, an attribute in an entity cannot have multiple values or duplicate attributes, which is the basic concept of the first paradigm.

What do you mean, for example, there is a "phone" field in a table with both a mobile phone number and a seat number, which does not meet the requirements of the first paradigm. At this point, the "phone" should be divided into "mobile phone" and "landline" so that it will become a data table in line with the first paradigm.

Summary: fields can no longer be divided.

Second normal form (2NF)

The second paradigm is established on the basis of the first paradigm, that is, to meet the second paradigm, we must first meet the first paradigm. The second paradigm requires that each instance or row in the database table must be uniquely distinguishable. To distinguish, you usually need to add a column to the table to store the unique identity of each instance, requiring that the attributes of the entity depend entirely on the primary key. The premise that a table character does not conform to the second normal form is that the primary key of the table is a combined primary key, and if it is not a combined primary key, it does not conform to the second normal form.

Summary: cannot be partially dependent, that is, when a table has a combined primary key, other non-primary key fields must be completely dependent on the primary key.

The third normal form (3NF)

The third normal form is to conform to the second normal form, which is a non-primary key field in a data table that cannot exist in other data tables, that is, if there are fields in other tables, it must be the primary key of that table.

If a table has three a b c fields, a-> b b-> c. Then c and an are transitive dependencies, which does not conform to the third paradigm. And no matter whether a can directly determine c, it belongs to transitive dependency, so only b and c do not depend on any field other than a to conform to the third normal form.

Summary: there can be no transitive dependency, which means that fields other than the primary key must depend on the primary key and not on other fields.

This is the end of the definition of hyperkeys, primary keys and foreign keys in the database. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.