This table is essentially identical to Customer, so the same errors tended to apply. Phone numbers are slightly trickier here, because they can be full international numbers. The International Telecommunications Union (ITU) set standards for telecommunications, including phone numbers. They specify the maximum length of a full international phone number to be fifteen digits, including country code, area code and local number (where applicable). An optional “+” can be added at the front, making a total of up to 16 characters However, this doesn’t mean that all international phone numbers are fifteen digits long! New Zealand phone numbers, for example, are between ten and twelve digits depending on whether it’s a landline or a mobile. Twelve digits is probably a reasonable minimum size for the Phone column.
This table is essentially identical to Customer, so the same errors tended to apply. Phone numbers are slightly trickier here, because they can be full international numbers. The International Telecommunications Union (ITU) set standards for telecommunications, including phone numbers. They specify the maximum length of a full international phone number to be fifteen digits, including country code, area code and local number (where applicable). An optional “+” can be added at the front, making a total of up to 16 characters However, this doesn’t mean that all international phone numbers are fifteen digits long! New Zealand phone numbers, for example, are between ten and twelve digits depending on whether it’s a landline or a mobile.
@@ -521,7 +521,7 @@ Component_Code NUMBER(8), Quantity NUMBER(4) NOT NULL CONSTRAINT Assembly_Quantity_Too_Low - CHECK ( Quantity BETWEEN 1 AND 9999 ), + CHECK ( Quantity BETWEEN 2 AND 9999 ), -- CONSTRAINT Assembly_PK PRIMARY KEY ( Product_Code, Component_Code ), @@ -534,6 +534,8 @@It doesn’t make sense to allow zero as a value of Quantity; if there are none of a particular component in a product, then you wouldn’t add an entry to this table anyway!
There was actually an inconsistency in the original specification: the definition of the Quantity column said “1–9999” (corrected above), while the following text said “at least two”. We accepted both answers, but awarded a bonus mark for spotting the typo and using the correct lower bound of 2.
It was great to see people using the workaround for checking the Sale_Date isn’t in the future. However, it was very frustrating to also see of them not bothering to explain it! We were quite happy to accept an extra column to store the sale entry date, as long as there was an explanation of why it was being added. (Note that the code above doesn’t include this to save space.)
It was great to see people using various workarounds for checking the Sale_Date isn’t in the future. (Note that the code above doesn’t include this to save space.)
Once again it doesn’t really make sense for Quantity in Sale_Line to be zero (although it does make sense for Actual_Price).
A few people forgot that strings in SQL are case-sensitive, and used mixed case values (e.g., “In Progress”) for the CHECK constraint on Status, rather than the specified lower case values (“in progress”). (This also applies to the Status column in Order_Head.)
Qty_Received in Order_Line seemed to confuse some people. It should be non-negative (unless you’re allowing for returns, but that wasn’t mentioned in the specification), but the upper bound (if there is one at all) should be greater than Qty_Ordered to enable BDL to record situations where they receive more components than they ordered. (This does happen in real life; sometimes you even get sent things that you never ordered!) A few people said Qty_Received >= Qty_Ordered (or similar), which would completely prevent us from ever entering an order, because Qty_Received will always initially be zero!