VEre1Pr4

1CREATE DATABASE IF NOT EXISTS zoo;
2USE zoo;
3
4SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
5START TRANSACTION;
6SET time_zone = "+00:00";
7
8CREATE TABLE `administration` (
9  `id_admin` int(11) NOT NULL,
10  `director_name` varchar(30) NOT NULL,
11  `place_admin_building` text NOT NULL,
12  `vetСouncil` text NOT NULL
13) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
14
15-- --------------------------------------------------------
16
17--
18-- Структура таблицы `animals`
19--
20
21CREATE TABLE `animals` (
22  `id_animals` int(11) NOT NULL,
23  `id_worker` int(11) NOT NULL,
24  `nickname` varchar(25) NOT NULL,
25  `cell` varchar(150) NOT NULL,
26  `place_origin` text NOT NULL
27) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
28
29-- --------------------------------------------------------
30
31--
32-- Структура таблицы `univer`
33--
34
35CREATE TABLE `univer` (
36  `id_univer` int(11) NOT NULL,
37  `id_worker` int(11) NOT NULL,
38  `nameUniver` varchar(370) NOT NULL,
39  `nameFacultaty` varchar(450) NOT NULL
40) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
41
42-- --------------------------------------------------------
43
44--
45-- Структура таблицы `workers`
46--
47
48CREATE TABLE `workers` (
49  `id_worker` int(11) NOT NULL,
50  `id_admin` int(11) NOT NULL,
51  `firstname` varchar(50) NOT NULL,
52  `lastname` varchar(120) NOT NULL
53) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
54
55--
56-- Индексы сохранённых таблиц
57--
58
59--
60-- Индексы таблицы `administration`
61--
62ALTER TABLE `administration`
63  ADD PRIMARY KEY (`id_admin`);
64
65--
66-- Индексы таблицы `animals`
67--
68ALTER TABLE `animals`
69  ADD PRIMARY KEY (`id_animals`),
70  ADD KEY `id_worker` (`id_worker`);
71
72--
73-- Индексы таблицы `univer`
74--
75ALTER TABLE `univer`
76  ADD PRIMARY KEY (`id_univer`),
77  ADD KEY `id_worker` (`id_worker`);
78
79--
80-- Индексы таблицы `workers`
81--
82ALTER TABLE `workers`
83  ADD PRIMARY KEY (`id_worker`),
84  ADD KEY `id_admin` (`id_admin`);
85
86--
87-- Ограничения внешнего ключа сохраненных таблиц
88--
89
90--
91-- Ограничения внешнего ключа таблицы `univer`
92--
93ALTER TABLE `univer`
94  ADD CONSTRAINT `univer_ibfk_1` FOREIGN KEY (`id_worker`) REFERENCES `workers` (`id_worker`);
95
96--
97-- Ограничения внешнего ключа таблицы `workers`
98--
99ALTER TABLE `workers`
100  ADD CONSTRAINT `workers_ibfk_1` FOREIGN KEY (`id_admin`) REFERENCES `administration` (`id_admin`),
101  ADD CONSTRAINT `workers_ibfk_2` FOREIGN KEY (`id_worker`) REFERENCES `animals` (`id_worker`);
102COMMIT;
103
104