Data Abstraction : Why & levels

A DBMS is a collection of interrelated data and a set of programs that allows users to access the data and modify the data.

A purpose of a DBMS is to provide the users with an abstract view of
data so for that proposed system hides certain details of how the data
is stored and maintained.

For the system is to usable it must retrieve data efficiently.This
concern led to the design of complex data structures for the
representation of data in the database.

Since many database users are not computer trained , developers hide
the complexity from users through different levels of abstraction to
simplify users interaction with the system.

1.Internal Level ( Physical Level ) :- It is
the one closest to the physical storage devices.It describes how data
are actually stored on the storage medium such as hard disk , magnetic
tapes etc.

2.Logical Level :- It describes what data are
stored in the database and what relationships exists among the data.The
entire database is described in terms of a small structures.This level
is used by the DBA who must decide what information is to be kept is the
database.

3.View Level ( External Level ) :- The
highest level of abstraction describes only a part of the entire
database. Despite the use of the simple structures at the logical level ,
some complexity remains due to the large size of the database.The
System may provide different views for same database.

Pawan Kumar

Pawankkmr@hotmail.com

Normal Forms : Lists and Definitions

1.First Normal Form ( 1NF ) [ Eliminate repeating groups ] :-
When a table has no repeating groups it is said to be in first normal
forms.Means for each cell in a table there can be only one value.This
value should be atomic i.e it cannot be decomposed there.The values
should be simple and indivisible into smaller pieces.

Multivalued attributes , composite attributes , nested relations are split such that they can be made atomic.

-Their should not be any duplicate columns in a table.

2.Second Normal Form ( 2NF ) [ Eliminate redundant data ] :-
If an attribute depends on only part of a multi-valued key, we have to
remove it to a separate table.A table is in second normal form if every
non key column depends on the entire key , just just part of it. The
issue arises only for composite key attribute with multiple columns.

A table is in the second normal form if all of its non key fields are fully dependent on the whole key.

3.Third Normal Form ( 3NF ) [ Remove all columns which are not dependent on the Primary Key ]
:- A relation is in third normal form if it is in 2nd normal form and
non prime attribute is functionally dependent on the other non prime
attribute.

3rd normal form is applied when the relations are in 2NF and there is
any non key attribute that transitively depends on primary key.

I will provide detailed examples on all the normal forms given above.

-Pawan Kumar

Pawankkmr@hotmail.com

Normalization : Definition and basics

1.Normalization is the process of designing a data model that efficiently store that data in a database.

2.Normalization is the process of simplifying the relationships.

3.It is also called as process of database refinement.

4.Normalization is carried for following reasons.

a) Simple retrieval of data in response to query.

b) Simple data maintainence.

c) Simplify maintainence of data through insertion , updation and deletions.

d) Reduce the need to restructure / reorganize data in case of new requirements.

5.Normalization reduces redundancy.Redundancy is defined as the
unnecessary repetition of a field.It can cause problems with storage ,
updation and retrieval of data.

6.It is a process of data design based on rules that help build relational databases.

7.Normalization will come to you at some cost because to get data you have apply joins.

Will explain all the normal forms in my next article.

-Pawan Kumar

Pawankkmr@hotmail.com

INDEXED VIEWS WITH SCHEMABINDING

Why indexed views have to be defined with schemabinding ?

Got this answer from an MSDN Article. Its very nice.

So that all objects the view references are unanbigously identified
using schemaname.objectname regardless of which user is accessing the
view and the object referred to in the view definition cannot be altered
in a way that would make the view definition illegal / incorrect or
force SQL SERVER to recreate the index on the view.

Pawan Kumar
Pawankkmr@hotmail.com

Why we have to first create Unique Clustured index on view ?

1.The index has to be unique to allow easy look up of records.

2.Prevention from creating duplicate indexes.

3.The index must be clustured index because only clustured index can enforce uniqueness and store the rows at the same time.

Pawan Kumar
Pawankkmr@hotmail.com