Different types of keys in database/dbms
In a database management system (DBMS), a key is a field (or set of fields) that uniquely identifies each record in a table. There are several types of keys that can be used in a DBMS, including:
Primary key
Super key
Candidate key
Foreign key
Alternate key
Composite key
Surrogate key
 |
| Different types of keys in dbms| SKILLS ARENA |
Primary key:
A primary key is a field (or set of fields) that uniquely identifies each record in a table. No two records in the table can have the same primary key value. A primary key can be used to enforce uniqueness and to establish relationships between tables. For example, in a table of employees, the employee ID might be the primary key.
Foreign key:
A foreign key is a field (or set of fields) in one table that refers to the primary key in another table. Foreign keys are used to establish relationships between tables and to enforce mapping constraints. For example, in a database of students and courses, the student table might have a foreign key for course ID that refers to the primary key in the course table.
Composite key:
A composite key is a primary key that is made up of two or more fields. Composite keys can be used when there is no single field that can uniquely identify a record. For example, in a table of orders, the combination of customer ID and order ID might be used as the composite primary key.
Candidate key:
A candidate key is a field (or set of fields) that could be used as the primary key for a table. A table may have multiple candidate keys, but only one of them can be chosen as the primary key.
Alternate key:
An alternate key is a candidate key that is not chosen as the primary key. Alternate keys can be used as secondary means of identifying records in a table.
Surrogate key:
A surrogate key is a unique identifier that is used as the primary key for a table. It has no intrinsic meaning and is typically used when the real-world entity that the table represents does not have a natural primary key. For example, a table of products might use a surrogate key as the primary key, rather than a product ID that is assigned by the manufacturer.
Super key:
In database systems, a superkey is a set of attributes (columns) in a table that can uniquely identify any tuple (row) in the table. In other words, no two tuples in the table can have the same values for the attributes in the superkey. A superkey is a generalization of the concept of a key, which is a minimal superkey.
For example,
consider a table called "Employees" that has the following attributes: EmployeeID, FirstName, LastName, and DateOfBirth. The set of attributes {EmployeeID} is a superkey for this table, because no two employees can have the same EmployeeID. The set of attributes {FirstName, LastName, DateOfBirth} is also a superkey, because no two employees can have the same first and last name and date of birth. However, the set of attributes {FirstName, LastName} is not a superkey, because it is possible for two employees to have the same first and last name.
A key is a minimal superkey, which means that it is a superkey that does not have any proper subset that is also a superkey. For example, in the Employees table, {EmployeeID} is a key, because it is a superkey and there is no proper subset of {EmployeeID} that is also a superkey. On the other hand, {FirstName, LastName, DateOfBirth} is not a key, because it has the proper subset {FirstName, LastName} which is not a superkey.
Here is an example of how these keys might be used in a database:
Consider a database that stores information about students and courses. The database might have two tables:
one for students and one for courses. The student table might include columns for
student ID (primary key), name, and major,
while the course table might include columns for
course ID (primary key), name, and instructor.
To establish a relationship between students and courses (i.e., a student can take multiple courses and a course can have multiple students), a third table called "enrollments" could be created.
This table would have foreign keys for student ID and course ID, as well as additional columns for the enrollment date and any other relevant information.
The combination of student ID and course ID could be used as a composite primary key in the enrollments table.
Alternatively, the enrollments table could have its own primary key (e.g., enrollment ID) and use student ID and course ID as alternate keys.
Various types of keys in DBMS | keys in relational model | what are the different types of keys in dbms|key in SQL | key in dbms
______________________________________
Please share this post and blog link with your friends.For more programs use this blog.
If you have any problem, please comment in comment box, subscribe this blog for notifications of new post on your email and follow this blog.If you have any method of this program or want to give any suggestion send email on hc78326@gmail.com
Created by-- HARSH CHAUHAN
