🥇DBMS کاربردی

دنیای پایگاه های داده مدت هاست که تحت سلطه DBMS های رابطه ای است که از زبان SQL استفاده می کنند. به طوری که انواع در حال ظهور NoSQL نامیده می شوند. آنها توانستند مکان خاصی را برای خود در این بازار ایجاد کنند، اما DBMS های رابطه ای نمی میرند و همچنان به طور فعال برای اهداف خود استفاده می شوند.

در این مقاله می خواهم مفهوم یک پایگاه داده کاربردی را شرح دهم. برای درک بهتر، این کار را با مقایسه با مدل رابطه‌ای کلاسیک انجام خواهم داد. به عنوان مثال، وظایف از تست های مختلف SQL که در اینترنت یافت می شوند استفاده خواهند شد.

معرفی

پایگاه داده های رابطه ای بر روی جداول و فیلدها کار می کنند. در یک پایگاه داده عملکردی، به ترتیب از کلاس ها و توابع استفاده می شود. یک فیلد در جدول با N کلید به عنوان تابعی از N پارامتر نمایش داده می شود. به جای روابط بین جداول، از توابعی استفاده می شود که اشیاء کلاسی را که اتصال به آن برقرار می شود، برمی گرداند. ترکیب تابع به جای JOIN استفاده خواهد شد.

قبل از حرکت مستقیم به وظایف، وظیفه منطق دامنه را شرح خواهم داد. برای DDL از نحو PostgreSQL استفاده خواهم کرد. برای عملکرد، نحو خاص خود را دارد.

جداول و فیلدها

یک شی Sku ساده با فیلدهای نام و قیمت:

رابطه ای

CREATE TABLE Sku
(
    id bigint NOT NULL,
    name character varying(100),
    price numeric(10,5),
    CONSTRAINT id_pkey PRIMARY KEY (id)
)

کاربردی

CLASS Sku; name = DATA STRING[100] (Sku); price = DATA NUMERIC[10,5] (Sku);

دوتا رو اعلام میکنیم توابع، که یک پارامتر Sku را به عنوان ورودی می گیرند و یک نوع اولیه را برمی گردانند.

فرض بر این است که در یک DBMS عملکردی هر شیء دارای کد داخلی است که به طور خودکار تولید می شود و در صورت لزوم می توان به آن دسترسی داشت.

بیایید قیمت محصول/فروشگاه/تامین کننده را تعیین کنیم. ممکن است در طول زمان تغییر کند، بنابراین بیایید یک فیلد زمانی به جدول اضافه کنیم. من از اعلام جداول برای دایرکتوری ها در یک پایگاه داده رابطه ای صرف نظر می کنم تا کد را کوتاه کنم:

رابطه ای

CREATE TABLE prices
(
    skuId bigint NOT NULL,
    storeId bigint NOT NULL,
    supplierId bigint NOT NULL,
    dateTime timestamp without time zone,
    price numeric(10,5),
    CONSTRAINT prices_pkey PRIMARY KEY (skuId, storeId, supplierId)
)

کاربردی

CLASS Sku; CLASS Store; CLASS Supplier; dateTime = DATA DATETIME (Sku, Store, Supplier); price = DATA NUMERIC[10,5] (Sku, Store, Supplier);

شاخص

برای مثال آخر، ما یک فهرست روی همه کلیدها و تاریخ ایجاد می کنیم تا بتوانیم به سرعت قیمت را برای یک زمان خاص پیدا کنیم.

رابطه ای

CREATE INDEX prices_date
    ON prices
    (skuId, storeId, supplierId, dateTime)

کاربردی

INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);

وظایف

بیایید با مسائل نسبتاً ساده که از موارد مربوطه گرفته شده است شروع کنیم مقاله در هابر

ابتدا، بیایید منطق دامنه را اعلام کنیم (برای پایگاه داده رابطه ای این به طور مستقیم در مقاله بالا انجام می شود).

CLASS Department; name = DATA STRING[100] (Department);

CLASS Employee; department = DATA Department (Employee); chief = DATA Employee (Employee); name = DATA STRING[100] (Employee); salary = DATA NUMERIC[14,2] (Employee);

وظیفه 1.1

لیستی از کارمندانی را که حقوقی بیشتر از سرپرست فوری خود دریافت می کنند نمایش دهید.

رابطه ای

select a.*
from   employee a, employee b
where  b.id = a.chief_id
and    a.salary > b.salary

کاربردی

SELECT name(Employee a) WHERE salary(a) > salary(chief(a));

وظیفه 1.2

کارمندانی را فهرست کنید که حداکثر حقوق را در بخش خود دریافت می کنند

رابطه ای

select a.*
from   employee a
where  a.salary = ( select max(salary) from employee b
                    where  b.department_id = a.department_id )

