DROP TABLE IF EXISTS data_collection.student ;
CREATE  TABLE IF NOT EXISTS data_collection.student
(
  student_pk        INT         NOT NULL   AUTO_INCREMENT,
  student_id        CHAR(10)    NOT NULL,
  student_surname   VARCHAR(45) NOT NULL,
  student_givenname VARCHAR(45) NOT NULL,
  PRIMARY KEY (student_pk),
  UNIQUE INDEX student_id_UNIQUE (student_id ASC)
)

DROP TABLE IF EXISTS data_collection.collection ;
CREATE  TABLE IF NOT EXISTS data_collection.collection
(
  collection_pk    INT   NOT NULL   AUTO_INCREMENT,
  collection_date  DATE  NOT NULL,
  collection_place SET('Beach', 'Bay', 'Surf', 'Other') NOT NULL,
  PRIMARY KEY (collection_pk)
)

DROP TABLE IF EXISTS data_collection.student_collection ;
CREATE  TABLE IF NOT EXISTS data_collection.student_collection
(
  student_collection_pk INT        NOT NULL  AUTO_INCREMENT,
  student INT(11)       NOT NULL,
  collection INT(11)    NOT NULL,
  PRIMARY KEY (student_collection_pk),
  INDEX fk_student (student ASC),
  INDEX fk_collection (collection ASC),
  CONSTRAINT fk_student
    FOREIGN KEY (student )
    REFERENCES data_collection.student (student_pk )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_collection
    FOREIGN KEY (collection )
    REFERENCES data_collection.collection (collection_pk )
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)