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

  • 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 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

  • 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.

 

Now let’s add information about the courses the student is taking.  I want to include the following information:

 

Course Number

Course Name

Number of credits

Semester the student took the course

Grade the student got in the course

 

We cannot carry information about the courses the student has taken on the Student Table because that would involve repeating groups.  So we know we will need another table.  When we start to layout this table we have the Student Id Number and the Course Number. 

 

StudentCourse Table

  • Student Id Number    combined for
  • Course Number         primary key

 

Combined, these would make the key for the courses a student has taken. Then the grade could go on as data because the grade relates to the student and the course.  But, there is a problem.  What about the student who fails or withdraws from the course and wants to take it again.  This structure would not allow the student to take the course again because it would not be a unique id.  The solution is to add a date to the key.  Probably not an actual date but something like F08 for fall 2008. The grade can then be added as data.  It relates to all parts of the key, it is

 

StudentCourse Table

  • Student Id Number     these 3 fields
  • Course Number           combined =
  • SemYr                          primary key
  • Grade

 

I cannot put course name or number of credits in because they relate to the course number which is only part of the key.  They do not relate to the whole key.  Therefore, I am going to have a Course Table that will contain the Course Number as the key and the Course Name and Number Credits as fields.

 

StudentCourse Table

  • Student Id Number     these 3 fields
  • Course Number           combined =
  • SemYr                          primary key
  • Grade

Course Table

  • Course Number      primary key
  • Course Name
  • Number Credits

 

Adding these two tables gives me a total of 4 tables.

 

Student Table

  • Student Id Number - PK
  • Student Name
  • Student City
  • Student State
  • Department Code
  • Student GPA

 

Department Table

  • Department Code - PK
  • Department Name
  • Department Chair

 

StudentCourse Table

  • Student Id Number     part1 PK
  • Course Number          part2 PK
  • SemYr                           part3 PK
  • Grade

Course Table

  • Course Number      PK
  • Course Name
  • Number Credits

 

Assignment:  Set up and populate these tables.

 

Queries:

 

  1. Display the student name, student GPA, department code and department name for students within a certain department (for example the CI department).
  2. Display the student id number, the student name, the GPA, the state, the course number, the course name for all students who have a GPA > 3 and live in MA.
  3. Display the student id number, the student name, the student GPA, the department code, the department name, the course number, the course name for GPA > 2 or a certain department code (for example CI).
  4. Display the student name, state, GPA and department code along with the department name for all students who have a certain department code (for example CI) and either have a GPA > 2 or a state equal to MA.