Page 301 - TP_IT_V1.0_C10_flipbpookl
P. 301

Different Types of Keys in a Relation
                 When the data is stored in the form of records, there are chances that 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, and 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 repetitions of values in different
                 columns, we may face the following issues:
                 •  If 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 the parents of Smith
                   who lives in Defence Colony because he has been absent for 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 a similar type. Different kinds of keys can
                 be created in a table for the efficient retrieval of the data. These keys are as follows:
                 •  Primary Key: A primary key uniquely identifies each record (row) in a table. It must contain unique values and
                   cannot contain NULL values (empty / nothing). At any time, no two rows in the table can neither have same values
                   for the primary key nor can data value for such field be left blank. In the given table DEPT, the primary key is the
                   DCODE column as it uniquely identifies each record. We can have only one primary key in a table.
                                                               Table: DEPT


                                                DCODE         DEPARTMENT            CITY
                                                  D01             Media            DELHI
                                                  D02           Marketing          DELHI

                                                  D03       INFRASTRUCTURE        MUMBAI
                                                  D05            Finance          KOLKATA


                                            Primary  Cadidate Key    Alternate
                                              Key                      Key
                 •  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. In the table DEPT, DCODE and DEPARTMENT are the candidate keys as they
                   have chances of unique values. We can have atleast one candidate key in a table.
                 •  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 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 combinations of
                   attributes is known as a Composite key. In the table DEPT, DEPARTMENT, CITY together are the composite keys as
                   they have chances of unique values.

                 •  Foreign Key: It is an attribute or a set of attributes whose values match the primary key of another table. A primary
                   key of one table when used in another table is called foreign key. A relationship between two tables matches the
                   primary key of one table with the foreign key of another table.




                                                                        Database Management System using LibreOffice Base  299
   296   297   298   299   300   301   302   303   304   305   306