locked
Confused about a concept in ER modelling RRS feed

  • Question

  • Hello!

    I've just recently started learning database design and would like to clarify my understanding in some concepts. To make the discussion more specific, here is a case:

    1. How do you represent the relationship between student and class where each student can only attend one class at a time, and each class can have many students attend the class. However over time, each student can attend many classes.

    So, STUDENT and CLASS are entities. 1 Student can attend only 1 class, but 1 class can have many students. So it's a 1:M relationship, but what about the "each student can attend many classes over time"?? Is there some way I can represent this?


    Saturday, March 24, 2012 12:07 PM

Answers

  • My first approach would be to have a relationship table StudentClass between the Student and Class entity tables. Each time a student took a Class would be represented by one row in the StudentClass table. The harder part is limiting each Student to one active course. I'd have some sort of Status column in StudentClass with values like Active, Completed, Dropped, and create a trigger that would enforce a rule that no student could have more than one row in StudentClass where Status = 'Active'. Would probably take both an insert and update trigger. In other systems triggers could enforce other business rules, such as a university setting a limit on the number of credit hours a student could take each quarter or semester.

    I published a one hour introduction to DB design video. While it's based on normalization instead of ER modelling, the example showing the kinds of problems you can address by going to fourth normal form is modelling student/class/period/room/instructor relationships. Have a look about 46 minutes into the video, and see if that discussion helps you think through some of the issues with the kinds of relationships you are looking at:

    http://msdn.microsoft.com/en-us/sqlserver/hh852617.aspx


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Saturday, March 24, 2012 3:59 PM
  • If each Student can only be in one class, by definition, and historical data is not kept, this is a 1:M design as you explained well.

    Historical data can be kept in numerous ways. For example, by making a history TABLE. Or a new Studentid. Both of these are not very good ideas. Making a relationship TABLE as Alan suggested is of the better methods.

    But, i think the initial assumption is flawed. Students only being able to attend one class at a time is not a data rule, because it is not inherently true. Instead, it is a rule superimposed upon the implementation. That is, a Student can attend many classes, but we may limit it to one class. In either case, the relation is M:M even if the current implementation is otherwise.

    Hence, Alan's solution is now correct, as it implements a many to many solution as he explains "My first approach".

    A rule to remember: Do not design to the implementation; design to the idea, and modify it for the (current) implementation.




    Monday, March 26, 2012 2:12 PM
    Answerer

All replies

  • My first approach would be to have a relationship table StudentClass between the Student and Class entity tables. Each time a student took a Class would be represented by one row in the StudentClass table. The harder part is limiting each Student to one active course. I'd have some sort of Status column in StudentClass with values like Active, Completed, Dropped, and create a trigger that would enforce a rule that no student could have more than one row in StudentClass where Status = 'Active'. Would probably take both an insert and update trigger. In other systems triggers could enforce other business rules, such as a university setting a limit on the number of credit hours a student could take each quarter or semester.

    I published a one hour introduction to DB design video. While it's based on normalization instead of ER modelling, the example showing the kinds of problems you can address by going to fourth normal form is modelling student/class/period/room/instructor relationships. Have a look about 46 minutes into the video, and see if that discussion helps you think through some of the issues with the kinds of relationships you are looking at:

    http://msdn.microsoft.com/en-us/sqlserver/hh852617.aspx


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Saturday, March 24, 2012 3:59 PM
  • If each Student can only be in one class, by definition, and historical data is not kept, this is a 1:M design as you explained well.

    Historical data can be kept in numerous ways. For example, by making a history TABLE. Or a new Studentid. Both of these are not very good ideas. Making a relationship TABLE as Alan suggested is of the better methods.

    But, i think the initial assumption is flawed. Students only being able to attend one class at a time is not a data rule, because it is not inherently true. Instead, it is a rule superimposed upon the implementation. That is, a Student can attend many classes, but we may limit it to one class. In either case, the relation is M:M even if the current implementation is otherwise.

    Hence, Alan's solution is now correct, as it implements a many to many solution as he explains "My first approach".

    A rule to remember: Do not design to the implementation; design to the idea, and modify it for the (current) implementation.




    Monday, March 26, 2012 2:12 PM
    Answerer
  • Thank you both!

    Alan, your video is very helpful! We covered normalisation this week and I was able to grasp the basic concepts with ease. I actually read ahead and looked at normalisation before our project and so I got confused about the whole 'design to idea not implementation' as Brian mentioned.

    The lecture should of made that clearer, as it would of helped relieve the stress of everyone.

    Wednesday, March 28, 2012 11:56 AM
  • I'm glad the video helped.

    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, March 29, 2012 10:00 PM
  • Thanks a lot for the videos. Cleared concept of normalization
    Saturday, March 31, 2012 9:58 AM