Concept of Keys in DBMS - Super, Primary, Candidate, Foreign Key, etc

hello friends and welcome back to steady tonight's YouTube channel in this video we will talk about DBMS keys students are always confused about this concept as there are many different kinds of DBMS keys and almost all are related to each other with a slight difference which makes it very difficult at times to get clear understand in this video we will learn what our database gives why we need these and the different types of the DBMS keys a database key or a DBMS key is an attribute or a set of attributes which help you uniquely identify any record or row of data in a table let's take an example in the table above we have s ID s name s branch in s email as attributes saving details about students if I ask you to tell me email address of the student with name Adam can you tell me no because there are two students with named Adam or if I ask you tell me name of student who is in CS branch again there will be many students in that branch but if you look closely s ID column has unique value for all the rows of data and the S email column also has unique email addresses and these two columns can be used to identify the course in the above table so we can say that s ID and s email are keys for this relation this brings us to the question why we need keys the first reason is pretty much obvious without a DBMS key you cannot access the rows of data stored in the table uniquely because without the key most of the time you will either receive multiple rows of data or empirical rules of data another big reason which is kind of an extension of the first reason is we can force identity of data and ensure integrity of data is maintained using TPMS keys by making an attribute a key and making it mandatory we can enforce that anything inserted in the table is identifiable and ensuring data integrity - and last but not the least we can establish a relationship between tables using keys it's time for the main event types of DBMS keys there are eight different types of DBMS keys of which super key is like the default key and composite and compound keys are like cousins we'll see when we learn about them so we will be starting with super key then we will move on to candidate key then primary and alternate keys then the foreign key which is different from all others and serves a purpose which none of the other keys too and then we will cover the remaining keys which are composite compound and surrogate superkey is nothing but a key it is a super set of keys in which all possible keys are included like in our example s ID registration ID email then combination of them s ID registration ID registration ID and email email and s ID and then all three together SI de registration ID email all our super keys there can be more as well in simpler words an attribute or a set of attributes that can be used to identify records in a table it is a super key then comes candidate key it is the minimal set of super key which can be used to uniquely identify those of data in a table we saw that we have so many options for super keys but most of them are formed by unnecessary pairing of keys like email ids ID registration ID together as a key or email s ID as a key when email s ID and registration ID can individually uniquely identify row of data hence in this case the three attributes s ID email and registration ID or our eg underscore ID are the minimal set of super key hence the candidate keys speaking books language if any proper subset of a super key is a super key then that cannot be a candidate key now let us try to understand this registration ID email and s ID together form a super key while email and s ID is a proper subset of it and is also a super key hence this is not a candidate key similarly email s ID is a super key and its proper subset email and s ID both can act as a super key individually hence this is not a candidate key this way we can come to a conclusive proof that s ID email and registration ID are the candidate keys for the relationship that we have now moving on to primary and alternate gives a primary key is a candidate key which is used to uniquely identify every row of data in a table now you must be thinking that we have three candidate keys and all of them can do so well in that case you can make anyone a primary key this is generally done by database administrator to set a candidate key as a primary key if we have to create a simple user interface to search for Student Information for our college using the table in the database that we have as an example what will make most si D which is an auto incremented value by the database itself or registration ID which every student knows and is more meaningful for college administration to hence in this example we can make our eg underscore ID or registration ID as primary key all the remaining candidate keys which are not selected as a primary key are called alternate keys now coming to foreign keys foreign key is nothing but an attribute in a table which is used to create a relationship of that table with some other table in case our tables have relationship using foreign keys helps us to maintain data integrity for the relationship for this we will have to involve another table in our example let's say we have one more table branch with information about the branches in the colleges it has a unique column with branch code branch name actually and some more trans related information in the students table the branch code can be made a foreign key to set up referential integrity or relationship between the table student and branch this way while making a new student entry a wrong ranch name cannot be provided as a TV will return error similarly if someone deletes a branch entry or updates a branch code in branch table they will have to change the same in students table too generally this is not allowed by the database and the database returns error when someone tries to modify the column value which is used as a foreign key in some other table hence this way we can force integrity of data in a relationship using foreign keys moving on to the remaining keys a composite key has multiple attributes hence all the super keys with more than one attribute are also composite key in our case email s ID together s ID registration ID together registration ID email together all our composite kids if a composite key one attribute which is foreign key then it is called a compound key moving on to the surrogate key sometimes a relation or a table has no attribute which can uniquely identify the rows in the table in this situation when there is no natural primary key we create an attribute to act as a primary key that is a surrogate key these keys do not add any meaning to the data but serves the sole purpose of identifying the data with this we have covered all the DBMS keys starting from super key candidate key then primary key alternate key foreign key composite and compound key and surrogate key I hope after watching this video you have a better understanding of what DBMS Keys are why they are required and the various different types of PBMs keys if you like this video give it a thumbs up share it with your friends and do not forget to subscribe to the state roads YouTube channel

Channel: Studytonight

Views: 210,301

Rating: 4.9076867 out of 5

Keywords: DBMS, Database Management System, DBMS Key, What is DBMS Key, Types of DBMS Keys, What is Super Key, What is Primary Key, Candidate Key, DBMS Foreign Key, DBMS Primary Key, Foreign Key relations, what is foreign key, candidate key in dbms, primary key in dbms, foreign key in dbms, composite key in dbms, super key in dbms

Id: p3yJZH8_bsc

Length: 9min 15sec (555 seconds)

Published: Fri Nov 01 2019