SQL规范化

Question

现在，我有一张 table :

Id  -  CollegeName   -   CourseName

此表未标准化，所以我每 1 所大学都有很多类(class)
我需要将其标准化为两个表:

Colleges:  CollegeID  -  CollegeName  
Courses:   CourseID  - CollegeID  - CourseName 

有没有简单的方法来做到这一点？
谢谢

Answer 1

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