کاربردی

maxSalary 'Максимальная зарплата' (Department s) = GROUP MAX salary(Employee e) IF department(e) = s; SELECT name(Employee a) WHERE salary(a) = maxSalary(department(a));

// или если "заинлайнить" SELECT name(Employee a) WHERE salary(a) = maxSalary(GROUP MAX salary(Employee e) IF department(e) = department(a));

هر دو پیاده سازی معادل هستند. برای مورد اول، در یک پایگاه داده رابطه ای می توانید از CREATE VIEW استفاده کنید که به همین ترتیب ابتدا حداکثر حقوق یک بخش خاص را در آن محاسبه می کند. در ادامه، برای وضوح، از حالت اول استفاده خواهم کرد، زیرا راه حل را بهتر نشان می دهد.

وظیفه 1.3

لیستی از شناسه های بخش را نمایش دهید، تعداد کارمندانی که در آنها از 3 نفر تجاوز نمی کند.

رابطه ای

select department_id
from   employee
group  by department_id
having count(*) <= 3

کاربردی

countEmployees 'Количество сотрудников' (Department d) = GROUP SUM 1 IF department(Employee e) = d; SELECT Department d WHERE countEmployees(d) <= 3;

وظیفه 1.4

لیستی از کارمندانی که مدیر مشخصی ندارند که در همان بخش کار می کند نمایش دهید.

رابطه ای

select a.*
from   employee a
left   join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where  b.id is null

کاربردی

SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));

وظیفه 1.5

لیستی از شناسه های بخش را با حداکثر حقوق کل کارکنان پیدا کنید.

رابطه ای

with sum_salary as
  ( select department_id, sum(salary) salary
    from   employee
    group  by department_id )
select department_id
from   sum_salary a       
where  a.salary = ( select max(salary) from sum_salary )

کاربردی

salarySum 'Максимальная зарплата' (Department d) = GROUP SUM salary(Employee e) IF department(e) = d; maxSalarySum 'Максимальная зарплата отделов' () = GROUP MAX salarySum(Department d); SELECT Department d WHERE salarySum(d) = maxSalarySum();

بیایید به کارهای پیچیده تر از دیگری برویم مقاله. این شامل تجزیه و تحلیل دقیق از نحوه پیاده سازی این کار در MS SQL است.

وظیفه 2.1

کدام فروشندگان در سال 1997 بیش از 30 دستگاه از محصول شماره 1 را فروخته اند؟

منطق دامنه (مانند قبل در RDBMS ما از اعلان صرفنظر می کنیم):

CLASS Employee 'Продавец'; lastName 'Фамилия' = DATA STRING[100] (Employee);


CLASS Product 'Продукт';

id = DATA INTEGER (Product);

name = DATA STRING[100] (Product);
CLASS Order 'Заказ';

date = DATA DATE (Order);

employee = DATA Employee (Order);
CLASS Detail 'Строка заказа';

order = DATA Order (Detail); product = DATA Product (Detail); quantity = DATA NUMERIC[10,5] (Detail);

رابطه ای

select LastName
from Employees as e
where (
  select sum(od.Quantity)
  from [Order Details] as od
  where od.ProductID = 1 and od.OrderID in (
    select o.OrderID
    from Orders as o
    where year(o.OrderDate) = 1997 and e.EmployeeID = o.EmployeeID)
) > 30

کاربردی

sold (Employee e, INTEGER productId, INTEGER year) = GROUP SUM quantity(OrderDetail d) IF employee(order(d)) = e AND id(product(d)) = productId AND extractYear(date(order(d))) = year; SELECT lastName(Employee e) WHERE sold(e, 1, 1997) > 30;

وظیفه 2.2

برای هر خریدار (نام، نام خانوادگی)، دو کالا (نام) را که خریدار در سال 1997 بیشترین پول را برای آن خرج کرده است، بیابید.

منطق دامنه را از مثال قبلی گسترش می دهیم:

CLASS Customer 'Клиент'; contactName 'ФИО' = DATA STRING[100] (Customer);


customer = DATA Customer (Order);

unitPrice = DATA NUMERIC[14,2] (Detail); discount = DATA NUMERIC[6,2] (Detail);

رابطه ای

SELECT ContactName, ProductName FROM (
SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
    PARTITION BY c.ContactName
    ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName
) t
WHERE RatingByAmt < 3

کاربردی

sum (Detail d) = quantity(d) * unitPrice(d) * (1 - discount(d)); bought 'Купил' (Customer c, Product p, INTEGER y) = GROUP SUM sum(Detail d) IF customer(order(d)) = c AND product(d) = p AND extractYear(date(order(d))) = y; rating 'Рейтинг' (Customer c, Product p, INTEGER y) = PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y; SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;

