1. Primary Key
2. Composite Key
3. Candidate Key
4. Super Key
5. Foreign Key
Primary Key:
- Primary key is the one which uniquely identifies the records in a table.
EMP ID
|
EMP NAME
|
SALARY
|
1
|
JOHN
|
5000
|
2
|
JACKSON
|
6000
|
3
|
JOHN
|
12000
|
- Here “EMP ID” is the PRIMARY KEY.
Composite Key:
- Composite Key is the collection of columns which uniquely identifies the records.
STUDENT ID
|
COURSE ID
|
STUDENT NAME
|
COURSE NAME
|
DATE OF COMPLETION
|
11AD12001
|
101
|
SAMRAT
|
COLD FUSION
|
15/01/2011
|
11AD12002
|
102
|
HARISH
|
JAVA
|
16/07/2011
|
11AD12001
|
102
|
SAMRAT
|
JAVA
|
16/07/2011
|
- Here Using Student Id / Course Id, we can’t retrieve a record. Hence to get unique data, we need the combination of Student Id & Course Id.
- Here (Student Id & Course Id) combined is the COMPOSITE KEY.
Candidate Key:
- Candidate Key is the collection of columns which are eligible for PRIMARY KEY.
VEHICLE NO
|
VEHICLE NAME
|
ENGINE NO
|
PRICE
|
MODEL
|
AP09AZ1001
|
PULSAR
|
1234-234-567
|
65000
|
150CC
|
AP12BR0007
|
CBZ
|
1023-433-876
|
67000
|
LZ
|
AP22RZ9999
|
Royal Enfield
|
4444-444-444
|
150000
|
Thunderbird
|
- Here Vehicle No, Engine No, are eligible for PRIMARY KEY.
- Hence Vehicle No, Engine No are CANDIDATE KEY.
- If Vehicle No is PRIMARY KEY, then Engine no is ALTERNATE KEY.
Super Key:
- Different Set of attributes which uniquely identifies a record in a relation.
DEPT NO
|
DEPT NAME
|
LOCATION
|
101
|
CSE
|
HYDERABAD
|
102
|
ECE
|
HYDERABAD
|
103
|
IT
|
HYDERABAD
|
- Here SUPER KEYS are :
DEPT NO + DEPT NAME + LOCATION = SUPER KEY 1
DEPT NO + DEPT NAME = SUPER KEY 2
DEPT NO + LOCATION = SUPER KEY 3
DEPT NAME + LOCATION = SUPER KEY 4
DEPT NO = SUPER KEY 5
DEPT NAME = SUPER KEY 6
-
-
- CANDIDATE KEY = MIN ( SUPER KEY ) { DEPT NO, DEPT NAME }
- If DEPT NO is PRIMARY KEY, Then DEPT NAME is ALTERNATE KEY.
FOREIGN KEY:
- It is used to establish the relationship between 2 relations (table).
EMP NO
|
EMP NAME
|
SALARY
|
DEPT NO
|
1
|
John
|
30000
|
101
|
2
|
Sam
|
25000
|
102
|
3
|
James
|
33000
|
101
|
DEPT NO
|
DEPT NAME
|
LOCATION
|
101
|
SALES
|
HYDERABAD
|
102
|
SUPPORT
|
HYDERABAD
|
- Here Foreign Key is “DEPT NO”.
- If the relationship is 1 to MANY, FOREIGN KEY must be added to many side tables.
- If the relationship is 1 to 1, FOREIGN KEY can be added to any table.
- If the relationship is Many to Many, FOREIGN KEY cannot be added to any of the tables.
- Hence RDBMS doesn’t support MANY to MANY relationships.
- In RDBMS, MANY to MANY can be split into one to many relations.
Supplier No
|
Supplier Name
|
101
|
John
|
102
|
Jackson
|
Customer No
|
Customer Name
|
301
|
James
|
302
|
Bill
|
Supplier No
|
Customer No
|
101
|
301
|
102
|
301
|
102
|
302
|
101
|
302
|
No comments:
Post a Comment