Completely Solved C, C++ Programs Assignment.

Different Database Keys

Filed Under:

Databases use tables to organize information. Each table consists of a number of rows, each of which corresponds to a single database record. Databases keep all of these records through the use of keys.

Candidate Key
A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.
Alternately, Candidate Key is a column or group of columns that uniquely describe every row in a table.
For instance, in a table that describes people (employees, customers, club members, whatever), there are many columns that describe those people. If you accept the assertion that you can't have two people with the same name, one of your CKs could be the person's name. If you accept the assertion that you can't have two people with a given phone number, one of your CKs could be the phone number. A candidate key must possess the following properties:
  • Unique identification - For every row the value of the key must uniquely identify that row.
  • Non redundancy - No attribute in the key can be discarded without destroying the property of unique identification.
Surrogate Key
A Surrogate Key (SK) is an arbitrary value that is used solely by the database (and therefore by any programs that use that database) to uniquely identify a row. This can be a GUID, an "automagically" assigned number, or just some arbitrary value that happens to be unique for every row. SKs are guaranteed to be unique (that is part of their definition), so they are always CKs. Using an SK can be extremely convenient because it allows the database to function independently of any changes to the business rules that make other CKs unique.

Primary Key
A unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key.
The value this key holds should be unique for each record in the database. For example, assume we have a table called Employees that contains personnel information for every employee in our firm. We’d need to select an appropriate primary key that would uniquely identify each employee. Your first thought might be to use the employee’s name.
This wouldn’t work out very well because it’s conceivable that you’d hire two employees with the same name. A better choice might be to use a unique employee ID number that you assign to each employee when they’re hired. Most organizations have used unique identifiers (employee ID, student ID, etc.).
Once you decide upon a primary key and set it up in the database, the database management system will enforce the uniqueness of the key. If you try to insert a record into a table with a primary key that duplicates an existing record, the insert will fail.
Most databases are also capable of generating their own primary keys. Microsoft Access, for example, may be configured to use the AutoNumber data type to assign a unique ID to each record in the table. While effective, this is a bad design practice because it leaves you with a meaningless value in each record in the table.

Alternate Key
An alternate key (or secondary key) is any candidate key which is not selected to be the primary key (PK).
For example, a relational database with a table "employee" could have attributes like "employee_id", "bank_acct_no", and so on. In this case, both "employee_id" and "bank_acct_no" serve as unique identifiers for a given employee, and could thus arguably be used for a primary key. Hence, both of them are called "candidate keys". If, for example, "bank_acct_no" was chosen as the primary key, "employee_id" would become the alternate key.

Foreign Keys
These keys are used to create relationships between tables. Natural relationships exist between tables in most database structures. Returning to our Employees database, let’s imagine that we wanted to add a table containing departmental information to the database. This new table might be called Departments and would contain a large amount of information about the department as a whole. We’d also want to include information about the employees in the department, but it would be redundant to have the same information in two tables (Employees and Departments). Instead, we can create a relationship between the two tables.
Let’s assume that the Departments table uses the Department Name column as the primary key. To create a relationship between the two tables, we add a new column to the Employees table called Department. We then fill in the name of the department to which each employee belongs. We also inform the database management system that the Department column in the Employees table is a foreign key that references the Departments table. The database will then enforce referential integrity by ensuring that all of the values in the Departments column of the Employees table have corresponding entries in the Departments table.
Note that there is no uniqueness constraint for a foreign key. We may have more than one employee belonging to a single department. Similarly, there’s no requirement that an entry in the Departments table have any corresponding entry in the Employees table. It is possible that we’d have a department with no employees.

Definition: A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous (unrelated) information in them.

Examples: Imagine a table with the fields , , and . This table has many possible superkeys. Three of these are , and . Of those listed, only is a candidate key, as the others contain information not necessary to uniquely identify records. 

Back to main directory:  DBMS

Get Free Programming Tutorials and Solved assignments