Normalization Basics

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. 

The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF).

First Normal Form (1NF)

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

• Eliminate duplicative columns from the same 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)

Second normal form (2NF) further addresses 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)

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.

Boyce-Codd Normal Form (BCNF or 3.5NF)

The Boyce-Codd Normal Form, also referred to as the "third and half (3.5) normal form", adds one more requirement:

• Meet all the requirements of the third normal form.
• Every determinant must be a candidate key.

Fourth Normal Form (4NF)

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.

De-normalization

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly.

A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.


Denormalization of Database! Why?

Only one valid reason exists for denormalizing a relational design - to enhance performance. However, there are several indicators which will help to identify systems and tables which are potential denormalization candidates. These are:

* Many critical queries and reports exist which rely upon data from more than one table. Often times these requests need to be processed in an on-line environment.
* Repeating groups exist which need to be processed in a group instead of individually.
* Many calculations need to be applied to one or many columns before queries can be successfully answered.
* Tables need to be accessed in different ways by different users during the same timeframe.
* Many large primary keys exist which are clumsy to query and consume a large amount of DASD when carried as foreign key columns in related tables.
* Certain columns are queried a large percentage of the time. Consider 60% or greater to be a cautionary number flagging denormalization as an option.

What is DBMS and Relational DBMS?

A Database Management System (DBMS) is a software package with computer programs that control the creation, maintenance, and the use of a database. It allows organizations to conveniently develop databases for various applications by database administrators (DBAs) and other specialists. A database is an integrated collection of data records, files, and other database objects. A DBMS allows different user application programs to concurrently access the same database. DBMSs may use a variety of database models, such as the relational model or object model, to conveniently describe and support applications. It typically supports query languages, which are in fact high-level programming languages, dedicated database languages that considerably simplify writing database application programs. Database languages also simplify the database organization as well as retrieving and presenting information from it. A DBMS provides facilities for controlling data access, enforcing data integrity, managing concurrency control, recovering the database after failures and restoring it from backup files, as well as maintaining database security.

 

A short definition of an RDBMS is: a DBMS in which data is stored in tables and the relationships among the data are also stored in tables. The data can be accessed or reassembled in many different ways without having to change the table forms.

The main difference between the DBMS and RDBMS is we can establish the relation between the tables in RDBMS.in case of DBMS we cannot establish relation between tables.

DBMS does not impose any constraints or security with regard to data manipulation it is user or the programmer responsibility to ensure the ACID PROPERTY of the database RDBMS is more with this regard bcz rdbms define the integrity constraint for the purpose of holding ACID PROPERTY.

DBMS is a set of program that controls the organization storage, management and retrieval of data in a database. RDBMS is a relational database management syatem is database management system based on the relational model.

What is DBMS?
A database managemant system (DBMS) contains a set of programs which helps users to construct and maintain a database. The DBMS is hence a common-purpose software system that helps the process of defining, creating and manipulating databases for different purposes. Defining a database includes opting of the data types, structures and constraints for the data to be saved in the database. Constructing the databsae is the process of storing the data itself on some storage devices which is controlled by the DBMS. Manipulating a database contains such functions as querying the database to grab required data, updating the database and building reports from the data.


What is RDBMS?
A database system is divided into small modules that to manage with each of the responsibilities of the entire system. Some of the functions of the database system may be done by the computer’s OS itself.

Installing the Server of Microsoft SQL Server

1. Start the computer
2. Log in using an account with administrative rights.If the computer you are using is connected to a network, make sure you specify the domain. If you are logged in locally, make sure you use the name of the computer.
   If you are installing in Microsoft Windows XP-7, make sure you log in with a password, otherwise the installation will fail.
   For our installation, we will log in as Administrator (in either Microsoft Windows 7 Professional/Ultimate or in a domain named functionx.local)
3. Insert the DVD in the drive. You may (should) receive a dialog box with a Run SETUP.EXE link:
   
   
   
   If so, click Run SETUP.EXE
4. The installation will start. You may get a message that the .NET Framework needs to be installed. If you get that message, click OK. You will be prompted to start the installation:
   

   
   
   After clicking the ACCEPT button, you can click Install. The installation of the .NET Framework would start. When the installation of the .NET Framework is over, you would receive a message:
   

   
   
   From there, you can click Exit. If you are installing in either Microsoft Windows XP or Microsoft Windows Server 2003, you may be asked to install a hotfix:
   

   
   
   

   
   
   In this case, accept to install it by clicking Next and following the wizard. When the installation is over, you may be asked to restart:
   

   
   
   Which you should do.
5. To start the installation, put the Microsoft SQL Server 2008 DVD in the drive. A window would come up:
   
   
   
6. In the left section, click Installation
   
   
   
7. If you are only interested in studying database design and development in Microsoft SQL Server, on the right side, click New SQL Server Stand-Alone... The installation would start:
   
   
8. After the message box, click OK
   
   
   
