Table of Contents
31. What is cardinality in Data Modeling?
In data modeling, cardinality refers to the relationship that a table is having with another table. In other words, cardinality tells how one table is mapped to another table. In database, we have different cardinalities like one-to-one, one-to-many / many-to-one and many-to-many. If the cardinality of a table is one-to-one, then it means each row in a table is mapped with one and only one row of another table. If the cardinality of a table is one-to-many / many-to-one, then it means each row in a table is mapped with more than one row of another table.
Consider our typical example of EMPLOYEES, DEPARTMENTS, and JOBS. Here DEPARTMENTS table is used to represent various departments in the organization. Hence it will not have any duplicate values. Each department will have its own employees. Here each department will have many employees and each employee can work for only one department. Hence the cardinality between DEPARTMENTS and EMPLOYEES is one to many. In other words, one department can have many employees. Similarly, each employee can have only one job but same job can be done by more than one employee. Hence the relationship between EMPLOYEES and JOBS is one to many.
32. What is cardinality in SQL?
In SQL, cardinality refers to the number of distinct values that a column can have. In other words, cardinality refers to the unique values in the column.
For example, in EMPLOYEES table with 1000 rows, cardinality of EMPLOYEE_ID column is 1000. That means EMPLOYEE_ID is a primary key column and it will have only distinct values. Hence we will not get any duplicate values in this column and its cardinality will always be equal to number of rows in the table. Consider the SEX column in EMPLOYEES table. It can have only two values – ‘M’ and ‘F’. Hence its cardinality is two. From these two examples, it is clear that cardinality is the number of distinct values in each column.
Suppose cardinality of a column is zero. This means that column has all NULL values and there is no unique values.
33. What is Selectivity in SQL?
Selectivity is used in association with index. In short, it refers to the frequency of selecting any particular column value. In any column in the table, there will be various values inserted. When a query is fired to select some particular value on that column, selecting that value depends on the frequency of occurrence of that value in the column. That means, it depends on the cardinality of the column. In other words, cardinality tells how many distinct values are there in that column and depending on this distinct values; we can determine the selection frequency using the formula:
Selectivity = Cardinality * 100 / Number of rows
It is useful in determining if we have to use index or not. If a table with huge number of data is queried for particular column value, then deciding if it has to use index or not depends on the selectivity of the column. Usually index is not used if the number of records to be retrieved is comparatively less compared to the number of rows. In other words, if a column has less selectivity, then index will not be used.
Consider the SEX column in the EMPLOYEES table. Its cardinality is two and say table has 1000 rows. Then its selectivity is:
Selectivity = 2 * 100 /1000 = 0.2
This is comparatively less and index on SEX column is not used for querying this column. This is because, there is only two possibilities of having values for SEX and there is high possibility that there can be 50% of the records will have one value and rest of them will have another value. Hence if we use index we have to query index table also for exactly 500 times to select all the SEX with female or male. But if we perform full table scan it will be faster. Hence using index will consume more resource and time in this case. Therefore, when there is less selectivity, it is always better not to use index.
Suppose we need to query PHONE_NUMBER column. Here all the rows will have different values for this column as no two employees can have same phone number. Then its selectivity is:
Selectivity = 1000 * 100 /1000 = 100
This is very high value. Using index in this case will be very useful.
34. What is the difference between cardinality and Selectivity?
35. What is the difference between pages and blocks?
In short both pages and blocks are same concepts, used to store the data. Both of them are used by operating system to read or write the files.
A block is a small unit of data that an operating system can be read or write into the file. It does not have any fixed size and can vary from depending upon the operating system and the use / context.
A page is also similar to blocks used by some of the operating system. It is also considered as virtual block. But it has fixed size – either 2K or 4K.
In most of the operating systems, pages are used as it makes the processing easier. Different operating system will have different block sizes. Hence if we use blocks to read or write, we need to first determine the block size and then we can read or write. But if we use pages, since it has fixed size, it makes read or write much simpler. Pages act as a bridge between the operating system and drivers to read or write, and then it will be updated in the drivers using blocks.
36. What is DUAL table?
It is the default dummy table created by the database when it is created. It has only one column with only one record X. Its column type is VARCHAR2 (1). This table belongs to SYS schema. This table can be used as temporary table to hold or calculate some temporary calculations.