лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Графовые базы данных Графовая модель данных на логическом уровне представляет собой направленный граф, состоящий из узлов и ребер. Узлы соответствуют объектам базы данных, а ребра – связям между этими объектами. И узлы, и ребра могут обладать свойствами, кроме того, ребра имеют тип, определяющий характер связи. Графовая модель хорошо отражает семантику предметной области с многочисленными связями. Например, пользователи социальной сети могут быть связаны между собой родственными, дружественными, производственными и прочими отношениями. Графовые СУБД используют графовую модель для описания и манипуляции данными в базе данных. Они относятся к направлению NoSQL, которое объединяет множество различных подходов к моделированию данных, отрицающих реляционную модель данных. Однако, в отличии от других моделей, графовые СУБД, как и базы данных SQL, поддерживают ACID- транзакции. Во многом это связано с тем, что данная модель не является агрегатной, и это препятствует её использованию в распределенной форме. Одной из популярных графовых СУБД является Neo4j, имеющей оригинальный декларативный язык запросов Cypher Наверняка, прочитав предыдущую пару абзацев, читатель уже задал себе вопрос, почему в учебнике по SQL появилась глава, посвященная графовым базам данных? Дело в том, что, начиная с версии 2017, SQL Server стал поддерживать графовую модель данных! Графовые базы данных SQL Server Кардинальным отличием графовых баз данных в SQL Server от графовых баз данных направления NoSQL является то, что для моделирования графа в SQL Server используются таблицы. Это таблицы двух специальных видов. Один вид таблиц используется для создания узлов, и другой – для создания ребер (связей). Отметим, что табличное представление узлов и ребер дает возможность писать запросы к этим таблицам на языке SQL. Таблица типа узла описывает некую сущность. Экземпляры этой сущности представлены строками таблицы и характеризуются одинаковым набором свойств (столбцами таблицы). Таблица типа ребра определяет характер и направление связи между узлами. Скажем, таблица ToBeFriends (дружить) могла бы описывать дружественную связь между экземплярами одной или разных сущностей. Вероятно, пора переходить к примерам. Предлагаю взять учебную базу « Окраска », и отобразить реляционную структуру этой базы на структуру графа без потери информации. Это позволит нам писать запросы и сравнивать результаты на двух моделях, представляющих в разной форме одну и ту же информацию. Итак, у нас есть два типа узлов, представляющих сущности квадрата и баллона, и одна связь между ними, которую можно выразить так: баллон окрашивает квадрат. Направление связи – от баллона к квадрату. Создадим таблицы узлов: 1. --Квадраты 2. CREATE TABLE utqG ( 3. q_id INT PRIMARY KEY , 4. q_name VARCHAR ( 35 ) NOT NULL , 5. ) AS NODE; 6. --Баллончики 7. CREATE TABLE utvG ( 8. v_id INT PRIMARY KEY , 9. v_name VARCHAR ( 35 ) NOT NULL , 10. v_color char ( 1 ) NOT NULL 11. ) AS NODE; Как видите, таблицы создаются аналогично обычным реляционным за исключением указания типа – AS NODE (т.е. узел). Теперь посмотрим на структуру созданных таблиц: 1. SELECT table_name, column_name, data_type 2. FROM information_schema. COLUMNS 3. WHERE table_name= 'utqG' ; table_name column_name data_type utqG graph_id_AA21DCF7CB44457BB308B21482806B87 bigint utqG $node_id_EEFDE0FB86F243E4A6667A5CE470F4F6 nvarchar utqG q_id int utqG q_name varchar При создании таблицы узлов, помимо пользовательских, автоматически создаются еще два псевдостолбца – graph_id и $node_id. Давайте наполним таблицу данными и посмотрим, что находится в этих столбцах. 1. INSERT INTO utqG ( q_id, q_name ) 2. SELECT * FROM [ sql-ex ] ..utq; Здесь мы просто берем готовые данные, которые находятся в учебной базе данных с именем sql-ex. 1. SELECT TOP 2 * FROM utqG; $node_id_EEFDE0FB86F243E4A6667A5CE470F4F6 q_id q_name {"type":"node","schema":"dbo","table":"utqG","id":0} 1 Square # 01 {"type":"node","schema":"dbo","table":"utqG","id":1} 2 Square # 02 Столбец graph_id отсутствует в выборке. Причиной является то, что этот столбец используется ядром СУБД и недоступен пользователю напрямую. Действительно, если выполнить запрос 1. SELECT graph_id FROM utqG; то мы получим сообщение об ошибке: Недопустимое имя столбца "graph_id". Столбец $node_id является уникальным идентификатором узла, представленным в формате JSON. Шестнадцатеричный суффикс в имени столбца делает имя столбца глобально уникальным, однако для доступа к столбцу он не используется. Например, 1. SELECT TOP 2 $node_id, q_id FROM utqG; $node_id_EEFDE0FB86F243E4A6667A5CE470F4F6 q_id {"type":"node","schema":"dbo","table":"utqG","id":0} 1 {"type":"node","schema":"dbo","table":"utqG","id":1} 2 Таблица utvG выглядит аналогично таблице utqG, и поэтому мы не будем представлять тут её содержимое. Теперь создадим таблицу ребра. 1. CREATE TABLE utbG ( 2. b_datetime datetime NOT NULL , 3. b_vol tinyint NOT NULL , 4. ) AS EDGE; Эта таблица содержит столбцы свойств – время окраски (B_datetime) и количество нанесенной из баллончика краски (b_vol). Она отличается от таблиц узлов типом – теперь это EDGE, а не NODE. Рассмотрим структуру этой таблицы: 1. SELECT column_name, data_type 2. FROM information_schema. COLUMNS WHERE table_name= 'utbG' ; column_name data_type graph_id_9D6121CFD14948A5B03BBD6A4BDB4774 bigint $edge_id_EAB5B85BC07649ED89435D6F2A2ACE83 nvarchar from_obj_id_D0D5B23329B5409895672D5C32283E51 int from_id_526FECFDEBB84E86B2F7440FF625BCF1 bigint $from_id_BF1E69FB306E4225A58F4DAFAA8FBE94 nvarchar to_obj_id_14C309807A224F7BA7BD8F88425A15AB int to_id_295F2F2BD42A425CAAD6ACC9F2174BC7 bigint $to_id_79504454FC184B7F849B1AB28BCCC670 nvarchar b_datetime datetime b_vol tinyint Как и для узлов, автоматически были созданы несколько псевдостолбцов, среди которых доступными пользователю являются: $edge_id – идентификатор ребра, формируется автоматически; $from_id – идентификатор узла, откуда исходит ребро; $to_id – идентификатор узла, куда входит ребро. Заполним и эту таблицу данными. Если мы вспомним структуру исходной таблицы utb, то для каждой строки в столбец $from_id мы должны поместить идентификатор того узла из таблицы utvG, для которого v_id равен b_v_id таблицы utb. Тогда столбец $to_id должен содержать идентификатор того узла из таблицы utqG, для которого q_id равен, соответственно, b_q_id из той же строки таблицы utb. Читателю, наверное, будет проще понять оператор, который вставит описанные данные: 1. INSERT INTO utbG ( b_datetime, b_vol, $from_id, $to_id ) 2. SELECT b_datetime, b_vol, 3. ( SELECT $node_id FROM utvG WHERE v_id = orig.b_v_id ) , 4. ( SELECT $node_id FROM utqG WHERE q_id = orig.b_q_id ) 5. FROM [ sql-ex ] ..utb orig Теперь мы можем посмотреть на данные в таблице ребра, которая устанавливает связь между двумя узлами – баллончиком с краской и окрашиваемым квадратом. 1. SELECT TOP 1 * FROM utbG; Поскольку ширина результирующей таблицы значительно превышает ширину страницы, представим результат в формате «ключ: значение» $edge_id_EAB5B85BC07649ED89435D6F2A2ACE83: {"type":"edge","schema":"dbo","table":"utbG","id":0} $from_id_BF1E69FB306E4225A58F4DAFAA8FBE94: {"type":"node","schema":"dbo","table":"utvG","id":49} $to_id_79504454FC184B7F849B1AB28BCCC670: {"type":"node","schema":"dbo","table":"utqG","id":21} b_datetime: "2000-01-01 01:13:36.000" b_vol: 50 Запросы к графовой базе данных Восстановить вид исходной таблицы Utb можно с помощью обычного SQL-запроса: 1. SELECT B.b_datetime, Q.q_id b_q_id, V.v_id b_v_id, B.b_vol 2. FROM utbG B JOIN utqG Q ON B.$to_id = Q.$node_id 3. JOIN utvG V ON B.$from_id = V.$node_id; Однако для облегчения навигации по графу в SQL Server добавлена функция MATCH, которая задает шаблон для поиска узлов в соответствии со связями. Синтаксис шаблона напоминает образцы , используемые в языке Cypher, графовой базы данных Neo4j. В упрощенной форме шаблон имеет вид: 1. <узел, откуда исходит ребро> - ( <ребро> ) -> <узел, в который входит ребро> Узел представляется именем таблицы или алиасом (псевдонимом). Алиас необходим, когда одна таблица используется в образце несколько раз, например, в случае самосоединений. Шаблоны в функции MATCH можно соединять при помощи оператора AND. Перейдем к примерам. Для начала перепишем предыдущий запрос в «графовом» стиле: 1. SELECT B.b_datetime, Q.q_id b_q_id, V.v_id b_v_id, B.b_vol 2. FROM utbG B, utqG Q, utvG V 3. WHERE match ( V- ( B ) ->Q ) ; Здесь мы используем алиасы только для сокращения записи. Мы можем поменять местами узлы, но направление связи должно сохраниться (от баллончика к квадрату): 1. SELECT B.b_datetime, Q.q_id b_q_id, V.v_id b_v_id, B.b_vol 2. FROM utbG B, utqG Q, utvG V 3. WHERE match ( Q<- ( B ) -V ) ; В отличие от Cypher направление связи является обязательным, т.е. использование функции 1. match ( V- ( B ) -Q ) ; будет вызывать ошибку. Cypher более гибок в этом отношении; направление можно не указывать, если оно может быть однозначно определено, например, если связь данного типа есть в одном направлении, но нет в другом. Найти квадраты, которые окрашивались красной краской. Вывести идентификатор квадрата и объем красной краски. Реляционная схема 1. SELECT b_q_id, SUM ( b_vol ) qty 2. FROM utB JOIN utV ON B_V_ID =V_ID 3. WHERE V_COLOR = 'R' 4. GROUP BY b_q_id; Графовая схема 1. SELECT q_id, SUM ( b_vol ) qty 2. FROM utbG B, utvG V, utqG Q 3. WHERE match ( V- ( B ) ->Q ) AND V_COLOR = 'R' 4. GROUP BY q_id; Найти квадраты, которые окрашивались как красной, так и синей краской. Вывести: название квадрата. Реляционная схема 1. SELECT q_name FROM utQ JOIN ( 2. SELECT b_q_id FROM utB JOIN utV ON B_V_ID =V_ID 3. WHERE V_COLOR = 'R' 4. INTERSECT 5. SELECT b_q_id FROM utB JOIN utV ON B_V_ID =V_ID 6. WHERE V_COLOR = 'B' 7. ) X ON Q_ID=b_q_id; Графовая схема 1. SELECT DISTINCT q_name 2. FROM utbG B1,utbG B2, utvG VR, utvG VB, utqG Q 3. WHERE 4. match ( VR- ( B1 ) ->Q<- ( B2 ) -VB ) AND VR.V_COLOR = 'R' AND VB.V_COLOR = 'B' ; Обратите внимание, что для в общем случае разных узлов, используемых в шаблоне, требуется добавлять соответствующую таблицу в предложение FROM (как и таблицу связи). Если же подразумевается один и тот же узел, как в нашем случае с квадратом, то мы можем использовать этот узел для двусторонней связи. Предикат 1. match ( VR- ( B1 ) ->Q<- ( B2 ) -VB ) можно было также записать в таком виде 1. match ( VR- ( B1 ) ->Q ) AND match ( VB- ( B2 ) ->Q ) или в таком 1. match ( VR- ( B1 ) ->Q AND VB- ( B2 ) ->Q ) DISTINCT в этих запросах необходим, поскольку возможны дубликаты, если квадрат окрашивался несколькими красными (и/или синими) баллончиками. Найти квадраты, которые окрашивались всеми тремя цветами. Реляционная схема Можно было бы добавить еще один запрос к предыдущему решению с помощью INTERSECT, однако давайте представим здесь более эффективное решение, опирающееся на тот факт, что количество уникальных цветов, которыми окрашивался квадрат, равно 3: 1. SELECT q_name FROM utQ JOIN ( 2. SELECT b_q_id 3. FROM utB JOIN utV ON B_V_ID =V_ID 4. GROUP BY b_q_id 5. HAVING COUNT ( DISTINCT v_color ) = 3 6. ) X ON q_id=b_q_id; Графовая схема 1. SELECT DISTINCT Q.q_name 2. FROM utbG B1,utbG B2,utbG B3, utvG VR, utvG VB, utvG VG, utqG Q 3. WHERE 4. match ( VR- ( B1 ) ->Q<- ( B2 ) -VB AND VG- ( B3 ) ->Q ) 5. AND VG.V_COLOR = 'G' AND VR.V_COLOR = 'R' AND VB.V_COLOR = 'B' ; Найти баллончики, которыми окрашивали более одного квадрата. Реляционная схема 1. SELECT v_name FROM 2. utB JOIN utV ON B_V_ID =V_ID 3. GROUP BY b_v_id,v_name 4. HAVING COUNT ( DISTINCT b_q_id ) > 1 ; Графовая схема 1. SELECT DISTINCT v_name 2. FROM utbG B1,utbG B2, utvG V, utqG Q,utqG Q2 3. WHERE 4. match ( Q2<- ( B2 ) -V- ( B1 ) ->Q ) AND Q.q_id <> Q2.q_id; Условие match(Q2<-(B2)-V-(B1)->Q) утверждает, что один баллон участвовал в двух окрасках, в то время как условие Q.q_id <> Q2.q_id говорит о том, что при этом окрашивались разные квадраты. Предварительные выводы На первый взгляд, запросы к реляционной и графовой схемам не так уж радикально отличаются. Более того, синтаксиса графовой базы данных можно не придерживаться, оставаясь в рамках языка SQL. Однако не стоит судить по рассмотренным примерам, поскольку преимущество графовых баз данных проявляется для тех предметных областей, которые характеризуются сложными связями между данными. Приложения Здесь приводятся описания всех учебных баз данных, используемых для решения задач на сайте SQL-EX.RU , а также список всех задач обучающего этапа тестирования. Задачи, рассмотренные в книге, отмечены знаком «+». Приложение 1. Описание учебных баз данных Компьютерная фирма Схема базы данных состоит из четырех отношений: 1. Product ( maker, model, type ) 2. PC ( code, model, speed, ram, hd, cd, price ) 3. Laptop ( code, model, speed, ram, hd, screen, price ) 4. Printer ( code, model, color, type, price ) Отношение Product представляет производителя (maker), номер модели (model) и тип (PC — ПК, Laptop — портативный компьютер или Printer — принтер). Предполагается, что номера моделей уникальны для всех производителей и типов продуктов. В отношении PC для каждого номера модели, обозначающего ПК, указаны скорость — speed (процессора в мегагерцах), общий объем RAM — ram (в мегабайтах), размер диска — hd (в гигабайтах), скорость считывающего устройства CD (например, 4х) и цена — price. Отношение Laptop аналогично отношению РС за исключением того, что вместо скорости CD-привода содержится размер экрана — screen (в дюймах). В отношении Printer для каждой модели принтера указывается, является ли он цветным — color ('y', если цветной), а также тип принтера — type (лазерный — Laser, струйный — Jet или матричный — Matrix) и цена — price. База данных «Компьютерная фирма» Схема БД состоит из четырех таблиц (рис.1.1): Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, screen, price) Printer(code, model, color, type, price) Таблица Product представляет производителя (maker), номер модели (model) и тип (PC — ПК, Laptop — портативный компьютер или Printer — принтер). Предполагается, что в этой таблице номера моделей уникальны для всех производителей и типов продуктов. В таблице PC для каждого номера модели, обозначающего ПК, указаны скорость процессора — speed (МГерц), общий объем оперативной памяти - ram (Мбайт), размер диска — hd (в Гбайт), скорость считывающего устройства - cd (например, '4х') и цена — price. Таблица Laptop аналогична таблице РС за исключением того, что вместо скорости CD- привода содержит размер экрана — screen (в дюймах). В таблице Printer для каждой модели принтера указывается, является ли он цветным — color ('y', если цветной), тип принтера — type (лазерный — Laser, струйный — Jet или матричный — Matrix) и цена — price. Рис. 1.1. Схема базы данных «Компьютерная фирма» Дополнительную информацию можно извлечь из представленной на рис. 1.1 логической схемы данных. Таблицы по типам продукции (ПК, портативные компьютеры и принтеры) содержат внешний ключ (model) к таблице Product. Связь «один-ко-многим» означает, что в каждой из этих таблиц может отсутствовать модель, имеющаяся в таблице Product. С другой стороны, модель с одним и тем же номером может встречаться в такой таблице несколько раз, причем даже с полностью идентичными техническими характеристиками, так как первичным ключом здесь является столбец code. Последнее требует пояснения, так как разные люди вкладывают в понятие модели разный смысл. В рамках данной схемы считается, что модель — это единство производителя и технологии. Например, одинаковые модели могут комплектоваться технологически идентичными накопителями, но разной емкости, скажем, 60 и 80 Гбайт. В частности, это означает, что допустимо присутствие в таблице PC двух ПК с одинаковыми номерами модели, но по разной цене. На языке предметной области данная схема может означать, что в таблице Product содержится информация обо всех известных поставщиках рассматриваемой продукции и моделях, которые они поставляют, а в остальных таблицах находятся имеющиеся в наличии (или продаже) модели. Поэтому вполне возможна ситуация, когда имеется поставщик (maker) с моделями, ни одной из которых нет в наличии. |