I would like to design a table for college mgmt system,for that I created a table(CollegeDetails) such a way that, it stores the college code(unique key),College name, course offered( EEE ,ECE,CSE…)…
我想为大学管理系统设计一个表,因为我创建了一个表(CollegeDetails)这样的方式,它存储大学代码(唯一键),学院名称,课程提供(EEE,ECE,CSE ...)...
CollegeDetails
CollegeCode CollegeName CourseOffered
--------------------------------------------
1000 xyz EEE,ECE,CSE
1001 ABC MECH,AERO,EEE
Here I am facing a problem. Each college contains many numbers of courses, for eg .xyz college offers 5 courses and abc college offers 8 courses… But I am not able to store in a single table, so I created two tables CollegeDetails and CourseOffered….
我在这里遇到了一个问题。每个学院都包含许多课程,例如.xyz学院提供5门课程,abc学院提供8门课程......但我无法存储在一张桌子中,所以我创建了两个表CollegeDetails和CourseOffered ....
CollegeDetails
CollegeCode CollegeName
--------------------------
1000 xyz
CourseOffered
CollegeCode CourseOffer
-------------------------
1000 EEE
1000 ECE
1001 EEE
But the CourseOffered table is getting duplicated(both the columns). Pls help to solve this …. I am using oracle 10g….
但CourseOffered表正在重复(两列)。请帮助解决这个问题....我正在使用oracle 10g ....
2 个解决方案
#1
1
A quick hack to keep duplicates out of the courseoffered table is to create a unique composite index on collegecode and courseoffer:
快速破解将重复数据保留在课程表中是为了在大学代码和课程上创建一个独特的复合索引:
CREATE UNIQUE INDEX <index_name> ON courseoffered(collegecode, courseoffer);
That does nothing about whatever process/logical problem you have that is inserting the same key multiple times, but putting the index on it will certainly point it out to you when you try to insert a duplicate, and hence where the problem is.
这对于你多次插入相同密钥的过程/逻辑问题没有任何作用,但是当你尝试插入副本时,将索引放在它上肯定会指出它,因此问题出在哪里。
#2
1
You could normalize it further by creating a Courses table, then linking to the Courses PK in your CourseOffered table. That way, you are storing the course name, e.g. 'EEE', in a single place.
您可以通过创建Courses表,然后链接到CourseOffered表中的Courses PK来进一步规范化它。这样,您就可以存储课程名称,例如'EEE',在一个地方。
#1
1
A quick hack to keep duplicates out of the courseoffered table is to create a unique composite index on collegecode and courseoffer:
快速破解将重复数据保留在课程表中是为了在大学代码和课程上创建一个独特的复合索引:
CREATE UNIQUE INDEX <index_name> ON courseoffered(collegecode, courseoffer);
That does nothing about whatever process/logical problem you have that is inserting the same key multiple times, but putting the index on it will certainly point it out to you when you try to insert a duplicate, and hence where the problem is.
这对于你多次插入相同密钥的过程/逻辑问题没有任何作用,但是当你尝试插入副本时,将索引放在它上肯定会指出它,因此问题出在哪里。
#2
1
You could normalize it further by creating a Courses table, then linking to the Courses PK in your CourseOffered table. That way, you are storing the course name, e.g. 'EEE', in a single place.
您可以通过创建Courses表,然后链接到CourseOffered表中的Courses PK来进一步规范化它。这样,您就可以存储课程名称,例如'EEE',在一个地方。