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.

employeeidfirstnamelastnamephoneemailmodified_atcreated_at
e1YusufShakeel6007008009yusuf@example.com2018-01-01 10:00:012018-01-01 10:00:01
e2JaneDoe1002003004jane@example.com2018-01-01 10:01:102018-01-01 10:01:10
e3JohnDoe2003004005john@example.com2018-01-01 10:02:002018-01-01 10:02:00

The employee_position table.

employeeidposition
e1Full Stack Developer
e2Android Developer
e3iOS 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.

employeeidprojectidjoined_atleft_at
e1p12018-01-01 10:00:01NULL
e1p32018-01-01 10:01:10NULL
e3p72018-01-01 10:02:00NULL
e2p22018-01-01 10:02:002018-10-01 10:02:00
e3p82018-01-01 10:02:00NULL

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.

studentidfirstnamelastnamemodified_atcreated_at
s1YusufShakeel2018-01-01 10:00:012018-01-01 10:00:01
s2JaneDoe2018-01-01 10:01:102018-01-01 10:01:10
s3JohnDoe2018-01-01 10:02:002018-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.