· 8 years ago · Jun 22, 2018, 04:56 AM
1- --------------------------------------------------------
2
3--
4-- Table structure for table `artikel`
5--
6
7CREATE TABLE IF NOT EXISTS `artikel` (
8 `artikelnr` int(11) NOT NULL AUTO_INCREMENT,
9 `omschrijving` varchar(255) NOT NULL,
10 `aanmaakdatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
11 PRIMARY KEY (`artikelnr`)
12) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
13
14--
15-- Dumping data for table `artikel`
16--
17
18INSERT INTO `artikel` (`artikelnr`, `omschrijving`, `aanmaakdatum`) VALUES
19(1, 'Banaan', '2009-06-16 22:08:09');
20
21-- --------------------------------------------------------
22
23--
24-- Table structure for table `instellingen`
25--
26
27CREATE TABLE IF NOT EXISTS `instellingen` (
28 `instelnr` int(11) NOT NULL,
29 `omschrijving` varchar(255) NOT NULL,
30 `waarde` int(11) NOT NULL,
31 PRIMARY KEY (`instelnr`)
32) ENGINE=InnoDB DEFAULT CHARSET=latin1;
33
34--
35-- Dumping data for table `instellingen`
36--
37
38
39-- --------------------------------------------------------
40
41--
42-- Table structure for table `klant`
43--
44
45CREATE TABLE IF NOT EXISTS `klant` (
46 `klantnr` int(11) NOT NULL AUTO_INCREMENT,
47 `naam` varchar(255) NOT NULL,
48 PRIMARY KEY (`klantnr`)
49) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
50
51--
52-- Dumping data for table `klant`
53--
54
55INSERT INTO `klant` (`klantnr`, `naam`) VALUES
56(1, 'Henk'),
57(2, 'Piet'),
58(3, 'Martijn');
59
60-- --------------------------------------------------------
61
62--
63-- Table structure for table `leverancier`
64--
65
66CREATE TABLE IF NOT EXISTS `leverancier` (
67 `leveranciernr` int(11) NOT NULL AUTO_INCREMENT,
68 `naam` varchar(255) NOT NULL,
69 PRIMARY KEY (`leveranciernr`)
70) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
71
72--
73-- Dumping data for table `leverancier`
74--
75
76INSERT INTO `leverancier` (`leveranciernr`, `naam`) VALUES
77(1, 'Menno'),
78(2, 'Wolfert'),
79(3, 'Gerard');
80
81-- --------------------------------------------------------
82
83--
84-- Table structure for table `ontvangst`
85--
86
87CREATE TABLE IF NOT EXISTS `ontvangst` (
88 `ontvangstnr` int(11) NOT NULL AUTO_INCREMENT,
89 `leveranciernr` int(11) NOT NULL,
90 `datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
91 PRIMARY KEY (`ontvangstnr`),
92 KEY `leveranciernr` (`leveranciernr`)
93) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
94
95--
96-- Dumping data for table `ontvangst`
97--
98
99
100-- --------------------------------------------------------
101
102--
103-- Table structure for table `ontvangstregel`
104--
105
106CREATE TABLE IF NOT EXISTS `ontvangstregel` (
107 `ontvangstnr` int(11) NOT NULL,
108 `artikelnr` int(11) NOT NULL,
109 `aantal` int(11) NOT NULL,
110 PRIMARY KEY (`ontvangstnr`,`artikelnr`),
111 KEY `artikelnr` (`artikelnr`)
112) ENGINE=InnoDB DEFAULT CHARSET=latin1;
113
114--
115-- Triggers `ontvangstregel`
116--
117DROP TRIGGER IF EXISTS `db02`.`ontvangstregel_check_aantal`;
118DELIMITER //
119CREATE TRIGGER `db02`.`ontvangstregel_check_aantal` BEFORE INSERT ON `db02`.`ontvangstregel`
120 FOR EACH ROW IF NEW.aantal < 0 THEN
121 SET NEW.aantal = 0;
122 END IF
123//
124DELIMITER ;
125
126--
127-- Dumping data for table `ontvangstregel`
128--
129
130
131-- --------------------------------------------------------
132
133--
134-- Table structure for table `uitgifte`
135--
136
137CREATE TABLE IF NOT EXISTS `uitgifte` (
138 `uitgiftenr` int(11) NOT NULL AUTO_INCREMENT,
139 `klantnr` int(11) NOT NULL,
140 `datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
141 PRIMARY KEY (`uitgiftenr`),
142 KEY `klantnr` (`klantnr`)
143) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
144
145--
146-- Dumping data for table `uitgifte`
147--
148
149
150-- --------------------------------------------------------
151
152--
153-- Table structure for table `uitgifteregel`
154--
155
156CREATE TABLE IF NOT EXISTS `uitgifteregel` (
157 `uitgiftenr` int(11) NOT NULL,
158 `artikelnr` int(11) NOT NULL,
159 `aantal` int(11) NOT NULL,
160 PRIMARY KEY (`uitgiftenr`,`artikelnr`),
161 KEY `artikelnr` (`artikelnr`)
162) ENGINE=InnoDB DEFAULT CHARSET=latin1;
163
164--
165-- Triggers `uitgifteregel`
166--
167DROP TRIGGER IF EXISTS `db02`.`uitgifteregel_check_aantal`;
168DELIMITER //
169CREATE TRIGGER `db02`.`uitgifteregel_check_aantal` BEFORE INSERT ON `db02`.`uitgifteregel`
170 FOR EACH ROW IF NEW.aantal < 0 THEN
171 SET NEW.aantal = 0;
172 END IF
173//
174DELIMITER ;
175
176--
177-- Dumping data for table `uitgifteregel`
178--
179
180
181-- --------------------------------------------------------
182
183--
184-- Table structure for table `voorraad`
185--
186
187CREATE TABLE IF NOT EXISTS `voorraad` (
188 `artikelnr` int(11) NOT NULL AUTO_INCREMENT,
189 `aantal` int(11) NOT NULL,
190 PRIMARY KEY (`artikelnr`)
191) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
192
193--
194-- Dumping data for table `voorraad`
195--
196
197
198--
199-- Constraints for dumped tables
200--
201
202--
203-- Constraints for table `ontvangst`
204--
205ALTER TABLE `ontvangst`
206 ADD CONSTRAINT `ontvangst_ibfk_1` FOREIGN KEY (`leveranciernr`) REFERENCES `leverancier` (`leveranciernr`);
207
208--
209-- Constraints for table `ontvangstregel`
210--
211ALTER TABLE `ontvangstregel`
212 ADD CONSTRAINT `ontvangstregel_ibfk_1` FOREIGN KEY (`ontvangstnr`) REFERENCES `ontvangst` (`ontvangstnr`),
213 ADD CONSTRAINT `ontvangstregel_ibfk_2` FOREIGN KEY (`artikelnr`) REFERENCES `artikel` (`artikelnr`);
214
215--
216-- Constraints for table `uitgifte`
217--
218ALTER TABLE `uitgifte`
219 ADD CONSTRAINT `uitgifte_ibfk_1` FOREIGN KEY (`klantnr`) REFERENCES `klant` (`klantnr`);
220
221--
222-- Constraints for table `uitgifteregel`
223--
224ALTER TABLE `uitgifteregel`
225 ADD CONSTRAINT `uitgifteregel_ibfk_1` FOREIGN KEY (`uitgiftenr`) REFERENCES `uitgifte` (`uitgiftenr`),
226 ADD CONSTRAINT `uitgifteregel_ibfk_2` FOREIGN KEY (`artikelnr`) REFERENCES `artikel` (`artikelnr`);