Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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)
- );
Advertisement