HwF7Pmga

1
2
3CREATE TABLE IF NOT EXISTS `autor` (
4`id_autora` int(11) NOT NULL,
5  `Imie` varchar(20) NOT NULL,
6  `Nazwisko` varchar(20) NOT NULL,
7  `Data_urodzenia` date NOT NULL
8) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
9
10
11INSERT INTO `autor` (`id_autora`, `Imie`, `Nazwisko`, `Data_urodzenia`) VALUES
12(1, 'Stephen', 'King', '1950-04-18'),
13(2, 'Adam', 'Mickiewicz', '1798-05-16'),
14(3, 'Juliusz', 'S?owacki', '1800-11-23');
15
16
17CREATE TABLE IF NOT EXISTS `klient` (
18`id_klienta` int(11) NOT NULL,
19  `Imie` varchar(20) NOT NULL,
20  `Nazwisko` varchar(20) NOT NULL,
21  `Adres` varchar(50) NOT NULL
22) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
23
24
25INSERT INTO `klient` (`id_klienta`, `Imie`, `Nazwisko`, `Adres`) VALUES
26(1, 'Jakub', 'Kurs', 'Skar?y?skiego'),
27(2, 'Jakub', 'Malinowski', 'Niewiadomska'),
28(3, 'Gal', 'Anonim', 'Anonimowska');
29
30
31CREATE TABLE IF NOT EXISTS `ksiazki` (
32`id_ksiazki` int(11) NOT NULL,
33  `Tytul` varchar(20) NOT NULL,
34  `Cena` float NOT NULL,
35  `Ilosc_sztuk` int(11) NOT NULL,
36  `Ilosc_stron` int(11) NOT NULL,
37  `Opis` text NOT NULL
38) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
39
40
41INSERT INTO `ksiazki` (`id_ksiazki`, `Tytul`, `Cena`, `Ilosc_sztuk`, `Ilosc_stron`, `Opis`) VALUES
42(1, 'To', 69.9, 5, 230, ''),
43(2, 'Dziady', 96.6, 69, 169, ''),
44(3, 'Kordian', 44.6, 10, 269, '');
45
46
47CREATE TABLE IF NOT EXISTS `ksiazki_autorzy` (
48`id_ksiazki` int(11) NOT NULL,
49  `id_autor` int(11) NOT NULL
50) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
51
52
53INSERT INTO `ksiazki_autorzy` (`id_ksiazki`, `id_autor`) VALUES
54(1, 1),
55(2, 2),
56(3, 3);
57
58
59CREATE TABLE IF NOT EXISTS `platnosc` (
60`Id_platnosci` int(11) NOT NULL,
61  `Koszt` float NOT NULL,
62  `Data_Zaplaty` date NOT NULL,
63  `Rodzaj_platnosci` varchar(20) NOT NULL
64) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
65
66
67INSERT INTO `platnosc` (`Id_platnosci`, `Koszt`, `Data_Zaplaty`, `Rodzaj_platnosci`) VALUES
68(1, 13.88, '2018-03-13', 'Karta'),
69(2, 50.88, '2018-04-03', 'Karta'),
70(3, 99.99, '2018-04-01', 'Karta');
71
72
73CREATE TABLE IF NOT EXISTS `zamowienie` (
74`id_zamowienia` int(11) NOT NULL,
75  `id_platnosci` int(11) NOT NULL,
76  `id_klienta` int(11) NOT NULL
77) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
78
79
80INSERT INTO `zamowienie` (`id_zamowienia`, `id_platnosci`, `id_klienta`) VALUES
81(4, 1, 1),
82(5, 2, 2),
83(6, 3, 3);
84
85
86CREATE TABLE IF NOT EXISTS `zamowienie_ksiazki` (
87  `id_zamowienia` int(11) NOT NULL,
88  `id_ksiazki` int(11) NOT NULL
89) ENGINE=InnoDB DEFAULT CHARSET=latin1;
90
91
92INSERT INTO `zamowienie_ksiazki` (`id_zamowienia`, `id_ksiazki`) VALUES
93(4, 1),
94(5, 2),
95(6, 3);
96
97
98ALTER TABLE `autor`
99 ADD PRIMARY KEY (`id_autora`), ADD KEY `id_autora` (`id_autora`,`Imie`,`Nazwisko`,`Data_urodzenia`);
100
101
102ALTER TABLE `klient`
103 ADD PRIMARY KEY (`id_klienta`), ADD KEY `id_klienta` (`id_klienta`,`Imie`,`Nazwisko`,`Adres`);
104
105
106ALTER TABLE `ksiazki`
107 ADD PRIMARY KEY (`id_ksiazki`);
108
109
110ALTER TABLE `ksiazki_autorzy`
111 ADD PRIMARY KEY (`id_ksiazki`), ADD KEY `id_ksiazki` (`id_ksiazki`,`id_autor`), ADD KEY `id_autor` (`id_autor`);
112
113
114ALTER TABLE `platnosc`
115 ADD PRIMARY KEY (`Id_platnosci`), ADD KEY `Id_platnosci` (`Id_platnosci`,`Koszt`,`Data_Zaplaty`,`Rodzaj_platnosci`);
116
117
118ALTER TABLE `zamowienie`
119 ADD PRIMARY KEY (`id_zamowienia`), ADD KEY `id_zamowienia` (`id_zamowienia`,`id_platnosci`,`id_klienta`), ADD KEY `id_klienta` (`id_klienta`), ADD KEY `id_zamowienia_2` (`id_zamowienia`,`id_platnosci`,`id_klienta`), ADD KEY `id_platnosci` (`id_platnosci`);
120
121
122ALTER TABLE `zamowienie_ksiazki`
123 ADD KEY `id_zamowienia` (`id_zamowienia`,`id_ksiazki`), ADD KEY `id_ksiazki` (`id_ksiazki`);
124
125
126ALTER TABLE `autor`
127MODIFY `id_autora` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
128
129
130ALTER TABLE `klient`
131MODIFY `id_klienta` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
132
133
134ALTER TABLE `ksiazki`
135MODIFY `id_ksiazki` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
136
137
138ALTER TABLE `ksiazki_autorzy`
139MODIFY `id_ksiazki` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
140
141
142ALTER TABLE `platnosc`
143MODIFY `Id_platnosci` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
144
145
146ALTER TABLE `zamowienie`
147MODIFY `id_zamowienia` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;
148
149
150ALTER TABLE `ksiazki_autorzy`
151ADD CONSTRAINT `ksiazki_autorzy_ibfk_1` FOREIGN KEY (`id_ksiazki`) REFERENCES `ksiazki` (`id_ksiazki`),
152ADD CONSTRAINT `ksiazki_autorzy_ibfk_2` FOREIGN KEY (`id_autor`) REFERENCES `autor` (`id_autora`);
153
154
155ALTER TABLE `zamowienie`
156ADD CONSTRAINT `zamowienie_ibfk_1` FOREIGN KEY (`id_klienta`) REFERENCES `klient` (`id_klienta`),
157ADD CONSTRAINT `zamowienie_ibfk_2` FOREIGN KEY (`id_platnosci`) REFERENCES `platnosc` (`Id_platnosci`);
158
159
160ALTER TABLE `zamowienie_ksiazki`
161ADD CONSTRAINT `zamowienie_ksiazki_ibfk_1` FOREIGN KEY (`id_zamowienia`) REFERENCES `zamowienie` (`id_zamowienia`),
162ADD CONSTRAINT `zamowienie_ksiazki_ibfk_2` FOREIGN KEY (`id_ksiazki`) REFERENCES `ksiazki` (`id_ksiazki`);
163
164/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
165/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
166/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;