table normalization

[netJP12L]

I tried my best to Normalize the tables but someone suggested me there is something wrong. Could anyone please help me out. COURSE[CourseCode, CourseName] INSTRUCTOR[InstructorNumber, InstructorName] CLASS[CourseCode, ClassCode, InstrNo, InstrName, CourseStartDate] ENROLLEMENT[CourseCode, ClassCode, StudentNumber, StudentName,Grade] Relationships: The relationship I have defined are as below. Instructor and Class tables: 1 to many. Class and Course: 1 to many Thanks

[_Damian S_]

Generally speaking, each data item (that is not a primary or foreign key) should only appear in one table, otherwise you will have referential integrity issues. Course table - looks fine. Instructor table - looks fine. Class table - Probably doesn't need a composite key between ClassCode and CourseCode, however I assume InstrName is the Instructor name - this doesn't need to be in there, as it's in the Instructor Table. Enrollment table - Students enrol in a class. You probably need a StudentNumber and a ClassCode, and a Grade. This of course depends on changes you make to your class table. Add a Student table in - StudentNumber, StudentName etc etc. Hope this points you in the right direction.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[Mycroft Holmes]

I suggest you get your naming convention sorted first, decide whether you are going to use CourseCode, CourseNo, CourseID and stick to it religiously, your life will be much happier. As a principle I avoid composite keys, this is a personal choice, same as I wash my hands after taking a pee. I would have class with a primary key of classid and a foreign key to course unless it is a many to many in which case you need another table ClassCourseLink Course is ok Instructor is ok Can a class only have 1 instructor - are you sure, what about relief instructors Can a class be in only 1 course, then use a many to many table Enrolment should be Student > Class no refernce to course

[Jorgen Andersson]

Mycroft Holmes wrote:

As a principle I avoid composite keys

Composite indices can on the other hand under certain circumstances considerably speed up a query.

[netJP12L]

Thanks you fellows for your guidance. I have corrected alot of mistakes I had made earlier. I have revised and resposted my work again. I am not sure of the Class and Enrollement tables relationship. Isn't whenever the Foregin Key is involved it becomes 1 to many relationship. Please correct me. Also, shouldn't the CourseStartDate and CourseExpiryDate be in the Class table and student enrolledDate in Enrollement table. STUDENT[StudentID(PK), StudentFName] COURSE[CourseID(PK), CourseName] INSTRUCTOR[InstructorID(PK), InstructorName] CLASS[ClassID(PK),CourseID(FK), InstructorID, Location, CourseStartDate,CourseExpiryDate] ENROLLEMENT[ClassID(PK), StudentID(PK),Grade, EnrolledDate] Relationships are as follows: Course and Class: 1 to many Class and Instructor: 1 to many.

modified on Thursday, November 26, 2009 11:34 AM

[The Man from U N C L E]

Looking good, however. It looks like your CourseStartDate and CourseExpiryDate should be on your course table. The classes probably have thier own dates within the course date range, unless they should be the ClassStartDate and ClassExpiryDate. Personaly I would prefer to call it the EndDate rather than ExpiryDate. 'ExpiryDate' makes it sound like the class will begin to smell if you leave it in the cupboard to long. :-D

If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) [My Articles]  [My Website]

[Mycroft Holmes]

netJP12L wrote:

Isn't whenever the Foregin Key is involved it becomes 1 to many relationship

You are correct! A many to many relationship requires a link table. Your structure loks good and I have yet to run across a smelly table no matter what the fields are named.

Never underestimate the power of human stupidity RAH

[Mycroft Holmes]

Jörgen Andersson wrote:

Composite indices can on the other hand under certain circumstances considerably speed up a query

Ah but I was being derogetry about composite KEYS not indexes. Composite indexes are a basic tool for tuning a database, composite keys annoy me.

Never underestimate the power of human stupidity RAH