面试题与答案

There is set of rules that has been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

=>Avoiding repetitive entries

=>Reducing required storage space

=>Preventing the need to restructure existing tables to accommodate new data.

=>Increased speed and flexibility of queries, sorts, and summaries.

Following are the three normal forms :-

For a table to be in first normal form, data must be broken up into the smallest un possible.In addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields.

The second normal form states that each field in a multiple field primary keytable must be directly related to the entire primary key. Or in other words,each non-key field should be a fact about all the fields in the primary key.

A non-key field should not depend on other Non-key field.

Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.

In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy �Third Normal form�.

Fifth normal form deals with reconstructing information from smaller pieces of information.

These smaller pieces of information can be maintained with less redundancy.

If we want relational system in conjunction with time we use sixth normal form. At this moment

SQL Server does not supports it directly.

Below Sql Query find the second highest salary:

SELECT * FROM wbEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM wbEmployeeSalary b WHERE b.salary>=a.salary))

�CROSS JOIN� or �CARTESIAN PRODUCT� combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.

SELECT INTO statement is used mostly to create backups. The below SQL backsup the

Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of

wbEmployeeBackup and wbEmployee table should be same. SELECT * INTO

wbEmployeeBackup FROM wbEmployee

It is a Form of attack on a database-driven Web site in which the attacker executes

unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization�s host computers through the computer that is hosting the database.

SQL injection attacks typically are easy to avoid by ensuring that a system has strong input

validation.

As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL

SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'

Now somebody does not put �x� as the input but puts �x ; DROP TABLE members;�.

So the actual SQL which will execute is :-

SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE

members;

Think what will happen to your database.

Data independence means that "the application is independent of the storage structure and access strategy of data". In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level. 

Two types of Data Independence: 

1. Physical Data Independence: Modification in physical level should not affect the logical level.

2. Logical Data Independence: Modification in logical level should affect the view level.

A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y. 

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true. 

1.) X is subset or equal to (or) XY = R. 

2.) X is a super key. 

A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true 

1) Ri = R for some i. 

2) The join dependency is implied by the set of FD, over R in which the left side is key of R.

A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.

1. Partial Key: It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator. 

2. Alternate Key: All Candidate Keys excluding the Primary Key are known as Alternate Keys.

Indexing is a technique for determining how quickly specific data can be found. 

Types: 

1. Binary search style indexing 

2. B-Tree indexing 

3. Inverted list indexing 

4. Memory resident table 

5. Table indexing 

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.

In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrentresource usage; dispatches and schedules user requests; and manages space usage within its table-spacestructures.

I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management.

Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.

The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.

Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.

PL/SQL uses cursors for all database information accesses statements. The language supports the use two types 

of cursors 

1.) Implicit 

2.) Explicit