What is the definition of power in SQL

Question

My school book Database Systems defines cardinality as follows:

The strength of a relationship is the number of tuples it contains. In contrast, the number of tuples is called the number of relations, and it changes as tuples are added or removed. High cardinality - many tuples, low cardinality - few tuples.

While the Wikipedia article on cardinality (SQL statements) defines it as follows:

Cardinality refers to the uniqueness of the data values ​​contained in a particular column (attribute) of a database table. The fewer the number of elements, the more duplicate elements in the column. There are 3 types of cardinality: high power, normal power and low power.

They may both be right, but I cannot relate the two definitions as related definitions. Rephrasing will be appreciated!

Answer 1

They say the same thing, and this has to do with tuples ( relational algebra ) or strings (layman term).

When he says that high power is the possible values ​​of a particular attribute (or field) that are unique, and therefore the number of rows or tuples is higher:

An example :

  StudentID Lastname Firstname Gender 101 Smith John M 102 Jones James M 103 Mayo Ann F 104 Jones George M 105 Smith Suse F 

How StudentID cardinality is high because it is unique. It has five (5) tuples / rows.

Lastname , on the other hand, has normal power, in particular, there are only three (3) unique tuples / rows. Thus, it has normal cardinality .

Finally, Paul has only two possible unique sets, thus Low Cardinality .

You are probably confusing cardinality here with a Degree relationship, which has something to do with the number of attributes/fields in the relation (or table).

On the other hand, the textbook for Database , speaking of cardinality , usually relates to an entity in relation to another object, that is, to the number of possible occurrence relationships for an object participating in this type of relationship, So, for example, for binary relationship cardinality can be either one-to-one , one-to-many , or many-to-many .

Answer 2

Both definitions try to say that power is the "number of lines." The difference is whether the comparison is “in a table” or “in a specific column”.

The version in the database textbook focuses on relational algebra and table structure (“relations” in this language).

Wikipedia is more practical. It covers the definition of a textbook, assuming that the table has a primary key (the power of the primary key is the same as the database). However, it can be applied to, say, a flag column. If the flag takes only two values ​​( 0 versus 1 ), then we can say that the column power is 2.

This is important for query optimization. Cardinality is one component of choosing the best methods for combining, aggregating, and selecting data. In practice, most databases use more information than power, the so-called “statistics” about columns and their values ​​for optimization.

Answer 3

If we have tables A and B, think of power as the number of rows of table B that will be associated with the row from table A. If the tables are PERSON and VEHICLE, and the ratio is RODE_ON, then the power is equal since most people drove many different vehicles in past, and most cars drove many people. If the ratio is OWN, then the power is low - most people have one vehicle, someone has no one, and the vehicle usually has one or two owners, no more.

Note that power on one side of the relationship is not equal to power on the other hand. If the tables are HUMAN and FINGERS, and the attitude is NON-POSITIVE, then a person has many fingers, but each finger belongs to only one person.

Answer 4

There are two concepts

1. Power index
2. power

I believe this refers to the number of index elements , which is VERY different. Https://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

• The power index is considered the number of unique values ​​in the index.
• The term is used to discuss creating indexes, scanning tables, accessing indexes and accessing tables, as it affects insertions, updates, deletions, storage space.

Here is another example: https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)

• In SQL (Structured Query Language), the term “power” refers to the uniqueness of the data values ​​contained in a particular column (attribute) of a database table.
• The fewer the number of elements, the more duplicate elements in the column. Thus, a column with the smallest possible number of elements will have the same value for each row. SQL databases use a number of elements to help determine the optimal query plan for a given query.

I suggest that the word cardinality focuses on relationships between tables. In particular, this is not a term used to discuss a single table or data uniqueness.

IBM Documentation (if you are looking for a unique word, it is not mentioned) https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cog_rlp.10.2. 2.doc / c_cog_rlp_rel_cardinality.html When you interpret the number of elements , you must consider the notation that appears at both ends of the relationship. Possible end marks are shown in the following list:

• 0..1 (zero or one match)
• 1..1 (exactly one match)
• 0..n (zero or more matches)
• 1..n (one or more matches)

In mathematics, the power of a set is a measure of the "number of elements in a set." (without mentioning the unique btw) https://en.wikipedia.org/wiki/Cardinality

In database design, the cardinality or fundamental principle of one aspect of the data with respect to another is a critical feature. The relationship of one to the other must be accurate and precise with each other in order to explain how each aspect connects together. In a relational model, tables can be related as one-to-many, many-to-many, one-to-one, or one, etc. This is called cardinality given table in relation to another. https://en.wikipedia.org/wiki/Cardinality_(data_modeling)