Checking the integrity of the data for an entity

Question

I have three tables STUDENT, DEPARTMENT and COURSE in a University database... STUDENT has a UID as a Primary key -> which is the UNIQUE ID of the student DEPARTMENT has Dept_id as a Primary Key -> which is the Dept. number COURSE has C_id as Primary Key -> which is the Course/subject Id

I need to store marks in a table by relating the primary key of STUDENT, DEPARTMENT and COURSE for each student in each course.

UID Dept_id C_id marks
1    CS     CS01  98
1    CS     CS02  96
1    ME     ME01  88
1    ME     ME02  90

The problem is if i create a table like this for marks then i feel the data operator might insert wrong combination of primary key of a student for example

UID Dept_id C_id marks
1    CS     CS01  98
1    CS     CS02  96
1    ME     CS01  88 //wrong C_id (course id) inputted by the DBA
1    ME     ME02  90

In which case how can i prevent him doing this? Also is there any other way to store marks for each student ? I mean like :

UID Dept_id CS01 CS02
1    CS      98   96
3    CS      95   92

Answer 1

You should avoid duplicating data in your database if possible:

UID Dept_id C_id marks
1    CS     CS01  98
     ^^     ^^

You could:

• Change the course ID to a two column key (department, course number), eg ('CS', '01').

or:

• Keep the course name as it is, but put the department ID field in the course table and omit it from your marks table. If you need to calculate the total marks for a specific department you can still do this easily by adding a JOIN to your query.

Your last suggestion seems to be a bad idea. You would need a column in your table for every course and most values would be NULL.

Answer 2

I'm not sure why you need the department in this table if the course indicates the department. Thus, why wouldn't your table be:

UID C_id marks
1    CS01  98
1    CS02  96
1    ME01  88
1    ME02  90

What is missing from this table is some indication of time. For example, a student could take the same course twice if they failed it the first time. Thus, you would need additional columns to indicate the semester and year.

Answer 3

You could add foreign key constraints to your tables to ensure that a valid value is entered for student IDs, course IDs and department IDs. You could also add unique constraints to the table to ensure inadvertent duplicates were not created. But in the end you can't prevent incorrect data from being inserted; if you knew it was incorrect, you wouldn't need to ask for it.

Example: 29th February 1957 couldn't be my birthday; 15th July 2025 couldn't be my birthday; 27th September 1974 wasn't my birthday.

Answer 4

Your suggestion would be a nightmare to maintain. You would have to add new columns every time a new course was added to the achedule. It also would be harder to query much of the time.

If you want to make sure that each course is appropriate for the department, you can do that in a trigger (make sure to handle multiple record inserts or updates) or in the application. This still won't prevent all data entry errors (it is possible to pick CS89 when you meant CS98), but it will reduce the amount of error. In this case it is unlikely the data would come from anywhere other than the application, so I'd probably choose to enforce the rules in the application. A pull down list where they chose the department and only the courses for that department showed would do the trick.