Database Normalization: A Complete Guide to 1NF, 2NF, 3NF, and BCNF

Minhaj Tapader — Fri, 27 Jun 2025 10:19:22 +0000

What is normalization in DBMS?

Database normalization is a process by which the database structure can be designed in a very effective way. Normalization in DBMS reduces the repetition of data and increases the integrity of data. In this process, various relational tables are created and duplicate data is removed from them. As a result, data repetition is reduced and data is not lost when inserting, deleting, updating.

Why is normalization needed?

Suppose you are creating a student information system. There is only one table in which all the information of the student (name, course, teacher) is stored. Now if you want to delete the information of a teacher, then the student's info along with the teacher and the course info will also be deleted. We will normalize so that the data is not lost during the CRUD operation. Here you have to create a separate table for students, courses, teachers through normalization so that data integrity is maintained while doing CRUD.

Normalization steps:

Normalization is usually carried out in several steps. Each step is called a normalization form (NF). Such as 1NF, 2NF, 3NF, BCNF etc. Below are some of the key steps:

First Normal Form (1NF)

Condition:

The table must not contain any repeating groups.
All column values must be atomic.

Example:

Before normalization

After normalization

The values of the subjects column in the table above are not atomic. Because in this column, different values are placed inside a column with a comma. So the 1NF values are placed in different rows. Now each column has an atomic value that satisfies the condition of 1NF.

Second Normal Form (2NF)

Condition:

The table must be in 1NF.
There must be no partial dependency (non-key columns must depend on the entire primary key).

Example:

Before normalization

After normalization

In the table above, StudentID + CourseID is the composite key. But CourseName does not depend on the entire composite key. It depends only on the CourseID so this is a partial dependency. Similarly, in the case of StudentName, it does not depend on the entire composite key. It depends only on the StudentID so this is also a partial dependency. Now after normalizing this is divided into two tables. The non-key columns in each table are now dependent on the entire primary key, which means there is no longer any partial dependency that meets the 2NF condition.

Third Normal Form (3NF)

Condition:

The table must be in 2NF.
There must be no transitive dependencies (non-key columns cannot be dependent on any other non-key column).

Example:

Before normalization

After normalization

In the table above, StudentID is the primary key that uniquely identifies each row. Here the DepartmentName depends on the DepartmentID and the DepartmentID depends on the StudentID which basically creates a transitive dependency. Because when a non-key column (DepartmentName) depends on another non-key column (DepartmentID), it is called a transitive dependency. Since there is a transitive dependency, two tables have been created by normalizing this table.

Boyce-Codd Normal Form (3.5NF)

Condition:

The table must be in 3NF.
Each determinant must be a candidate key. A determinant is a column or set of columns that determines another column.

Example:

Before normalization

After normalization

In the table above, StudentID + CourseID is the candidate key. Functional dependency is CourseID- > Instructor but here CourseID is not a candidate key but it is part of the candidate key so it violates the condition of BCNF. So now two new tables have been created through normalization so that the Determinant is always the candidate key. Actually, BCNF is an updated version of 3NF.

Benefits of normalization:

Reduces data redundancy.
Maintains data consistency.
It is easy to update the data.
Storage can be saved.

Socket.IO for Beginners: Build a Live Online Users Tracker with MERN

Minhaj Tapader — Thu, 19 Jun 2025 22:24:02 +0000

What is Socket.IO?

Socket.IO is a JavaScript library used for real-time data exchange between client and server.

Why should you use Socket.IO?

You can use Socket.IO when you need to implement real-time features in your project. With this, you can update the data in real time. Once connected, both the client and the server can exchange data in real time. Socket.IO uses Event based communication. For example, you can create an event and then send and receive it to both the client and the server. With the help of Socket.IO you can handle hundreds of connections if you want. Socket.IO can be used in various places such as: chat applications, online games, real-time notifications, etc.

Required Setup:

You need to have a basic understanding of JavaScript, React.js, Node.js, Mongoose, and npm.
VS Code and Node.js must be installed.

Project Setup:

Now I will create a folder named project. This will be the main project folder. Inside it, I will create two more folders named client and server.

Creating a project directory:

mkdir project
cd project
mkdir server
mkdir client

Create Backend:

cd server
npm init -y
npm i mongoose express cors dotenv nodemon validator
npm i socket.io (This package is used to implement socket.IO)
code .

Create Express server:

Inside server/index.js, write the server code to initialize the server.

import express from 'express';
import dotenv from 'dotenv';
import { app, server } from './socket/socket.js';
import cors from 'cors';

app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
import connectDB from './config/db.js';
import userRoutes from './route/userRoute.js';

dotenv.config();
connectDB();
const PORT = process.env.PORT || 3000;

app.use('/api/users', userRoutes);
server.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

Inside socket / socket.js Here you need to write the code to initialize the socket and then connect to the server. You can create any real time event within the connection. Here I am just creating an event to see how many people are online after creating the user and sending it to the UI. The getOnlineUsers event will return an array that contains the IDs of the online users.

import express from 'express';
import http from 'http';
import { Server } from 'socket.io';

