vwwaXBmY

1Drop database if exists vanzari;
2CREATE SCHEMA IF NOT EXISTS vanzari;
3USE vanzari;
4
5CREATE TABLE IF NOT EXISTS `beneficiar` (
6  `Cod_B` int(11) NOT NULL,
7  `Nume` varchar(255) NOT NULL,
8  `Oras` varchar(255) NOT NULL
9) ENGINE=InnoDB DEFAULT CHARSET=latin1;
10
11--
12-- Salvarea datelor din tabel `beneficiar`
13--
14
15INSERT INTO `beneficiar` (`Cod_B`, `Nume`, `Oras`) VALUES
16(1, 'Serv SIM', 'Sibiu'),
17(2, 'ARIPA Light', 'Bucuresti'),
18(3, 'E Agricultura', 'Caras-Severin'),
19(4, 'NET IT', 'Olt'),
20(5, 'Fier CTC', 'Galati'),
21(6, 'RAF ALI', 'Neamt'),
22(7, 'FIRENZE', 'Bacau'),
23(8, 'Quinta Essenta', 'Arad'),
24(9, 'Swietelsky Construct', 'Braila'),
25(10, 'IRIDEX GROUP SI', 'Harghita');
26
27-- --------------------------------------------------------
28
29--
30-- Structura de tabel pentru tabelul `cereri`
31--
32
33CREATE TABLE IF NOT EXISTS `cereri` (
34  `Cod_B` int(11) NOT NULL,
35  `Cod_P` int(11) NOT NULL,
36  `Pret` int(11) NOT NULL,
37  `Cantitate` int(11) NOT NULL
38) ENGINE=InnoDB DEFAULT CHARSET=latin1;
39
40--
41-- Salvarea datelor din tabel `cereri`
42--
43
44INSERT INTO `cereri` (`Cod_B`, `Cod_P`, `Pret`, `Cantitate`) VALUES
45(3, 3, 300, 12),
46(3, 5, 300, 20),
47(6, 2, 300, 12);
48
49-- --------------------------------------------------------
50
51--
52-- Structura de tabel pentru tabelul `furnizor`
53--
54
55CREATE TABLE IF NOT EXISTS `furnizor` (
56  `Cod_F` int(11) NOT NULL,
57  `Nume` varchar(255) NOT NULL,
58  `Oras` varchar(255) NOT NULL
59) ENGINE=InnoDB DEFAULT CHARSET=latin1;
60
61--
62-- Salvarea datelor din tabel `furnizor`
63--
64
65INSERT INTO `furnizor` (`Cod_F`, `Nume`, `Oras`) VALUES
66(1, 'Dacia Service', 'Cluj'),
67(2, 'Mercury Lighting', 'Cluj'),
68(3, 'Agromec AG', 'Targu Mures'),
69(4, 'Computer Sharing', 'Bucuresti'),
70(5, 'Metal Brasov', 'Brasov'),
71(6, 'Interfrig', 'Prahova'),
72(7, 'Leoni Wiring', 'Vaslui'),
73(8, 'Remedia', 'Alba Iulia'),
74(9, 'Constructii Feroviare', 'Dambovita'),
75(10, 'Sidor Construct', 'Iasi');
76
77-- --------------------------------------------------------
78
79--
80-- Structura de tabel pentru tabelul `oferte`
81--
82
83CREATE TABLE IF NOT EXISTS `oferte` (
84  `Cod_F` int(11) NOT NULL,
85  `Cod_P` int(11) NOT NULL,
86  `Pret` int(11) NOT NULL,
87  `Cantitate` int(11) NOT NULL
88) ENGINE=InnoDB DEFAULT CHARSET=latin1;
89
90--
91-- Salvarea datelor din tabel `oferte`
92--
93
94INSERT INTO `oferte` (`Cod_F`, `Cod_P`, `Pret`, `Cantitate`) VALUES
95(3, 2, 250, 10),
96(8, 5, 400, 15);
97
98-- --------------------------------------------------------
99
100--
101-- Structura de tabel pentru tabelul `produs`
102--
103
104CREATE TABLE IF NOT EXISTS `produs` (
105  `Cod_P` int(11) NOT NULL,
106  `uM` varchar(40) NOT NULL,
107  `Nume` varchar(255) NOT NULL
108) ENGINE=InnoDB DEFAULT CHARSET=latin1;
109
110--
111-- Salvarea datelor din tabel `produs`
112--
113
114INSERT INTO `produs` (`Cod_P`, `uM`, `Nume`) VALUES
115(1, 'bucata', 'Bujie'),
116(2, 'bucata', 'Placa video'),
117(3, 'kg', 'Fertilizator'),
118(4, 'kg', 'Cuie'),
119(5, 'kg', 'Cauciuc'),
120(6, 'kg', 'Cauciuc'),
121(7, 'bucata', 'Electrozi'),
122(8, 'kg', 'Cupru'),
123(9, 'bucata', 'Procesor Intel'),
124(10, 'litri', 'Freon');
125
126-- --------------------------------------------------------
127
128--
129-- Structura de tabel pentru tabelul `tranzactii`
130--
131
132CREATE TABLE IF NOT EXISTS `tranzactii` (
133  `Cod_T` int(11) NOT NULL,
134  `Cod_F` int(11) NOT NULL,
135  `Cod_P` int(11) NOT NULL,
136  `Cod_B` int(11) NOT NULL,
137  `Pret` int(11) NOT NULL,
138  `Cantitate` int(11) NOT NULL
139) ENGINE=InnoDB DEFAULT CHARSET=latin1;
140
141--
142-- Salvarea datelor din tabel `tranzactii`
143--
144
145INSERT INTO `tranzactii` (`Cod_T`, `Cod_F`, `Cod_P`, `Cod_B`, `Pret`, `Cantitate`) VALUES
146(1, 5, 8, 1, 250, 15),
147(2, 1, 2, 2, 700, 25),
148(3, 2, 6, 8, 2200, 10),
149(4, 7, 7, 10, 500, 10),
150(5, 5, 4, 9, 1800, 35),
151(6, 4, 2, 4, 3, 1900),
152(7, 9, 8, 5, 4500, 16),
153(8, 10, 10, 7, 1000, 50),
154(9, 3, 5, 2, 2700, 5);
155
156--
157-- Indexes for dumped tables
158--
159
160--
161-- Indexes for table `beneficiar`
162--
163ALTER TABLE `beneficiar`
164 ADD PRIMARY KEY (`Cod_B`);
165
166--
167-- Indexes for table `cereri`
168--
169ALTER TABLE `cereri`
170 ADD PRIMARY KEY (`Cod_B`,`Cod_P`), ADD KEY `Cod_P` (`Cod_P`);
171
172--
173-- Indexes for table `furnizor`
174--
175ALTER TABLE `furnizor`
176 ADD PRIMARY KEY (`Cod_F`);
177
178--
179-- Indexes for table `oferte`
180--
181ALTER TABLE `oferte`
182 ADD PRIMARY KEY (`Cod_F`,`Cod_P`), ADD KEY `Cod_P` (`Cod_P`);
183
184--
185-- Indexes for table `produs`
186--
187ALTER TABLE `produs`
188 ADD PRIMARY KEY (`Cod_P`);
189
190--
191-- Indexes for table `tranzactii`
192--
193ALTER TABLE `tranzactii`
194 ADD PRIMARY KEY (`Cod_T`,`Cod_F`,`Cod_P`,`Cod_B`), ADD KEY `Cod_F` (`Cod_F`), ADD KEY `Cod_P` (`Cod_P`), ADD KEY `Cod_B` (`Cod_B`);
195
196--
197-- Restrictii pentru tabele sterse
198--
199
200--
201-- Restrictii pentru tabele `cereri`
202--
203ALTER TABLE `cereri`
204ADD CONSTRAINT `cereri_ibfk_1` FOREIGN KEY (`Cod_B`) REFERENCES `beneficiar` (`Cod_B`) ON DELETE CASCADE ON UPDATE CASCADE,
205ADD CONSTRAINT `cereri_ibfk_2` FOREIGN KEY (`Cod_P`) REFERENCES `produs` (`Cod_P`) ON DELETE CASCADE ON UPDATE CASCADE;
206
207--
208-- Restrictii pentru tabele `oferte`
209--
210ALTER TABLE `oferte`
211ADD CONSTRAINT `oferte_ibfk_1` FOREIGN KEY (`Cod_F`) REFERENCES `furnizor` (`Cod_F`) ON DELETE CASCADE ON UPDATE CASCADE,
212ADD CONSTRAINT `oferte_ibfk_2` FOREIGN KEY (`Cod_P`) REFERENCES `cereri` (`Cod_P`) ON DELETE CASCADE ON UPDATE CASCADE;
213
214--
215-- Restrictii pentru tabele `tranzactii`
216--
217ALTER TABLE `tranzactii`
218ADD CONSTRAINT `tranzactii_ibfk_1` FOREIGN KEY (`Cod_F`) REFERENCES `furnizor` (`Cod_F`) ON DELETE CASCADE ON UPDATE CASCADE,
219ADD CONSTRAINT `tranzactii_ibfk_2` FOREIGN KEY (`Cod_P`) REFERENCES `produs` (`Cod_P`) ON DELETE CASCADE ON UPDATE CASCADE,
220ADD CONSTRAINT `tranzactii_ibfk_3` FOREIGN KEY (`Cod_B`) REFERENCES `beneficiar` (`Cod_B`) ON DELETE CASCADE ON UPDATE CASCADE;
221