SQL Interview Questions -- 3

                                       SQL Interview Questions

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain

data records and indices in tables. Relationships may be created and maintained across and among the

data and tables. In a relational database, relationships between data items are expressed by means of

tables. Interdependencies among these tables are expressed by data values rather than by pointers.

This allows a high degree of data independence. An RDBMS has the capability to recombine the data

items from different files, providing powerful tools for data usage.

What is normalization?

Database normalization is a data design and organization process applied to data structures based on

rules that help build relational databases. In relational database design, the process of organizing data

to minimize redundancy. Normalization usually involves dividing a database into two or more tables and

defining relationships between the tables. The objective is to isolate data so that additions, deletions,

and modifications of a field can be made in just one table and then propagated through the rest of the

database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field

contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key                                                                                                                                          

If attributes do not contribute to a description of the key, remove them to a separate table. All

attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct

tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many

relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first

fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored

in the server database. Stored procedures accept input parameters so that a single procedure can be

used over the network by several clients using different input data. And when the procedure is

modified, all clients automatically get the new version. Stored procedures reduce network traffic and

improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)

occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential

integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed;
the DBMS automatically fires the trigger as a result of a data modification to the associated table.
Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is
stored at the database level. Stored procedures, however, are not event-drive and are not attached to a
specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the
procedure while triggers are implicitly executed. In addition, triggers can also execute stored
procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing
another trigger. A trigger that contains data modification logic within itself is called a nested trigger.