DBMS Interview Questions - Set 5

DBMS Interview Questions


This page consists of DBMS interview questions and answers.

Q1: What is the difference between UNION and UNION ALL?

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.

Q2: What is a Data Model?

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

Q3: What is a Relationship?

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

Q4: How many types of relationships exists in a database?

There are three types of relationship.

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

Q5: Explain One-to-One relationship

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 email 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.

Q6: Explain One-to-Many relationship

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.

Q7: Explain Many-to-Many relationship

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.

Q8: Define Relation Schema and Relation

Relation Schema is defined as R(A1, A2, ..., An) where, R is the relation name and A1, A2, ..., An are the list of attributes.

A relation is a set of tuples (rows).

For example, if R is a relation containing set tuple (t1, t2, ..., tn) then each tuple ti contains ordered list of n values ti = (v1, v2, ..., vn).

Q9: Define degree of a relation

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

Q10: What is functional dependency?

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.