If we wanted to allow for additional qualification types in future, we could create a separate Qualification_Type lookup table, with a single column Type. We could then replace the CHECK constraint Qualification_Type_Valid with a foreign key to the lookup table. If we use the existing values, this can even be done without breaking the specification.
If we wanted to allow for additional qualification types in future, we could create a separate Qualification_Type lookup table, with a single column Type. We could then replace the CHECK constraint Qualification_Type_Valid with a foreign key to the lookup table. If we use the existing values, this can even be done without breaking the specification!
Remember also that SQL string comparison is case sensitive, so it’s important to get the values in the CHECK constraint for Type correct and apply them consistently.
As with Qualification.Type, we could create a separate lookup table for Period. This table could even store additional information about the period, such as its full name, start and end dates, etc.
There are several ways that we could write the regular expression for the paper code. The expression above could be shortened even further by replacing the [0-9] with \d, which means “any digit 0–9”.
The most common problems here were omitting either the primary key or the foreign keys. Note also that there’s no need to repeat constraints on, e.g., Paper_Code, because the foreign key already forces the value to be one that exists in Paper anyway.
It’s important when implementing “Boolean” style columns such as International in Student and Release in Assessment that you are consistent in the values that you use across all such columns (more precisely, all such columns should have the same domain). Quite a few people did something like implement one column as 'T', 'F' and the other as 'True', 'False', or even more subtle, 't', 'f' (remembering that SQL is case sensitive). Using inconsistent domains for columns that should have the same domain could lead to subtle bugs later on.
The subtype hierarchy seemed to confuse a few people. The most common example of this was, e.g., including both Person_ID and Staff_ID in Staff. This is unnecessary because they should be the same value anyway, and a column can appear in both the primary key and a foreign key at the same time. Also make sure that the foreign keys are referring to the correct tables as specified in the ERD (e.g., Supervisor_ID should reference Staff, not Person).
This of course is really a consequence of not having a proper BOOLEAN data type in SQL. However, one person did discover—possibly inadvertently, given that it isn’t mentioned anywhere in the documentation—that BOOLEAN data type in CREATE TABLE! This certainly wasn’t possible in older versions, where BOOLEAN was available in PL/SQL only. The inconsistency of implementation of a Boolean data type across various DBMSs means that it’s generally safer to use the approach above if portability is important.
It’s important when implementing “Boolean” style columns such as International in Student and Release in Assessment that you’re consistent in the values that you use across all such columns (more precisely, all such columns should have the same domain). A few people did something like implement one column as 'T', 'F' and the other as 'True', 'False', or even more subtle, 't', 'f' (remembering that SQL string comparsion is case sensitive). Using inconsistent domains for columns that should have the same domain could lead to subtle bugs later on.
A few people implemented something like 'T', 'F', 't', 'f' on the seemingly reasonable theory that it was more “flexible”. (One person even included 'Y', 'N', 'y', 'n', '0', 1'!) This kind of “flexibility” usually causes more problems than it solves, unfortunately. The main problem is that it’s now harder to write queries to find all the (for example) “true” values: we either have to test for all the possible permutations of “true”, or we have convert everything to the same format before comparing. Either way it's a pain to implement, and easy to get wrong. Keep things as simple as possible! You’ll thank yourself later.
This of course is really a consequence of not having a proper BOOLEAN data type in SQL. There was a period where BOOLEAN, but this must have been unintentional, as it was never documented, and it no longer works as of BOOLEAN in
Once again, we could use a lookup table for Staff.Rank.
Role could be constrained using a lookup table.
A surprising number of people appear to have missed the statement of the year that the Institute was founded in the first paragraph of the specification! Again, Role could be constrained using a lookup table.
A surprising number of people appear to have missed the statement of when the Institute was founded in the first paragraph of the specification!
- -Another very common error was to set the size of the Comments column to a relatively small number, like 200. Think about how much you can say in that many characters (one and a bit text messages), then consider what kinds of things you might want to enter into a general comments column. We deducted marks for anything smaller than 500 characters. When you have no idea of how much you’re going to get, it’s much better to go large than to go small!
A common error (especially amongst those who didn’t use the schema checker) was to set the size of the Comments column to a relatively small number, like 200. Think about how much you can say in that many characters (one and a bit text messages, maybe 30 words), then consider what kinds of things you might want to enter into a general comments column. We deducted marks for anything smaller than 500 characters. When you have no idea of how much you’re going to get, it’s much better to go large than to go small!
Type could be constrained by a lookup table.
A lot of people got the sizes of the various NUMBER columns incorrect. Remember that the number of decimal places is included in the total number of digits, not in addition to.
Quite a few people got the sizes of the various NUMBER columns incorrect. Remember that the number of decimal places is included in the total number of digits, not in addition to.
We also accepted FLOAT and BINARY_FLOAT for Weighted_Mark. Anything with a fixed number of decimal places was marked down, as this is a fixed point number, not a floating point number.
We also accepted FLOAT, DOUBLE (although it’s really overkill), BINARY_FLOAT and BINARY_DOUBLE for Weighted_Mark. Anything with a fixed length or number of decimal places was marked down, as this is a fixed point number, not a floating point number. The whole point (!) is that the precision and decimal places should adjust to what’s required at the time.
As a couple of students discovered, Weighted_Mark and Percentage_Mark. Unfortunately, virtual columns expressions can’t refer to columns in other tables, so the only way we can achieve this is to duplicate the Maximum_Mark and Weight columns from Assessment into Result. This could actually be useful in practice, however, as would enable us to vary the weight and maximum mark on a per-submission basis (perhaps to cater for students who were unable to complete the entire assignment due to unusual circumstances). We could use a trigger that runs before an INSERT on Result to copy across the “default” values from Assessment. Result could then look something like this: