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

Решил немного потренироваться с БД, придумал таблицы магазина.
Нормализация бд: решил использовать "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;

Спойлер: Вывести все ноутбуки, у которых ОЗУ 8 ГБ: решил сделать по полю ram.quantity_ram или лучше по 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';

Спойлер: Вывести ноутбуки с ОЗУ 16 и 4

Код (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;

Спойлер: Вывести все ноутбуки, у которых ОЗУ 8 ГБ и все остальные

Код (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;

--- Добавлено ---

 

Спойлер: Наполнение БД

Код (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. );