nVidia provides linux drivers from its website. Unfortunately it doesn't work with recent kernels. The installation script fails in multiple ways. After much googling and with tons of diverse advices I found the simple solution.

BTW: By default nv driver is installed for nVidia cards which provides very poor graphics performance even for non-gaming usage. Also you cannot use dual monitors with it using TwinView or Xinerama.

You need to install the rpm distribution from livna. However before that you need to ensure that you have the proper kernel for your cpu. Please follow the guide here:
How (& Why) To Install i686 Kernel In Fedora Core 6 in 7 Steps

1. First install the livna repository:
rpm -Uhv http://rpm.livna.org/livna-release-6.rpm

Alternatively you can directly add the following entries to /etc/yum.conf:
[livna-stable]
name=Livna.org Fedora Compatible Packages (stable)
baseurl= http://rpm.livna.org/fedora/$releasever/$basearch/yum/stable
gpgcheck=1

2. Then import the livna GPG key for verification of rpm:
rpm –import http://rpm.livna.org/RPM-LIVNA-GPG-KEY

3. Now you are ready to install the rpm:
yum install kmod-nvidia

This should automatically do everthing required to install the driver.

4. Reboot the machine. You should see nVidia logo flash by.

5. Execute:
glxinfo | grep direct

If you see: direct rendering: Yes, you are good to go. You can also display further details about the installed driver as follows:
yum info kmod-nvidia

You will see something like this:
Loading "installonlyn" plugin
Setting up repositories
Reading repository metadata in from local files
Installed Packages
Name : kmod-nvidia
Arch : i686
Version: 1.0.9755
Release: 2.2.6.20_1.2933.fc6
Size : 7.1 M
Repo : installed
Summary: nvidia kernel module(s)

Use nVidia Display Settings to further configure your driver. Next I will talk about setting twin monitors on Fedora Core 6.