A PC subcontractor supplies three components that go to make a PC – memory, moth

Question

A PC subcontractor supplies three components that go to make a PC – memory, motherboards, and video controllers. A motherboard consists of a base component (called base), memory cards (there are four memory sockets, which are populated with memory cards), and the video controller. The video controller consists of the video controller processor and a single memory card. The motherboards can have any number of memory cards, the minimum being one. Motherboards with different amounts of memory are considered as different items. For simplicity sake, assume that all memory cards (whether used in a motherboard or a video controller) are exactly alike. The manufacturer wants to have all his item information in a database, and correctly identifies that the ER model represents a unary many-to-many relationship. That ER model is represented in this ER diagram (the diagrams are exactly the same as in the class notes):

Question: what will be the number of rows in the relations ITEM and COMPONENT respectively? All I need finally are the two numbers, but do explain how you arrived at your answer.

Explanation / Answer

PC parts SQL database tables These are the tables inside computer parts SQL database: bus_interfaces computer_case_sizes cooling_fan_sizes drive_bay_widths ethernet_standards expansion_slots hard_drive_features manufacturers memory_types motherboard_form_factors optical_disk_formats optical_drive_features peripheral_interfaces power_supply_standards processor_sockets sound_channel_standards motherboard_nb_chipsets motherboard_sb_chipsets gpus lan_chips sound_chips computer_cases hard_drives memories motherboards optical_drives power_supplies processors processor_cores sound_cards video_adapters l_hard_drives_hard_drive_features l_motherboards_expansion_slots l_motherboard_bus_interfaces l_motherboard_peripheral_interfaces l_optical_drives_optical_disk_formats l_optical_drives_optical_drive_features l_computer_cases_cooling_fans l_computer_cases_drive_bay_widths PC parts SQL database entity-relationship diagram Entity relationship diagram for database consisting of 39 tables is quite complicated and takes a lot of screen space. Please click HERE to view PC parts database ER diagram. PC parts SQL 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 of one combination of 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;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.