rPVpdv5X

1BEGIN TRANSACTION;
2
3DROP TABLE IF EXISTS user_task CASCADE;
4DROP TABLE IF EXISTS user_account CASCADE;
5DROP TABLE IF EXISTS home CASCADE;
6DROP TABLE IF EXISTS appliance CASCADE;
7DROP TABLE IF EXISTS mechanical_asset CASCADE;
8DROP TABLE IF EXISTS infrastructure CASCADE;
9DROP TABLE IF EXISTS appliance CASCADE;
10DROP TABLE IF EXISTS appliance_type CASCADE;
11DROP TABLE IF EXISTS mechanical_asset_type CASCADE;
12DROP TABLE IF EXISTS infrastructure_type CASCADE;
13DROP TABLE IF EXISTS question CASCADE;
14DROP TABLE IF EXISTS type_question CASCADE;
15DROP TABLE IF EXISTS task CASCADE;
16
17
18
19DROP SEQUENCE IF EXISTS seq_user_id;
20DROP SEQUENCE IF EXISTS seq_home_id;
21DROP SEQUENCE IF EXISTS seq_appliance_id;
22DROP SEQUENCE IF EXISTS seq_asset_id;
23DROP SEQUENCE IF EXISTS seq_infrastructure_id;
24DROP SEQUENCE IF EXISTS seq_user_task_id;
25DROP SEQUENCE IF EXISTS seq_appliance_type_id;
26DROP SEQUENCE IF EXISTS seq_mechanical_asset_type_id;
27DROP SEQUENCE IF EXISTS seq_infrastructure_type_id;
28DROP SEQUENCE IF EXISTS seq_question_id;
29DROP SEQUENCE IF EXISTS seq_user_task_id;
30DROP SEQUENCE IF EXISTS seq_task_id;
31DROP SEQUENCE IF EXISTS seq_type_question_id;
32
33
34
35CREATE TABLE user_account (
36        user_id serial NOT NULL,
37        first_name varchar(20) NOT NULL,
38        last_name varchar(20) NOT NULL,
39        email_address varchar(320) NOT NULL,
40        notification_preference varchar(20) NOT NULL,
41        phone_number varchar(320),
42        
43        CONSTRAINT pk_user_id PRIMARY KEY (user_id)
44);
45
46
47CREATE TABLE home (
48        home_id serial NOT NULL,
49        user_id int NOT NULL,
50        street_address varchar(30) NOT NULL,
51        address_two varchar(30),
52        city varchar(30) NOT NULL,
53        state varchar(30) NOT NULL,
54        zip int NOT NULL,
55        occupants int,
56       
57        CONSTRAINT pk_home_id PRIMARY KEY (home_id),
58        CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES user_account(user_id)
59
60);
61
62
63CREATE TABLE appliance (
64        appliance_id serial NOT NULL,
65        home_id int NOT NULL,
66        type varchar(20) NOT NULL,
67        age varchar,
68        model_number varchar(50),
69        
70        CONSTRAINT pk_appliance_id PRIMARY KEY (appliance_id),
71        CONSTRAINT fk_home_id FOREIGN KEY (home_id) REFERENCES home(home_id),
72        CONSTRAINT appliance_type_check CHECK ((type = 'Refrigerator') OR (type = 'Dishwasher') OR (type = 'Washing Machine') OR (type = 'Dryer') OR (type = 'Range') OR (type = 'Oven'))
73        
74);
75
76CREATE TABLE mechanical_asset (
77        asset_id serial NOT NULL,
78        home_id int NOT NULL,
79        type varchar(20) NOT NULL,
80        age date,
81        model_number varchar(50),
82        maintenance_duration int NOT NULL,
83        
84        CONSTRAINT pk_asset_id PRIMARY KEY (asset_id),
85        CONSTRAINT fk_home_id FOREIGN KEY (home_id) REFERENCES home(home_id),
86        CONSTRAINT mechanical_asset_type_check CHECK ((type = 'Furnace') OR (type = 'Water Heater') OR (type = 'Air Conditioner') OR (type = 'Boiler') OR (type = 'Heat Pump') OR (type = 'Forced Air') )
87        
88);
89
90CREATE TABLE infrastructure (
91        infrastructure_id serial NOT NULL,
92        home_id int NOT NULL,
93        type varchar(20) NOT NULL,
94        age date,
95        model_number varchar(50),
96        maintenance_duration int,
97        
98        CONSTRAINT pk_infrastructure_id PRIMARY KEY (infrastructure_id),
99        CONSTRAINT fk_home_id FOREIGN KEY (home_id) REFERENCES home(home_id),
100        CONSTRAINT infrastructure_type_check CHECK ((type = 'Fence') OR (type = 'Roof') OR (type = 'Driveway') OR (type = 'Patio'))
101        
102);
103
104CREATE TABLE task (
105        the_task_id serial,
106        type varchar,
107        task_name varchar,
108        task_description text,
109        reason text,
110        task_url text,
111        task_interval varchar(40),
112        
113        CONSTRAINT pk_the_task_id PRIMARY KEY (the_task_id)
114);
115
116
117CREATE TABLE user_task(
118        user_task_id serial NOT NULL,
119        appliance_id int,
120        asset_id int,
121        infrastructure_id int,
122        home_id int NOT NULL,
123        task_id int NOT NULL,
124        date_last_performed varchar,
125        associated_products varchar,
126        maintenance_duration int,
127        
128        CONSTRAINT pk_user_task_id PRIMARY KEY (user_task_id),
129        CONSTRAINT fk_home_id FOREIGN KEY (home_id) REFERENCES home(home_id),
130        CONSTRAINT fk_infrastructure_id FOREIGN KEY (infrastructure_id) REFERENCES infrastructure(infrastructure_id),
131        CONSTRAINT fk_asset_id FOREIGN KEY (asset_id) REFERENCES mechanical_asset(asset_id),
132        CONSTRAINT fk_appliance_id FOREIGN KEY (appliance_id) REFERENCES appliance(appliance_id),
133        CONSTRAINT fk_task_id FOREIGN KEY (task_id) REFERENCES task(the_task_id)
134);
135
136CREATE TABLE appliance_type (
137        appliance_type_id serial NOT NULL,
138        type varchar(20) NOT NULL,
139        
140        CONSTRAINT pk_type_id PRIMARY KEY (appliance_type_id)
141);
142
143CREATE TABLE mechanical_asset_type(
144        mechanical_asset_type_id serial NOT NULL,
145        type varchar(20) NOT NULL,
146        
147        CONSTRAINT pk_mechanical_asset_type_id PRIMARY KEY (mechanical_asset_type_id)
148);
149
150CREATE TABLE infrastructure_type (
151        infrastructure_type_id serial NOT NULL,
152        type varchar(20) NOT NULL,
153        
154        CONSTRAINT pk_infrastructure_type_id PRIMARY KEY (infrastructure_type_id)
155);
156
157CREATE TABLE question (
158        question_id serial NOT NULL,
159        question varchar NOT NULL,
160        sub_question varchar,
161        
162        CONSTRAINT pk_question_id PRIMARY KEY (question_id)
163);
164CREATE TABLE type_question (
165        type_question_id serial,
166        mechanical_type_id int,
167        appliance_type_id int,
168        infrastructure_type_id int,
169        question_id int,
170        
171        CONSTRAINT pk_type_question_id PRIMARY KEY (type_question_id),
172        CONSTRAINT fk_mechanical_type_id FOREIGN KEY (mechanical_type_id) REFERENCES mechanical_asset_type(mechanical_asset_type_id),
173        CONSTRAINT fk_appliance_type_id FOREIGN KEY (appliance_type_id) REFERENCES appliance_type(appliance_type_id),
174        CONSTRAINT fk_infrastructure_type_id FOREIGN KEY (infrastructure_type_id) REFERENCES infrastructure_type(infrastructure_type_id),
175        CONSTRAINT fk_question_id FOREIGN KEY (question_id) REFERENCES question(question_id)
176
177);
178
179
180END TRANSACTION;