CEFARIS PROJECT | Deliverable 1
Group 4 : Lawrence Hoo, John Teasdale, Julieta Jakubowicz
COMP 421, Professor Bettina Kemme and Supervisor Éric Tourigny
January 31, 2011
Data To Be Stored
This is basically listed in the handout. We plan on using extended keys instead of the three-letter acronyms to facilitate understanding. (In our diagram, primary keys appear in red instead of underlined, as a limitation of Google Docs. We chose this environment over a DBMS-specific one because it allowed for flexible collaboration among team members and real-time updates.)
Operations on Data
This list is compiled based on our experience using the software, and the presentations by Éric Tourigny and his colleagues. However, we have tried to leave the database as extensible as possible, in case there are other queries that we were not aware of.
Look-up student record by: Username, Date
One may want to see all the tests taken by one student ever, all tests taken on one date, or a test taken by one student at a specific date. We anticipate that this query will be the most popular; thus, we have made the date and Username the primary key for most relations.
Accept/Reject/DLLF Test Mark a test as valid...
Store Notes
These notes may come from the tester’s impression during the oral interview, but they also allow for any other comments that may come up.
Access Level For: General, Oral, Advanced, Interview
An algorithm will tally test section results to output a recommended Level, which will be stored in the attribute Level, thus avoiding re-computation every time someone wants to access this data.
Set Level For: General, Oral, Advanced, Interview. This data will be set once during the testing process, but can also be modified later if necessary.
Approve for Courses
Signal that a student is allowed to register for specific courses.
Retake section: General, Advanced.
Signal that a student is allowed to log back into the system and change answers in the specifies section.
Modify score for general/advanced answer.
Get score for block or section/part
Determine number of points earned for a question.
Compare the given answer in the Record - Question relationship with the correct answer.
Determine number of points earned for a section.
Computes the number of points for all questions in a section according to the test type.
Constraints/Restrictions
These restrictions were set by the Department, and they actually allowed for a more concise, precise design.
All test types have the same amount of each type of question.
There are different variations of the type of test a student may take. Luckily, they all have the same abstract structure, so these variations don’t translate into our design, allowing for one unified design for a record.
A logged in user can only start one test at any time.
By making the Username and the Date, together, the primary key and unique, we ensure that no user is reported as taking more than one test simultaneously.
Unique and Difficult Aspects
Some keys can store variable numbers of values.
Because the format of some values for the same key vary, we were sometimes forced to pick the more general of two formats, such as a varchar over a char.
Values that are semantically related may not be needed for similar queries.
We considered a design where all the answer scores were kept in the same relation, to allow for faster querying of total section scores. However, in the final design, we opted to keep all the attributes relating to an answer closer to each other, since they are more semantically related.
The most memory efficient way to store a record is as a single row in a table, but this may slow down certain queries.
The single-row design would allow to query all the information for one record at once. However, this option seemed less interesting/challenging as a student project. In addition, it led to verbose queries, since it included essentially no implicit information. Our final design has more logic embedded into the structure.
A student can take a test multiple times.
This issue was resolved by separating the Username form the Date as two separate attributes, thus allowing multiple, unique records for a single student, separated by their date.
Some attributes are related to specific answers whereas others relate to entire sections, yet all attributes are related to a section, as well.
We considered a variety of designs to resolve this issue. For example, we considered creating entity sets for Oral Part I, Oral Part II, etc. We settled on a question-by-question design.
The current record stores information that would be better computed on the fly (ie. section score/weight/delay, and point scores for answers).
Regardless, we kept all the information required by the specifications. Most likely, this trade-off will be beneficial if this information is accessed several times, yet computed only once.
As mentioned above, we assume the reason for the acronyms was a restriction of the system. We also assumed that longer keys will not hinder efficiency, and will allow for a more legible design.
The decision to use the current keys that were based on the constraints of the old system or to update them into a more readable format.
As mentioned above, we assume the reason for the acronyms was a restriction of the system. We also assumed that longer keys will not hinder efficiency, and will allow for a more legible design.