-
Notifications
You must be signed in to change notification settings - Fork 0
/
tenant_rental_01.sql
95 lines (83 loc) · 2.16 KB
/
tenant_rental_01.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
--
-- Create database
--
CREATE DATABASE tenant_rental;
--
-- Connect to the database
--
\c tenant_rental;
--
-- Create complexes table
--
CREATE TABLE complexes (
id serial PRIMARY KEY,
name varchar(255) NOT NULL
);
--
-- Create buildings table
--
CREATE TABLE buildings (
id serial PRIMARY KEY,
complex_id integer NOT NULL,
name varchar(255) NOT NULL,
address varchar(255)
);
--
-- Add fk_buildings_complex_id foreign key to buildings
--
ALTER TABLE buildings
ADD CONSTRAINT fk_buildings_complex_id FOREIGN KEY (complex_id) REFERENCES complexes(id) ON UPDATE RESTRICT ON DELETE CASCADE;
--
-- Create apartments table
--
CREATE TABLE apartments (
id serial PRIMARY KEY,
building_id integer NOT NULL,
name varchar(255) NOT NULL,
address varchar(255)
);
--
-- Add fk_apartments_building_id foreign key to buildings
--
ALTER TABLE apartments
ADD CONSTRAINT fk_apartments_building_id FOREIGN KEY (building_id) REFERENCES buildings(id) ON UPDATE RESTRICT ON DELETE CASCADE;
--
-- Create tenants table
--
CREATE TABLE tenants (
id serial PRIMARY KEY,
name varchar(255) NOT NULL,
address varchar(255)
);
--
-- Create tenant_apartments table
--
CREATE TABLE tenant_apartments (
tenant_id integer NOT NULL,
apartment_id integer NOT NULL,
PRIMARY KEY (tenant_id, apartment_id)
);
--
-- Add fk_tenant_apartments_tenant_id foreign key to tenant_apartments table
--
ALTER TABLE tenant_apartments
ADD CONSTRAINT fk_tenant_apartments_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON UPDATE RESTRICT ON DELETE CASCADE;
--
-- Add fk_tenant_apartments_apartment_id foreign key to tenant_apartments table
--
ALTER TABLE tenant_apartments
ADD CONSTRAINT fk_tenant_apartments_apartment_id FOREIGN KEY (apartment_id) REFERENCES apartments(id) ON UPDATE RESTRICT ON DELETE CASCADE;
--
-- Create requests table
--
CREATE TABLE requests (
id serial PRIMARY KEY,
apartment_id integer NOT NULL,
status varchar(25),
description varchar(1000)
);
--
-- Add fk_requests_apartment_id foreign key to requests table
--
ALTER TABLE requests
ADD CONSTRAINT fk_requests_apartment_id FOREIGN KEY (apartment_id) REFERENCES apartments(id) ON UPDATE RESTRICT ON DELETE CASCADE;