DBMS Interview Questions

This page consists of DBMS interview questions and answers.

We use the UNION and UNION ALL command to join two or more tables together.

The UNION command removes all the duplicate rows and selects only those rows that forms a distinct row after joining all the tables.

The UNION ALL command does not removes duplicate rows. It simple takes all the rows from the tables and joins them.

Data Model is a collection of conceptual tools for describing the data, data relationships, data semantics and constraints.

A relationship is an association of two or more entities (tables) in a database.

There are three types of relationship.

- One-to-One
- One-to-Many
- Many-to-Many

One-to-One relationship occurs if there is only one record in the first table that is connected with exactly one record in the second table.

For example if we consider `employee`

and `employee_position`

table then we can easily say that one employee can hold only one position in the company. So, this is a one-to-one relationship.

The employee table.

employeeid | firstname | lastname | phone | modified_at | created_at | |
---|---|---|---|---|---|---|

e1 | Yusuf | Shakeel | 6007008009 | yusuf@example.com | 2018-01-01 10:00:01 | 2018-01-01 10:00:01 |

e2 | Jane | Doe | 1002003004 | jane@example.com | 2018-01-01 10:01:10 | 2018-01-01 10:01:10 |

e3 | John | Doe | 2003004005 | john@example.com | 2018-01-01 10:02:00 | 2018-01-01 10:02:00 |

The employee_position table.

employeeid | position |
---|---|

e1 | Full Stack Developer |

e2 | Android Developer |

e3 | iOS Developer |

So, we can see that one employee can take only one position at a time.

In the One-to-Many relationship an entry in the first table is connected with more than one entries in the second table.

For example a single employee can work on multiple projects.

The employee_project table.

employeeid | projectid | joined_at | left_at |
---|---|---|---|

e1 | p1 | 2018-01-01 10:00:01 | NULL |

e1 | p3 | 2018-01-01 10:01:10 | NULL |

e3 | p7 | 2018-01-01 10:02:00 | NULL |

e2 | p2 | 2018-01-01 10:02:00 | 2018-10-01 10:02:00 |

e3 | p8 | 2018-01-01 10:02:00 | NULL |

So, we can see that one employee e3 is working in two projects p7 and p8.

In Many-to-Many relationship many entries in the first table is connected with many entries in the second table.

For example many employees can work on a given project. And a given project can have many employees working on that project.

Relation Schema is defined as R(A_{1}, A_{2}, ..., A_{n}) where, R is the relation name and A_{1}, A_{2}, ..., A_{n} are the list of attributes.

A relation is a set of tuples (rows).

For example, if R is a relation containing set tuple (t_{1}, t_{2}, ..., t_{n}) then each tuple t_{i} contains ordered list of n values `t`

._{i} = (v_{1}, v_{2}, ..., v_{n})

The degree of a relation is equal to the number of attributes in the relation schema.

Functional dependency in relational database is defined as the constraint that describes the relationship between attributes in a relation.

For example, in a relation R, a set of attributes X is said to functionally determine another set of attributes Y, also in R, if and only if each X value in R is exactly associated with one Y value in R.

Then, R is said to satisfy the functional dependency X → Y.

Consider the following student table.

studentid | firstname | lastname | modified_at | created_at |
---|---|---|---|---|

s1 | Yusuf | Shakeel | 2018-01-01 10:00:01 | 2018-01-01 10:00:01 |

s2 | Jane | Doe | 2018-01-01 10:01:10 | 2018-01-01 10:01:10 |

s3 | John | Doe | 2018-01-01 10:02:00 | 2018-01-01 10:02:00 |

We have the following functional dependencies for the above table.

```
studentid → firstname
```

So from studentid we can determine the firstname of the student.

```
studentid → lastname
```

So from studentid we can determine the lastname of the student.

```
studentid → {firstname, lastname}
```

So from studentid we can determine the firstname and lastname of the student.