DBMS Keys

What is Keys?
  • Keys play an important role in the database.
  • Keys are used to uniquely identify any record from the table.
  • Keys allow you to find the relationship between tables.
Different Types of Keys:-
  • PRIMARY KEY:-Primary key is a column or group of columns in a table that uniquely identifies any record in that table. A table cannot have more than one primary key.
Rules for defining Primary key:-
  • The primary key field cannot be null.
  • Every row must have a primary key value.
  • Two rows cant have the same primary key.
  • The values in a primary key column can never be modified or updated if any foreign key refers to that primary key.
Example:- Consider the following Student Table:-

 Student_IdName  Age  Country
 101 Rahul 20 India
 102 Riya 22 London
 103 John 21 Africa
 104 Marry 20 India
 105 Kia 21 London

In this table, "Student_Id" is a primary key that uniquely identifies the record.

  • CANDIDATE KEY:- Candidate key is a set of attributes that uniquely identifies tuples in a table. A candidate key is a super key with no repeated attributes. The primary key should be selected from the candidate keys. A table can have multiple candidate keys but only a single primary key.
In this given table Student  "StuId", "Roll No", "Email" is the candidate keys that uniquely identify the student record in the table. And "StuId" is a primary key.
Student Table:-

 StuId Roll No FirstName LastName Email
 1 1011 Kia Arora kia@gmail.com
 2 1012 Tom Mehra Tom@gmail.com
 3 1013 Marry Malhotra Marry@gmail.com

  • SUPER KEY:-  Super key is a superset of a candidate key. It is a set of an attribute which can uniquely identify a tuple.
How Candidate key is different from the super key?
Candidate keys are selected from the set of super keys, the only thing we should consider while selecting the candidate key is: It should not have any duplicate attribute.

Example:-

 EmpSSN EmpNum EmpName
 9812345684 A101 John
 7776232134 A102 Kia
 1345790745 A103 Tad

In the table above "EmpSSN", "EmpNum" is the super key.

  • ALTERNATE KEY:- In a table, we have multiple candidate keys that uniquely identifies every row of a table. Among these candidate keys,only one key gets selected as the primary key,the remaining keys are known as alternative or secondary keys.
Example:- In the below table "StuId", "RollNo", "Email" are the candidate key but since "StuId" is a primary key, "RollNo", "Email" becomes the alternate key.

STUDENT TABLE:-
 StuId Roll No FirstName LastName Email
 1 1011 Kia Arora kia@gmail.com
 2 1012 Tom Mehra Tom@gmail.com
 3 1013 Marry Malhotra Marry@gmail.com


  • FOREIGN KEY:-   Foreign key creates a relationship between two tables. Foreign keys are the column of a table that points to the primary key of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. Foreign keys maintain the data integrity and allows the navigation between two different tables.
Example:- In the table below the Stu_Id column in Course_Enrollment table is a foreign key as it points to the primary key of the Student table.

Course_Enrollment table:-

 Course_IdStu_Id 
 C101 1011
 C102 1012
 C103 1013
 C104 1014

Student Table:-

 Stu_IdNameAge 
 1011 Ajay 21
 1012 Akash 24
 1013 Kia 20



Comments

Post a Comment

Popular posts from this blog

ASP.NET Overview

ASP.NET CORE:- The ASP.NET Core is a brand new cross-platform, high-performance, light-weight, open-source Framework which is used for developing modern, Internet-connected, cloud-based Web Applications, IoT, and Web APIs which can be developed and run on Windows, Linux, or Mac operating systems. ASP.NET Core Framework is completely rewritten of existing ASP.NET 4.x, with architectural changes which make it more modular, scalable, open-source, high-performance, light-weight, and a cross-platform web framework.    ASP.NET Core benefits and features: Nowadays, the ASP.NET Core the framework becomes more and more popular among the developers for several reasons. Let us discuss the benefits and features provided by this framework in detail. ·      Open Source:- The ASP.NET Core framework is Open Source. The Source Code of this Framework is available at   https://github.com/aspnet  and you are free to download the source code and even if you want ...
Read more

SQL AND ,OR, NOT Operators

Image
SQL AND, OR, NOT  OPERATOR:-  The WHERE clause can be combined with AND, OR, NOT Operators. These operators are used to filter records based on more than one condition. SQL AND OPERATOR:- The AND operator displays a record if all the conditions separated by AND are TRUE. SYNTAX:- SELECT column1 ,column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ... ; EXAMPLE:- Consider the CUSTOMERS table having the following records:- AND Example:- The following SQL statement selects all fields from "CUSTOMERS" where Address is "Delhi" AND Age is "23 ":-  SELECT CustomerID , Name ,Address , Age ,Salary   FROM CUSTOMERS  WHERE Address = 'Delhi' AND Age = 23 ; OUTPUT:- SQL OR OPERATOR:- The SQL OR Operator is used to create a SQL statement where records are returned when any one of the conditions met. Syntax:- SELECT column1 ,column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ... ; EXAMPLE:- The followin...
Read more

SQL Joins

SQL JOINS:-  JOIN means to combine something. In case of SQL join clause is used to combine rows from two or more table, based on a related column between them. IMPORTANCE OF JOIN:- If you want to combine two or more table the SQL JOIN is used. If you want to access more than one table through a SELECT statement. The joining of two or more tables is based on common filed between them. TYPES OF JOIN:- INNER JOIN:- INNER JOIN returns records that have matching values in both the table. LEFT(OUTER) JOIN:- This join returns all records from the left table , and the matched record from the right table. RIGHT(OUTER) JOIN:- This join returns all records from the right table, and the matched records from the left table. FULL(OUTER) JOIN:- This join returns all records when there is s match in either left or the right table.
Read more