DBMS Questions(61-70)

61.When is a functional dependency F said to be minimal?

Every dependency in F has a single attribute for its right hand side. We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F. We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

62.What is the difference between oracle, sql and sql server ?

Oracle is based on RDBMS.

SQL is Structured Query Language.

SQL Server is another tool for RDBMS provided by Microsoft.

63.why you need indexing ? where that is stored and what you mean by schema object? For what purpose we are using view?

We cant create an Index on Index.. Index is stored in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view. indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database.View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

64.Difference between Store Procedure and Trigger?

we can call stored procedure explicitly. but trigger is automatically invoked when the action defined in trigger is done. ex: create trigger after Insert on this trigger invoked after we insert something on that table. Stored procedure can't be inactive but trigger can be Inactive. Triggers are used to initiate a particular activity after fulfilling certain condition. It need to define and can be enable and disable according to need.

65.What is the advantage to use trigger in your PL?

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.

Another reason of using triggers can be for automatic pupation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.

Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.

Triggers can be used to publish information about database events to subscribers.Database event can be a system event like Database startup or shutdown or it can be a user even like User login in or user logoff.

66.What the difference between UNION and UNIONALL?

Union will remove the duplicate rows from the result set while Union all doesn’t.

67.What is the difference between TRUNCATE and DELETE commands?

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster. Whereas DELETE call is an DML command and can be rolled back.

68.Which system table contains information on constraints on all the tables created?

USER_CONSTRAINTS, system table contains information on constraints on all the tables created

69.Explain Normalization?

Normalization means refining the redundancy and maintain stabilization. there are four types of normalization :

first normal forms, second normal forms, third normal forms and fourth Normal forms.

70.How to find out the database name from SQL*PLUS command prompt?

Select * from global_name; This will give the database name which u r currently connected to.....