Page 1 :
Types of Keys in, Database Management System
Page 2 :
Why we have Keys in DB?, • A Key is an attribute or a set of attributes in a relation that identifies a tuple, (record) in a relation., , • The keys are defined in a table to access or sequence the stored data quickly, and smoothly., , • They are also used to create relationship between different tables.
Page 3 :
Types of Keys in Database, 1., 2., 3., 4., 5., 6., 7., , Primary Key, Candidate Key, Alternate Key, Super Key, Composite Key, Foreign Key, Unique Key
Page 4 :
Primary Key, • Which is Unique & Can’ t be have NULL Value, • Is the column you choose to maintain uniqueness in a table at, row level., , • Here in Employee table we can choose either E mployeeID or SSN column for a PK., • EmployeeID is preferable choice because SSN is a secure (PII) value., , • Primary key is the minimal super keys. In the ER diagram primary key is, represented by underlining the primary key attribute., , • Ideally a primary key is composed of only a single attribute., • But it is possible to have a primary key composed of more than one attribute.
Page 5 :
To define a field as primary key,, following conditions had to be met :, •, •, •, •, , No two rows can have the same primary key value., Every row must have a primary key value., The primary key field cannot be null., Value in a primary key column can never be modified or updated, if any, foreign key refers to that primary key
Page 6 :
Candidate Key, • Are individual columns in a table that qualifies for, uniqueness of each row/tuple., , • Here in Employee table EmployeeID & SSN are eligible, for a Primary Key and thus are Candidate keys., , • Candidate Keys are super keys for which no proper subset, is a super key. In other words candidate keys are, minimal super keys.
Page 7 :
Alternate Key, • Candidate column other the Primary column, like if, EmployeeID is set for a PK then SSN would be the, Alternate key.
Page 8 :
Super Key, • If you add any other Column /Attribute to a Primary Key, then it become a Super Key, like EmployeeID, + EmployeeName is a Super Key., , • Super key stands for superset of a key., • A Super Key is a set of one or more attributes that are, taken collectively and can identify all other attributes, uniquely.
Page 9 :
Composite Key, • If a table do have a single column that qualifies for a Candidate, key, then you have to select 2 or more columns to make a row, unique., , • Like if there is no EmployeeID or SSN columns, then you can, make EmployeeName + DateO fBirth (DOB) as Composite, Primary Key. But still there can be a narrow chance of duplicate rows.
Page 10 :
Foreign Key, • Here in below tables DeptID of Department table is Primary Key where as, DeptID of Employee is an Foreign key., , • It means it has referred to another table. This concept is also know as, Referential Integrity.
Page 11 :
Unique Key, • Unique key is same as primary with the difference, being the existence of null., , • Unique key field allows one value as NULL value.
