Download to read offline
Uploaded bySurkhab Shelly
Types of keys dbms
The document discusses different types of keys used in database management systems. It defines primary key as a minimal set of attributes that uniquely identify each row in a table. It also discusses candidate keys, alternate keys, super keys, composite keys, and foreign keys. Candidate keys are attributes that qualify for uniqueness. Alternate keys are other candidate keys besides the primary key. Super keys are attributes that include the primary key attributes. Composite keys are formed using multiple attributes when no single attribute uniquely identifies rows. Foreign keys link tables and enforce referential integrity.
In this document
Powered by AI
An overview of the presentation on the types of keys in Database Management System by Mrs. Surkhab Shelly.
Keys are attributes that identify records in a table, allowing efficient data access and defining relationships between tables.
Lists the various types of keys in databases: Primary, Candidate, Alternate, Super, Composite, Foreign, and Unique Keys.
The primary key uniquely identifies each record, cannot be NULL, and there can only be one primary key per table.
Candidate keys are individual columns that qualify for unique identification of rows; can serve as Primary Keys.
Alternate keys are candidate columns other than the primary key, for example, SSN when EmployeeID is the primary key.
A super key is a superset of a key formed by adding attributes to a Primary Key to ensure unique identification.
Composite keys combine two or more columns to create a unique identifier when a single column is insufficient.
Foreign keys link tables, serving as primary keys from another table, enforcing referential integrity and relationships.
A unique key is similar to a primary key, allowing one NULL value; it maintains unique row identification.
Describes a relation with attributes A, B, C, D, E and outlines how to identify Primary, Candidate, Super, and Composite keys.
Answers provided for the practical example: Primary Key A, Candidate Keys A & BC, Super Keys A, BC, AE, AD & ABC, Composite Key BC.
More Related Content
Similar to Types of keys dbms
Types of keys dbms
- 1. Types of Keysin Database Management System Presented By :- Mrs. Surkhab Shelly
- 2. Why we haveKeys in DB? • A Key is an attribute or a set of attributes in a relation that identifies a tuple (record) in a relation. • The keys are defined in a table to access or sequence the stored data quickly and smoothly. • They are also used to create relationship between different tables.
- 3. Types of Keysin Database • 1. Primary Key • 2. Candidate Key • 3. Alternate Key • 4. Super Key • 5. Composite Key • 6. Foreign Key • 7. Unique Key
- 4. Primary Key Employee EmployeeID EmployeeName SSN DeptID DOB • Whichis Unique & Can’t be have NULL Value • The minimal set of attributes (or attribute) which can uniquely identify the rows (tuples) in a table is known as a primary key.Key Points about Primary Key • Primary key is use to identify each row uniquely in a table. • Primary key cannot have a NULL value. • Each table can have only one primary key.
- 5. Candidate Key Employee EmployeeID EmployeeName SSN DeptID DOB • Areindividual columns in a table that qualifies for uniqueness of each row/tuple. • A candidate key is an attribute or set of an attribute which can uniquely identify a tuple. • Here in Employee table EmployeeID& SSN areeligible for a Primary Key and thus are Candidate keys.
- 6. Dr. Kamal Gulati Alternate Key Employee EmployeeID EmployeeName SSN DeptID DOB •Candidate column other the Primary column, like if EmployeeID is set for a PK then SSN would be the Alternate key.
- 7. Super Key Employee EmployeeID EmployeeNam e SSN DeptID DOB • Ifyou add any other Column / Attribute to a Primary Key then it become a Super Key, like EmployeeID + EmployeeName is a Super Key. • Super key stands for superset of a key. • The name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key. The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-NAME), etc. • A super key is a set of one of more columns (attributes) to uniquely identify rows in a table.
- 8. Dr. Kamal Gulati Composite Key Employee EmployeeID EmployeeName SSN DeptID DOB •If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique. • Like if there is no EmployeeID or SSN columns, then you can make EmployeeName + DateOfBirth (DOB) as Composite Primary Key. But still there can be a narrow chance of duplicate rows.
- 9. Foreign Key Employee EmployeeID EmployeeName SSN DeptID DOB Department DeptID DeptName •A foreignkey is a field in table which is generally a primary key from another table. •In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B. •A foreign key is those keys which are used to define a relationship between two or more tables. •When we want to implement a relationship between two tables then we use the concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table
- 11. Unique Key • Uniquekey is same as primary with the difference being the existence of null. • Unique key field allows one value as NULL value. Employee EmployeeID EmployeeName SSN EmailID DOB
- 12. Practical Example • • TableR1. Let A,B,C,D,E are the attributes of this relation. A→BCDE (This means the attribute 'A' uniquely determines the other attributes B,C,D,E.) BC→ADE (This means the attributes 'BC' jointly determines all the other attributes A,D,E in the relation.) • Find the following: – Primary Key – Candidate Key – Super Key – Composite Key Table – R1 A B C D E
- 13. Dr. Kamal Gulati Answers: •Primary Key:A •Candidate Key: A & BC •Super Key:A, BC, AE, AD & ABC •Composite Key: BC