8fbqSAse

1
2
3
4DROP TABLE IF EXISTS "ETHNICITY";
5CREATE TABLE ETHNICITY
6(
7	ETHNICITY_ID BIGSERIAL NOT NULL PRIMARY KEY,
8	CODE             INTEGER NOT NULL,
9	DESCRIPTION      VARCHAR(2048) NOT NULL,
10);
11
12
13DROP TABLE IF EXISTS "GENDER";
14CREATE TABLE GENDER
15(
16	GENDER_ID BIGSERIAL NOT NULL PRIMARY KEY,
17	CODE             INTEGER NOT NULL,
18	DESCRIPTION      VARCHAR(2048) NOT NULL,
19);
20
21DROP TABLE IF EXISTS "RACE";
22CREATE TABLE RACE
23(
24	RACE_ID BIGSERIAL NOT NULL PRIMARY KEY,
25	CODE             INTEGER NOT NULL,
26	DESCRIPTION      VARCHAR(2048) NOT NULL,
27);
28
29DROP TABLE IF EXISTS "INSTITUTION_TYPE";
30CREATE TABLE INSTITUTION_TYPE
31(
32	INSTITUTION_TYPE_ID BIGSERIAL NOT NULL PRIMARY KEY,
33	CODE             INTEGER NOT NULL,
34	DESCRIPTION      VARCHAR(2048) NOT NULL,
35);
36
37DROP TABLE IF EXISTS "FACILITY_TYPE";
38CREATE TABLE FACILITY_TYPE
39(
40	FACILITY_TYPE_ID BIGSERIAL NOT NULL PRIMARY KEY,
41	CODE             INTEGER NOT NULL,
42	DESCRIPTION      VARCHAR(2048) NOT NULL,
43);
44
45
46--new columns
47
48ALTER TABLE INSTITUTION
49ADD INSTITUTION_TYPE_ID INTEGER NULL
50ALTER TABLE INSTITUTION ADD CONSTRAINT INSTITUTION_TYPE_ID_FKEY 
51FOREIGN KEY (INSITUTION_TYPE_ID) 
52REFERENCES DATA_TYPE (DATA_TYPE_ID);
53
54ADD COLUMN CONTACT_NAME TYPE VARCHAR(64);
55ADD COLUMN CONTACT_PHONE TYPE VARCHAR(64);
56ADD COLUMN EMAIL VARCHAR(256);
57ADD COLUMN EMAIL_SUBJECT VARCHAR(256);
58
59
60
61DROP TABLE IF EXISTS "FACILITY";
62CREATE TABLE FACILITY
63(
64	FACILITY_ID BIGSERIAL NOT NULL PRIMARY KEY,
65	NAME    VARCHAR(256) NOT NULL UNIQUE,
66    FACILITY_TYPE_ID   INTEGER NOT NULL,
67    CONTACT_NAME      VARCHAR(64) NOT NULL,
68	CONTACT_PHONE     VARCHAR(64) NOT NULL,
69	EMAIL VARCHAR(256) NOT NULL,
70	EMAIL_SUBJECT VARCHAR(256) NOT NULL,
71	INSTITUTION_ID   INTEGER NOT NULL,
72	CREATED_BY           VARCHAR(256) NOT NULL,
73	CREATED_DATE         TIMESTAMPTZ DEFAULT Now() NOT NULL, 
74	UPDATED_BY           VARCHAR(256),
75	UPDATED_DATE         TIMESTAMPTZ,
76	FOREIGN KEY (FACILITY_TYPE_ID) REFERENCES DATA_TYPE (DATA_TYPE_ID),
77	FOREIGN KEY (INSTITUTION_ID) REFERENCES INSTITUTION (INSTITUTION_ID)
78);
79
80DROP TABLE IF EXISTS "UNIT";
81CREATE TABLE UNIT
82(
83	UNIT_ID BIGSERIAL NOT NULL PRIMARY KEY,
84	CODE        INTEGER NOT NULL,
85	NAME        VARCHAR(256) NOT NULL UNIQUE,
86	DESCRIPTION VARCHAR(2048) NOT NULL,
87    FACILITY_ID INTEGER NOT NULL,
88	CREATED_BY           VARCHAR(256) NOT NULL,
89	CREATED_DATE         TIMESTAMPTZ DEFAULT Now() NOT NULL, 
90	UPDATED_BY           VARCHAR(256),
91	UPDATED_DATE         TIMESTAMPTZ,
92    FOREIGN KEY (UNIT_ID) REFERENCES UNIT (UNIT_ID)
93);
94
95
96DROP TABLE IF EXISTS "EDU_FAMILY_SURVEY";
97CREATE TABLE EDU_FAMILY_SURVEY
98(
99	EDU_FAMILY_SURVEY_ID BIGSERIAL NOT NULL PRIMARY KEY,
100	HOUSEHOLD_ID     VARCHAR(256) NOT NULL UNIQUE,
101    INSTITUTION_ID   INTEGER NOT NULL,
102    FACILITY_ID      INTEGER NOT NULL,
103    UNIT_ID          INTEGER NOT NULL,
104    GENDER_ID        INTEGER NOT NULL,
105	RACE_ID          INTEGER NOT NULL,
106	ATTENDEE_ID INTEGER NOT NULL,
107	CREATED_BY           VARCHAR(256) NOT NULL,
108	CREATED_DATE         TIMESTAMPTZ DEFAULT Now() NOT NULL, 
109	UPDATED_BY           VARCHAR(256),
110	UPDATED_DATE         TIMESTAMPTZ,
111);