9. Another message box would come up. When it finishes, the next page of the wizard would come up. If you are using the evaluation version, read the message and click the first radio button.
   
   
   
   If you are installing with a product key, click the second radio button and enter the key
   
   
   
10. Click Next
11. The License Terms page is presented to you. Read it. If you don't agree with the license, click Cancel. Otherwise, click the I Accept check box:
    
    
    
    
    
12.  Click Next
    
    
    
13. Read the messages and click Install:
    
    
    
14. You may receive a warning message about the firewall:
    
    
    For now, ignore it (there is no true justfication in trying to disable the firewall, the installation will proceed fine).
15. If you are installing on the only computer you use as your server, you may receive a warning that it is not recommended to install Microsoft SQL Server on a domain controller:
    
    
    
    
    
    
    
    Ignore the warning for now.
    Click Next
    (We are not encouraging you to ignore warnings; just these ones)
16. If you had previously installed Microsoft SQL Server Express, you may receive a message box asking you to specify an instance name. If so, specify it as MSSQLSERVER.
    The next page allows you to specify what you want to install and what would be left out. For our example, we select all, then removed Reporting Services.
    
    The eventual location of Microsoft SQL Server is specified in the bottom text box:
    
    
    
    If you are installing the Developer Edition and if you to change the directory of Microsoft SQL Server, you can type it in the Shared Feature Directory text box, or you can click the browse button to select it.
    
    
    
    If you are installing the Enterprise Edition, if you to change the directory of Microsoft SQL Server, click the broser button on the right side of the Shared Feature Directory text box and locate the directory you want.
    
    After making your selections, click Next
17. The next page allows you to name the instance of Microsoft SQL Server (and to specify where the server would be installed). In most cases, you should accept the suggested name of the instance as MSSQLSERVER:
    
    
    
    After making your selections, click Next
18. The next page mostly allows you to check and confirm the disk space:
    
    
    
    After checking it, click Next
19. The next page allows you to specify the account that is performing the installation:
    
    
    
    Click Use the Same Account For All Server Services
20. A dialog box would come up.
    If you are installing in Microsoft Windows 7 and you want to use a local account, in the Account Name text box, enter the name of the computer, followed by a back slash, followed by the user name, and press Tab. In the Password text box, enter the password:
    
    
    
    If you are installing in Microsoft Windows Server 2008, in the Account Name text box, enter the name of the domain, followed by a back slash, followed by the user name, and press Tab. In the Password text box, enter the password:
    
    
    
21. Click OK
    
    
    
    
22. Click Next
23. After making your selections, click Next
24. In the next page, specify how authentication would be made. You should also specify the account used as the administrator. To do this, you can click Add Current User:
    
    
    
25. Microsoft SQL Server provides a default user named sa. To prepare that user for database authentication, click the Mixed Mode
26. Type a password in the Enter Password text box. For this exercise, you can use P@assword1 anad press Tab
27. Enter the same password in the Confirm Password text box To add an additional account, click Add...
    A dialog box would display. In the bottom text box, type the (complete) user name of a user and click Check Names. In the same way, you can add as many users as possible
    
    
    
28. Click Next
29. The next step allows you to specify an account for the analysis services. Click Add Current User
    
    
    
    To add an additional account, click Add... In the bottom text box, type the (complete) user name of a user and click Check Names. In the same way, you can add as many users as possible
    
    
    
30. Click Next
31. In the Reporting Services Configuration page, click the last radio button to install but without configuring the reporting:
    
    
    
32. Click Next.
    The next page allows you to specify whether you want and installation report to be sent to Microsoft:
    
    
    
33. Read it and click Next
34. The next page gives you a summary of tests that were performed before the installation:
    
    
    
35. After reading, click Next.
    This would indicate that the installation is ready to proceed:
    
    
    
36. To start the installation, click Install. The installation would start and a progress bar would indicate the evolution. This can take a while
    
    
    
    
37. When this phase is over, a message box will let you know. After reading it, you can click Next. The last page of the wizard should announce that the installation was complete
    
    
    
38. After reading the message, click Close

Introduction To MS Sql Server

Microsoft SQL Server is an application used to create computer databases for the Microsoft Windows family of server operating systems. Microsoft SQL Server provides an environment used to generate databases that can be accessed from workstations, the Internet, or other media such as a personal digital assistant (PDA).

If you are just learning, you can use a computer that runs Microsoft Windows XP Professional, Microsoft Windows Vista, Microsoft Windows 7 (Professional or Ultimate), Microsoft Windows Server 2003, or Microsoft Windows Server 2008.

Before installing Microsoft SQL Server 2008, make sure you have installed all service packs on your operating system.

Introduction

Hi all the Workaholic Developers, I am starting a new blog on SQL Programming where I would try my best to add all the Ifs and Buts of SQL server Programming features.

Happy Coding!!