2TvTrEij

1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3
4CREATE TABLE IF NOT EXISTS `kategoria` (
5  `id` int(11) NOT NULL PRIMARY KEY,
6  `nazwa` varchar(30) COLLATE utf8_polish_ci NOT NULL
7) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
8
9ALTER TABLE `kategoria`
10  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
11
12CREATE TABLE IF NOT EXISTS `producent` (
13    `id` int(11) NOT NULL PRIMARY KEY,
14    `nazwa` varchar(30) COLLATE utf8_polish_ci NOT NULL
15  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
16
17ALTER TABLE `producent`
18    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
19
20INSERT INTO `kategoria` (`id`, `nazwa`) VALUES
21  (NULL, 'Akcja'),
22  (NULL, 'MMO'),
23  (NULL, 'RPG'),
24  (NULL, 'Przygodowe');
25
26INSERT INTO `producent` (`id`, `nazwa`) VALUES
27  (NULL, 'Rockstar Games'),
28  (NULL, 'Ubisoft');
29
30CREATE TABLE IF NOT EXISTS `gra` (
31  `id` int(11) NOT NULL PRIMARY KEY,
32  `nazwa` varchar(30) COLLATE utf8_polish_ci NOT NULL,
33  `data_premiery` date COLLATE utf8_polish_ci NOT NULL,
34  `fk_kategoria` int(11) NOT NULL,
35  `fk_producent` int(11) NOT NULL,
36  `cena` int(11) NOT NULL
37) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
38
39INSERT INTO `gra` (`id`, `nazwa`, `data_premiery`, `fk_kategoria`, `fk_producent`, `cena`) VALUES
40(1, 'GTA V', '17-09-2013', 1, 1, 200),
41(2, 'Assassin Creed Odyssey', '05-10-2018', 2, 2, 299);
42
43ALTER TABLE `gra`
44  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
45
46ALTER TABLE `gra` ADD FOREIGN KEY (`fk_kategoria`) references kategoria(`id`);
47ALTER TABLE `gra` ADD FOREIGN KEY (`fk_producent`) references producent(`id`);
48
49CREATE TABLE IF NOT EXISTS `gryUzytkownika` (
50    `id` int(11) NOT NULL PRIMARY KEY,
51    `uid` int(11) NOT NULL,
52    `id_gry` int(11) NOT NULL
53  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
54
55  ALTER TABLE `gryUzytkownika`
56    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
57
58  ALTER TABLE `gryUzytkownika` ADD FOREIGN KEY (`uid`) references uzytkownicy(`id`);
59  ALTER TABLE `gryUzytkownika` ADD FOREIGN KEY (`id_gry`) references gra(`id`);
60
61  CREATE TABLE IF NOT EXISTS `powiadomienia` (
62      `id` int(11) NOT NULL PRIMARY KEY,
63      `uid` int(11) NOT NULL,
64      `tresc` text NOT NULL,
65      `odczytane` int(11) NOT NULL
66    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
67
68    ALTER TABLE `powiadomienia`
69      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
70
71    ALTER TABLE `powiadomienia` ADD FOREIGN KEY (`uid`) references uzytkownicy(`id`);
72
73    CREATE TABLE IF NOT EXISTS `listaZnajomych` (
74        `id` int(11) NOT NULL PRIMARY KEY,
75        `uid` int(11) NOT NULL,
76        `fid` int(11) NOT NULL
77      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;