Database- Frequently Asked Questions

1.) Explain Various Normalization Forms?

The Normal Forms Are As Follows…

First normal form (1NF) sets the basic rules for an organized database:

* Eliminate duplicative columns from the same database table.
* Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second normal form (2NF) further Handles the concept of removing duplicative data:

* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.

Third normal form (3NF) goes one large step further:

* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.

Finally, fourth normal form (4NF) has one additional requirement:

* Meet all the requirements of the third normal form.
* A relation is in 4NF if it has no multi-valued dependencies


2.) What is the difference between delete,drop and truncate?

The delete statement will result in the table spaces or memories would remain as the current size and table structureremain in the database. Delete is not effective when you use it to remove all data from a table, because it takes up storeage spaces due to unclear memories. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it free table storage spaces and only use it when you need to remove all data from a table. The drop statement will result in completely removing the table from the database. Note: Statements above are made with the assumption that a commit is has been executed. Once a commit statement is executed (commit is final). Therefore, rollback a transaction will not work if commit statement is executed.

3.) Which view shows all partitions of a table and their tablespaces?

user_tab_partitions,all_tab_partitions,dba_tab_partitions

4.) What is a value set?

A set of permitted values for the given attribute. eg GRADE attribute in STUDENT table can have only values like A, B,C, D only.

5.) Difference between user and end-user?

The final or ultimate user of a computer system. The end user is the individual who uses the product after it has been fully developed and marketed. The term is useful because it distinguishes two classes of users, users who require a bug -free and finished product (end users), and users who may use the same product for development purposes. The term end user usually implies an individual with a relatively low level of computer expertise. Unless you are a programmer or engineer, you are almost certainly an end user. An individual who uses a computer. This includes expert programmers as well as novices. An end user is any individual who runs an application program.

6.) Briefly explain the difference between first, second, third and fourth normal forms?

1NF: A table is set to be in first NF if we identify the functional dependency. 2NF: A table is set to be in first NF if we identify and delete partial functional dependency. 3NF: A table is set to be in 3rd NF when we identify and delete transitive dependency. Functional dependency: identify a non key attribute which is depends on key attribute. Partial functional dependency: identify an attribute which is partially depends on key attribute. Transitive: identify an key attribute which is independent itself.

7.) What is ER Diagram?

The Entity-Relationship (ER) model was originally proposed by Peter in 1976 [Chen76] as a way to unify the network and relational database views. Simply stated the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects.Since Chen wrote his paper the model has been extended and today it is commonly used for database design For the databasedesigner, the utility of the ER model is: it maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. it is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user. In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software