📖 MySQL Sample Database Design - College Classes
A database at a college must support the following requirements.
FIELDS
We'll start by identifying the fields and functional dependencies.
- For each course the student takes, store the course code, the course description, and the grade earned.
- Also store the number and name of the student's advisor.
- Assume a student can only have 1 advisor, but an advisor can have multiple students.
For a DEPARTMENT, store it's name and number.
- Fields
- DEPT_NAME, DEPT_NUM.
- Functional Dependencies
- DEPT_NUM => DEPT_NAME.
For an ADVISOR, store his or her number, last name, first name, and the department number to which the advisor is assigned.
- Fields
- ADVISOR_NUM, ADVISOR_FIRSTNAME, ADVISOR_LASTNAME
- Functional Dependencies
- ADVISOR_NUM => ADVISOR_LASTNAME, ADVISOR_FIRSTNAME, DEPT_NUM
For a COURSE, store its code and description.
- Fields
- COURSE_CODE, COURSE_DESC
- Functional Dependencies
- COURSE_CODE => COURSE_DESC
For a STUDENT, store his or her number, first name and last name.
- Fields
- STUDENT_NUM, STUDENT_FIRSTNAME, STUDENT_LASTNAME, GRADE
- Functional Dependencies
- STUDENT_NUM => STUDENT_FIRSTNAME, STUDENT_LASTNAME, ADVISOR_NUM
For a GRADE, store the student number, course code and grade.
The functional dependencies on STUDENT_NUM above are pretty obvious. The second one, below, is a little trickier. The grade depends on the student as well as the course, so it requires a compound key.
- Fields
- STUDENT_NUM, COURSE_CODE, GRADE
- Functional Dependencies
- STUDENT_NUM, COURSE_CODE => GRADE
TABLES
Now we can start identifying our tables. It looks like we have four main entities we are dealing with: DEPARTMENT, ADVISOR, COURSE and STUDENT.
Based on the requirements listed above, we can now identify the necessary tables and fields.
DEPARTMENT (DEPT_NUM, DEPT_NAME)
ADVISOR (ADVISOR_NUM, ADVISOR_FIRSTNAME, ADVISOR_LASTNAME, DEPT_NUM)
COURSE (COURSE_CODE, COURSE_DESC)
STUDENT (STUDENT_NUM, STUDENT_FIRSTNAME, STUDENT_LASTNAME, ADVISOR_NUM, ADVISOR_FIRSTNAME, ADVISOR_LASTNAME, COURSE_CODE, COURSE_DESC, GRADE)
Normal Forms
This is our first take on the tables, based on the requirements and is not normalized. So now, we need to go through the normalization process:
- First Normal Form (1NF)
- Remove repeating groups to a new table and identify the primary key.
The STUDENT table has a repeating group, so we need to remove it to a separate table. The key for the new table will be a compound key consisting of STUDENT_NUM and COURSE_CODE which we know from our functional dependencies. So now we have:
STUDENT (STUDENT_NUM, STUDENT_FIRSTNAME, STUDENT_LASTNAME, ADVISOR_NUM, ADVISOR_FIRSTNAME, ADVISOR_LASTNAME)
GRADE (STUDENT_NUM, COURSE_CODE, COURSE_DESC, GRADE)
- Second Normal Form (2NF)
- Move fields that are dependent on only a portion of the primary key to a new table.
In the GRADE table, COURSE_DESC is dependent on COURSE_CODE, but not STUDENT_NUM. Since we already have a COURSE table, we can simply remove COURSE_DESC from the GRADE table.
GRADE (STUDENT_NUM, COURSE_CODE, GRADE)
- Third Normal Form (3NF)
- Remove fields that are dependent on a non-key attribute.
In the STUDENT table, we have ADVISOR_FIRSTNAME and ADVISOR_LASTNAME which are dependent on ADVISOR_NUM which is a non-key field. Once again, we have already identified an ADVISOR table, so all we have to do here is remove the redundant information from theSTUDENT table.
STUDENT (STUDENT_NUM, STUDENT_FIRSTNAME, STUDENT_LASTNAME, ADVISOR_NUM)
Final Design
Our final database tables are shown below.
DEPARTMENT (DEPT_NUM, DEPT_NAME)
ADVISOR (ADVISOR_NUM, ADVISOR_FIRSTNAME, ADVISOR_LASTNAME, DEPT_NUM)
COURSE (COURSE_CODE, COURSE_DESC)
STUDENT (STUDENT_NUM, STUDENT_FIRSTNAME, STUDENT_LASTNAME, ADVISOR_NUM)
GRADE (STUDENT_NUM, COURSE_CODE, GRADE)
We have now designed and normalized a database.