const app = express();
const server = http.createServer(app);
const io = new Server(server, {
  cors: {
    origin: process.env.CLIENT_URL,
    methods: ['GET', 'POST'],
  },
});
let userConnections = {};
export const getReciverSocketId = (reciverId) => userConnections[reciverId];

io.on('connection', (socket) => {
  console.log('socket', socket.handshake.query);
  const { userId } = socket.handshake.query;
  if (userId) {
    userConnections[userId] = socket.id;
    console.log(`User ${userId} connected and socket id is ${socket.id}`);
  }
  io.emit('getOnlineUsers', Object.keys(userConnections));
  console.log('userConnections', userConnections);
  socket.on('disconnect', () => {
    if (userId) {
      delete userConnections[userId];
      console.log(`User ${userId} disconnected and socket id is ${socket.id}`);
    }
    io.emit('getOnlineUsers', Object.keys(userConnections));
  });
});
export { app, server, io };

Inside model / userModel.js Here I am creating a mongoose model to register the user. To keep it simple, I have created a model with only username and email.

import mongoose from 'mongoose';
import validator from 'validator';

const userSchema = new mongoose.Schema(
  {
    email: {
      type: String,
      required: [true, 'Email is required'],
      unique: true,
      validate: {
        validator: validator.isEmail,
        message: 'Please enter a valid email',
      },
    },

    username: {
      type: String,
      required: [true, 'Username is required'],
      minlength: [3, 'Username must be at least 3 characters'],
    },
  },
  {
    timestamps: true,
  }
);
const User = mongoose.model('User', userSchema);
export default User;

Inside controller / userController.js This is where the user register and all user-related logics are written.

import User from '../model/userModel.js';

export const register = async (req, res) => {
  try {
    const { username, email } = req.body;
    if (!username || !email) {
      return res
        .status(401)
        .json({ message: 'All fields are required', success: false });
    }
    const userExistsEmail = await User.findOne({ email });

    if (userExistsEmail) {
      return res
        .status(401)
        .json({ message: 'User already exists', success: false });
    }

    const user = await User.create({
      username,
      email,
    });

    return res
      .status(201)
      .json({ message: 'User created successfully', success: true, user });
  } catch (error) {
    console.log(error);
  }
};
export const allUsers = async (req, res) => {
  try {
    const users = await User.find({});
    return res
      .status(200)
      .json({ message: 'Users fetched successfully', success: true, users });
  } catch (error) {
    console.log(error);
    return res.status(500).json({ message: 'Internal server error' });
  }
};

Inside route / userRoute.js all the user routes are written. Since I'm only using sockets to see how many users are online, there are just 2 routes here, you can add more routes if you want.

import express from 'express';
import { allUsers, register } from '../controller/userController.js';

const router = express.Router();
router.post('/register', register);
router.get('/all', allUsers);

export default router;

Inside config / db.js here is the function to connect the mongoose database.

import mongoose from 'mongoose';
const connectDB = async () => {
  try {
    const conn = await mongoose.connect(process.env.MONGO_URI);
    console.log(`MongoDB connected: ${conn.connection.host}`);
  } catch (error) {
    console.error(`Error: ${error.message}`);
    process.exit(1);
  }
};
export default connectDB;

Inside.env contains port numbers, client URLs, and database URIs for use in different files.

MONGO_URI=mongodb+srv://yourdbusername:password@cluster0.dfl8qwc.mongodb.net/socketio?retryWrites=true&w=majority&appName=Cluster0
PORT=5000
CLIENT_URL=http://localhost:5173

Create Front end:

cd ..
cd client

Create VIte and setup tailwind:

npm create vite@latest
npm i axios for data fetching from backend
npm i socket.io-client for initialize socket in fontend
npm install tailwindcss @tailwindcss/vite  for styling UI

Inside vite.config.js

import { defineConfig } from 'vite';
import react from '@vitejs/plugin-react';
import tailwindcss from '@tailwindcss/vite';
export default defineConfig({
  plugins: [react(), tailwindcss()],
});

Inside index.css

@import "tailwindcss";
npm run dev

Now inside components folder write Home.jsx file. There are two jobs here. One is to send the data to the server and the other is to render here if there is a user and also show how many online users are there. To see the online users, you have to match the ID of each user using the find method with the online user array.

