За последние 24 часа нас посетили 17348 программистов и 987 роботов. Сейчас ищут 475 программистов ...

Паттерн eav и запросы для него

Тема в разделе "PostgreSQL", создана пользователем Dimon2x, 7 фев 2022.

  1. Dimon2x

    Dimon2x Старожил

    С нами с:
    26 фев 2012
    Сообщения:
    2.199
    Симпатии:
    184
    Решил немного потренироваться с БД, придумал таблицы магазина.
    Нормализация бд: решил использовать "EAV".

    Дайте советы по структуре, что нужно исправить и ответьте пожалуйста на некоторые вопросы.

    Придумайте ещё дополнительные задания по этой БД, для меня.

    Код (Text):
    1. CREATE TABLE category (
    2.     id smallserial PRIMARY KEY,
    3.     name_category char(60)
    4. );
    5.  
    6. INSERT INTO category (name_category) VALUES ('Lenovo');
    7. INSERT INTO category (name_category) VALUES ('Honor');
    8. INSERT INTO category (name_category) VALUES ('Acer');
    9. INSERT INTO category (name_category) VALUES ('Digma');
    10. INSERT INTO category (name_category) VALUES ('Dell');
    11. INSERT INTO category (name_category) VALUES ('HP');
    12.  
    13. CREATE TABLE diagonal (
    14.     id smallserial PRIMARY KEY,
    15.     size char(10)
    16. );
    17.  
    18.  
    19. INSERT INTO diagonal (size) VALUES ('15.6');
    20. INSERT INTO diagonal (size) VALUES ('17.3');
    21. INSERT INTO diagonal (size) VALUES ('14');
    22. INSERT INTO diagonal (size) VALUES ('14.1');
    23.  
    24.  
    25. CREATE TABLE screen (
    26.     id smallserial PRIMARY KEY,
    27.     permission char(20)
    28. );
    29.  
    30. INSERT INTO screen (permission) VALUES ('1920х1080');
    31. INSERT INTO screen (permission) VALUES ('1600х900');
    32. INSERT INTO screen (permission) VALUES ('1366х768');
    33.  
    34.  
    35. CREATE TABLE cpu (
    36.     id smallserial PRIMARY KEY,
    37.     cpu_name char(40)
    38. );
    39.  
    40. INSERT INTO cpu (cpu_name) VALUES ('AMD Ryzen 5 5600H');
    41. INSERT INTO cpu (cpu_name) VALUES ('Intel Core i3');
    42. INSERT INTO cpu (cpu_name) VALUES ('AMD 3020e');
    43. INSERT INTO cpu (cpu_name) VALUES ('Intel Core i5');
    44. INSERT INTO cpu (cpu_name) VALUES ('Intel Celeron N4500');
    45. INSERT INTO cpu (cpu_name) VALUES ('AMD Ryzen 3');
    46. INSERT INTO cpu (cpu_name) VALUES ('Intel Pentium J3710');
    47. INSERT INTO cpu (cpu_name) VALUES ('Intel Core i7');
    48. INSERT INTO cpu (cpu_name) VALUES ('Intel Celeron 4205U');
    49.  
    50.  
    51. CREATE TABLE ram (
    52.     id smallserial PRIMARY KEY,
    53.     quantity_ram char(40)
    54. );
    55.  
    56.  
    57. INSERT INTO ram (quantity_ram) VALUES ('8');
    58. INSERT INTO ram (quantity_ram) VALUES ('16');
    59. INSERT INTO ram (quantity_ram) VALUES ('4');
    60.  
    61.  
    62.  
    63. CREATE TABLE gpu (
    64.     id smallserial PRIMARY KEY,
    65.     gpu_name char(60)
    66. );
    67.  
    68. INSERT INTO gpu(gpu_name) VALUES ('NVIDIA GeForce RTX 3050');
    69. INSERT INTO gpu(gpu_name) VALUES ('Intel UHD Graphics');
    70. INSERT INTO gpu(gpu_name) VALUES ('AMD Radeon');
    71. INSERT INTO gpu(gpu_name) VALUES ('Intel UHD Graphics');
    72. INSERT INTO gpu(gpu_name) VALUES ('Intel Iris Xe graphics');
    73. INSERT INTO gpu(gpu_name) VALUES ('Intel HD Graphics 405');
    74. INSERT INTO gpu(gpu_name) VALUES ('NVIDIA GeForce RTX 3060');
    75.  
    76. CREATE TABLE disk (
    77.     id smallserial PRIMARY KEY,
    78.     capacity_disk char(60)
    79. );
    80.  
    81. INSERT INTO disk(capacity_disk) VALUES ('256 ГБ');
    82. INSERT INTO disk(capacity_disk) VALUES ('128 ГБ');
    83. INSERT INTO disk(capacity_disk) VALUES ('512 ГБ');
    84. INSERT INTO disk(capacity_disk) VALUES ('1000 ГБ');
    85.  
    86.  
    87. CREATE TABLE system (
    88.     id smallserial PRIMARY KEY,
    89.     system_name char(40)
    90. );
    91.  
    92. INSERT INTO system(system_name) VALUES ('noOS');
    93. INSERT INTO system(system_name) VALUES ('Free DOS');
    94. INSERT INTO system(system_name) VALUES ('Windows 10 Home');
    95. INSERT INTO system(system_name) VALUES ('Eshell');
    96. INSERT INTO system(system_name) VALUES ('Linux');
    97. INSERT INTO system(system_name) VALUES ('Windows 11 Home');
    98.  
    99.  
    100. CREATE TABLE laptops (
    101.     id smallserial PRIMARY KEY,
    102.     name_notebook char(40),
    103.     price real,
    104.     diagonal_id numeric,
    105.     screen_id numeric,
    106.     cpu_id numeric,
    107.     ram_id numeric,
    108.     gpu_id numeric,
    109.     disk_id numeric,
    110.     system_id numeric,
    111.     category_id numeric NOT NULL
    112. );

    Код (Text):
    1. id  name_notebook                     price  diagonal_id  screen_id  cpu_id  ram_id   gpu_id  disk_id     system_id     category_id
    2.  
    3. 1   Lenovo IP Gaming 3 15ACH6         70 990    1           1           1      1        1       1           1               1
    4.  
    5. 2   Lenovo IdeaPad S145-15IIL         33 990    1           1           2      1        2       2           2               1
    6.  
    7. 3   Lenovo IdeaPad 3 17ADA05          41 290    2           2           3      1        3       1           1               1    
    8.  
    9. 4   Honor MagicBook X14               45 990    3           1           2      1        4       1           3               2
    10.  
    11. 5   Acer Aspire 3 A317-33-C2SS        29 990    2           2           5      3        4       2           3               3          
    12.  
    13. 6   Acer Aspire 3 A317-52-332C        35 190    2           2           2      3        4       1           4               3
    14.  
    15. 7   Acer Nitro 5 AN515-55-57HB        84 990    1           1           4      2        1       3           4               3
    16.  
    17. 8   Acer Aspire 3 A315-23-R5HA        32 790    1           1           6      1        3       2           4               3
    18.  
    19. 9   Acer Aspire 3 A315-56-34DD        38 390    1           1           2      1        4       4           4               3
    20.  
    21. 10  Digma EVE 14 P416                 19 790    3           1           7      3        6       2           3               4
    22.  
    23. 11  Ноутбук DELL G15 5510             110 990   1           1           8      2        7       3           5               5
    24.  
    25. 12  DELL Vostro 3515                  43 570    1           1           6      1        3       1           6               5
    26.  
    27. 13  DELL Inspiron 3583                25 590    1           3           9      3        4       2           5               5

    SELECT name_notebook, price, size, permission, cpu_name, quantity_ram, gpu_name, capacity_disk, system_name FROM laptops
    JOIN diagonal ON diagonal_id=diagonal.id
    JOIN screen ON screen_id=screen.id
    JOIN cpu ON cpu_id=cpu.id
    JOIN ram ON ram_id=ram.id
    JOIN gpu ON gpu_id=gpu.id
    JOIN disk ON disk_id=disk.id
    JOIN system ON system_id=system.id

    ORDER BY laptops.id;

    Код (Text):
    1. SELECT name_notebook, price, size, permission, cpu_name, quantity_ram, gpu_name, capacity_disk, system_name FROM laptops
    2. JOIN diagonal ON diagonal_id=diagonal.id
    3. JOIN screen ON screen_id=screen.id
    4. JOIN cpu ON cpu_id=cpu.id
    5. JOIN ram ON ram_id=ram.id
    6. JOIN gpu ON gpu_id=gpu.id
    7. JOIN disk ON disk_id=disk.id
    8. JOIN system ON system_id=system.id
    9.  
    10. GROUP BY laptops.id, name_notebook, price, size, permission, cpu_name, quantity_ram, gpu_name, capacity_disk, system_name
    11.  
    12. HAVING ram.quantity_ram = '8';

    Код (Text):
    1. SELECT name_notebook
    2. FROM laptops
    3. JOIN ram ON laptops.ram_id = ram.id AND laptops.ram_id IN (2,3)
    4. GROUP BY name_notebook, ram.quantity_ram;

    Код (Text):
    1. SELECT name_notebook,ram.quantity_ram
    2. FROM laptops
    3. LEFT OUTER JOIN ram ON laptops.ram_id = ram.id AND ram.quantity_ram = '8'
    4. GROUP BY name_notebook, ram.quantity_ram
    5. HAVING ram.quantity_ram = '8';
    6.  
    7. Я так понял, что при использовании HAVING, поля с NULL игнорируются?
    8.  
    9. SELECT name_notebook,ram.quantity_ram
    10. FROM laptops
    11. LEFT OUTER JOIN ram ON laptops.ram_id = ram.id AND ram.quantity_ram = '8'
    12. GROUP BY name_notebook, ram.quantity_ram;
    Код (Text):
    1. SELECT name_notebook, ram.quantity_ram
    2. FROM laptops
    3. LEFT OUTER JOIN ram ON laptops.ram_id = ram.id AND ram.quantity_ram = '8'
    4. GROUP BY name_notebook, ram.quantity_ram;

    Здесь за основу будет таблица "ram" и к ней уже присоединяется таблица "laptops", потому что RIGHT JOIN
    Почему, если убрать HAVING то будут лишние поля, если я указал, что нужно только ram.quantity_ram = '16'?

    Код (Text):
    1. SELECT name_notebook, ram.quantity_ram FROM laptops
    2. RIGHT OUTER JOIN ram ON laptops.ram_id = ram.id AND ram.quantity_ram = '16'
    3. GROUP BY name_notebook, ram.quantity_ram
    4. HAVING ram.quantity_ram = '16';
    Код (Text):
    1. SELECT ram.quantity_ram, count(ram.quantity_ram)
    2. FROM laptops
    3. JOIN ram ON laptops.ram_id = ram.id
    4. GROUP BY ram.quantity_ram;

    Почему для GROUP BY надо указывать теже поля, что и для SELECT?

    Код (Text):
    1. SELECT name_notebook, category.name_category FROM laptops
    2. JOIN category ON category.id = laptops.category_id GROUP BY name_notebook, category.name_category;

    Почему, если для "count" указать "name_category", то те категории, которые пустые, в нгих посчитается 1?

    Код (Text):
    1. SELECT name_category, count(name_category) FROM category
    2. LEFT JOIN laptops ON category.id = laptops.category_id GROUP BY category.name_category;
    А вот так, уже правильно считает.

    Код (Text):
    1. SELECT name_category, count(laptops.category_id) FROM category
    2. LEFT JOIN laptops ON category.id = laptops.category_id GROUP BY category.name_category;
    --- Добавлено ---
     
  2. Dimon2x

    Dimon2x Старожил

    С нами с:
    26 фев 2012
    Сообщения:
    2.199
    Симпатии:
    184
    Код (Text):
    1. INSERT INTO laptops (
    2.                     name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    3.                  
    4. ) VALUES (
    5.         'Lenovo IP Gaming 3 15ACH6', '70990', '1', '1', '1', '1', '1', '1', '1', '1'
    6. );
    7.  
    8. INSERT INTO laptops (
    9.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    10.                  
    11. ) VALUES (
    12.         'Lenovo IdeaPad S145-15IIL', '33990', '1', '1', '2', '1', '2', '2', '2', '1'
    13. );
    14.  
    15. INSERT INTO laptops (
    16.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    17.                  
    18. ) VALUES (
    19.         'Lenovo IdeaPad 3 17ADA05', '41290', '2', '2', '3', '1', '3', '1', '1', '1'
    20. );
    21.  
    22. INSERT INTO laptops (
    23.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    24.                  
    25. ) VALUES (
    26.         'Honor MagicBook X14', '45990', '3', '1', '2', '1', '4', '1', '3', '2,
    27. );
    28.  
    29. INSERT INTO laptops (
    30.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    31.                  
    32. ) VALUES (
    33.         'Acer Aspire 3 A317-33-C2SS', '29990', '2', '2', '5', '3', '4', '2', '3', '3'
    34. );
    35.  
    36. INSERT INTO laptops (
    37.                     name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    38.                  
    39. ) VALUES (
    40.         'Acer Aspire 3 A317-52-332C', '35190', '2', '2', '2', '3', '4', '1', '4', '3'
    41. );
    42.  
    43. INSERT INTO laptops (
    44.                     name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    45.                  
    46. ) VALUES (
    47.         'Acer Nitro 5 AN515-55-57HB', '84990', '1', '1', '4', '2', '1', '3', '4', '3'
    48. );
    49.  
    50. INSERT INTO laptops (
    51.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    52.                  
    53. ) VALUES (
    54.         'Acer Aspire 3 A315-23-R5HA', '32790', '1', '1', '6', '1', '3', '2', '4', '3,
    55. );
    56.  
    57. INSERT INTO laptops (
    58.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    59.                  
    60. ) VALUES (
    61.         'Acer Aspire 3 A315-56-34DD', '38390', '1', '1', '2', '1', '4', '4', '4', '3'
    62. );
    63.  
    64. INSERT INTO laptops (
    65.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    66.                  
    67. ) VALUES (
    68.         'Digma EVE 14 P416', '19790', '3', '1', '7', '3', '6', '2', '3', '4'
    69. );
    70.  
    71. INSERT INTO laptops (
    72.                     name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    73.                  
    74. ) VALUES (
    75.         'Ноутбук DELL G15 5510', '110990', '1', '1', '8', '2', '7', '3', '5', '5'
    76. );
    77.  
    78. INSERT INTO laptops (
    79.                     name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    80.                  
    81. ) VALUES (
    82.         'DELL Vostro 3515 ', '43570', '1', '1', '6', '1', '3', '1', '6', '5'
    83. );
    84.  
    85. INSERT INTO laptops (
    86.                    name_notebook, price, diagonal_id, screen_id, cpu_id, ram_id, gpu_id, disk_id, system_id, category_id
    87.                  
    88. ) VALUES (
    89.         'DELL Inspiron 3583', '25590', '1', '3', '9', '3', '4', '2', '5', '5'
    90. );