Key
Download as PPTX, PDF
1 like915 views
This document defines and describes different types of keys used in relational databases, including super keys, candidate keys, primary keys, and foreign keys. It provides examples to illustrate each type of key. Super keys are fields that uniquely identify records, candidate keys are minimal super keys, primary keys are chosen candidate keys, and foreign keys link two tables based on matching primary keys. The document explains the properties and purposes of each key type for structuring database tables and enforcing integrity.
Key
- 1. Key DBMS Prepared By: Bikash Sapkota Deerwalk Ins. Of Technology Kathmandu, Nepal
- 2. Introduction Keys(Name clearly define) A key part of a relational database and a vital part of the structure of a table. They ensure each record within a table can be uniquely identified by one or a combination of fields within the table. They help enforce integrity and help identify the relationship between tables.
- 3. Keys Super Key Candidate Key Primary Key
- 4. Super Key A Super key is any combination of fields within a table that uniquely identifies each record within that table.
- 5. Book Book ID Name Author B1 Xyz A1 B2 ABC A1 B3 XYZ A2 B4 PQR A3 B5 RST A1 B6 ABC A3
- 6. Candidate Key Candidate key is a subset of super key A candidate key is a single field or the least combination of fields that uniquely identifies each record in table The least combination of fields distinguishes a candidate key from a super key. Every table must have at least one candidate Properties Unique Not null Contains minimum number of fields to ensure uniqueness Must uniquely identify each record in the table
- 7. Book ID Name Author B1 XYZ A! B2 ABC A1 B3 XYZ A2 B4 PQR A3 B5 RST A1 B6 ABC A3 Name BookId Author Name Author
- 8. Primary Key It is a candidate key Uniquely identify a specific instance of an entity Primary key cannot contain any Null value because we cannot uniquely identify multiple Null values. Properties Stable Minimal Definitive Accessible
- 9. Book ID Name Author B1 Xyz A! B2 ABC A1 B3 XYZ A2 B4 PQR A3 B5 RST A1 B6 ABC A3 Book ID Name Author
- 10. CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ); CREATE TABLE Person ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) );
- 11. Foreign Key Foreign key is a field in a relational table that matches the primary key column of another table. It can be used to link two tables together
- 12. CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) ); CREATE TABLE products ( product_id numeric(10) not null, supplier_id numeric(10) not null, CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) );
- 13. Remaining Alternate Key Remaining candidate key which are not selected as primary key Composite Key Primary key made up of more than one attribute
Editor's Notes
- There are three main types of keys, candidate keys, primary keys and foreign keys. There is also an alternative key or secondary key that can be used, as the name suggests, as a secondary or alternative key to the primary key
- If 3 are super key then name and author are redundant key
- Cannot be reduced further