عملگر PARTITION بر اساس اصل زیر کار می کند: عبارت مشخص شده بعد از SUM (اینجا 1) را در گروه های مشخص شده (در اینجا مشتری و سال، اما می تواند هر عبارتی باشد) جمع می کند، در گروه ها با عبارات مشخص شده در ORDER مرتب می شود. اینجا خریداری شده است، و اگر برابر باشد، طبق کد محصول داخلی).

وظیفه 2.3

برای انجام سفارشات فعلی چه تعداد کالا باید از تامین کنندگان سفارش داده شود.

بیایید دوباره منطق دامنه را گسترش دهیم:

CLASS Supplier 'Поставщик'; companyName = DATA STRING[100] (Supplier);


supplier = DATA Supplier (Product);

unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product); reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);

رابطه ای

select s.CompanyName, p.ProductName, sum(od.Quantity) + p.ReorderLevel — p.UnitsInStock as ToOrder
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
join Products p on od.ProductID = p.ProductID
join Suppliers s on p.SupplierID = s.SupplierID
where o.ShippedDate is null
group by s.CompanyName, p.ProductName, p.UnitsInStock, p.ReorderLevel
having p.UnitsInStock < sum(od.Quantity) + p.ReorderLevel

کاربردی

orderedNotShipped 'Заказано, но не отгружено' (Product p) = GROUP SUM quantity(OrderDetail d) IF product(d) = p; toOrder 'К заказу' (Product p) = orderedNotShipped(p) + reorderLevel(p) - unitsInStock(p); SELECT companyName(supplier(Product p)), name(p), toOrder(p) WHERE toOrder(p) > 0;

مشکل با ستاره

و مثال آخر شخصا از من است. منطق یک شبکه اجتماعی وجود دارد. مردم می توانند با هم دوست باشند و همدیگر را دوست داشته باشند. از منظر پایگاه داده عملکردی به شکل زیر است:

CLASS Person; likes = DATA BOOLEAN (Person, Person); friends = DATA BOOLEAN (Person, Person);

یافتن نامزدهای احتمالی برای دوستی ضروری است. به طور رسمی تر، باید همه افراد A، B، C را پیدا کنید به طوری که A با B دوست باشد، و B با C دوست باشد، A دوست C باشد، اما A با C دوست نباشد.
از منظر پایگاه داده عملکردی، پرس و جو به صورت زیر است:

SELECT Person a, Person b, Person c WHERE likes(a, c) AND NOT friends(a, c) AND friends(a, b) AND friends(b, c);

خواننده تشویق می شود که این مشکل را در SQL به تنهایی حل کند. فرض بر این است که تعداد دوستان بسیار کمتری نسبت به افرادی که دوست دارید وجود دارد. بنابراین آنها در جداول جداگانه قرار دارند. در صورت موفقیت، یک کار با دو ستاره نیز وجود دارد. در آن دوستی متقارن نیست. در یک پایگاه داده عملکردی به شکل زیر است:

SELECT Person a, Person b, Person c WHERE likes(a, c) AND NOT friends(a, c) AND (friends(a, b) OR friends(b, a)) AND (friends(b, c) OR friends(c, b));

UPD: راه حل مشکل با ستاره اول و دوم از dss_kalika:

SELECT 
   pl.PersonAID
  ,pf.PersonAID
  ,pff.PersonAID
FROM Persons                 AS p
--Лайки                      
JOIN PersonRelationShip      AS pl ON pl.PersonAID = p.PersonID
                                  AND pl.Relation  = 'Like'
--Друзья                     
JOIN PersonRelationShip      AS pf ON pf.PersonAID = p.PersonID 
                                  AND pf.Relation = 'Friend'
--Друзья Друзей              
JOIN PersonRelationShip      AS pff ON pff.PersonAID = pf.PersonBID
                                   AND pff.PersonBID = pl.PersonBID
                                   AND pff.Relation = 'Friend'
--Ещё не дружат         
LEFT JOIN PersonRelationShip AS pnf ON pnf.PersonAID = p.PersonID
                                   AND pnf.PersonBID = pff.PersonBID
                                   AND pnf.Relation = 'Friend'
WHERE pnf.PersonAID IS NULL

;WITH PersonRelationShipCollapsed AS (
  SELECT pl.PersonAID
        ,pl.PersonBID
        ,pl.Relation 
  FROM #PersonRelationShip      AS pl 
  
  UNION 

  SELECT pl.PersonBID AS PersonAID
        ,pl.PersonAID AS PersonBID
        ,pl.Relation
  FROM #PersonRelationShip      AS pl 
)
SELECT 
   pl.PersonAID
  ,pf.PersonBID
  ,pff.PersonBID
