Designing a basic student database:

A relational database is arranged into tables using a variety of rules to establish an effective database. In this example, we are going to talk about some of those rules in a reasonably un-technical way.

We have the following data and we want to make a database to contain the information:

Student Information

• Student Id Number
• Student Name
• Student City
• Student State
• Department Code (Major)
• Department (Major) Name
• Department (Major) Chair
• Student GPA

When we set up a table, the rules for designing a database say that all of the fields in the table must relate directly to the primary key. Remember, a primary key uniquely defines a record in a file or table. In this table we will make the Student Id Number the primary key because it uniquely defines the record. Now lets look at the data, Student Name relates directly to the Student Id Number primary key. So do Student City and Student State and Department (Major) Code. However, Department (Major) Name relates directly to the Department (Major) Code not to the Student Id Number, so it should not be in this table. The same applies to Department (Major) Chair. Finally, we can see that the Student GPA does belong in the table because it relates directly to the Student Id Number.

Based on this analysis, our first table should look like this:

Student Table

• Student Id Number - primary key
• Student Name
• Student City
• Student State
• Department Code
• Student GPA

Now we will develop a new table to hold the department information. This makes sense. It certainly would be a waste to key in the department name and the name of the chair over and over on each student record. And what about when the department chair changes, do we have to go to each student record and change the name. We don't want to do that. It there was a separate department table, we could just change the department chairs name in one record and we would be all set.

Department Table

• Department Code
• Department Name
• Department Chair

Looking at this table, the Department Code can be established as the primary key because it uniquely identifies a record within a table.

Department Table

• Department Code - primary key
• Department Name
• Department Chair

Now we have set up two tables:

Student Table Student Id Number - primary key Student Name Student City Student State Department Code Student GPA

Department Table Department Code - primary key Department Name Department Chair

Notice that Department Code appears in both tables. It links the two tables together so when I am processing a student and I want to know the Department Name, I can go to the Department Table and use the Department Code to get the correct Department Name. The Department Code on the Student Table is called a foreign key because it is a field on that table that links to a primary key on another table - in this case the Department Table.