Relational Design

Notation

• Primary keys are bolded and underlined.
• Foreign keys are marked with * at the end of their name and the referenced entity is written in parentheses.
• Required (not optional) attributes are bolded.

Tables

1. users (id, username, password, email)

2. admins (user_id* (Users))

3. components (id, name, brand, price, type, img_url)

4. cpu (component_id* (Components), socket, cores, threads, base_clock, boost_clock, tdp)

5. gpu (component_id* (Components), vram, tdp, base_clock, boost_clock, chipset, length)

6. memory (component_id* (Components), type, speed, capacity, modules)

7. storage (component_id* (Components), type, capacity, form_factor)

8. power_supply (component_id* (Components), type, wattage, form_factor)

9. motherboard (component_id* (Components), socket, chipset, form_factor, ram_type, num_ram_slots, max_ram_capacity, pci_express_slots)

10. pc_case (component_id* (Components), cooler_max_height, gpu_max_length)

11. case_storage_form_factors (case_id*(PC_Case), form_factor, num_slots)

12. case_ps_form_factors (case_id*(PC_Case), form_factor)

13. case_mobo_form_factors (case_id*(PC_Case), form_factor)

14. cooler (component_id* (Components), type, height, max_tdp_supported)

15. cooler_cpu_sockets (cooler_id*(Cooler), socket)

16. memory_card (component_id* (Components), num_slots, interface)

17. optical_drive (component_id* (Components), form_factor, type, interface, write_speed, read_speed)

18. sound_card (component_id* (Components), sample_rate, bit_depth, chipset, interface, channel)

19. cables (component_id* (Components), length_cm, type)

20. network_adapter (component_id* (Components), wifi_version, interface, num_antennas)

21. network_card (component_id* (Components), num_ports, speed, interface)

22. build (id, user_id* (Users), name, created_at, description, total_price, is_approved)

23. build_component (build_id*(Build), component_id*(Component), num_components)

24. favorite_build (build_id*(Build), user_id*(Users))

25. rating_build (build_id*(Build), user_id*(Users), value)

26. review (id, build_id* (Build), user_id* (Users), content, created_at)

27. suggestions (id, user_id* (Users), admin_id* (Admins), link, admin_comment, description, status, component_type)

DDL script for creating the database schema

schema_creation.sql​

DML script for filling tables with data

data_load.sql​

Relational diagram