際際滷

際際滷Share a Scribd company logo

Key-Database Management

Download as PPTX, PDF
Bikash Sapkota
Bikash Sapkota

Keys such as primary keys and foreign keys are vital parts of relational database structure that ensure each record is uniquely identifiable. A primary key uniquely identifies each record in a table and cannot contain null values. A foreign key is a field that matches a primary key in another table and is used to link two tables together. Different types of keys like super keys, candidate keys, and alternate keys were also discussed in the document.

1 of 13
Key DBMS
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.
Keys Super Key Candidate Key Primary Key
Super Key A Super key is any combination of fields within a table that uniquely identifies each record within that table.
Book Book ID Name Author B1 Xyz A1 B2 ABC A1 B3 XYZ A2 B4 PQR A3 B5 RST A1 B6 ABC A3
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
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
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
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
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) );
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
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) );
Remaining Alternate Key Remaining candidate key which are not selected as primary key Composite Key Primary key made up of more than one attribute

More Related Content

Key-Database Management

  • 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

  1. 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
  2. If 3 are super key then name and author are redundant key
  3. Cannot be reduced further