Every year IBPS conducts an exam for Specialist Officer (SO) just like IBPS PO/Clerk. IBPS SO is a very reputed exam and a lot of candidates prepare for it. The syllabus for IBPS SO phase I (prelims) is almost similar to that of IBPS PO and Clerk prelims, but its mains section is totally different from common subjects of bank exam. A total of 6 Specialist Officer Posts for which IBPS conducts the SO exam. These are IT officer, Agricultural Field officer, Rajbhasha Adhikari, Law officer, HR/Personnel Officer, Marketing Officer. Phase II (mains) exam consists of 60 professional knowledge questions from the respective streams mentioned above. In this article, we will discuss a very important topic of IBPS SO IT mains, which is a Database Management System (DBMS) keys. Around 10 – 12 questions came from DBMS subjects and almost 20% – 25% are from subtopic DBMS keys.
Relational Model of DBMS
Before starting with DBMS keys, we will learn about the relational model of DBMS.
Relational model: Data and relationship when represented by a collection of inter-related tables is called the relational model of data.
Example: Table given below shows the student id (S_ID), student name (S_NAME), student date of birth (S_DOB) and course id (C_ID), the sequence number (S_SN)-
The table given below shows the course id (C_ID), course name (C_NAME)-
Components of the Relational model:
- Table: A table or entity in the relational model is known as a Relation and a table is a collection of rows and columns.
Ex: STUDENT and COURSE are two entities represented by tables.
- Columns: Column or field of a relation represents the characteristics of the entity. Characteristics are also known as Field or Attribute.
Ex: S_ID, S_NAME, S_DOB, and C_ID are attributes of the STUDENT table and C_ID and C_NAME are attributes of the COURSE table.
- Rows: The row of a table contains a single record of that relation. Rows are also known as Tuple or Record.
Keys are very important in the Relational database management system (RDBMS), they are used to uniquely identify data and to establish a relationship between relations. Keys in DBMS can be a single attribute or a set of attributes that can uniquely identify a record in a table or table in a database.
Type of DBMS keys:
DBMS keys are of various types which are mentioned below:
Super key is defined as the set of one or more attributes (columns) that can uniquely identify a record in the relation.
Note: A super key may have extra (additional or redundant) attributes that are not needed to uniquely identify a record.
Ex: In the STUDENT table, S_ID, S_NAME, and S_DOB together and any combination of S_ID or C_ID with any other attribute works as super keys.
While in the COURSE table, C_ID and C_ID and C_NAME together are super keys.
A primary key is a single attribute or set of attributes that uniquely identify records in a relation.
Condition for primary key:
- The primary key cannot be NULL.
- It must have a unique value.
- A table must have a primary key.
Ex: For the STUDENT table, S_ID is a primary key and for the COURSE table C_ID works as the primary key.
A candidate key is a single attribute or set of attributes that uniquely identify records in a relation. In other words, we can say that a super with no redundant data is called the candidate key.
Note: The candidate key with a minimal number of attributes is selected for the primary key.
Ex: In STUDENT table S_ID, C_ID and S_NAME together, S_NAME and S_DOB together are candidate key.
In DBMS keys, the unique key is similar to the primary key and is used to uniquely identify records in the table. The only difference between the primary key and the unique key is that a unique key can accept NULL values.
There may be more than one candidate for a primary key, out of which only one is selected as primary and remaining keys which are not primary keys are called alternate keys.
Ex: C_ID and S_NAME together and S_NAME and S_DOB together are considered as alternate keys.
It may not be possible that a single attribute uniquely identifies a record but a combination of more than one attribute can identify that record uniquely. The combination of two or more attributes that uniquely identify a record in a relation is called compound or composite key.
Ex: In the STUDENT table, C_ID and S_NAME together and S_NAME and S_DOB together are compound keys.
The foreign key is a column of the table which can uniquely identify records in another table. In other words, the primary key of a table is the foreign key of another table.
Ex: C_ID of COURSE table is a foreign key for the STUDENT table.
A Surrogate key is an artificial key that is used to uniquely identify records in the current database. The Surrogate key is created in the absence of the primary key and does not have any meaning in the table. The Surrogate key is generally an integer value that is generated right before the insertion of data in the table.
Ex: In the STUDENT table, in the absence of S_ID, S_SN can work as the surrogate key because every time data is inserted to the table its value is increased by ‘1’. So, S_SN can uniquely identify a record in the table.
The partial key is an attribute of a weak entity type that cannot uniquely identify all the records of the table. More than one record can be selected using a partial key in RDBMS.
Ex: In the STUDENT table, C_ID is a partial key, since there are two students Alex and Bob associated with C_ID 9005.
This is all from us in this article on DBMS keys.
- 100+ Free Mock Tests
- Oliveboard Mobile App
- Oliveboard’s discuss forum
- Oliveboard Telegram Group
- Download BOLT – Our Monthly General Awareness free e-book