1. Define Database.
A fixed collection of figures known as data is called database.
2. What is DBMS?
Database Management Systems (DBMS) are applications designed especially which enable user to influence with other applications.
3. What are the features of Database language?
A database language may also include features like:
4. Define database model.
A data model conditioned fundamentally how data can be stored, worked and organised and the structure of the database logically is called database model.5. Define Normalization.
Organized data void of inconsistent dependency and sacking within a database is called normalization.6. Enlist some commands of DDL.
They are:CREATE:Create is used in the CREATE TABLE statement.
Syntax is: CREATE TABLE [column name] ( [column definitions] ) [ table parameters] ALTER: It helps in modification of an existing object of database.
syntax is: ALTER objecttype objectname parameters. DROP: It destroys an existing database, index, table or view.
syntax is:
DROP objecttype objectname.
7. Why is group-clause used?
Group-clause uses aggregate values to be obtain by collecting similar data.8. Define Aggregate functions.
Functions which operate against a collection of values and depart the single value is called aggregate functions9. Define Scalar functions.
Scalar function is depended on the argument given and returns sole value.10. What restrictions can you apply when you are creating views?
Following are the restrictions that are creating views:11. Define “correlated subqueries”.
A ‘correlated subquery’ is a sort of sub query but correlated subquery is reliant on another query for a value that is returned. In case of execution, the sub query is executed first and then the correlated query.12. List the disadvantages of query.
13. What is Executive Plan?
An executive plan is,Server (SQL) supply collected method or the plan of query execution and used thereafter by following calls.14. Define Fragmentation.
A database feature of server that develop control on data which is stored at table level by the user is called Fragmentation.15. Differentiate Nested Loop, Hash Join and Merge Join.
Nested loop (loop over loop)An outer loop within an inner loop is formed contains few entries and then for individual entry, inner loop is individually processed.
E.g.
Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2;
It’s processing takes place in this way:
For i in (select * from col1) loop
For j in (select * from col2 where col2=i.col1) loop
Results are displayed;
End of the loop;
End of the loop;
The Steps of nested loop are:
Nested Loops is executed from the inner to the outer as:
While joining large tables, the use of Hash Join is preferred.
Algorithm of Hash Join is divided into:
Two independent sources of data are joined in sort merge join. They presentation is better as differentiate to nested loop when the data volume is big enough but normally it is not good as hash joins.
The full operation can be divided into parts of two:
Sort join operation :
Get first row R1 from input1
Get first row R2 from input2.
Merge join operation:
‘while’ is not present at either loop’s end.
if R1 joins with R2
next row is got R2 from the input 2
return (R1, R2)
else if R1 < style=””> next row is got from R1 from input 1
else
next row is got from R2 from input 2
end of the loop
