Jenski said:
Hi all,
Can anyone tell me if I'm going in the right direction with this EER
Diagram and point to any improvements I could make?
Thanks in advance
We have been given this problem to do:
http://www.myfilestash.com/userfiles/jenski/questo.txt
Jenski,
Are you saying that *this* is the specification you are required to
work from?
It is, well, pretty thin.
I'd hate to be stuck doing design work from it.
Especially since the person who wrote it can't spell (handing out
coursework with spelling errors, I mean, really . . .), and also
uses words that even Google can't locate ("DECOMPONENTMENT").
Honestly, the specification above is so thin that it is very
difficult to determine if what you have done it correct, or not.
I'll make some comments, but please read them knowing that I know
little about what you are doing.
------------------------
Several attributes appear to have spaces in them. Never use
anything but letters, underscores, and numbers, in your table and
column names; and avoid underscores and numbers as much as you can.
Student:
- Why is "Course Code" an attribute of a student? Is this some
piece of data that helps to describe a student, or is it something
that really describes a course the student happens to be taking?
Other:
- Nothing is mentioned on how grades are calculated or assigned (a
weighting system is mentioned) and the grade values appear to
contain both character and numeric data, making it appear that
mathematical aggregate functions cannot operate on them. Are grade
values like "D5" really two separate attributes? (AVG and SUM can't
be used on "D5".)
- I see that "Student Takes Subject" and "Student Takes Component"
both have "Overall Grade" attributes. This hardly seems like a
primary or foreign key, so why is a copy of the same attribute being
stored in two different locations?
- I see that Students are taking Subjects, Components, and Elements.
This is going to create difficulty for you later on. To find out
what the Student is taking, all three relationships will have to be
JOINed in queries in fairly awkward ways. Instead, you might
consider:
Students take Subjects: StudentSubjects
The StudentSubjects relationship entity has attributes:
StudentsID
SubjectsID
Subjects have Components: SubjectComponents
The SubjectComponents relationship entity has attributes:
SubjectsID
ComponentsID
Components have Elements: ComponentElements
The ComponentElements relationship entity has attributes:
?? ComponentElementsID <--Possible surrogate/artificial key
ComponentsID
ElementsID
Elements have Grades: ElementGrades
Note: Grades may be an entity in the data model, but may, or may
not, become a table in the physical database.
The ElementGrades relationship entity has attributes:
ElementsID
StudentsID
Grade <-- This would be the base grade for the element.
I would not store any grades anywhere but in ElementGrades (or at
least only in one place). I would calculate the summary grade
values for Components and Subjects when I needed them from the data
in ElementGrades unless severe performance problems showed up in
full scale testing. Storing grades in multiple locations will cause
as many headaches as having three tables for what a student is
taking.
Rows that get INSERTed into ElementGrades are based on the Elements
of the Components of the Subject the student is taking. On an
actual GUI form somewhere, all that information will be thrown onto
the main form by one multi-table JOIN query, the instructor will
select an element, and enter a grade, click a save/ok button, and an
append query will be run against ElementGrades to add the necessary
information (after validation/error-checking, of course).
When you want to locate which Elements a student has taken, the
tables would be JOINed together appropriately, rather than have
three separate tables for
Of course, this is overly simplified. There may be situations where
students have to retake an element, etc. A method of uniquely
identifying the current attempt by a student to study the Subject
(and its Components and Elements) will need to be introduced
(probably a date of the school year or the semester or however it is
this is identified in this case).
I'm looking at this further, as I sit here, and knowing how little I
understand your particular specification just looms larger and
larger, so I will stop.
My apologies, I have just now recognized that I slipped into naming
entities as plural/collective in the middle of the comments, when
you have all entities named in the singular.
Sincerely,
Chris O.
