Which of the following attribute in one table that uniquely identifies a row of another table?

Explain Primary key with suitable example.

Explain following terms with suitable example

(i) Primary key (ii) Candidate key (iii) Foreign key (iv) Super key

1 Answer

i. Primary key:

• A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.
• A primary key’s main features are:
  • It must contain a unique value for each row of data.
  • It cannot contain null values.
• A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.
• For example, students are routinely assigned unique identification (ID) numbers, and all adults receive government-assigned and uniquely-identifiable Social Security numbers.

ii. Candidate key:

• A candidate key is the most minimal subset of fields that uniquely identifies a tuple.
• Candidate keys are defined as the set of fields from which primary key can be selected. It is an attribute or set of attribute that can act as a primary key for a table to uniquely identify each record in that table.

• Let’s take an example of an Employee table:

  CREATETABLEEmployee (
  EmployeeID, 
  EmployeeName, 
  SSN, 
  DeptID
  )
  

  Here in Employee table EmployeeID & SSN are eligible for a Primary Key and thus are Candidate keys.

iii. Foreign Key:

• A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.
• For example, a table called Employee has a primary key called employee_id. Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between both the tables.
• A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

iv. Super key:

• A superkey is defined in the relational model of database organization as a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set.
• Equivalently a superkey can also be defined as a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent.
• A super key is a set of fields that contains a key.
• Consider the example where the combination of "user_id" and "pet_id" uniquely identifies a tuple if we added "pet_name" (which is not key because we can have multiple pets named "fluffy") it would be a super key. Basically it's like a candidate key without the "minimal subset of fields" constraint.

Please log in to add an answer.

In relational databases, each table has a primary key that creates a unique value identifying each distinct data record or row. This applies to every type of table within the data warehouse.

The types of keys that can be assigned to a table include:

Which key structure you use to identify a unique attribute in a table depends on the nature of your data and business requirements. The following diagram shows how the different key structures can be used to identify a calling center.

The simple key shows how you can identify a calling center with only its Call_Ctr_id. This means that every calling center has its own unique ID.

In the compound key, calling centers are identified by both Call_Ctr_id and Region_id. This means that two calling centers from different regions can share the same Call_Ctr_id. For example, there can be a calling center with ID 1 in region A, and another calling center with ID 1 in region B. In this case, you cannot identify a unique calling center without knowing both the Call_Ctr_id and the Region_id.

Simple keys are generally easier to handle in the data warehouse than are compound keys because they require less storage space and they allow for simpler SQL. Compound keys tend to increase SQL query complexity, query time, and required storage space. However, compound keys have a more efficient ETL process.

Which key structure you use for a particular attribute depends entirely on the nature of the data and your system. Consider what key structures work best when creating lookup tables in the physical warehouse schema.

For information on defining the primary key for tables included in a MicroStrategy project, see Defining the primary key for a table.

Which of the following is attribute in one table that uniquely identifies a row of another table?

Which of the following uniquely identify a row in a table?

Which key is used for uniquely identify a row?

Which of the following uniquely identifies each row in the table using the values in two or more columns?