Saturday, 16 October 2021

Concept of Key OR Different types of 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.

Why are the Keys Required?

A key is used in the definitions of various kinds of integrity constraints. A table in a database represents a collection of records or events for a particular relation. Now there can be thousands and thousands of such records, some of which may be duplicated.  There should be a way to identify each record separately and uniquely, i.e. no duplicates. Keys allow us to be free from this hassle.

1.Super Key-

  1. A super key is a set of attributes that can identify each tuple uniquely in the given relation.
  2. A super key is not restricted to have any specific number of attributes.
  3. Thus, a super key may consist of any number of attributes.
Example-

Consider the following Student schema-

Student ( roll , name , sex , age , address , class , section )

Given below are the examples of super keys since each set can uniquely identify each student in the Student table-
  • ( roll , name , sex , age , address , class , section )
  • ( class , section , roll )
  • class , section , roll , sex ) ( name , address )
NOTE-

All the attributes in a super key are definitely sufficient to identify each tuple uniquely in the given relation but all of them may not be necessary.

2. Candidate Key-

A minimal super key is called as a candidate key. 
                                OR
A set of minimal attribute(s) that can identify each tuple uniquely in the given relation is called as a candidate key.

Example-

Consider the following Student schema-

Student ( roll , name , sex , age , address , class , section )

Given below are the examples of candidate keys since each set consists of minimal attributes required to identify each student uniquely in the Student table-
  • ( class , section , roll ) 
  • ( name , address )
NOTES-
  1. All the attributes in a candidate key are sufficient as well as necessary to identify each tuple uniquely.
  2. Removing any attribute from the candidate key fails in identifying each tuple uniquely.
  3. The value of candidate key must always be unique.
  4. The value of candidate key can never be NULL.
  5. It is possible to have multiple candidate keys in a relation.
  6. Those attributes which appears in some candidate key are called as prime attributes.

3. Primary Key-

A primary key is a candidate key that the database designer selects while designing the database. OR
Candidate key that the database designer implements is called as a primary key.

NOTES-
  1. The value of primary key can never be NULL
  2.  The value of primary key must always be unique.
  3. The values of primary key can never be changed i.e. no updation is possible.
  4. The value of primary key must be assigned when inserting a record.
  5. A relation is allowed to have only one primary key.

5. Foreign Key-

  1. An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its values are dependent on the values of attribute ‘Y’.
  2. The attribute ‘X’ can assume only those values which are assumed by the attribute ‘Y’.
  3. Here, the relation in which attribute ‘Y’ is present is called as the referenced relation.
  4. The relation in which attribute ‘X’ is present is called as the referencing relation.
  5. The attribute ‘Y’ might be present in the same table or in some other table.


Here, t_dept can take only those values which are present in dept_no in Department table since only those departments actually exist.

NOTES-
  1. Foreign key references the primary key of the table.
  2. Foreign key can take only those values which are present in the primary key of the referenced relation.
  3. Foreign key may have a name other than that of a primary key.
  4. Foreign key can take the NULL value.
  5. There is no restriction on a foreign key to be unique.
  6. In fact, foreign key is not unique most of the time.
  7. Referenced relation may also be called as the master table or primary table.
  8. Referencing relation may also be called as the foreign table.

Introduction to Relational model

The relational model uses a collection of tables to represent both data and the relationships among those data. Tables are logical models. It is a combination of three components such as structural, integrity and manipulative parts.


Structural part: The structural part defines the database as a collection of relations.
Integrity part: The database integrity is maintained in the relational model using primary key and foreign key.

Manipulative part: The relational algebra and relational calculus are the tools used to manipulate data in the database. This relational has a strong mathematical background.

Features of Relational model:

Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple, and it has all the properties and capabilities required to process data with storage efficiency.




Attribute: Each column in a Table is called as an attribute. Attributes are the properties which define a relation. e.g., Sid, Sname, DOB

Degree: The total number of attributes which are present in a relation is called the degree of the relation.

Tuple: A single row of a table, which contains a single record for that relation, is called a tuple.
Cardinality: Total number of rows present in the Table

Relation instance: A finite set of tuples in the relational database system represents relation instance. Relation instances do not have duplicate tuples.

Relation schema: A relation schema describes the relation name (table name), attributes, and their names.


Relation key:
Each row has one or more attributes, known as relation key, which can identify the row in the relation (table) uniquely.

Attribute domain: Every attribute has some pre-defined value scope, known as attribute domain.

Codd's Rule for Relational DBMS

Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple and it has all the properties and capabilities required to process data with storage efficiency. Relational Model was proposed by E.F. Codd to model data in the form of relations or tables.

Codd's Rule for Relational DBMS:

1. Information Rule: Data stored in Relational model must be a value of some cell of a table. Everything in a database must be stored in a table format.

2. Guaranteed Access Rule: Every data element (value) must be accessible by combination of table name, primary-key (row value), and attribute-name (column value).

3. Systematic Treatment of NULL values: The NULL values in a database must be given a systematic and uniform treatment. NULL value in database must only correspond to missing, unknown or not applicable values.

4. Active Online Catalogue: Structure of database must be stored in an online catalogue, known as data dictionary, which can be accessed by authorized users.

5. Comprehensive Data Sub-language Rule: A database should be accessible by a language supported for definition, manipulation and transaction management operation. If the database allows access to data without any help of this language, then it is considered as a violation.

6. View Updating Rule: Different views created for various purposes should be automatically updatable by the system.

7. High level insert, update and delete rule: Relational Model should support insert, delete, update etc. operations at each level of relations. Also, set operations like Union, Intersection and minus should be supported.

8. Physical data independence: Any modification in the physical location of a table should not enforce modification at application level. The data stored in a database must be independent of the applications that access the database.

9. Logical data independence: Any modification in logical or conceptual schema of a table should not enforce modification at application level. For example, merging of two tables into one should not affect application accessing it which is difficult to achieve.

10. Integrity Independence: Integrity constraints modified at database level should not enforce modification at application level. This rule makes a database independent of the front-end application and its interface.

11. Distribution Independence: Distribution of data over various locations should not be visible to end-users. This rule has been regarded as the foundation of distributed database systems.

12. Non-Subversion Rule: Low level access to data should not be able to bypass integrity rule to change data.

Find Us On Facebook

Computer Basics

More

C Programming

More

Java Tutorial

More

Data Structures

More

MS Office

More

Database Management

More
Top