Pasted

CREATE TABLE product (
    product_name varChar(50) PRIMARY KEY,
    company_name varChar(50) NOT NULL 
);
 
CREATE TABLE drug (
    drug_name varchar(50) PRIMARY KEY
);
 
CREATE TABLE product_drug_relation (
    product_name varChar(50),
    drug_name varChar(50),
    
    FOREIGN KEY (product_name) REFERENCES product(product_name),
    FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
    
    PRIMARY KEY (product_name, drug_name)
);
 
CREATE SEQUENCE drug_category_serial START 1;
 
CREATE TABLE drug_category (
    drug_category_ID integer PRIMARY KEY DEFAULT nextval('drug_category_serial'),
    drug_category_name varChar(30) UNIQUE NOT NULL
);
 
CREATE TABLE drug_in_category (
    drug_name varChar(50),
    drug_category_ID integer,
    
    FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
    FOREIGN KEY (drug_category_ID) REFERENCES drug_category(drug_category_ID),
    
    PRIMARY KEY(drug_name, drug_category_ID)
);
 
CREATE TABLE disease_category (
    disease_category_ID integer PRIMARY KEY,
    disease_category_name varChar(50) UNIQUE NOT NULL
);
 
CREATE TABLE disease (
    disease_name varChar(50) PRIMARY KEY,
    disease_category_ID integer,
    
    FOREIGN KEY (disease_category_ID) REFERENCES disease_category(disease_category_ID)
);
 
CREATE TABLE treats (
    drug_name varChar(50),
    disease_name varChar(50),
    
    FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
    FOREIGN KEY (disease_name) REFERENCES disease(disease_name),
    
    PRIMARY KEY(drug_name, disease_name)
);
 
CREATE SEQUENCE side_effect_serial START 1;
 
CREATE TABLE side_effect (
    side_effect_ID integer PRIMARY KEY DEFAULT nextval('side_effect_serial'),
    side_effect_name varChar(30) UNIQUE NOT NULL
);
 
CREATE TABLE drug_side_effects (
    drug_name varChar(50),
    side_effect_ID integer,
    
    FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
    FOREIGN KEY (side_effect_ID) REFERENCES side_effect(side_effect_ID),
    
    PRIMARY KEY(drug_name, side_effect_ID)
);
 
CREATE TABLE drug_interaction (
    drug_name_A varChar(50),
    drug_name_B varChar(50),
    
    FOREIGN KEY (drug_name_A) REFERENCES drug(drug_name),
    FOREIGN KEY (drug_name_B) REFERENCES drug(drug_name),
    
    PRIMARY KEY(drug_name_A,drug_name_B)
);
 
CREATE TABLE institution (
    institution_name varChar(50) PRIMARY KEY,
    address varChar(50) NOT NULL,
    country varChar(50) NOT NULL,
 
    UNIQUE (address, country)
);
 
CREATE SEQUENCE researcher_serial START 1;
 
CREATE TABLE researcher (
    researcher_ID integer PRIMARY KEY DEFAULT nextval('researcher_serial'),
    researcher_name varChar(50) NOT NULL,
    institution_name varChar(50),
    
    FOREIGN KEY (institution_name) REFERENCES institution(institution_name)
);
 
CREATE SEQUENCE condition_serial START 1;
 
CREATE TABLE condition (
    condition_ID integer PRIMARY KEY DEFAULT nextval('condition_serial'),
    condition_name varChar(30) UNIQUE NOT NULL
);
 
CREATE TABLE trial (
    trial_ID integer PRIMARY KEY,
    title varChar(100)  UNIQUE NOT NULL,
    start_date time NOT NULL,
    completion_date time NOT NULL,
    participants integer NOT NULL,
    status varChar(20) NOT NULL,
    institution_name varChar(50),
    main_researcher_ID integer,
    condition_ID integer,
    
    FOREIGN KEY (institution_name) REFERENCES institution(institution_name),
    FOREIGN KEY (main_researcher_ID) REFERENCES researcher(researcher_ID),
    FOREIGN KEY (condition_ID) REFERENCES condition(condition_ID)
);
 
CREATE TABLE trial_researchers (
    trial_ID integer,
    researcher_ID integer,
    
    FOREIGN KEY (trial_ID) REFERENCES trial(trial_ID),
    FOREIGN KEY (researcher_ID) REFERENCES researcher(researcher_ID),
    
    PRIMARY KEY(trial_ID, researcher_ID)
);
 
CREATE TABLE trial_drug_disease (
    trial_ID integer,
    drug_name varChar(50),
    disease_name varChar(50),
    
    FOREIGN KEY (trial_ID) REFERENCES trial(trial_ID),
    FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
    FOREIGN KEY (disease_name) REFERENCES disease(disease_name),
    
    PRIMARY KEY(trial_ID, drug_name, disease_name)
);