ПКС / many2many_example
.pdfПРИМЕР ОПИСАНИЯ СООТНОШЕНИЯ MANY-MANY (в лабораторной нужно сделать 2 таких соотношения)
Составные параметры внутренних номеров и отделов (VK_DivTel_to_Div) контактов из табл. 1 связаны следующим образом:
внутренним номерам соответствуют следующие отделы:
отделу охрана (индекс 0) соответствуют номера 347-324, 653-314 отделу бухгалтерия (индекс 1) соответствуют номера 347-324, 653-314, 983-572
отделу администрация (индекс 2) соответствуют номера, 983-572, 347-324 отделу технический персонал (индекс 3) соответствуют номера, 347-324, 653-314 отделу продаж (индекс 4) соответствуют номера 653-314, 983-572 отделу разработки (индекс 5) соответствуют номера 653-314, 983-572
отделам точно так же, соответствуют следующие внутренние номера:
номеру 347-324 (индекс 0) соответствуют отделы охрана, бухгалтерия, администрация, технический персонал
номеру 653-314 (индекс 1) соответствуют отделы охрана, бухгалтерия, технический персонал, отдел разработки
номеру 983-572 (индекс 2) соответствуют отделы бухгалтерия, администрация, отделу продаж, отделу разработки
Для добавления и заполнения таблицы VK_DivTel_to_Div следует добавить код
(https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=60c01b2a644666a6d0b0e105f44bc152): SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS VK_Mask;
DROP TABLE IF EXISTS VK_Div; DROP TABLE IF EXISTS VK_DivTel; DROP TABLE IF EXISTS VK_Empl;
DROP TABLE IF EXISTS VK_DivTel_to_Div; SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE VK_Mask ( VK_mask_id INT(5), VK_mask_num VARCHAR(30), VK_mask_name VARCHAR(30), PRIMARY KEY (VK_mask_id) );
CREATE TABLE VK_Div (
VK_div_id INT(5), VK_div_name VARCHAR(30), PRIMARY KEY (VK_div_id) );
CREATE TABLE VK_DivTel ( VK_divtel_id INT(5), VK_divtel_tel VARCHAR(30),
FOREIGN KEY (VK_divtel_id) REFERENCES VK_Div (VK_div_id)
);
CREATE TABLE VK_DivTel_to_Div ( id INT(5),
id_DivTel INT(5), id_Div INT(5), PRIMARY KEY (id),
FOREIGN KEY (id_DivTel) REFERENCES VK_DivTel(VK_divtel_id), FOREIGN KEY (id_Div) REFERENCES VK_Div(VK_div_id)
);
CREATE TABLE VK_Empl ( VK_emp_id INT(5), VK_empsurn VARCHAR(30), VK_mask_d INT(5), VK_emp_tel VARCHAR(30), VK_div_d INT(5),
PRIMARY KEY (VK_emp_id),
FOREIGN KEY (VK_mask_d) REFERENCES VK_Mask (VK_mask_id), FOREIGN KEY (VK_div_d) REFERENCES VK_Div (VK_div_id)
);
INSERT INTO VK_Mask (VK_mask_id, VK_mask_num, VK_mask_name)
VALUES (0, '+7', 'Russia'),
(1, '+380', 'Ukraine'), (2, '+375', 'Belarus'), (3, '+46', 'Sweden');
INSERT INTO VK_Div (VK_div_id, VK_div_name) VALUES (0, 'Охрана'),
(1, 'Бухгалтерия'), (2, 'Администрация'),
(3, 'Технический персонал'), (4, 'Отдел продаж'), (5, 'Отдел разработки');
INSERT INTO VK_DivTel (VK_divtel_id, VK_divtel_tel) VALUES (0, '347-324'),
(1, '653-314'), (2, '983-572');
INSERT INTO VK_Empl (VK_emp_id, VK_empsurn, VK_mask_d, VK_emp_tel, VK_div_d) VALUES (112, 'Swalow', 0, 9994322323, 0),
(324, 'Ivanov', 2, 9013126728, 2), (653, 'Petrov', 3, 4798101068, 0), (151, 'Vlasov', 0, 872238130, 2), (234, 'Moshekov', 3, 71235139923, 2), (395, 'Simens', 3, 43856884506, 2), (235, 'Price', 0, 98290003855, 3), (652, 'Soap', 1, 548191942, 4),
(038, 'Milan', 0, 30188706095, 1), (214, 'Sheppard', 2, 058101645, 1), (401, 'Scandakova', 2, 6560833942, 0), (535, 'Domina', 1, 773249564, 4);
ALTER TABLE VK_DivTel_to_Div AUTO_INCREMENT=1;
ALTER TABLE VK_DivTel_to_Div CONVERT TO CHARACTER SET utf8; SET FOREIGN_KEY_CHECKS = 1;
DESCRIBE VK_DivTel_to_Div;
INSERT INTO VK_DivTel_to_Div (id, id_DivTel, id_Div) VALUES (0, 1, 5),
(1, 0, 0), (2, 2, 4), (3, 1, 0), (4, 2, 5), (5, 1, 3), (6, 0, 3), (7, 2, 1), (8, 0, 2), (9, 0, 1), (10, 1, 4), (11, 2, 2), (12, 1, 1);
SELECT VK_DivTel_to_Div.id AS 'Индекс', VK_DivTel.VK_divtel_tel AS 'Внутренний номер', VK_Div.VK_div_name AS 'Отдел'
FROM VK_DivTel_to_Div, VK_Div, VK_DivTel WHERE VK_Div.VK_div_id = VK_DivTel_to_Div.id_Div
AND VK_DivTel.VK_divtel_id = VK_DivTel_to_Div.id_DivTel;
В результате получим значения по параметрам контактов, связанным соотношением «многие-ко-многим», представленные на рис. 1.
Рис. 1. Результат выполнения скрипта MySQL
Представим диаграмму типа «сущность-связь» с помощью онлайн-инструмента PlantUML (https://www.planttext.com/ ) для построения таблиц контактов при следующих условиях:
соотношения «один-ко-многим» в виде отдельной индексированной таблицы: o масок телефонов (Mask)
o вариантов отделов (Div)
o вариантов внутренних телефонов (DivTel)
соотношения «многие-ко-многим» в виде отдельной таблицы объединяющей свойства: o вариантов внутренних номеров и отделов.
Рис. 2. IDEF1X-диаграмма базы данных контактов (здесь «PK» (Primary Key) – первичный ключ; «FK» (Foreigh Key) – внешний ключ)
Диаграмме на рис. 2 соответствует следующий скрипт:
@startuml
left to right direction
entity VK_Empl{
{field}VK_emp_id : int(5) <<PK><U+003E> VK_empsurn : varchar(30)
VK_emp_tel : varchar(30) {field}VK_mask_d : int(5) <<FK><U+003E> {field}VK_div_d : int(5) <<FK><U+003E>
}
entity VK_Mask{
{field}VK_mask_id : int(5) <<PK><U+003E> VK_mask_num : varchar(30) VK_mask_name : varchar(30)
}
entity VK_Div{
{field}VK_div_id : int(5) <<PK><U+003E> VK_div_name : varchar(30)
}
entity VK_DivTel{
{field}VK_divtel_id : int(5) <<PK><U+003E> VK_divtel_tel : varchar(30)
}
entity VK_DivTel_to_Div{ {field}id : int(5) <<PK><U+003E>
{field}id_DivTel : int(5) <<FK><U+003E> {field}id_Div : int(5) <<FK><U+003E>
}
VK_Empl }|..|| VK_Div VK_Empl }|..|| VK_DivTel VK_Empl }|..|| VK_Mask
VK_DivTel }|..|| VK_DivTel_to_Div VK_Div }|..|| VK_DivTel_to_Div @enduml
Вывод: В процессе работы мы изучили прикладное применение соотношения «многие-ко- многим» и сделали ER-диаграмму (IDEFIX-диаграмму) для своей базы данных. Соотношение «многие-ко-многим» позволяет нормализовать работу с таблицей и его эффективность неизменно растёт вместе с увеличением ее объёмов данных.