FROM #Persons                      AS p
--Лайки                      
JOIN PersonRelationShipCollapsed  AS pl ON pl.PersonAID = p.PersonID
                                 AND pl.Relation  = 'Like'                                  
--Друзья                          
JOIN PersonRelationShipCollapsed  AS pf ON pf.PersonAID = p.PersonID 
                                 AND pf.Relation = 'Friend'
--Друзья Друзей                   
JOIN PersonRelationShipCollapsed  AS pff ON pff.PersonAID = pf.PersonBID
                                 AND pff.PersonBID = pl.PersonBID
                                 AND pff.Relation = 'Friend'
--Ещё не дружат                   
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
                                   AND pnf.PersonBID = pff.PersonBID
                                   AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL

نتیجه

لازم به ذکر است که نحو زبان داده شده تنها یکی از گزینه های پیاده سازی مفهوم داده شده است. SQL به عنوان پایه در نظر گرفته شد و هدف این بود که تا حد امکان شبیه به آن باشد. البته ممکن است برخی نام کلمات کلیدی، ثبت کلمات و غیره را دوست نداشته باشند. نکته اصلی در اینجا خود مفهوم است. در صورت تمایل، می توانید هر دو C++ و Python را به نحوی مشابه بسازید.

مفهوم پایگاه داده توصیف شده، به نظر من، دارای مزایای زیر است:

سهولت. این یک شاخص نسبتا ذهنی است که در موارد ساده مشخص نیست. اما اگر به موارد پیچیده تر (مثلاً مشکلات ستاره) نگاه کنید، به نظر من، نوشتن چنین پرس و جوها بسیار آسان تر است.
Инкапсуляция. در برخی از مثال ها من توابع میانی را اعلام کردم (به عنوان مثال، فروخته شده, خریداری شده و غیره)، که از آن توابع بعدی ساخته شد. این به شما امکان می دهد منطق عملکردهای خاص را در صورت لزوم بدون تغییر منطق عملکردهایی که به آنها وابسته هستند تغییر دهید. به عنوان مثال، شما می توانید فروش انجام دهید فروخته شده از اشیاء کاملاً متفاوت محاسبه شد، در حالی که بقیه منطق تغییر نخواهد کرد. بله، در RDBMS این کار با CREATE VIEW قابل انجام است. اما اگر تمام منطق را به این صورت بنویسید، آن وقت چندان خواندنی به نظر نمی رسد.
بدون شکاف معنایی. چنین پایگاه داده ای بر روی توابع و کلاس ها (به جای جداول و فیلدها) عمل می کند. درست مانند برنامه نویسی کلاسیک (اگر متد را تابعی با اولین پارامتر در قالب کلاسی که به آن تعلق دارد فرض کنیم). بر این اساس، "دوستی" با زبان های برنامه نویسی جهانی باید بسیار آسان تر باشد. علاوه بر این، این مفهوم اجازه می دهد تا عملکردهای بسیار پیچیده تری اجرا شود. به عنوان مثال، می توانید عملگرهایی مانند:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
وراثت و چندشکلی. در یک پایگاه داده عملکردی، می توانید وراثت چندگانه را از طریق ساختارهای CLASS ClassP: Class1، Class2 معرفی کنید و چندشکلی چندگانه را پیاده سازی کنید. احتمالاً در مقالات بعدی دقیقاً چگونه خواهم نوشت.

اگرچه این فقط یک مفهوم است، ما قبلاً پیاده سازی هایی در جاوا داریم که تمام منطق عملکردی را به منطق رابطه ای ترجمه می کند. به علاوه، منطق بازنمایی ها و بسیاری چیزهای دیگر به زیبایی به آن متصل شده است که به لطف آن یک کل به دست می آوریم بستر های نرم افزاری. اساسا، ما از RDBMS (در حال حاضر فقط PostgreSQL) به عنوان یک "ماشین مجازی" استفاده می کنیم. گاهی اوقات مشکلاتی با این ترجمه ایجاد می شود زیرا بهینه ساز پرس و جو RDBMS آمار خاصی را که FDBMS می داند نمی داند. در تئوری، امکان پیاده‌سازی یک سیستم مدیریت پایگاه داده وجود دارد که از ساختار خاصی به عنوان ذخیره‌سازی استفاده می‌کند که به طور خاص برای منطق عملکردی سازگار شده است.

منبع: www.habr.com

DBMS عملکردی

معرفی

جداول و فیلدها

شاخص

وظایف

وظیفه 1.1

وظیفه 1.2

وظیفه 1.3

وظیفه 1.4

وظیفه 1.5

وظیفه 2.1

وظیفه 2.2

وظیفه 2.3

مشکل با ستاره

نتیجه

اضافه کردن نظر انصراف از پاسخ