Functional DBMS

The world of databases has long been captured by relational databases that use the SQL language. So much so that the emerging varieties are called NoSQL. They managed to recapture a certain place in this market, but they do not intend to die relational DBMS, and continue to be actively used for their own purposes.

In this article I want to describe the concept of a functional database. For a better understanding, I will do this by comparing with the classical relational model. As examples, tasks from various SQL tests found on the Internet will be used.

Introduction

Relational databases operate on tables and fields. In the functional database, classes and functions will be used instead. A field in the table with N keys will be represented as a function of N parameters. Instead of links between tables, functions will be used that return objects of the class to which the link is going. Instead of a JOIN, the composition of functions will be used.

Before going directly to the tasks, I will describe the task of domain logic. For DDL, I will use PostgreSQL syntax. For functional its own syntax.

')

Tables and Fields

Simple Sku object with fields name and price:

Relational

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, .

, , , .

/ / . , . , :

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

ID , 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

ID .

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?

( ):

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), ( Customer Year, ), , ORDER ( bought, , ).

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 .

, :

. , . (, ), , , .
. (, sold, bought ..), . . , , sold , . , CREATE VIEW. , .

. ( ). , ( , — , ). , «» . , . , :

CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = '' MESSAGE  '-        2019 ';

. CLASS ClassP: Class1, Class2 . , .

, , Java, . , . , ( PostgreSQL) « ». , , . , , , .

Source: https://habr.com/ru/post/458774/

All Articles

Functional DBMS

Introduction

Tables and Fields

1.1

1.2

1.3

1.4

1.5

2.1

2.2

2.3

More articles: