Consider the database table of student (table be called as Relation)
Sid Sname Saddr Phone course
1 ram vja 98888 java
2 sam hyd 89000 sql
The above table can be represented by ER model as
Fig: ER Diagram
In the above diagram
Student is the Entity
And it has the attributes
{Sid, sname , course , phno , saddr}
Entity : An entity is a business object that represents a group, or category of data
Attributes: set of values or properties that give information about the entity
Before going to Candidate key
First of all we need to know about the superkey
Super key:
super key is a set of attributes of a relation variable for which it holds that in all relations
assigned to that variable,there are no two distinct tuples (rows) that have the
same values forthe attributes in this set
i.e in more non technical terms it is nothing but a set of attributes which uniquely identifies the relation
example in our table student
{ Sid}
{sid,sname}
{sid,sname,saddr}
{sid,sname,sddr,phno}
{sid,sname,saddr,phno}
{sname,saddr}
{sanem,saddr,phno}
{sname,saddr,phno,course}
etc......
i.e in practically
Sid is unique for each student-we can refer the student by sid
Ex:in our table sid=1 of student is Ram
and
sid,sname-we refer the student record having Sid and sname
ex: sid=1 sname=ram -so it refers only those student having
sid=1 and snam=ram
and third there is only one person having sid=1,sname=ram and sddr=vja
--which will uniquely identify relation
and so on........
so hence we conclude that super key referes the attributes in a relation
which can uniquely identify the record
there are many super keys for the Relation
Candidate key:
a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that
1.the relation does not have two distinct tuples (i.e. rows or records in common database language) with
the same values for these attributes (which means that the set of attributes is a superkey)
2.there is no proper subset of these attributes for which (1) holds (which means that the set is minimal).
in our table : super key- sid- having only one attribute uniquely identify the record
and the second sid,sname also uniquly identify the relation
hence they are candidate keys
so there may be more than one candidate key that uniquely identify the record
ie...with
{sid} {sid,sname}{sid,sname,addr}
we can and remaining combinations we can uniquely identify the record
so a table contain more than one candidate key
Note:
the Candidate key cannot be NULL
"if the candidate key having more than one attributes
then one of them will be primary key and the remaining will be altenate keys"
that is primary key is the subset of candidate key
Primary key:
primary key is the key which uniquely identify the record and is derived from
candidate key
EX: in our student table only the student name will be enough to identify the record
Composite key:
A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient. It cannot prevent the same student selecting the same course from being inserted into this table
Sid Sname Saddr Phone course
1 ram vja 98888 java
2 sam hyd 89000 sql
The above table can be represented by ER model as
Fig: ER Diagram
In the above diagram
Student is the Entity
And it has the attributes
{Sid, sname , course , phno , saddr}
Entity : An entity is a business object that represents a group, or category of data
Attributes: set of values or properties that give information about the entity
Before going to Candidate key
First of all we need to know about the superkey
Super key:
super key is a set of attributes of a relation variable for which it holds that in all relations
assigned to that variable,there are no two distinct tuples (rows) that have the
same values forthe attributes in this set
i.e in more non technical terms it is nothing but a set of attributes which uniquely identifies the relation
example in our table student
{ Sid}
{sid,sname}
{sid,sname,saddr}
{sid,sname,sddr,phno}
{sid,sname,saddr,phno}
{sname,saddr}
{sanem,saddr,phno}
{sname,saddr,phno,course}
etc......
i.e in practically
Sid is unique for each student-we can refer the student by sid
Ex:in our table sid=1 of student is Ram
and
sid,sname-we refer the student record having Sid and sname
ex: sid=1 sname=ram -so it refers only those student having
sid=1 and snam=ram
and third there is only one person having sid=1,sname=ram and sddr=vja
--which will uniquely identify relation
and so on........
so hence we conclude that super key referes the attributes in a relation
which can uniquely identify the record
there are many super keys for the Relation
Candidate key:
a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that
1.the relation does not have two distinct tuples (i.e. rows or records in common database language) with
the same values for these attributes (which means that the set of attributes is a superkey)
2.there is no proper subset of these attributes for which (1) holds (which means that the set is minimal).
in our table : super key- sid- having only one attribute uniquely identify the record
and the second sid,sname also uniquly identify the relation
hence they are candidate keys
so there may be more than one candidate key that uniquely identify the record
ie...with
{sid} {sid,sname}{sid,sname,addr}
we can and remaining combinations we can uniquely identify the record
so a table contain more than one candidate key
Note:
the Candidate key cannot be NULL
"if the candidate key having more than one attributes
then one of them will be primary key and the remaining will be altenate keys"
that is primary key is the subset of candidate key
Primary key:
primary key is the key which uniquely identify the record and is derived from
candidate key
EX: in our student table only the student name will be enough to identify the record
Composite key:
A Primary Key uniquely identifies each row in a table, it is not always a single-column key,it could be
- a single-column key
- or a composite key
A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient. It cannot prevent the same student selecting the same course from being inserted into this table
