Custom Search

Tuesday, May 5, 2009

SQL FAQs


Questions & Answers

1. What do you understand by client-server concept?

A. A software component that services requests from another software component is called a server. The component making the requests is the client.

The idea of client/server programming is to maximize the benefits of the desktop. The client and server can be present on the same computer or on geographically distant computer connected over a network . Some of the benefits of C/s computing are :

Flexibility, scalability and cost savings in terms of resource utilization and centralized control.

2. What is meant by 3-Tier architecture?

A. 3 Tier architecture is a method of deploying the client/server technology in real –world situations. The 3 tiers are :

· GUI front-end - Takes care of the end-user interaction and validation of input

data

· Business logic layer - It encapsulates the business processing logic for the data

entered by the user or the data to be processed and given as output to the user. It can be implemented as a collection of reusable and upgradable software components .

· Data-processing layer - It takes care of the data processing , storage and

retrieval of the stored data.

3. What are the advantages of 3-Tier architecture over 2-Tier architecture?

A. In 2-tier architecture we only have the application layer and the data-processing layer.

The application layer is a combination of the GUI front-end and the business logic.

The following are the disadvantages of the 2-tier architecture (which in turn are the advantages of 3-tier over 2-tier ) :

· The application is not easily upgradable.

· The size of the application is large.

3-tier arch. Provides :

· Maximum performance

· Security

· Ease of maintenance.

4. What do normalization and Denormalization mean?

Normalization is the process of simplifying the relationship between data elements in a data structure .

OR

The task of the database designer is to structure the data in a way that eliminates unnecessary duplication and provides a rapid search path to all necessary information. The process of dividing the information into separate tables that meet these goals is called Normalization.

Denormalization is the reverse process of normalization wherein the normalized data is merged to form a larger data structure that might have redundant data.


5. What are the different degrees of normalization?

A. A relation is in 1st Normal form if and only if all underlying domains contain atomic values only.

A relation is in 2nd normal form if and only if it is in 1NF and every nonkey attribute is fully dependent on the primary key.

A relation is in 3rd Normal form if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.

A relation R in 4th normal form if and only if , whenever there exists a multi-valued dependency in R, say A ->-> B (attribute B is multi-dependant on attribute A), then all attributes of R are also functionally dependent on A .

A relation R is said to be in fifth normal form also called Projection Join Normal form (PJNF) if and only if every join dependency in R is implied by the candidate keys of R.

6. Do you know ODBC?

A. ODBC is Open-database Connectivity. It provides access to any database which has a

ODBC database driver .

The database can be a client/server database that conform to ODBC standards

eg. SQL Server , Oracle etc.

An ODBC driver-manager provides an interface from the host language to the specific

Back-end data source driver. The driver manager is responsible for :

· Loading the remote database drivers specified in the DSN entry

· Initializing the interface

· Providing entry points to driver entry points

· Validating parameters and managing serialization of ODBC functions

ODBC driver is a DLL that can interface with a specific back-end database engine

Eg. MS-SQL server , Oracle , sybase etc.

An ODBC driver takes advantage of specific back-end features such as cursors or parameterized queries . It can also implement some other features not supported by the back-end. If the driver doesn’t support a specific feature it returns an error stating that it doesn’t support the feature .

7. What are the factors that influence ODBC response time ?

A. The main factors that influence the ODBC response time are :

· Network load

· The complexity of the SQL queries

· The speed of the machine on which the server executes

· The RAM of the machine on which the server executes

· The number of records that are likely to be returned as result of the query.

· The physical location of the database and number of connections to database

8. In how many ways can VB implement ODBC ?

A. VB can implement ODBC in the following ways :

· Using Data control and connecting to a database like SQL Server , Oracle

· Using DAO objects and using the dbUseODBC parameter in the CreateWorkspace methods ie. Using ODBC Direct

· Using Remote Data Control

· Using Remote Data Objects

· Using ODBC API calls

9. What is OOPS ?

A. OOPS is Object Oriented Programming. The main features of OOPS are :

Data hiding & encapsulation

Polymorphism

Inheritance

Data Abstraction

The advantages of OOPS over structured programming are :

· Data hiding

· Code reusability

· Lower costs in building systems

· Flexibility

10. How VB 5.0 implements OOPS ?

A. VB implements OOPS concepts in the following ways :

· Data hiding is implemented using the “Public , Private scoping keywords “

· Polymorphism is implemented using the “Implements “ , ParamArray , Optional keywords

· Inheritance is implemented in the sense of interface inheritance and not implementation inheritance (ie. Not like C++ inheritance)