Results 1 to 6 of 6

Thread: ERD challenge

  1. #1
    Join Date
    Dec 2003
    Posts
    6

    ERD challenge

    Hi , everybody !

    I have confused with one situation need to model with ERD model :
    Students study on course and each semestr get grade on it so i built three tables : student , course and student's course ther i will ave his grade.

    But i have to accumulate some historical data : the average grade and accumulated by student credit point's . So how i can do it . Its derived data but it isnt derived from fields of the same row , its derived from rows of definite student (it may be 1 to 8 semestrs)
    And i need to save overall average and credit points each semestr.

    The more general problem how i save the historical and derived data in
    database an how to model it in ERD.

    How i can to solve it . Combine the student with one more table there i save all these data but calculating it from table of student's course ?

    Any help ?

    Thank in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    student
       \                                course
        \          semester              /
         \           /                  /
          \         /                  /
           \       /                  /
        studentsemester              /
                \                   /
                 \                 /
                  \               /
                   \             /
                    \           /
                 studentsemestercourse
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Posts
    6
    Ok !

    That was understood and where i will save current average and accumulated credit points of student ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    overall average and credit points each semester go into studentsemester

    overall average and credit points overall go into student
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Posts
    6
    Is it ok to put calculated overall average and credit points in student while these calclculated from anothe table of student semestr ?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i wouldn't, but you could

    i would use views to produce totals grouped by semester and student

    you said you had to save them, and of course you would save them in the appropriate record

    but saving them means an update dependency -- if you add a new row to studentsemestercourse with credit points, then you have to re-calculate the average in studentsemester, and also student, whereas the averages in the view are correct at all times, since the view calculates them from the studentsemestercourse rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •