DBMS Interview Questions and Answers

In 1969 Dr. E. F. Codd laid down some 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS.

"All information in a relational data base is represented explicitly at the logical level and in

exactly one way - by values in tables."

"Each and every datum (atomic value) in a relational data base is guaranteed to be logically

accessible by resorting to a combination of table name, primary key value and column name."

In flat files we have to parse and know exact location of field values. But if a DBMS is truly

RDBMS you can access the value by specifying the table name, field name, for instance

Customers.Fields [‘Customer Name’].

"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.". 

"The data base description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.

"A relational system may support several languages and various modes of terminal use (for

example, the fill-in-the-blanks mode). However, there must be at least one language whose

statements are expressible, per some well-defined syntax, as character strings and that is

comprehensive in supporting all the following items.

Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers. Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID 

Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON

Customers.CustomerID =Orders.CustomerID 

Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON

Customers.CustomerID =Orders.CustomerID

Following are difference between them: 

“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.

Aggregate and Scalar functions are in built function for counting and calculations.

Aggregate functions operate against a group of values but returns only one value.

AVG(column) :- Returns the average value of a column

COUNT(column) :- Returns the number of rows (without a NULL value) of a column

COUNT(*) :- Returns the number of selected rows

MAX(column) :- Returns the highest value of a column

MIN(column) :- Returns the lowest value of a column

Scalar functions operate against a single value and return value on basis of the single value.

UCASE(c) :- Converts a field to upper case

View is a virtual table which is created on the basis of the result set returned by the select

statement.

CREATE VIEW [MyView] AS SELECT * from pcdsEmployee where LastName = 'singh'

In order to query the view.

SELECT * FROM [MyView]

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes. 

It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true 

1. X is a Super-key of R. 

2. A is a prime attribute of R. 

In other words, if every non prime attribute is non-transitively dependent on primary key. 

The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

1. Atomicity: Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions. 

2. Aggregation: A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.

No. 

PRODUCT: Concatenation of every row in one relation with every row in another. 

JOIN: Concatenation of rows from one relation and related rows from another.

It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.

It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.