SQL规范化

发布于 2024-10-11 14:29:54 字数 275 浏览 2 评论 0原文

现在，我有一张表：

Id  -  CollegeName   -   CourseName

这张表没有标准化，所以我每 1 所大学都有很多课程
我需要将其规范化为两个表：

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

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

原文

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:  CollegeID  -  CollegeName  
Courses:   CourseID  - CollegeID  - CourseName

Is there an easy way to do this?
Thank you

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

殊姿 2024-10-18 14:29:54

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

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

回复收藏 0 原文

水溶 2024-10-18 14:29:54

如果您使用 Colleges.CollegeID 和 Courses.CourseID 作为自动编号字段创建 2 个新表，则可以使用：

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 ;

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 ;

回复收藏 0 原文