SQL normalization

right now, i have a table:

Id  -  CollegeName   -   CourseName

this table is not normalized so i have many Courses for every 1 College

I need to normalize this into two tables:

Colleges:  Coll开发者_如何学GoegeID  -  CollegeName  
Courses:   CourseID  - CollegeID  - CourseName 

Is there an easy way to do this?

Thank you

CREATE TABLE dbo.College
(
  CollegeId     int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  CollegeName   nvarchar(100) NOT NULL
)

CREATE TABLE dbo.Course
(
  CourseId      int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  CollegeId     int NOT NULL,
  CourseName    nvarchar(100) NOT NULL
)

ALTER TABLE dbo.Course
  ADD CONSTRAINT FK_Course_College FOREIGN KEY (CollegeId)
  REFERENCES dbo.College (CollegeId)

--- add colleges
INSERT INTO dbo.College (CollegeName)
SELECT DISTINCT CollegeName FROM SourceTable

--- add courses
INSERT INTO dbo.Course (CollegeId, CourseName)
SELECT
  College.CollegeId,
  SourceTable.CourseName
FROM
  SourceTable
INNER JOIN
  dbo.College ON SourceTable.CollegeName = College.CollegeName

If you create the 2 new tables with Colleges.CollegeID and Courses.CourseID as auto numbered fields, you can go with :

INSERT INTO Colleges (CollegeName)
  SELECT DISTINCT CollegeName 
    FROM OLdTable ;

INSERT INTO Courses (CollegeID, CourseName)
  SELECT Colleges.CollegeID, OldTable.CourseName 
    FROM OldTable
      JOIN Colleges
        ON OldTable.CollegeName = Colleges.CollegeName ;

I agreed with @Andomar's first comment: remove the seemingly redundant Id column and your CollegeName, CourseName table is already in 5NF.

What I suspect you need is a further table to give courses attributes so that you can model the fact that, say, Durham University's B.Sc. in Computing Science is comparable with Harvard's A.B. in Computer Science (via attributes 'computing major', 'undergraduate', 'country=US, 'country=UK', etc).

Sure.

Create a College table with a college_id (primary key) column, and a college_name column which is used as a unique index column.

Just refer to the college_id column, not college_name, in the Course table.