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