Page 266 - Trackpad 402_Class-X_Final
P. 266

Different Types of Keys in a Relation
        When the data is stored in the form of records then there are chances where the data values are repeated. For
        example, in a class of forty students if a table is created with - Name, Address, City, Date of Birth, Phone number then
        there may be two or more students with the same name. There are also chances when twins staying in the same house
        will have the same address, Date of Birth and phone numbers. Two or more classmates may have different addresses
        or phone numbers but share the same birth date. So with so many similarities and so many repetition of values in
        different columns we may face the following issues:
        •  If a specific information is needed in a database then searching will become difficult. For example if we see the
          above table where there are two students with the same name- SMITH. If a teacher wants to call parents of Smith
          who lives in Defence Colony because he is absent from a long time without any information then it becomes difficult
          to search unless both records are uniquely identified.
        •  Searching might not give you the correct information.
        •  There are chances of storing duplicate and unwanted/ erroneous values in the table.
        •  If data is needed from two or more tables then linking of multiple tables cannot be done unless there is a common
          and unique column.
        A key or an attribute solves all the above problems. It stores data values of the similar type. There are different kinds
        of keys that can be created in a table for the efficient retrieval of the data. These keys are as follows:
        •  Candidate Key: An attribute or a set of attributes that can uniquely identify a record and is capable of being a
          primary key is called a candidate key. For example, if we consider a student table then Admno, Rollno, DateofBirth
          are the candidate keys as they are chances of unique values.
        •  Primary Key: A candidate key that uniquely identifies a row in a tuple by storing Not Null and unique values is known
          as a Primary key. For example in a STUDENT table - Admission number is a primary key because it is unique for each
          student. Even if you pass out from the school and you come after a few years to get some information then using
          your admission number you can easily get your details. In an EMPLOYEE  table- Employee code is a key with a unique
          value and is never repeated for the other employees. In a CUSTOMER table - Customer_Id is the primary key.
          In the given table DEPT, the primary key is the DCODE column as it uniquely identifies each record.

                                                       Table: DEPT
                                        DCODE          DEPARTMENT             CITY

                                          D01       MEDIA                DELHI
                                          D02       MARKETING            DELHI

                                          D03       INFRASTRUCTURE       MUMBAI
                                          D05       FINANCE              KOLKATA

                                             Candidate Key
                                    Primary Key           Alternate Key

        Data is arranged on the basis of Primary Keys in the database so that searching becomes accurate, easier and faster.
        •  Alternate Key: A remaining candidate key which is not selected as the primary key is called an alternate key. In the
          table DEPT, the primary key is DCODE, and the alternate key is DEPARTMENT.
        •  Composite Primary Key: Sometimes a single attribute cannot be used as a primary key then in that case two or
          more attributes in combination will form a unique set of values which can be used as a primary key. Such a set
          of combination of attributes is known as a Composite Primary key. For example, if admno is not taken then a
          combination of Rollno + Phoneno will give a unique combination and can be used as a composite Primary key.
        •  Foreign Key: It is an attribute or a set of attributes whose values match the primary key of another table. A relationship
          between two tables matches the primary key of one table with the foreign key of another table.

          266   Trackpad Information Technology-X
   261   262   263   264   265   266   267   268   269   270   271