DBMS Interview Questions - Set 2
DBMS Interview Questions
This page consists of DBMS interview questions and answers.
Q1: What is a super key?
A super key is a set of columns that can uniquely identify a row in a table.
Following is a simple customer table.
Note! Every customer gets a unique customerid and has a unique email address.
| customerid | firstname | lastname | phone | modified_at | created_at | |
|---|---|---|---|---|---|---|
| c1 | Yusuf | Shakeel | 6007008009 | yusuf@example.com | 2018-01-01 10:00:01 | 2018-01-01 10:00:01 |
| c2 | Jane | Doe | 1002003004 | jane@example.com | 2018-01-01 10:01:10 | 2018-01-01 10:01:10 |
| c3 | John | Doe | 2003004005 | john@example.com | 2018-01-01 10:02:00 | 2018-01-01 10:02:00 |
| c4 | Jane | Doe | 9002003004 | jane2000@example.com | 2018-02-04 10:02:10 | 2018-02-04 10:02:10 |
| c5 | John | Doe | 6003004005 | john8991@example.com | 2018-05-01 13:49:50 | 2018-05-01 13:49:50 |
Following are some of the superkeys for the above customer table that can uniquely identify a row in the table.
- customerid, phone
- customerid, email
- phone, email
- customerid, phone, email
- phone, email, modified_at, created_at
- firstname, lastname, phone, email
Q2: What is a candidate key?
A candidate key is a set of minimum number of columns that can uniquely identify a row in a table.
There can be more than one candidate key for a given table.
Value of a candidate key is unique and non-null value for all tuple (rows).
Following are some of the candidate key for the customer table that can uniquely identify a row in the table.
- customerid
- customerid, phone
- customerid, email
- phone, email
Q3: What is a composite candidate key?
A candidate key can have one colume and it can also have multiple columns.
A candidate key with multiple columns is called a composite candidate key.
Following are some of the composite candidate key for the customer table that can uniquely identify a row in the table.
- customerid, phone
- customerid, email
- phone, email
Q4: What is a primary key?
A primary key is one of the candidate key that is selected to uniquely identify rows of a table.
There can be more than one number of candidate keys for a given table and it is upto the architect to choose any one of the candidate key and make it the primary key.
We have selected customerid as the primary key for the customer table as it can uniquely identify a row in the table.
Q5: What is an alternate key?
All candidate keys that are not the primary key are called the alternate keys.
Q6: What is a foreign key?
A foreign key is an attribute of a table that will take only those values that are present in an attribute of other table.
Following is the customer_order table.
| orderid | customerid | amount | modified_at | created_at |
|---|---|---|---|---|
| o1 | c1 | 100.70 | 2018-01-01 10:20:01 | 2018-01-01 10:20:01 |
| o2 | c3 | 99.10 | 2018-01-01 10:31:10 | 2018-01-01 10:31:10 |
| o3 | c2 | 19.99 | 2018-01-01 10:52:00 | 2018-01-01 10:52:00 |
| o4 | c5 | 7.99 | 2018-02-04 11:02:10 | 2018-02-04 11:02:10 |
| o5 | c5 | 299.99 | 2018-05-01 14:01:50 | 2018-05-01 14:01:50 |
In the above table the customerid attribute is the foreign key in the customer_order table.
The customerid column of the customer_order table will take only those values that are present in the customerid column of the customer table.
Q7: Can a primary key column of a table have more than one row sharing the same value?
No.
Primary key column of a table is supposed to have only unique values.
Q8: Can a foreign key column of a table have more than one row sharing the same value?
Yes.
Foreign key column of a table points at the primary key column of some other table. So, it is possible to have more than one row having the same value.
In the above customer_order table the foreign key column customerid has same value c5 appearing twice.
| customerid |
|---|
| c1 |
| c3 |
| c2 |
| c5 |
| c5 |
Q9: What is the difference between Primary key and Unique constraint?
For an table there can only be ONE Primary key but the table can have multiple unique constraint.
Primary key can't have NULL value whereas, unique constraint can have NULL value.
Primary key is a unique contraint of a table.
Q10: What is SQL?
SQL or Structured Query Language is a standardised query language that allows us to access and manage databases.