Designing a basic student database:

 

Now we are going to look at making two tables following some rules that we will examine in more detail next week.

 

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

 

Student Information

 

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 as do Student City and Student State and Department or Major Code.  However, Department or Major Name relates directly to the Department or Major Code not to the Student Id Number, so it should not be in this table.  The same applies to Department or 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

 

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

 

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

 

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.

 

Your assignment is to go to Access, set up these two tables and populate them with data.  You make up the Data.