-- ALTER TABLE baseattrib_t DROP COLUMN ba_role;
-- DROP TABLE roles_t;

-- Table ROLES_T
CREATE TABLE roles_t (
  role_id  INTEGER  NOT NULL,
  role_name  VARCHAR(50)  NOT NULL,
  CONSTRAINT roleid_pk
     PRIMARY KEY ( role_id )
) engine = InnoDB;

INSERT INTO roles_t VALUES (20, 'NO_ROLE');
INSERT INTO roles_t VALUES (21, 'KEY');
INSERT INTO roles_t VALUES (22, 'LABEL');
INSERT INTO roles_t VALUES (23, 'PREDICTOR');

ALTER TABLE baseattrib_t ADD 
  ba_role INTEGER;
  
-- next command does not work for some reason...
-- ALTER TABLE baseattrib_t ADD 
--  CONSTRAINT ba_role_fk 
--     FOREIGN KEY ( ba_role )
--     REFERENCES ROLES_T ( role_id );

-- ... so we use this workaround:
ALTER TABLE baseattrib_t ADD 
  CONSTRAINT ba_role_ch 
     CHECK ( ba_role IN (20,21,22,23));
     
-- Now we update the M4 entries:
UPDATE baseattrib_t SET ba_role = 20;

-- simplifying the conceptual data types:
UPDATE baseattrib_t SET ba_condtid = 1 WHERE ba_condtid IN (1,2,3,4);
UPDATE baseattrib_t SET ba_condtid = 7 WHERE ba_condtid IN (6,7,8,9);
UPDATE baseattrib_t SET ba_condtid = 10 WHERE ba_condtid IN (5,10);
