Show
SummaryIn this article, you will learn 7 types of keys in DBMS. Take a glimpse below.
Read the full article to learn more in detail. What are the Keys in DBMS?A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). Keys are also used to establish relationships between the different tables and columns of a relational database. Individual values in a key are called key values. Check out our free technology courses to get an edge over the competition. This blog will cover everything you need to know about the keys in DBMS and attribute closure to find the Key of any relation (table). Stick till the end of the article for some critical GATE questions on keys in DBMS. For example, every unique identification number is used to identify candidates in an educational institute. These can also help find all the available details maintained on the server about the candidate, such as their address, passport number, or phone number are keys unique to each candidate. Keys are imperative for analyzing and identifying data types. You can also consider doing our Java Bootcamp course from upGrad. Why are the Keys Required?A key is used in the definitions of various kinds of integrity constraints. A table in a database represents a collection of records or events for a particular relation. Now there can be thousands and thousands of such records, some of which may be duplicated.  Featured Program for you: Fullstack Development Bootcamp There should be a way to identify each record separately and uniquely, i.e. no duplicates. Keys allow us to be free from this hassle. Let us take a real-life example of the database of each student studying in an engineering college. What attribute of the student do you think will uniquely identify each of them? You could refer to a student by using their name, department, year and section. Or, you can mention only the university roll number of the student, and you can get all the other details from that. A key could either be a combination of more than one attribute (or columns) or just a single attribute. The main motive of this is to give each record a unique identity. Also Read: DBMS vs RDBMS Types of Keys in DBMSThere are broadly seven types of keys in DBMS. All these types of keys in SQL must be implemented appropriately for the relevant database to negate redundancy. Correct identification will lead to database accuracy, improving results in a limited time. Let’s explore these DBMS keys to learn more about what are keys in SQL.
Let’s look at each of them separately. 1. Primary KeyIf you’re wondering what is primary key in DBMS, primary key is a column of a table or a set of columns that helps to identify every record present in that table uniquely. There can be only one primary Key in a table. Also, the primary Key cannot have the same values repeating for any row. Every value of the primary key must be different with no repetitions. Amid many details, a primary key is the most significant one to understand what are keys and what is primary key in DBMS. The PRIMARY KEY (PK) constraint put on a column or set of columns will not allow them to have any null values or any duplicates. One table can have only one primary key constraint. Any value in the primary key cannot be changed by any foreign keys (explained below) which refer to it. In-Demand Software Development Skills2. Super KeySuper Key is the set of all the keys which help to identify rows in a table uniquely. This means that all those columns of a table than capable of identifying the other columns of that table uniquely will all be considered super keys. Super Key is the superset of a candidate key (explained below). The Primary Key of a table is picked from the super key set to be made the table’s identity attribute. upGrad’s Exclusive Software and Tech Webinar for you – SAAS Business – What is So Different? https://cdn.upgrad.com/blog/mausmi-ambastha.mp4 3. Candidate Key Candidate keys are those attributes that uniquely identify rows of a table. The Primary Key of a table is selected from one of the candidate keys. So, candidate keys have the same properties as the primary keys explained above. There can be more than one candidate keys in a table. There can be more candidate keys than just one for any table, but they can never be empty. Every candidate key carries unique information and value. Besides these characteristics, a combination of attributes also works as a set of candidate keys. Explore Our Software Development Free Courses4. Alternate KeyAs stated above, a table can have multiple choices for a primary key; however, it can choose only one. So, all the keys which did not become the primary Key are called alternate keys. 5. Foreign KeyForeign Key is used to establish relationships between two tables. A foreign key will require each value in a column or set of columns to match the Primary Key of the referential table. Foreign keys help to maintain data and referential integrity. Foreign keys are essential for maintaining a difference between two entities that might be linked with the same information but do not share similar information. In such cases, the tables are linked to maintaining a connection but do not entirely work as a replacement for each other. For instance, any individual working for the marketing department might have marketing department information in its employee table, but that does not mean the table can be similar to the department table held by the marketing department. Explore our Popular Software Engineering Courses6. Composite KeyA composite Key is a set of two or more attributes that help identify each tuple in a table uniquely. The attributes in the set may not be unique when considered separately. However, when taken all together, they will ensure uniqueness. The ‘concatenated key’ is another name for a composite key. 7. Unique KeyUnique Key is a column or set of columns that uniquely identify each record in a table. All values will have to be unique in this Key. A unique Key differs from a primary key because it can have only one null value, whereas a primary Key cannot have any null values. Among the seven types of keys available in the DBMS, a few other types of keys in SQL are also accessible. The key type is called Artificial Keys. An artificial has no relevance or meaning to the business but is often used to tackle conflicting data management situations. For example, if there is no attribute that comprises all required primary key properties or if the primary keys are complex. Functional DependenciesNow that we know a different kind of keys in DBMS, let’s see how to identify them when given a table from a database. For this, we use the concept of functional dependencies. A functional dependency (FD) is a constraint between two sets of attributes. This constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X], then they have t1[Y] = t2[Y]. This means the value of the X component of a tuple uniquely determines the value of component Y. FD is denoted as X ? Y (this is read as “Y is functionally dependent on X”). The left side is called the determinant, and the right side is called the dependent. Closure of a set of AttributesA closure is a set of all possible FDs derived from a given set of FDs. It is also referred to as a complete set of FDs. If F is used to donate the set of FDs for relation R, then the closure of a set of FDs implied by F is denoted by F+. We will now define the closure of a set of attributes concerning a given set of FDs. It will help identify the super Key of the relationship and find whether an FD can be inferred from a given set of FDs or an FD is redundant. After finding a set of functional dependencies on a relation, the next step is to find the Super Key for that relation (table). Then we find out the set of attributes’ closure to decide whether an attribute (or set of attributes) of any table is a key for that table or not. The set of attributes that are functionally dependent on the attribute X is called Attribute Closure of X, and it can be represented as X+. Below are some rules needed to determine F+:
How to find Candidate Keys and Super Keys using Attribute Closure?
Also visit upGrad’s Degree Counselling page for all undergraduate and postgraduate programs. Let’s discuss a few previously asked GATE questions to see the applications of attribute closure. GATE 2014Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} ? {G}, {F} ? {I, J}, {E, H} ? {K, L}, K ? {M}, L ? {N} on R. What is the key for R? (A) {E, F} (B) {E, F, H} (C) {E, F, H, K, L} (D) {E} Approach: We will check the attribute closure of all the options provided. The set whose closure will give us the entire relation R will be the correct answer. A: {E, F} + = {EFGIJ} ≠ R B: {E, F, H} + = {EFGHIJKLMN} = R C: {E, F, H, K, L} + = {EFGHIJKLMN} = R D: {E} + = {E} ≠ R Both options B and C give us the entire relation scheme. However, we choose the minimal option to be the correct answer because a Candidate Key should be the minimal Super Key. Answer: B GATE 2013Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH ? G, A ? BC, B ? CFH, E ? A, F ? EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys does the relation R have? (A) 3 (B) 4 (C) 5 (D) 6 Approach: We will take the LHS of each functional dependency given in the question and find their attribute closures. CH+ = G A+ = ABCEFGH B+ = ABCEFGH E+ = ABCEFGH F+ = ABCEFGH So we see that closures of A, B, E, F have the entire relationship except for attribute D. So there is a total of 4 candidate keys AD, BD, ED and FD. Answer: B Learn Software Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career. Read our Popular Articles related to Software DevelopmentConclusionKeys and functional dependencies play a very vital role in designing a database. These concepts also help to find the difference between good and bad database design. The final process of removing redundancies and making the database efficient is normalization, which uses all concepts mentioned in this article. Keys’ characteristics of establishing integrity and identifying the relationship between two tables are their most vital feature, allowing users to edit data with precision while maintaining uniqueness. From alternate keys to composite, the goal is to separate the identity of each record through unique keys defining separate qualities. If you’re interested to learn more about full-stack development, check out upGrad & IIIT-B’s Executive PG Program in Full-stack Software Development, which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms. What are the different types of keys in DBMS?There are several types of keys used in a database. The primary key is used to identify a specific row in a table. The unique key is used to ensure that there is only one entry in a specific table. A foreign key is used to link entries in one table to another. A composite key is a collection of several columns in a table that all together are used to identify a row. These keys help you to identify a particular column of a row of a table accurately and uniquely. Hence, it is very important to use the accurate columns as a key as per your use case. What is a foreign key in DBMS?Foreign key is a column or a set of columns that's used to link the rows of one table to the rows of another table. It's used to prevent data inconsistency. In DBMS, foreign key is implemented using a single column or a set of columns (logical column) and the DBMS checks that the values in these columns match the values in the referenced columns (primary key). For example, let's say table A and table B both have a column called column A. You can say that column A in table A is referential to column A in table B. This can be imagined as an arrow with a foreign key value pointing to the target table. What is a composite key in DBMS?The composite key is a superkey. It is a combination of one or more columns. It allows a single key to be constructed from a combination of different columns. It is also called composite identifier, composite key, or compound key. In short, you can use a set of columns to identify a unique row in your table, in case you don’t a single column that can identify a row uniquely. In such a case, the set of all the columns that you will use will be considered as a composite key. Hence, it can be rightfully said that of a primary key has more than one column, it is renamed as a composite key. Want to share this article? Become a Full Stack DeveloperWhich key is used for data retrieval purpose?Secondary key: An attribute (or combination of attributes) used strictly for data retrieval purposes.
What are key attributes in database?Key attribute or primary attribute: is an ID, key, letter or number that uniquely identifies that item. For example, it can be the number of a certain invoice (e.g. the individual ID of that invoice). A table that contains a single key attribute is considered a strong entity.
Which is the combination of two or more attributes used as a primary key?Primary key and candidate key both are the combination of one or more column values in a table that make a row of data unique within the table. Hence candidate is a set of attributes which uniquely identify the rows or tuples in a table is correct since it also covers primary key.
What do you call an attribute or a combination of attributes that uniquely identifies any given row?A key is an attribute or set of attributes which helps us in uniquely identifying the rows of a table. It also helps in establishing relationship among tables.
|
Which key is used as an attribute or combination of attributes for data retrieval purpose?
zusammenhängende Posts
Urheberrechte © © 2024 paraquee Inc.
