dkalaxdk

Miniproject SQL

Oct 30th, 2020 (edited)
929
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE product (
  2.     product_name varChar(50) PRIMARY KEY,
  3.     company_name varChar(50) NOT NULL
  4. );
  5.  
  6. CREATE TABLE drug (
  7.     drug_name varchar(50) PRIMARY KEY
  8. );
  9.  
  10. CREATE TABLE product_drug_relation (
  11.     product_name varChar(50),
  12.     drug_name varChar(50),
  13.    
  14.     FOREIGN KEY (product_name) REFERENCES product(product_name),
  15.     FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
  16.    
  17.     PRIMARY KEY (product_name, drug_name)
  18. );
  19.  
  20. CREATE SEQUENCE drug_category_serial START 1;
  21.  
  22. CREATE TABLE drug_category (
  23.     drug_category_ID integer PRIMARY KEY DEFAULT nextval('drug_category_serial'),
  24.     drug_category_name varChar(30) UNIQUE NOT NULL
  25. );
  26.  
  27. CREATE TABLE drug_in_category (
  28.     drug_name varChar(50),
  29.     drug_category_ID integer,
  30.    
  31.     FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
  32.     FOREIGN KEY (drug_category_ID) REFERENCES drug_category(drug_category_ID),
  33.    
  34.     PRIMARY KEY(drug_name, drug_category_ID)
  35. );
  36.  
  37. CREATE TABLE disease_category (
  38.     disease_category_ID integer PRIMARY KEY,
  39.     disease_category_name varChar(50) UNIQUE NOT NULL
  40. );
  41.  
  42. CREATE TABLE disease (
  43.     disease_name varChar(50) PRIMARY KEY,
  44.     disease_category_ID integer,
  45.    
  46.     FOREIGN KEY (disease_category_ID) REFERENCES disease_category(disease_category_ID)
  47. );
  48.  
  49. CREATE TABLE treats (
  50.     drug_name varChar(50),
  51.     disease_name varChar(50),
  52.    
  53.     FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
  54.     FOREIGN KEY (disease_name) REFERENCES disease(disease_name),
  55.    
  56.     PRIMARY KEY(drug_name, disease_name)
  57. );
  58.  
  59. CREATE SEQUENCE side_effect_serial START 1;
  60.  
  61. CREATE TABLE side_effect (
  62.     side_effect_ID integer PRIMARY KEY DEFAULT nextval('side_effect_serial'),
  63.     side_effect_name varChar(30) UNIQUE NOT NULL
  64. );
  65.  
  66. CREATE TABLE drug_side_effects (
  67.     drug_name varChar(50),
  68.     side_effect_ID integer,
  69.    
  70.     FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
  71.     FOREIGN KEY (side_effect_ID) REFERENCES side_effect(side_effect_ID),
  72.    
  73.     PRIMARY KEY(drug_name, side_effect_ID)
  74. );
  75.  
  76. CREATE TABLE drug_interaction (
  77.     drug_name_A varChar(50),
  78.     drug_name_B varChar(50),
  79.    
  80.     FOREIGN KEY (drug_name_A) REFERENCES drug(drug_name),
  81.     FOREIGN KEY (drug_name_B) REFERENCES drug(drug_name),
  82.    
  83.     PRIMARY KEY(drug_name_A,drug_name_B)
  84. );
  85.  
  86. CREATE TABLE institution (
  87.     institution_name varChar(50) PRIMARY KEY,
  88.     address varChar(50) NOT NULL,
  89.     country varChar(50) NOT NULL,
  90.  
  91.     UNIQUE (address, country)
  92. );
  93.  
  94. CREATE SEQUENCE researcher_serial START 1;
  95.  
  96. CREATE TABLE researcher (
  97.     researcher_ID integer PRIMARY KEY DEFAULT nextval('researcher_serial'),
  98.     researcher_name varChar(50) NOT NULL,
  99.     institution_name varChar(50),
  100.    
  101.     FOREIGN KEY (institution_name) REFERENCES institution(institution_name)
  102. );
  103.  
  104. CREATE SEQUENCE condition_serial START 1;
  105.  
  106. CREATE TABLE condition (
  107.     condition_ID integer PRIMARY KEY DEFAULT nextval('condition_serial'),
  108.     condition_name varChar(30) UNIQUE NOT NULL
  109. );
  110.  
  111. CREATE TABLE trial (
  112.     trial_ID integer PRIMARY KEY,
  113.     title varChar(100)  UNIQUE NOT NULL,
  114.     start_date time NOT NULL,
  115.     completion_date time NOT NULL,
  116.     participants integer NOT NULL,
  117.     status varChar(20) NOT NULL,
  118.     institution_name varChar(50),
  119.     main_researcher_ID integer,
  120.     condition_ID integer,
  121.    
  122.     FOREIGN KEY (institution_name) REFERENCES institution(institution_name),
  123.     FOREIGN KEY (main_researcher_ID) REFERENCES researcher(researcher_ID),
  124.     FOREIGN KEY (condition_ID) REFERENCES condition(condition_ID)
  125. );
  126.  
  127. CREATE TABLE trial_researchers (
  128.     trial_ID integer,
  129.     researcher_ID integer,
  130.    
  131.     FOREIGN KEY (trial_ID) REFERENCES trial(trial_ID),
  132.     FOREIGN KEY (researcher_ID) REFERENCES researcher(researcher_ID),
  133.    
  134.     PRIMARY KEY(trial_ID, researcher_ID)
  135. );
  136.  
  137. CREATE TABLE trial_drug_disease (
  138.     trial_ID integer,
  139.     drug_name varChar(50),
  140.     disease_name varChar(50),
  141.    
  142.     FOREIGN KEY (trial_ID) REFERENCES trial(trial_ID),
  143.     FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
  144.     FOREIGN KEY (disease_name) REFERENCES disease(disease_name),
  145.    
  146.     PRIMARY KEY(trial_ID, drug_name, disease_name)
  147. );
RAW Paste Data