Student Record Keeping System

Again this is only a rough and very basic student record keeping system done to illustrate the concepts of third normal form within a relational database.

Information needed:

Student id#
Name
Major code
Major name
Department chair in major
Address and phone information
Date started at college
Date graduated
Degree awarded
Courses taken - for each course need

Course number
Course name
Credits
Semester/year the course was taken
Grade the student got in the course

Upon analyzing this, we discover we needed:

a student file that contains information about the student
a major file that contains the major name and department chair
a course file that contains the course number and name of all courses offered by the college
a student course file that contains the courses taken by students

We needed to remove everything about the major except the major code, we needed to remove the course name and we needed to remove the repeating groups (the courses the students have taken) to put this into third normal form.

Student File	Student Course File	Course File	Major File

Student id# = KEY	Student id# + Course # + Semester/year taken = KEY	Course # = KEY	Major code = KEY
Name		Course Name	Major name
Major code		Credits	Dept chair
Address
Phone	Grade
Date started college
Date graduated
Degree awarded

Note:

Relative to the course file, discussed the problem if the name and credits change over time and the need to carry time information as part of the key. This was not included.
The information about section number and instructor are not included in this example.