ProLab2-3/sqlcode.txt at main · StartaBafras/ProLab2-3 · GitHub

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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
-- This script was generated by a beta version of the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public."banka_müdür_tablosu"
(
    "banka_müdür_id" integer NOT NULL,
    isim_soyisim character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "şifre" character varying(30) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "banka_müdür_tablosu_pkey" PRIMARY KEY ("banka_müdür_id")
);

CREATE TABLE IF NOT EXISTS public.faiz_tablosu
(
    faiz_id integer NOT NULL,
    "faiz_adı" character varying(30) COLLATE pg_catalog."default" NOT NULL,
    "faiz_miktarı" numeric(10, 2) NOT NULL,
    CONSTRAINT faiz_tablosu_pkey PRIMARY KEY (faiz_id)
);

CREATE TABLE IF NOT EXISTS public."hesap_açma_talep_tablosu"
(
    talep_id integer NOT NULL,
    talep_eden_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    talep_edilen_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    kur_id integer NOT NULL,
    talep_durumu integer,
    CONSTRAINT "hesap_açma_talep_tablosu_pkey" PRIMARY KEY (talep_id)
);

CREATE TABLE IF NOT EXISTS public.hesap_silme_talep_tablosu
(
    talep_id integer NOT NULL,
    talep_eden_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    talep_edilen_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    silinecek_hesap_no integer NOT NULL,
    talep_durumu integer,
    CONSTRAINT hesap_silme_talep_tablosu_pkey PRIMARY KEY (talep_id)
);

CREATE TABLE IF NOT EXISTS public.kredi_tablosu
(
    kredi_id integer NOT NULL,
    kredi_sahibi_no character varying(11) COLLATE pg_catalog."default" NOT NULL,
    "alınna_ana_para" numeric(10, 2) NOT NULL,
    "alınan_faiz" numeric(10, 2) NOT NULL,
    "faiz_oranı" numeric(10, 2) NOT NULL,
    "gecikme_faiz_oranı" numeric(10, 2) NOT NULL,
    "ödenen_ay" integer NOT NULL,
    "vade_sayısı" integer NOT NULL,
    "ödenen_ana_para" numeric(10, 2) NOT NULL,
    "ödenen_faiz" numeric(10, 2) NOT NULL,
    "gecikme_ayı" integer NOT NULL DEFAULT 0,
    verilme_tarih date NOT NULL,
    CONSTRAINT kredi_tablosu_pkey PRIMARY KEY (kredi_id)
);

CREATE TABLE IF NOT EXISTS public.kredi_talep_tablosu
(
    talep_id integer NOT NULL,
    talep_eden_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    talep_edilen_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    ana_para numeric(10, 2) NOT NULL,
    "vade_sayısı" integer NOT NULL,
    talep_durumu integer,
    CONSTRAINT kredi_talep_tablosu_pkey PRIMARY KEY (talep_id)
);

CREATE TABLE IF NOT EXISTS public.kurlar_tablosu
(
    kur_id integer NOT NULL,
    kur_ismi character varying(30) COLLATE pg_catalog."default" NOT NULL,
    "kur_fiyatı" numeric(10, 2) NOT NULL,
    CONSTRAINT kurlar_tablosu_pkey PRIMARY KEY (kur_id)
);

CREATE TABLE IF NOT EXISTS public."maaş_tablosu"
(
    "maaş_id" integer NOT NULL,
    "maaş_miktarı" integer NOT NULL DEFAULT 4250,
    CONSTRAINT "maaş_tablosu_pkey" PRIMARY KEY ("maaş_id")
);