import React, { useEffect, useState } from 'react';
import axios from 'axios';
const Home = ({ userInfo, setUser, onlineUsers }) => {
  const [formData, setFormData] = useState({ email: '', username: '' });
  const [users, setUsers] = useState([]);

  const handleChange = (e) => {
    setFormData({ ...formData, [e.target.name]: e.target.value });
  };

  const handleSubmit = async (e) => {
    e.preventDefault();
    const res = await axios.post(
      'http://localhost:5000/api/users/register',
      formData
    );
    if (res?.data?.success) {
      localStorage.setItem('user', JSON.stringify(res.data.user));
      setUser(res.data.user);
      fetchAllUsers();
    }
  };
  useEffect(() => {
    fetchAllUsers();
  }, [onlineUsers]);
  const fetchAllUsers = async () => {
    try {
      const response = await axios.get('http://localhost:5000/api/users/all');
      setUsers(response?.data?.users);
    } catch (error) {
      console.error('Error fetching users:', error);
    }
  };

  if (userInfo) {
    return (
      <div className='min-h-screen flex items-center justify-center bg-gradient-to-r from-gray-800 to-black text-white'>
        <div className='bg-gray-900 rounded-xl p-8 shadow-lg w-[90%] max-w-md text-center'>
          <h1 className='text-3xl font-bold mb-4'>
            Welcome, {userInfo.username} 👋
          </h1>
          <div className='mt-10 text-left'>
            <h2 className='text-xl font-semibold mb-4'>All Users</h2>
            <ul className='space-y-3'>
              {users.map((u, index) => {
                const isOnline = onlineUsers.find(
                  (onlineUser) => onlineUser === u._id
                );
                return (
                  <li
                    key={index}
                    className='flex items-center justify-between bg-gray-800 p-3 rounded-lg'
                  >
                    <div>
                      <p className='font-medium'>{u.username}</p>
                      <p className='text-sm text-gray-400'>{u.email}</p>
                    </div>
                    <span
                      className={`w-3 h-3 rounded-full ${
                        isOnline ? 'bg-green-400' : 'bg-gray-500'
                      }`}
                      title={isOnline ? 'Online' : 'Offline'}
                    />
                  </li>
                );
              })}
            </ul>
          </div>
        </div>
      </div>
    );
  }

  return (
    <div className='min-h-screen flex items-center justify-center bg-gradient-to-r from-gray-800 to-black text-white'>
      <div className='bg-gray-900 rounded-xl p-8 shadow-lg w-[90%] max-w-md'>
        <h2 className='text-2xl font-bold mb-6 text-center'>Register</h2>
        <form onSubmit={handleSubmit} className='space-y-4'>
          <div>
            <label htmlFor='username' className='block text-sm mb-1'>
              Username
            </label>
            <input
              type='text'
              name='username'
              id='username'
              value={formData.username}
              onChange={handleChange}
              required
              className='w-full px-4 py-2 rounded bg-gray-800 text-white border border-gray-600 focus:outline-none focus:ring-2 focus:ring-green-400'
            />
          </div>
          <div>
            <label htmlFor='email' className='block text-sm mb-1'>
              Email
            </label>
            <input
              type='email'
              name='email'
              id='email'
              value={formData.email}
              onChange={handleChange}
              required
              className='w-full px-4 py-2 rounded bg-gray-800 text-white border border-gray-600 focus:outline-none focus:ring-2 focus:ring-green-400'
            />
          </div>
          <button
            type='submit'
            className='w-full bg-green-500 hover:bg-green-600 text-white font-semibold py-2 px-4 rounded transition-all'
          >
            Register
          </button>
        </form>
      </div>
    </div>
  );
};

export default Home;

inside the src folder create a file named App.jsx and write the following code. This is where the socket is actually initialized in the frontend. At the same time it checks from localStorage whether a user exists. If a user exists then The user data is passed to the home page using props. After the socket is initialized, the real time event getOnlineUsers which was created on the server is received here. You can receive any event inside socket.on if you want. Here, getOnlineUsers returns an array in which the online users id is stored. With the help of this ID, you can find out who is online.

import { useEffect, useState } from 'react';
import './App.css';
import Home from './components/Home';
import { io } from 'socket.io-client';

function App() {
  const [user, setUser] = useState(() => {
    const stored = localStorage.getItem('user');
    return stored ? JSON.parse(stored) : null;
  });
  const [onlineUsers, setOnlineUsers] = useState([]);
  useEffect(() => {
    if (user) {
      const sock = io('http://localhost:5000', {
        query: { userId: user?._id },
        transports: ['websocket'],
      });
      sock.on('getOnlineUsers', (onlineUsers) => {
        setOnlineUsers(onlineUsers);
      });

      return () => {
        sock.close();
      };
    }
  }, [user]);
  return (
    <>
      <Home userInfo={user} setUser={setUser} onlineUsers={onlineUsers} />
    </>
  );
}

export default App;

You can create many real time update related applications using socket.IO. Today, I have only shown how socket.IO can be used in a MERN stack application. You can add more if you want, such as receiving and sending user messages in a chat application , social media reactions etc.

DEV Community: Minhaj Tapader

Database Normalization: A Complete Guide to 1NF, 2NF, 3NF, and BCNF

What is normalization in DBMS?

Why is normalization needed?

Normalization steps:

First Normal Form (1NF)

Condition:

Example:

Second Normal Form (2NF)

Condition:

Example:

Third Normal Form (3NF)

Condition:

Example:

Boyce-Codd Normal Form (3.5NF)

Condition:

Example:

Benefits of normalization:

Socket.IO for Beginners: Build a Live Online Users Tracker with MERN

What is Socket.IO?

Why should you use Socket.IO?

Required Setup:

Project Setup:

Creating a project directory:

Create Backend:

Create Express server:

Create Front end:

Create VIte and setup tailwind: