Scribd Logo
Upload

Welcome to Scribd!

  • 50 views

    Keys in DBMS

    Uploaded by

    Maryam Fatima
    The document defines several key concepts in database management systems: 1. Primary keys uniquely identify each record in a table and cannot contain null values. Foreign keys appear in one table but refer to the primary key of another, linking the tables. 2. Candidate keys are attributes that uniquely identify records and one can be selected as the primary key. Alternate keys are other candidate keys not chosen as primary. 3. Composite keys contain two or more columns to uniquely identify records. Tables are in second normal form if non-key attributes depend on the whole primary key, not just a part.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Keys in DBMS

    Uploaded by

    Maryam Fatima
    50 views8 pages
    The document defines several key concepts in database management systems: 1. Primary keys uniquely identify each record in a table and cannot contain null values. Foreign keys appear in one table but refer to the primary key of another, linking the tables. 2. Candidate keys are attributes that uniquely identify records and one can be selected as the primary key. Alternate keys are other candidate keys not chosen as primary. 3. Composite keys contain two or more columns to uniquely identify records. Tables are in second normal form if non-key attributes depend on the whole primary key, not just a part.

    Original Title

    KEYS IN DBMS

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document defines several key concepts in database management systems: 1. Primary keys uniquely identify each record in a table and cannot contain null values. Foreign keys appear in one table but refer to the primary key of another, linking the tables. 2. Candidate keys are attributes that uniquely identify records and one can be selected as the primary key. Alternate keys are other candidate keys not chosen as primary. 3. Composite keys contain two or more columns to uniquely identify records. Tables are in second normal form if non-key attributes depend on the whole primary key, not just a part.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    50 views8 pages

    Keys in DBMS

    Uploaded by

    Maryam Fatima
    The document defines several key concepts in database management systems: 1. Primary keys uniquely identify each record in a table and cannot contain null values. Foreign keys appear in one table but refer to the primary key of another, linking the tables. 2. Candidate keys are attributes that uniquely identify records and one can be selected as the primary key. Alternate keys are other candidate keys not chosen as primary. 3. Composite keys contain two or more columns to uniquely identify records. Tables are in second normal form if non-key attributes depend on the whole primary key, not just a part.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 8

    KEYS IN DBMS

    A key in DBMS is an attribute or a set of attributes that help to uniquely


    identify a tuple (or row) in a relation (or table). Keys are also used to establish
    relationships between the different tables and columns of a relational database.
    Individual values in a key are called key values.
    There are some following keys in DBMS:
    1. Primary key
    2. Foreign key
    3. Candidate key2q2q
    4. Alternative key
    5. Super key
    6. Composite key
    1.Primary Key
    A primary key is a special relational database table column designated to
    uniquely identify each table record.
    A primary key is used as a unique identifier to quickly parse data within the
    table. A table cannot have more than one primary key.
    A primary key’s main features are:

     It must contain a unique value for each row of data.


     It cannot contain null values.
     Every row must have a primary key value.
    2. Foreign Key
    A foreign key is generally a primary key from one table that appears as a field in
    another where the first table has a relationship to the second. In other words, if we
    had a table A with a primary key X that linked to a table B where X was a field in
    B, then X would be a foreign key in B.

    An example might be a student table that contains the course_id the student is
    attending. Another table lists the courses on offer with course_id being the primary
    key. The 2 tables are linked through course_id and as such course_id would be a
    foreign key in the student table.
    3. Candidate Key
    A candidate is a subset of a super key. A candidate key is a single field or the
    least combination of fields that uniquely identifies each record in the table. The
    least combination of fields distinguishes a candidate key from a super key.
    Every table must have at least one candidate key but at the same time can have
    several.

    As an example we might have a student_id that uniquely identifies the students


    in a student table. This would be a candidate key. But in the same table we
    might have the student’s first name and last name that also, when combined,
    uniquely identify the student in a student table. These would both be candidate
    keys.

    In order to be eligible for a candidate key it must pass certain criteria.

     It must contain unique values


     It must not contain null values
     It contains the minimum number of fields to ensure uniqueness
     It must uniquely identify each record in the table

    Once your candidate keys have been identified you can now select one to be
    your primary key.

    4. Alternative Key
    Alternate Key or Secondary Key is the key that has not been selected to be the
    primary key, but are candidate keys. However, it is considered a candidate key
    for the primary key.
    A candidate key not selected as a primary key is called alternate or secondary
    key. Candidate key is an attribute or set of attributes that you can consider as a
    Primary key.
    Example:

    Student_ID Student_Enroll Student_Name Student_Email

    096 2717 Manish [email protected]

    055 2655 Manan [email protected]

    067 2699 Shreya [email protected]

    Above, Student_ID, Student_Enroll and Student_Email are the candidate


    keys. They are considered candidate keys since they can uniquely identify the
    student record. Select any one of the candidate key as the primary. Rest of the
    two keys would be Alternate or Secondary Key.
    Let’s say you selected Student_ID as primary key,
    therefore Student_Enroll and Student_Email will be Alternate Key
    (candidates of primary key).

    5. Super key
    Super Key is an attribute (or a set of attributes) that uniquely identify a tuple
    i.e. an entity in entity set.
    It is a superset of Candidate Key, since Candidate Keys are selected from super
    key.
    Example
    Let us see an example −
    <Student>

    Student_ID Student_Enroll Student_Name Student_Email

    S02 4545 Dave [email protected]

    S34 4541 Jack [email protected]

    S22 4555 Mark [email protected]


    The following are the super keys for the above table −

    {Student_ID}
    {Student_Enroll}
    {Student_Email}
    {Student_ID, Student_Enroll}
    {Student_ID, Student_Name}
    {Student_ID, Student_Email}
    {Student_Name, Student_Enroll}
    {Student_ID, Student_Enroll, Student_Name}
    {Student_ID, Student_Enroll, Student_Emai}
    {Student_ID, Student_Enroll, Student_Name, Student_Email}
    The following would be the candidate key from the above −

    {Student_ID}
    {Student_Enroll} {Student_Email}

    6. Composite key
    A primary key having two or more attributes is called composite key. It is a
    combination of two or more columns.
    An example can be −
    <Student>
    StudentID StudentEnrollNo StudentMarks StudentPercentage

    S001 0721722 570 90

    S002 0721790 490 80

    S003 0721766 440 86

    Above, our composite keys are StudentID and StudentEnrollNo. The table has two


    attributes as primary key.
    Therefore, the Primary Key consisting of two or more attribute is called Composite
    Key.

    2 Normal form (2NF)


    It is a normalization level in DBMS. A relation is said to be in the 2nd
    Normal Form in DBMS (or 2NF) when it is in the First Normal Form
    but has no non-prime attribute functionally dependent on any
    candidate key’s proper subset in a relation.
    Used for:
    It is make use of functional dependency and tries to remove problem
    of redundant data that was introduced by 1NF.
    “A relation is in 2NF, if it is in 1NF and every non-key attributes
    is fully functionality depended on primary key of the relation.”
    RULES:
     It should be in 1NF.
     There should not be any partial dependency.

    Decompose the following table into 2NF:

    TUTOR table

    TUTOR_ID COURSE TUTOR_AGE

    2115 Java 30

    2115 C 30

    4997 Python 35

    8663 C++ 38

    8663 Go 38

    Answer:

    TUTOR_DETAIL table:
    TUTOR_ID TUTOR_AGE

    2115 30

    4997 35

    8663 38

    TUTOR_COURSE table:

    TUTOR_ID COURSE

    2115 Java

    2115 C

    4997 Python

    8663 C++

    8663 Go

    Example 2:
    Decompose the following table into 2NF:

    <Candidate_Courses>

    Candidate_ID Course_ID Candidate_Name Course_Name

    C829 A09 Beverly CSS

    C736 A07 Sheldon PHP


    C546 A03 Leonard HTML

    C952 A05 Zach Ruby

    Answer:

    <Candidate_Info>

    Candidate_ID Course_ID Candidate_Name

    C829 A09 Beverly

    C736 A07 Sheldon

    C546 A03 Leonard

    C952 A05 Zach

    <Course_Info>

    Course_ID Course_Name

    A09 CSS

    A07 PHP

    A03 HTML

    A05 Ruby

    You might also like