[go: up one dir, main page]
Scribd
Upload
157 views14 pages

Keys in DBMS

BC is a composite candidate key since the attributes B and C jointly determine uniqueness. A is the primary key since it is the minimal candidate key chosen by the designer. A, BC, AE, AD and ABC are all super keys since they determine uniqueness.

Uploaded by

Krishna
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
157 views14 pages

Keys in DBMS

BC is a composite candidate key since the attributes B and C jointly determine uniqueness. A is the primary key since it is the minimal candidate key chosen by the designer. A, BC, AE, AD and ABC are all super keys since they determine uniqueness.

Uploaded by

Krishna
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 14

Keys in Database

Management System
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.
Types of Keys in Database

• 1. Primary Key
• 2. Candidate Key
• 3. Alternate Key
• 4. Super Key
• 5. Composite Key
• 6. Foreign Key
• 7. Unique Key
Primary Key
• Which is Unique & Can’t be have NULL
Employee
Value
EmployeeID • Is the column you choose to maintain
EmployeeName uniqueness in a table at row level.
SSN • Here in Employee table we can choose
either EmployeeID or SSN
DeptID
column for a PK.
DOB
• EmployeeID is preferable choice because
SSN is a secure (PII) value.
Primary Key
• It is a candidate key that is chosen by the
database designer to identify entities with in
an entity set.
• 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.
To define a field as primary key,
following conditions had to be met :
• 1. No two rows can have the same primary key
value.
• 2. Every row must have a primary key value.
• 3. The primary key field cannot be null.
• 4. Value in a primary key column can never be
modified or updated, if any foreign key refers
to that primary key.
Candidate Key
• Are individual columns in a table that
Employee
qualifies for uniqueness of each
EmployeeID row/tuple.
EmployeeName
• Here in Employee table EmployeeID
& SSN are eligible for a
SSN Primary Key and thus are
DeptID Candidate keys.
DOB
• Candidate Keys are super keys for
which no proper subset is a super key.
In other words candidate keys are
minimal super keys.
Alternate Key
Employee
EmployeeID
• Candidate column other the
EmployeeName Primary column,
SSN like if EmployeeID is set for a PK
DeptID then SSN would be the Alternate
DOB
key.
Super Key
• If you add any other Column / Attribute to a
Employee
Primary Key then it become a Super Key,
EmployeeID like EmployeeID + EmployeeName is a
Super Key.
EmployeeName • Super key stands for superset of a key.
SSN • A Super Key is a set of one or more
attributes that are taken collectively and
DeptID can identify all other attributes uniquely.

DOB
Composite Key
Employee • If a table do have a single column that
qualifies for a Candidate key, then you
EmployeeID have to select 2 or more columns to make
a row unique.
EmployeeName • Like if there is no EmployeeID or SSN
columns, then you can make
SSN
EmployeeName +
DateOfBirth (DOB) as Composite Primary
DeptID
Key.
DOB But still there can be a narrow chance of
duplicate rows.
Foreign Key
Employee
EmployeeID Department

EmployeeName DeptID
SSN DeptName

DeptID
DOB

• Here in above 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.
Unique Key
Employee

EmployeeID
• Unique key is same as
primary with the difference
EmployeeName
being the existence of null.
SSN
• Unique key field allows one
EmailID
value as NULL value.
DOB
Practical Example
Table – R1 • Table R1. Let A,B,C,D,E are the attributes
A of this relation.
B • A→BCDE (This means the attribute 'A'
uniquely determines the other attributes
C
B,C,D,E.)
D BC→ADE (This means the attributes 'BC'
E jointly determines all the other attributes
A,D,E in the relation.)
• Find the following:
– Primary Key
– Candidate Key
– Super Key
– Composite Key
Answers:
•Primary Key: A
•Candidate Key: A & BC
•Super Key: A, BC, AE, AD &
ABC
•Composite Key: BC

You might also like