CREATE TABLE IF NOT EXISTS public."müşteri_bilgisi_tablosu"
(
    "müsteri_no_tc" character varying(11) COLLATE pg_catalog."default" NOT NULL,
    isim_soyisim character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "şifre" character varying(30) COLLATE pg_catalog."default" NOT NULL,
    telefon_no character varying(11) COLLATE pg_catalog."default" NOT NULL,
    e_posta character varying(50) COLLATE pg_catalog."default" NOT NULL,
    adres text COLLATE pg_catalog."default" NOT NULL,
    temsilci_id integer,
    CONSTRAINT "müşteri_bilgisi_tablosu_pkey" PRIMARY KEY ("müsteri_no_tc")
);

CREATE TABLE IF NOT EXISTS public."müşteri_hesap_tablosu"
(
    hesap_id integer NOT NULL,
    "müşteri_no" character varying(11) COLLATE pg_catalog."default" NOT NULL,
    "hesap_türü" integer NOT NULL,
    bakiye numeric(10, 2) NOT NULL,
    CONSTRAINT "müşteri_hesap_tablosu_pkey" PRIMARY KEY (hesap_id)
);

CREATE TABLE IF NOT EXISTS public.temsilci_tablosu
(
    temsilci_id integer NOT NULL,
    isim_soyisim character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "şifre" character varying(30) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT temsilci_tablasu_pkey PRIMARY KEY (temsilci_id)
);

CREATE TABLE IF NOT EXISTS public."işlem_tablosu"
(
    islem_no_id integer NOT NULL,
    "islem_kaynak" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    islem_hedef character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "işlem_çeşidi" character varying(20) NOT NULL,
    tutar numeric(10, 2) NOT NULL,
    kaynak_bakiye numeric(20, 2) NOT NULL,
    hedef_bakiye numeric(20, 2) NOT NULL,
    tarih date NOT NULL,
    CONSTRAINT "İşlem_tablosu_pkey" PRIMARY KEY (islem_no_id)
);

CREATE TABLE IF NOT EXISTS public.banka_bilgisi_tablosu
(
    banka_id integer NOT NULL,
    banka_anapara numeric(15,2) NOT NULL,
    banka_tarih date,
    CONSTRAINT banka_bilgisi_tablosu_pkey PRIMARY KEY (banka_id)
)


ALTER TABLE IF EXISTS public.banka_bilgisi_tablosu
    OWNER to postgres;


ALTER TABLE IF EXISTS public."hesap_açma_talep_tablosu"
    ADD CONSTRAINT kurlar_tablosu FOREIGN KEY (kur_id)
    REFERENCES public.kurlar_tablosu (kur_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public."hesap_açma_talep_tablosu"
    ADD CONSTRAINT "talep_müşteri" FOREIGN KEY (talep_eden_id)
    REFERENCES public."müşteri_bilgisi_tablosu" ("müsteri_no_tc") MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.hesap_silme_talep_tablosu
    ADD CONSTRAINT silme_hesap FOREIGN KEY (silinecek_hesap_no)
    REFERENCES public."müşteri_hesap_tablosu" (hesap_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.hesap_silme_talep_tablosu
    ADD CONSTRAINT "talep_müşteri" FOREIGN KEY (talep_eden_id)
    REFERENCES public."müşteri_bilgisi_tablosu" ("müsteri_no_tc") MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.kredi_talep_tablosu
    ADD CONSTRAINT "müşteri_kredi_talep" FOREIGN KEY (talep_eden_id)
    REFERENCES public."müşteri_bilgisi_tablosu" ("müsteri_no_tc") MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public."müşteri_bilgisi_tablosu"
    ADD CONSTRAINT "müşteri_temsilci" FOREIGN KEY (temsilci_id)
    REFERENCES public.temsilci_tablosu (temsilci_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public."müşteri_hesap_tablosu"
    ADD CONSTRAINT "hesap_müşteri" FOREIGN KEY ("müşteri_no")
    REFERENCES public."müşteri_bilgisi_tablosu" ("müsteri_no_tc") MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public."müşteri_hesap_tablosu"
    ADD CONSTRAINT kur FOREIGN KEY ("hesap_türü")
    REFERENCES public.kurlar_tablosu (kur_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;