I'm trying to build a database for students official exams where a student study in a school and does his official exams in a different school.

My current CDM:

enter image description here

After converting this CDM into PDM then into SQL I'm having some problems using the database.

My problems with the current design is:

  1. Student table has a SCHOOLID for the school that the student studies in and SCH_SCHOOLID for the school the student will be doing his exams in and this is what I actually want. The problem is that I'm not sure how to link the student exam school with his CLASSROOM and SEAT in that school.
  2. Another thing I need in my database is that one or many CLASS can have the same EXAM with the same LANGUAGE or in a different LANGUAGE. The generated SQL does not seem to provide the option to do that.

Any help on how I can improve my CDM to get the desired result is much appreciated.

This is my generated SQL: enter image description here


My thoughts after a quick review (edited):

  • An Exam should not have a Day/Start Time/End time. The Exam entity represents the EXAM definition, it should have subject, year etc, NOT the time it is to be sat. I assume from your design that a class can have multiple exams. What you are missing is an ExamEvent entity. This would represent an exam event, where everyone sits down and takes it, NOT the definition of an exam (as the same exam can happen in different schools, by your description). An ExamEvent needs to have an ExamID, ClassCode (think about having a surrogate ID for the CLASSEXAM Entity), Time/Date, LanguageID (assuming everyone sits the exam at the same time in the same language), RoomCode. It should also have its own surrogate key, if needed: ExamEventID. Review the name of this entity.
  • It looks like you are then missing another key entity - a StudentExam entity, which would have: StudentID, ExamEventID and SeatCode. This represents the student attending the exam and where they are sitting. If each student sits the exam in a different language, but at the same time, the Language entity needs to relate to this entity NOT the ExamEvent entity.
  • Sticking with what I have specified above, you should then remove the 2nd Student/School link (currently representing the place the student is taking the exam). Instead, using the design I specified, the place the student is taking the exam is defined by the StudentExam object as it links via ExamForClass, through to ClassRoom, through to School.
  • Thanks for your answer, I was looking to improve my CDM and not SQL but your answer helped me re-write few things.
    – Fouad
    Nov 28 '16 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.