Keys_in_DBMS_VALID_INFORMATION_IS_AVAILA

All about keys in dbms

1. “ TRIBHUVAN UNIVERSITY” DEPARTMENT OF PUBLIC ADMINISTRATION PUBLIC ADMINISTRATION CAMPUS PRESENTATION DIFFERENT TYPES OF KEYS IN DATABASE SUBMITED BY: PADAM NEPAL 029/074 6 TH SEM

2. TABLE OF CONTENT KEYS TYPES OF KEYS SUPER KEY: EXAMPLE CANDIDATE KEY: PROPERTIES/DIFFERENCE PRIMARY KEY: EXAMPLE/RULES ALTERNATE KEY UNIQUE KEY COMPOSITE KEY FOREIGN KEY NATURAL KEY SURROGATE KEY CONCLUSION

3. KEYS KEYS in DBMS  is an attribute or set of attributes which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table.  Key is also helpful for finding unique record or row from the table.  It is used to fetch or retrieve records / data-rows from data table according to the condition/requirement. STU ID NAME SUBJECT 29/074 PADAM NEPAL COMPUTER 10/074 JANAK BHANDARI COMPUTER

4. TYPES OF KEYS

5. Super key in DBMS  A super key is a set of one or more attributes (columns), which can uniquely identify a row in a table . A Super key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set. A super key is a combine form of Primary Key, Alternate key and Unique key and Primary Key, Unique Key and Alternate Key are subset of super key . A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row. A super key can have a single column.

6. example Example: Super Keys in college_Info Table. university_Id college_Name college_Code { university_Id , college_Code } { college_Name , college_Code } Super Keys in Student_Information Table : Student_Id College_Id stu_Roll_No { Student_Id, Student_Name} { College_Id, Branch_Id } { stu_Roll_No , Session }

7. Candidate Key A Candidate key is an attribute or set of attributes that uniquely identifies a record. Among the set of candidate, one candidate key is chosen as Primary Key. So a table can have multiple candidate key but each table can have maximum one primary key . a candidate key is a minimal super key with no redundant attributes.  Example: Possible Candidate Keys in college_Info table. university_Id college _Name college _Code Possible Candidate keys in Student_Information table. Student_Id College_Id s tu_Rollno

8. Properties of Candidate key It must contain unique values Candidate key may have multiple attributes Must not contain null values It should contain minimum fields to ensure uniqueness Uniquely identify each record in a table

9. How candidate key is different from super key ? Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is : It should not have any redundant attribute . That’s the reason they are also termed as minimal super key . all the candidate keys are super keys. This is because the candidate keys are chosen out of the super keys.

10. Primary Key A Primary key uniquely identifies each record in a table and must never be the same for the 2 records. Primary key is a set of one or more fields ( columns) of a table that uniquely identify a record in database table . A table can have only one primary key and one candidate key can select as a primary key. The primary key should be chosen such that its attributes are never or rarely changed, for example, we can’t select Student_Id field as a primary key because in some case Student_Id of student may be changed

11. Primary key examples Primary Key in college_Info table: university_Id Primary Key in Student_Information Table: College_Id

12. Rules for defining Primary key: Two rows can't have the same primary key value It must for every row to have a primary key value. The primary key field cannot be null. The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

13. What is the Alternate key?  A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key . Alternate keys are candidate keys that are not selected as primary key. Alternate key can also work as a primary key. Alternate key is also called “ Secondary Key ”. Example: Alternate Key in college_Info table: college_Name college_Code Alternate Key in Student_Information table: Student_Id stu_Roll_No

14. Unique Key: A unique key is a set of one or more attribute that can be used to uniquely identify the records in table. Unique key is similar to primary key but unique key field can contain a “ Null ” value but primary key doesn’t allow “ Null ” value. Other difference is that primary key field contain a clustered index and unique field contain a non-clustered index. Example: Possible Unique Key in college_Info table. college_Name Possible Unique Key in Student_Information table: stu_Roll_No

15. Composite Key : Composite key is a combination of more than one attributes that can be used to uniquely identity each record. It is also known as “Compound” key. A composite key may be a candidate or primary key. Example: Composite Key in college_Info table. { Branch_Name , Branch_Code } Composite Key in Student_Information table: { Student_Id, Student_Name }

16. Foreign Keys: Foreign key is used to generate the relationship between the tables. Foreign Key is a field in database table that is Primary key in another table. A foreign key can accept null and duplicate value . The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.  Foreign keys are the columns of a table that points to the  primary key  of another table. Example: university_Id is a Foreign Key in Student_Information table that primary key exist in college_Info(university_Id ) table.

17. Cont. Practically, the foreign key has nothing to do with the primary key tag of another table, if it points to a unique column (not necessarily a primary key) of another table then too, it would be a foreign key. So, a correct definition of foreign key would be: Foreign keys are the columns of a table that points to the  candidate key  of another table.

18. Natural Keys: A natural key is a key composed of columns that actually have a logical relationship to other columns within a table. For example, if we use Student_Id, Student_Name and Father_Name columns to form a key then it would be “Natural Key” because there is definitely a relationship between these columns and other columns that exist in table. Natural keys are often called “Business Key ” or “Domain Key”.

19. Surrogate Key : Surrogate key is an artificial key that is used to uniquely identify the record in table. For example, in SQL Server or Sybase database system contain an artificial key that is known as “ Identity ”. Surrogate keys are just simple sequential number. Surrogate keys are only used to act as a primary key.

20. Conclusion Database generally only contain Primary Key, Foreign Key, Unique Key and Surrogate key and other remaining keys are just concept. A table must have a unique key. According to Dr. E. F. Codd ‘s third rule  “Every single data element (value) is guaranteed to be accessible logically with a combination of table-name, primary-key (row value), and attribute-name (column value)” . So each table must have keys , because use of keys make data highly reliable and provide several types of content like unique data and null values.