USE studies;
DROP TABLE studies;
CREATE TABLE studies
(
id INTEGER IDENTITY,
title VARCHAR(300),
description VARCHAR(4000),
url VARCHAR(255),
period VARCHAR(300),
dtstart DATE,
dtend DATE,
rpo CHAR(1),
accrual CHAR(1),
layman_title VARCHAR(300),
layman_description VARCHAR(4000),
layman_categories VARCHAR(300),
layman_keywords VARCHAR(300),
expiration DATE,
irb CHAR(1),
irb_study_code VARCHAR(20),
study_plan VARCHAR(4000),
study_purpose VARCHAR(2000),
keywords VARCHAR(300),
PRIMARY KEY (id)
);
DROP TABLE grant_amounts;
CREATE TABLE grant_amounts
(
id INTEGER IDENTITY,
study_id INTEGER,
currency VARCHAR(15),
amount FLOAT(2),
PRIMARY KEY (id)
);
DROP TABLE entities;
CREATE TABLE entities
(
id INTEGER IDENTITY,
entity_type VARCHAR(10),
study_id INTEGER,
tel VARCHAR(25),
email VARCHAR(300),
url VARCHAR(255),
org VARCHAR(300),
fn VARCHAR(300),
PRIMARY KEY (id)
);
DROP TABLE addresses;
CREATE TABLE addresses
(
id INTEGER IDENTITY,
entity_id INTEGER,
staffer_id INTEGER,
post_office_box VARCHAR(10),
street_address VARCHAR(300),
extended_address VARCHAR(300),
locality VARCHAR(30),
region VARCHAR(30),
postal_code VARCHAR(10),
country_name VARCHAR(30),
PRIMARY KEY (id)
);
DROP TABLE focus_regions;
CREATE TABLE focus_regions
(
id INTEGER IDENTITY,
study_id INTEGER,
locality VARCHAR(30),
region VARCHAR(30),
postal_code VARCHAR(10),
country VARCHAR(30),
tel VARCHAR(10),
email VARCHAR(300),
url VARCHAR(300),
org VARCHAR(300),
fn VARCHAR(300),
PRIMARY KEY (id)
);
DROP TABLE staffers;
CREATE TABLE staffers
(
id INTEGER IDENTITY,
study_id INTEGER,
ufid VARCHAR(30),
involvement VARCHAR(30),
fn VARCHAR(30),
PRIMARY KEY (id)
);