From ERD to SQL: Implementing a Normalised Database Step-by-Step

If you’re working towards a Degree in Computer Science or Software Development, I bet you have looked at a messy spreadsheet and have heard someone say, “Normalise it.” It might sound like some boring academic exercise, but it is, in fact, the most important skill in a Database Developer’s arsenal.

The biggest challenge seems to be when you go from designing a diagram (ERD) into an actual SQL database. You get the general idea, but making it into solid SQL statements with all the appropriate Primary and Foreign Keys can be like trying to assemble a jigsaw puzzle without all the pieces.

Today, we’re going to cut through all the technical jargon and show, in very basic steps, how we can take a messy data sample and turn it into a beautiful, clean, 3NF-compliant Oracle SQL Database.

The "Messy" Starting Point: Unnormalised Form (UNF) 🗑️

Imagine we are building a database for a local college. We start with a flat file or a spreadsheet that looks like this:

This is a nightmare. We have Redundancy (John Smith’s name is repeated) and Update Anomalies (If Dr. Brown leaves, we have to change the name in every single row).

Step 1: 1NF (Atomic Values & Unique Keys) ⚛️

To reach First Normal Form (1NF), we must ensure every cell contains a single value (atomicity) and that every row is unique.

Rule: No repeating groups or multi-valued attributes.

Result: Our table is technically in 1NF because each cell has one value, but the redundancy is still there. We identify our Composite Primary Key: StudentID + CourseID.

Step 2: 2NF (Removing Partial Dependencies) ✂️

This is where students often struggle and where I did when i first learned the concept. Second Normal Form (2NF) is about making sure every non-key column depends on the entire Primary Key.

In our 1NF table, StudentName only depends on StudentID. It doesn't care about the CourseID. This is a Partial Dependency.

Action: We break the table apart.

Table 1 (Student): StudentID (PK), StudentName.

Table 2 (Course_Enrollment): StudentID (FK), CourseID (FK), Grade.

Step 3: 3NF (Removing Transitive Dependencies) 🧹

Now we look at Third Normal Form (3NF). Here, we remove Transitive Dependencies. This means a non-key column shouldn't depend on another non-key column.

In our current structure, LecturerName depends on CourseID, but CourseName also depends on CourseID. If the lecturer is tied to the course, we need to move them out to keep the data clean.

Final Entities:

Student: ID, Name.

Course: ID, Title, LecturerID.

Lecturer: ID, Name.

Enrollment: StudentID, CourseID, Grade.

The Translation: From Logic to SQL 🛠️

Now that we have our clean, 3NF logic, we need to write the Oracle SQL to build it. As a Lead Developer, I expect to see strict constraints to protect Data Integrity.

1. The Student Table

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR2(100) NOT NULL
);

2. The Lecturer Table

CREATE TABLE Lecturer (
    LecturerID INT PRIMARY KEY,
    LecturerName VARCHAR2(100) NOT NULL
);

3. The Course Table (Linking to Lecturer)

CREATE TABLE Course (
    CourseID VARCHAR2(10) PRIMARY KEY,
    CourseName VARCHAR2(100) NOT NULL,
    LecturerID INT,
    CONSTRAINT fk_lecturer 
        FOREIGN KEY (LecturerID) 
        REFERENCES Lecturer(LecturerID)
);

4. The Enrollment Table (The "Bridge" Table)

This table handles the Many-to-Many relationship between Students and Courses.

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID VARCHAR2(10),
    Grade CHAR(1),
    PRIMARY KEY (StudentID, CourseID), -- Composite PK
    CONSTRAINT fk_student 
        FOREIGN KEY (StudentID) 
        REFERENCES Student(StudentID),
    CONSTRAINT fk_course 
        FOREIGN KEY (CourseID) 
        REFERENCES Course(CourseID)
);

Why This Matters for Your Project 🎓

When you submit your Database unit, don't just hand in the SQL. Show the transition.

Show the Messy Data: Prove you identified the problems.

Show the ERD: Use a tool like LucidChart to visualize the 3NF structure.

Explain the Constraints!

In your report, mention why you used NOT NULL or REFERENCES. This shows you aren't just copy-pasting code; you’re managing Referential Integrity.

By following this step-by-step pipeline, you ensure that your database is efficient, scalable, and - most importantly - easy to query using CRUD operations.