SQL computer hardware database

License

SQL computer hardware database is released under GPLv2 license.

Description

This code enables you to create flexible SQL database you can freely use for your SQL study or even if you want to start your own computer parts web shop.

SQL computer hardware database tables

These are the tables inside computer hardware SQL database:

  1. bus_interfaces
  2. computer_case_sizes
  3. cooling_fan_sizes
  4. drive_bay_widths
  5. ethernet_standards
  6. expansion_slots
  7. hard_drive_features
  8. manufacturers
  9. memory_types
  10. motherboard_form_factors
  11. optical_disk_formats
  12. optical_drive_features
  13. peripheral_interfaces
  14. power_supply_standards
  15. processor_sockets
  16. sound_channel_standards
  17. motherboard_nb_chipsets
  18. motherboard_sb_chipsets
  19. gpus
  20. lan_chips
  21. sound_chips
  22. computer_cases
  23. hard_drives
  24. memories
  25. motherboards
  26. optical_drives
  27. power_supplies
  28. processors
  29. processor_cores
  30. sound_cards
  31. video_adapters
  32. l_hard_drives_hard_drive_features
  33. l_motherboards_expansion_slots
  34. l_motherboard_bus_interfaces
  35. l_motherboard_peripheral_interfaces
  36. l_optical_drives_optical_disk_formats
  37. l_optical_drives_optical_drive_features
  38. l_computer_cases_cooling_fans
  39. l_computer_cases_drive_bay_widths

SQL computer hardware database entity-relationship (ER) diagram

Entity relationship diagram for for this database is rather complicated and takes a lot of screen space so you can review it using following link:

SQL computer hardware database query examples

User wants list of all bus standards inside database:

SELECT
 	bus_interface_name
FROM bus_interfaces;

User wants list of all motherboards with manufacturer name, processor socket, supported memory type and form factor without integrated video subsystem:

SELECT
 	motherboard_name,
	manufacturer_name,
	processor_socket_name,
	memory_type_name,
	motherboard_form_factor_name
FROM motherboards 	
	INNER JOIN manufacturers USING(manufacturer_id)
	INNER JOIN processor_sockets USING(processor_socket_id)
	INNER JOIN memory_types USING(memory_type_id)
	INNER JOIN motherboard_form_factors USING(motherboard_form_factor_id)
WHERE motherboards.gpu_id IS NULL;

User wants list of all motherboards with manufacturer name, processor socket, supported memory type and form factor without integrated video subsystem:

SELECT
 	motherboard_name,
	manufacturer_name,
	processor_socket_name,
	memory_type_name,
	motherboard_form_factor_name
FROM motherboards 	
	INNER JOIN manufacturers USING(manufacturer_id)
	INNER JOIN processor_sockets USING(processor_socket_id)
	INNER JOIN memory_types USING(memory_type_id)
	INNER JOIN motherboard_form_factors USING(motherboard_form_factor_id)
WHERE motherboards.gpu_id IS NULL;

User wants list of all LGA775 processors with information about manufacturer, processor name, processor core name, core count and speed:

SELECT 	
	processors.processor_name,
	manufacturers.manufacturer_name,
	processors.frequency_mhz,
	processor_cores.processor_core_name,
	processor_cores.processor_core_count
FROM processors
	INNER JOIN manufacturers USING(manufacturer_id)   
	INNER JOIN processor_sockets USING(processor_socket_id)
	INNER JOIN processor_cores USING(processor_core_id)
WHERE processor_sockets.processor_socket_name LIKE 'LGA 775';

User wants list of all motherboard, processor, HDD combinations that meet the following requirements: Motherboard must support AMD processor and SATA HDD interface with SMART support. Results should be sorted descending by motherboard manufacturer name. Every hardware component inside one combination must be mutually compatible:

SELECT	
        motherboard_name,
	mbo_manus.manufacturer_name AS motherboard_manufacturer_name, 	
	processor_name,
	proc_manus.manufacturer_name AS processor_manufacturer_name,
	bus_interface_name,
	hard_drive_name,
	hard_drive_feature_name 
FROM motherboards
	INNER JOIN manufacturers AS mbo_manus ON motherboards.manufacturer_id = mbo_manus.manufacturer_id 
	INNER JOIN processors ON motherboards.processor_socket_id = processors.processor_socket_id
	INNER JOIN manufacturers AS proc_manus ON processors.manufacturer_id = proc_manus.manufacturer_id
	INNER JOIN l_motherboard_bus_interfaces ON motherboards.motherboard_id = l_motherboard_bus_interfaces.motherboard_id
	INNER JOIN bus_interfaces ON l_motherboard_bus_interfaces.bus_interface_id = bus_interfaces.bus_interface_id
	INNER JOIN hard_drives ON bus_interfaces.bus_interface_id = hard_drives.bus_interface_id
	INNER JOIN l_hard_drives_hard_drive_features ON hard_drives.hard_drive_id = l_hard_drives_hard_drive_features.hard_drive_id
	INNER JOIN hard_drive_features ON l_hard_drives_hard_drive_features.hard_drive_feature_id = hard_drive_features.hard_drive_feature_id
WHERE proc_manus.manufacturer_name LIKE 'AMD' 
	AND bus_interfaces.bus_interface_name LIKE 'Serial ATA' 
	AND hard_drive_features.hard_drive_feature_name LIKE 'SMART'
ORDER BY mbo_manus.manufacturer_name;

Download

I have prepared two packages. First package is MySQL Workbench .mwb package. This file contains SQL database structure, ER diagram and create database SQL script. I've also prepared bunch of PC hardware example data with insert SQL script. It's all there inside this .mwb file. So here's the first package in MySQL Workbench .mwb format:

Second package is plain old .zip package that holds everything .mwb package holds but you don't need MySQL Workbench to open it.

SQL computer hardware database feedback

If you have any questions please don't hesitate to leave you comment here and I will do my best to help you. Cheers!

5 thoughts on “SQL computer hardware database

  1. Jordan

    Wow, this is seriously amazing.

    I’m currently making a Smart System Builder at University for a project and this is definitely helping me out. Wonderful work and thank you so very much!

    Reply
  2. Leo

    First of all thank you very much for this post, it will help me alot.
    Do you have any suggestions on how to populate this database: where to get information on all computer hardware?

    Also how would you go about to relate the information in this database to products on Amazon and other computer part retailers or price comparison sites?

    You dont need to answer these guestion but maybe you could point me in the right direction on how I could find this out (except the obvius “google it” :) )

    Best / Leo

    Reply
  3. Kristo

    Hi, nice work.
    Is there a way to get a complete database with all international brands (manufactures) and there hardware to fill in that database?
    Thanks in advance.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *