forked from jessicapardo/ETL-challenge
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathschema.sql
122 lines (100 loc) · 3.5 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
-- Exported from QuickDBD: https://www.quickdatabasediagrams.com/
-- Link to schema: https://app.quickdatabasediagrams.com/#/d/k71TIH
CREATE TABLE "state" (
"state_id" integer NOT NULL,
"state_abr" varchar(2) NOT NULL,
"state_name" varchar(30) NOT NULL,
"lifeQualityRank" integer NOT NULL,
"healthCareRank" integer NOT NULL,
"educationRank" integer NOT NULL,
"economyRank" integer NOT NULL,
CONSTRAINT "pk_state" PRIMARY KEY ("state_id")
);
CREATE TABLE "housing" (
"state_id" integer NOT NULL,
"date" date NOT NULL,
"MedianListingPricePerSqft_AllHomes" integer NOT NULL,
"MedianRentalPricePerSqft_AllHomes" integer NOT NULL,
"PctOfHomesDecreasingInValues_AllHomes" float NOT NULL,
"PctOfHomesIncreasingInValues_AllHomes" float NOT NULL
);
CREATE TABLE "income" (
"state_id" integer NOT NULL,
"household_income" float NOT NULL,
"year" integer NOT NULL
);
CREATE TABLE "crime" (
"state_id" integer NOT NULL,
"year" integer NOT NULL,
"crime_rate_per_100000" float NOT NULL,
"CPOPARST" float NOT NULL,
"CPOPCRIM" float NOT NULL,
"AG_ARRST" float NOT NULL,
"AG_OFF" float NOT NULL,
"COVIND" float NOT NULL,
"INDEX" float NOT NULL,
"MODINDX" float NOT NULL,
"MURDER" float NOT NULL,
"RAPE" float NOT NULL,
"ROBBERY" float NOT NULL,
"AGASSLT" float NOT NULL,
"BURGLRY" float NOT NULL,
"LARCENY" float NOT NULL,
"MVTHEFT" float NOT NULL,
"ARSON" float NOT NULL,
"population" float NOT NULL,
"FIPS_ST" float NOT NULL,
"FIPS_CTY" float NOT NULL
);
CREATE TABLE "public_schools" (
"state_id" integer NOT NULL,
"City" varchar NOT NULL,
"SchoolName" varchar NOT NULL,
"Address" varchar NOT NULL,
"Zip" integer NOT NULL,
"X" float NOT NULL,
"Y" float NOT NULL,
"DistrictID" integer NOT NULL,
"StartGrade" varchar NOT NULL,
"EndGrade" varchar NOT NULL
);
CREATE TABLE "hospitals" (
"id" serial primary key,
"state_id" integer NOT NULL,
"name" varchar NOT NULL,
"address" varchar NOT NULL,
"city" varchar NOT NULL,
"zip" integer NOT NULL,
"website" varchar NOT NULL,
"owner" varchar NOT NULL,
"rating" float NOT NULL
);
CREATE TABLE "regionCode" (
"code" int primary key,
"name" varchar);
CREATE TABLE "unemployment" (
"state_id" integer NOT NULL,
"regionCode" int NOT NULL,
"year" float NOT NULL,
"unemployment" float,
PRIMARY KEY ("regionCode", "year")
);
CREATE TABLE "min_wage" (
"state_id" integer NOT NULL,
"Year" integer NOT NULL,
"min_wage" float NOT NULL
);
ALTER TABLE "housing" ADD CONSTRAINT "fk_housing_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");
ALTER TABLE "income" ADD CONSTRAINT "fk_income_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");
ALTER TABLE "crime" ADD CONSTRAINT "fk_crime_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");
ALTER TABLE "public_schools" ADD CONSTRAINT "fk_public_schools_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");
ALTER TABLE "hospitals" ADD CONSTRAINT "fk_hospitals_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");
ALTER TABLE "unemployment" ADD CONSTRAINT "fk_unemployment_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");
ALTER TABLE "min_wage" ADD CONSTRAINT "fk_min_wage_state_id" FOREIGN KEY("state_id")
REFERENCES "state" ("state_id");