CREATE TABLE Testing_Laboratory
(
  lab_code INT NOT NULL,
  lab_details VARCHAR(20) NOT NULL,
  PRIMARY KEY (lab_code)
);

CREATE TABLE Paternity_Test
(
  test_id INT NOT NULL,
  test_details VARCHAR(20) NOT NULL,
  lab_code INT NOT NULL,
  PRIMARY KEY (test_id),
  FOREIGN KEY (lab_code) REFERENCES Testing_Laboratory(lab_code)
);

CREATE TABLE Address
(
  address_id INT NOT NULL,
  street_number INT NOT NULL,
  street_name VARCHAR(20) NOT NULL,
  suburb VARCHAR(20) NOT NULL,
  postcode INT NOT NULL,
  PRIMARY KEY (address_id)
);

CREATE TABLE Mental_Illness
(
  mental_illness_code INT NOT NULL,
  mental_illness_description VARCHAR(20) NOT NULL,
  PRIMARY KEY (mental_illness_code)
);

CREATE TABLE Government_Certifier
(
  certifier_id INT NOT NULL,
  certifier_details VARCHAR(20) NOT NULL,
  PRIMARY KEY (certifier_id)
);

CREATE TABLE Accreditation
(
  accredation_name VARCHAR(20) NOT NULL,
  accredation_id INT NOT NULL,
  PRIMARY KEY (accredation_id)
);

CREATE TABLE Results
(
  result_id INT NOT NULL,
  lab_code INT NOT NULL,
  PRIMARY KEY (result_id),
  FOREIGN KEY (lab_code) REFERENCES Testing_Laboratory(lab_code)
);

CREATE TABLE Client
(
  client_id INT NOT NULL,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(20) NOT NULL,
  address_id INT NOT NULL,
  test_id INT NOT NULL,
  PRIMARY KEY (client_id),
  FOREIGN KEY (address_id) REFERENCES Address(address_id),
  FOREIGN KEY (test_id) REFERENCES Paternity_Test(test_id)
);

CREATE TABLE Treatment
(
  treatment_code INT NOT NULL,
  treatment_detail VARCHAR(20) NOT NULL,
  client_id INT NOT NULL,
  certifier_id INT NOT NULL,
  PRIMARY KEY (treatment_code),
  FOREIGN KEY (client_id) REFERENCES Client(client_id),
  FOREIGN KEY (certifier_id) REFERENCES Government_Certifier(certifier_id)
);

CREATE TABLE Affects
(
  client_id INT NOT NULL,
  mental_illness_code INT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES Client(client_id),
  FOREIGN KEY (mental_illness_code) REFERENCES Mental_Illness(mental_illness_code)
);

CREATE TABLE Professional
(
  professional_id INT NOT NULL,
  professional_parent_id INT NOT NULL,
  professional_details VARCHAR(20) NOT NULL,
  accredation_id INT NOT NULL,
  PRIMARY KEY (professional_id),
  FOREIGN KEY (professional_parent_id) REFERENCES Professional(professional_id),
  FOREIGN KEY (accredation_id) REFERENCES Accreditation(accredation_id)
);

CREATE TABLE Meets
(
  client_id INT NOT NULL,
  professional_id INT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES Client(client_id),
  FOREIGN KEY (professional_id) REFERENCES Professional(professional_id)
);