diff --git a/BDL.xml b/BDL.xml
index 92adcf3..8c6b218 100644
--- a/BDL.xml
+++ b/BDL.xml
@@ -431,7 +431,7 @@
Internally generated 8 digit identifier |
- |
+ * |
Suppliers_Code |
Up to 25 character identifier provided by supplier |
@@ -472,14 +472,12 @@
CHECK ( Stock_Count BETWEEN 0 AND 9999999 ),
Supplier_ID NUMBER(7) NOT NULL,
--
- CONSTRAINT Component_PK PRIMARY KEY ( Component_Code, Suppliers_Code ),
+ CONSTRAINT Component_PK PRIMARY KEY ( Component_Code ),
--
CONSTRAINT Component_FK_To_Supplier
FOREIGN KEY ( Supplier_ID ) REFERENCES Supplier
);
-
-
Suppliers_Code
is technically redundant in the primary key (i.e., the key isn’t minimal), but that’s what was specified, so eliminating it without a good explanation lost marks.
@@ -521,22 +519,21 @@
CREATE TABLE Assembly
( Product_Code NUMBER(8),
Component_Code NUMBER(8),
- Suppliers_Code VARCHAR2(25),
Quantity NUMBER(4) NOT NULL
CONSTRAINT Assembly_Quantity_Too_Low
CHECK ( Quantity BETWEEN 1 AND 9999 ),
--
CONSTRAINT Assembly_PK
- PRIMARY KEY ( Product_Code, Component_Code, Suppliers_Code ),
+ PRIMARY KEY ( Product_Code, Component_Code ),
--
CONSTRAINT Assembly_FK_To_Product
FOREIGN KEY ( Product_Code ) REFERENCES Product,
CONSTRAINT Assembly_FK_To_Component
- FOREIGN KEY ( Component_Code, Suppliers_Code ) REFERENCES Component
+ FOREIGN KEY ( Component_Code ) REFERENCES Component
);
- Suppliers_Code
is part of the primary key of Component
, so it must by definition be part of the primary key here (unless we created a surrogate key, but that wasn’t in the specification). It also doesn’t make much 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!
+ 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!
@@ -797,7 +794,6 @@
CREATE TABLE Order_Line
( Order_Num NUMBER(10),
Component_Code NUMBER(8),
- Suppliers_Code VARCHAR2(25),
Qty_Ordered NUMBER(5) NOT NULL
CONSTRAINT Order_Line_Qty_Ord_Too_Low
CHECK ( Qty_Ordered BETWEEN 0 AND 99999 ),
@@ -807,10 +803,10 @@
Qty_Received NUMBER(6) NOT NULL,
--
CONSTRAINT Order_Line_PK
- PRIMARY KEY ( Order_Num, Component_Code, Suppliers_Code ),
+ PRIMARY KEY ( Order_Num, Component_Code ),
--
CONSTRAINT Order_Line_FK_To_Comp
- FOREIGN KEY ( Component_Code, Suppliers_Code ) REFERENCES Component,
+ FOREIGN KEY ( Component_Code ) REFERENCES Component,
CONSTRAINT Order_Line_FK_To_Ord_Hd
FOREIGN KEY ( Order_Num ) REFERENCES Order_Head
);