Database Design for College Dept

I had previously posted this question. I am trying to do a class project. I want to build a database for the computer department of a college. Any help would be appreciated. Below are the tables I have created so far. The tables I am having problem with are the course table not quite sure if creditvalue and courseduration should be in this table. Also having some problem with the enrollment table - don't know if I have it set up properly.

Student table
StudentID (int 4) – primary key not null
SFirstName (nvarchar 20) not null
SLastName (nvarchar 20) not null
SContactNumber (varchar 20) not null
SAddress (nvarchar 20)
SCity (nvarchar 20)
SState (varchar 2)
SZipCode (varchar 5)

Course table
CourseID (int) – primary key not null
CourseTitle (nvarchar)
CreditValue (smallint)?
CourseDuration

Offering table
OfferingID (int) – primary key not null
InstructorID – foreign key not null
CourseID (int) foreign key not null
StartDate (datetime)
EndDate (datetime)
DateWkHeld (datetime)
LabNumber (smallint)
Section
HrsHeld
CourseFee –

Enrollment Table
OfferingID (int) foreign key
CourseID (int) foreign key not null
StudentID (int) foreign key not null
EnrollmentDate (datetime)
NumGrade (smallint)
Pass/Fail (bit)
CourseLoad(FT/PT) (bit)
TypeClassID (int) key would have one of 2 values: 1 or 2 1=MAC 2=PC)
OnlineClass/InPerson (bit)
CreditClass/NonCreditClass (bit)
CourseFee
CourseFeePayDate

Instructor table
InstructorID (int) primary key not null
IFirstName (nvarchar 20) not null
ILastName (nvarchar 20) not null
IContactNumber varchar 20) not null
IAddress (nvarchar)
ICity (nvarchar 20)
IState (varchar 2)
IZipCode (varchar 5)

Just my opininon, but:

I'd keep creditvalue and courseduration in the course table, I'd also move HrsHeld and CourseFee from offering to the course table. Not sure what you mean by section so you may or may not want to move it as well. I'd also probably move all of the following from enrollment to the offering table:

TypeClassID
OnlineClass/InPerson
CourseFee (possibly part of course instead)
CourseFeePayDate

Not sure about: CourseLoad(FT/PT), is that relating to the student or the course? Either way I don't think it should be in offering.

The key to table design is to think of the tables as distinct objects, only place columns in the table that fit the "idea" for the table. You can then build relationship tables to resolve the many to many relationships that will form. Its a bit of a black art to design without over normalizing and without killing the performance. Also it is perfectly ok to do multiple iterations on the design. The design will never be perfect the first time around and the design will need to be changed as performance/programming issues arise.

Good luck,

Eric

I haven't gone through the tables too thoroughly but I think your course table looks ok.

The best thing to do is either build the database 3 or 4 differnet ways and pick the best one.

Usually time does not permit such luxury.
Second best (or maybe even better) is to apply a good set of rules or even some hueristic where the special cases are few and can be worked out at the end.

If you try to normailize everything to 3rd normal form your not far from correct most of the time.

Here is my simplistic view of what 3rd normal form is. (I may be wrong or slightly vauge, but then lets call it the hueristic not the rule version)

Make sure you have a coulmn or columns that can be used as a key to usinquely indentify every row without ambiguity.

Make sure any data that repeatedly appears in several different rows eg. suburb names. gets taken out and placed in a new table and referneced via a key eg. postcode.

Make sure all fields in each table depend directly on the key column/columns and not on each other. Again suburb name would be moved out of a record for a person and their address here because a persons address would be assoicated with a postcode and a the postcode would be associated with a suburb name
(or perhaps the reverse, the suburb may be kept with the person and the postcode and suburb name in another table)

In your case the Instructor table has a state field which should probably removed and placed in its own table because you can work out the state from the zipcode. But you cant work out what state it is if you know the city because some cities in different states have the same name.

Also in your student table I would be inclined to move the student details out into another table. ie. contact number, address etc. as these do not rely on the firstname or lastname but only on the person id, so they should be moved out to a student details table with studentId as key. This also allows for the growth of new student detail feilds in your system without affecting other parts that only care about the student name.
Im also in favour or moving phone numbers into their own table so that people can have more than one phone number.

In your course table the credit value column may be fine if all courses have the same type of value. But if some courses are measured in credit points and others are measured in percentages then you would need to rework it so that you can tell which type you are talking about and what the value actually was.

One final note your Enrollment table may need splitting as it seems a little bloated. But all depends on your interpretation of what fields hold. I think things like pass/fail and course load would probably be better out in some other tables of their own.

Don't just take my word for it I may be totally wrong. But remeber
a wise king has many advisers