Fibzni75

1#@(#) script.ddl
2
3DROP TABLE IF EXISTS PAPILDOMAS_MOKESTIS;
4DROP TABLE IF EXISTS SUTARTIS;
5DROP TABLE IF EXISTS NUOLAIDA;
6DROP TABLE IF EXISTS BATAI;
7DROP TABLE IF EXISTS PREKIU_KREPSELIS;
8DROP TABLE IF EXISTS GARANTINIS_APTARNAVIMAS;
9DROP TABLE IF EXISTS KLIENTAS;
10DROP TABLE IF EXISTS DARBUOTOJAS;
11DROP TABLE IF EXISTS SANDELYS;
12DROP TABLE IF EXISTS PARDUOTUVE;
13DROP TABLE IF EXISTS MODELIS;
14DROP TABLE IF EXISTS KURJERIS;
15DROP TABLE IF EXISTS PRISTATYMO_IMONE;
16DROP TABLE IF EXISTS MOKESTIS;
17DROP TABLE IF EXISTS MIESTAS;
18DROP TABLE IF EXISTS GAMINTOJAS;
19CREATE TABLE GAMINTOJAS
20(
21	pavadinimas varchar (20) NOT NULL,
22	id_GAMINTOJAS int NOT NULL,
23	PRIMARY KEY(id_GAMINTOJAS)
24);
25
26CREATE TABLE MIESTAS
27(
28	pavadinimas varchar (20) NOT NULL,
29	id_MIESTAS int NOT NULL,
30	PRIMARY KEY(id_MIESTAS)
31);
32
33CREATE TABLE MOKESTIS
34(
35	pavadinimas varchar (20) NOT NULL,
36	aprasymas varchar (20) NOT NULL,
37	id_MOKESTIS int NOT NULL,
38	PRIMARY KEY(id_MOKESTIS)
39);
40
41CREATE TABLE PRISTATYMO_IMONE
42(
43	pavadinimas varchar (20) NOT NULL,
44	tel_nr varchar (12) NOT NULL,
45	el_pastas varchar (20) NOT NULL,
46	miestas varchar (20) NOT NULL,
47	gatve varchar (20) NOT NULL,
48	pastato_nr int NOT NULL,
49	pasto_kodas char NOT NULL,
50	imones_kodas char NOT NULL,
51	id_PRISTATYMO_IMONE int NOT NULL,
52	PRIMARY KEY(id_PRISTATYMO_IMONE)
53);
54
55CREATE TABLE KURJERIS
56(
57	tabelio_nr int NOT NULL,
58	vardas varchar (20) NOT NULL,
59	pavarde varchar (20) NOT NULL,
60	tel_nr varchar (20) NOT NULL,
61	fk_PRISTATYMO_IMONEid_PRISTATYMO_IMONE int NOT NULL,
62	PRIMARY KEY(tabelio_nr),
63	CONSTRAINT dirba2 FOREIGN KEY(fk_PRISTATYMO_IMONEid_PRISTATYMO_IMONE) REFERENCES PRISTATYMO_IMONE (id_PRISTATYMO_IMONE)
64);
65
66CREATE TABLE MODELIS
67(
68	pavadinimas varchar (20) NOT NULL,
69	id_MODELIS int NOT NULL,
70	fk_GAMINTOJASid_GAMINTOJAS int NOT NULL,
71	PRIMARY KEY(id_MODELIS),
72	CONSTRAINT turi2 FOREIGN KEY(fk_GAMINTOJASid_GAMINTOJAS) REFERENCES GAMINTOJAS (id_GAMINTOJAS)
73);
74
75CREATE TABLE PARDUOTUVE
76(
77	pavadinimas varchar (20) NOT NULL,
78	tel_nr varchar (12) NOT NULL,
79	el_pastas varchar (20) NOT NULL,
80	darbo_laikas datetime NOT NULL,
81	miestas varchar (20) NOT NULL,
82	gatve varchar (20) NOT NULL,
83	pastato_nr int NOT NULL,
84	pasto_kodas char NOT NULL,
85	id_PARDUOTUVE int NOT NULL,
86	fk_MIESTASid_MIESTAS int NOT NULL,
87	fk_PRISTATYMO_IMONEid_PRISTATYMO_IMONE int NOT NULL,
88	PRIMARY KEY(id_PARDUOTUVE),
89	CONSTRAINT turi7 FOREIGN KEY(fk_MIESTASid_MIESTAS) REFERENCES MIESTAS (id_MIESTAS),
90	CONSTRAINT bendradarbiauja FOREIGN KEY(fk_PRISTATYMO_IMONEid_PRISTATYMO_IMONE) REFERENCES PRISTATYMO_IMONE (id_PRISTATYMO_IMONE)
91);
92
93CREATE TABLE SANDELYS
94(
95	pavadinimas varchar (20) NOT NULL,
96	miestas varchar (20) NOT NULL,
97	gatve varchar (20) NOT NULL,
98	pastato_nr int NOT NULL,
99	pasto_kodas char NOT NULL,
100	id_SANDELYS int NOT NULL,
101	fk_MIESTASid_MIESTAS int NOT NULL,
102	PRIMARY KEY(id_SANDELYS),
103	CONSTRAINT turi6 FOREIGN KEY(fk_MIESTASid_MIESTAS) REFERENCES MIESTAS (id_MIESTAS)
104);
105
106CREATE TABLE DARBUOTOJAS
107(
108	tabelio_nr int NOT NULL,
109	vardas varchar (20) NOT NULL,
110	pavarde varchar (20) NOT NULL,
111	tel_nr varchar (12) NOT NULL,
112	fk_SANDELYSid_SANDELYS int NOT NULL,
113	fk_PARDUOTUVEid_PARDUOTUVE int NOT NULL,
114	PRIMARY KEY(tabelio_nr),
115	CONSTRAINT priziuri FOREIGN KEY(fk_SANDELYSid_SANDELYS) REFERENCES SANDELYS (id_SANDELYS),
116	CONSTRAINT dirba1 FOREIGN KEY(fk_PARDUOTUVEid_PARDUOTUVE) REFERENCES PARDUOTUVE (id_PARDUOTUVE)
117);
118
119CREATE TABLE KLIENTAS
120(
121	vardas varchar (20) NOT NULL,
122	pavarde varchar (20) NOT NULL,
123	tel_nr varchar (20) NOT NULL,
124	el_pastas varchar (20) NOT NULL,
125	miestas varchar (20) NOT NULL,
126	gatve varchar (20) NOT NULL,
127	namo_nr int NOT NULL,
128	pasto_kodas char NOT NULL,
129	id_KLIENTAS int NOT NULL,
130	fk_KURJERIStabelio_nr int NOT NULL,
131	PRIMARY KEY(id_KLIENTAS),
132	CONSTRAINT pristato FOREIGN KEY(fk_KURJERIStabelio_nr) REFERENCES KURJERIS (tabelio_nr)
133);
134
135CREATE TABLE GARANTINIS_APTARNAVIMAS
136(
137	galioja_nuo date NOT NULL,
138	galioja_iki date NOT NULL,
139	id_GARANTINIS_APTARNAVIMAS int NOT NULL,
140	fk_KLIENTASid_KLIENTAS int NOT NULL,
141	PRIMARY KEY(id_GARANTINIS_APTARNAVIMAS),
142	CONSTRAINT priklauso FOREIGN KEY(fk_KLIENTASid_KLIENTAS) REFERENCES KLIENTAS (id_KLIENTAS)
143);
144
145CREATE TABLE PREKIU_KREPSELIS
146(
147	id int NOT NULL,
148	kiekis int NOT NULL,
149	suma decimal NOT NULL,
150	spalva varchar (20) NOT NULL,
151	gamintojas varchar (20) NOT NULL,
152	dydis char (4) NOT NULL,
153	fk_KLIENTASid_KLIENTAS int NOT NULL,
154	PRIMARY KEY(id),
155	CHECK(dydis in ('40', '41', '42', '43', '44.5', '44', '45', '45.5', '46', '46.5', '47', '48', '49', '50')),
156	CONSTRAINT susideda FOREIGN KEY(fk_KLIENTASid_KLIENTAS) REFERENCES KLIENTAS (id_KLIENTAS)
157);
158
159CREATE TABLE BATAI
160(
161	id int NOT NULL,
162	medziaga varchar (20) NOT NULL,
163	spalva varchar (20) NOT NULL,
164	pagaminimo_data date NOT NULL,
165	dydis char NOT NULL,
166	kilmes_salis varchar (20) NOT NULL,
167	verte decimal NOT NULL,
168	padas char (17) NOT NULL,
169	tipai char (23) NOT NULL,
170	fk_PREKIU_KREPSELISid int NOT NULL,
171	fk_MODELISid_MODELIS int NOT NULL,
172	PRIMARY KEY(id),
173	CHECK(padas in ('guminis', 'poliretanininis', 'etileno_vinilinis', 'termoplastinis')),
174	CHECK(tipai in ('kedai', 'krepsinio_bateliai', 'futbolo_bateliai', 'auliniai_batai', 'kalnu_slidziu_bateliai', 'mokasinai', 'sandalai', 'baleto_bateliai', 'riedlentininku_bateliai', 'proginiai_bateliai')),
175	CONSTRAINT turi4 FOREIGN KEY(fk_PREKIU_KREPSELISid) REFERENCES PREKIU_KREPSELIS (id),
176	CONSTRAINT turi1 FOREIGN KEY(fk_MODELISid_MODELIS) REFERENCES MODELIS (id_MODELIS)
177);
178
179CREATE TABLE NUOLAIDA
180(
181	nuolaidos_kodas int NOT NULL,
182	suma decimal NOT NULL,
183	fk_PREKIU_KREPSELISid int NOT NULL,
184	PRIMARY KEY(nuolaidos_kodas),
185	CONSTRAINT turi3 FOREIGN KEY(fk_PREKIU_KREPSELISid) REFERENCES PREKIU_KREPSELIS (id)
186);
187
188CREATE TABLE SUTARTIS
189(
190	sutarties_id int NOT NULL,
191	sutarties_data date NOT NULL,
192	imones_kodas char NOT NULL,
193	busena char (11) NOT NULL,
194	mokejimo_tipas char (17) NOT NULL,
195	pristatymas char (23) NOT NULL,
196	fk_KLIENTASid_KLIENTAS int NOT NULL,
197	fk_PREKIU_KREPSELISid int NOT NULL,
198	fk_PARDUOTUVEid_PARDUOTUVE int NOT NULL,
199	fk_PRISTATYMO_IMONEid_PRISTATYMO_IMONE int NOT NULL,
200	PRIMARY KEY(sutarties_id),
201	CHECK(busena in ('laukiama', 'patvirtinta', 'nutraukta')),
202	CHECK(mokejimo_tipas in ('grynais', 'el.bankininkyste', 'kreditine_kortele')),
203	CHECK(pristatymas in ('i_namus', 'pastu', 'i_pastomata', 'atsiemimas_parduotuveje')),
204	UNIQUE(fk_PREKIU_KREPSELISid),
205	CONSTRAINT sudaro1 FOREIGN KEY(fk_KLIENTASid_KLIENTAS) REFERENCES KLIENTAS (id_KLIENTAS),
206	CONSTRAINT turi5 FOREIGN KEY(fk_PREKIU_KREPSELISid) REFERENCES PREKIU_KREPSELIS (id),
207	CONSTRAINT sudaro2 FOREIGN KEY(fk_PARDUOTUVEid_PARDUOTUVE) REFERENCES PARDUOTUVE (id_PARDUOTUVE),
208	CONSTRAINT sudaro3 FOREIGN KEY(fk_PRISTATYMO_IMONEid_PRISTATYMO_IMONE) REFERENCES PRISTATYMO_IMONE (id_PRISTATYMO_IMONE)
209);
210
211CREATE TABLE PAPILDOMAS_MOKESTIS
212(
213	kiekis int NOT NULL,
214	suma decimal NOT NULL,
215	id_PAPILDOMAS_MOKESTIS int NOT NULL,
216	fk_MOKESTISid_MOKESTIS int NOT NULL,
217	fk_SUTARTISsutarties_id int NOT NULL,
218	PRIMARY KEY(id_PAPILDOMAS_MOKESTIS),
219	CONSTRAINT priskaiciuotas FOREIGN KEY(fk_MOKESTISid_MOKESTIS) REFERENCES MOKESTIS (id_MOKESTIS),
220	CONSTRAINT priskirta FOREIGN KEY(fk_SUTARTISsutarties_id) REFERENCES SUTARTIS (sutarties_id)
221);