Manufacturing Defect Dataset

• defects.product_id →references products.product_idmany to one: each Defects row points to one Products row
• defect_images.defect_id →references defects.defect_idmany to one: each Defect images row points to one Defects row

1. Can a machine learning model predict defect severity based on product features?

   Consider joining products and defects tables and analyzing the relationship between material_type, manufacturing_line and severity.

   tables: products, defects

2. What is the distribution of defect types across different manufacturing lines?

   Join products and defects, then group by manufacturing_line and defect_type to count occurrences.

   tables: products, defects

3. How does the frequency of defects change over time for specific product batches?

   Join products and defects, filter by a specific product_id or product_name, and group by date (extracted from timestamp) to count defects.

   tables: products, defects

4. Identify the top 3 most common defect types and their average severity.

   Group defects by defect_type, count occurrences, and analyze the distribution of severity for each type.

   tables: defects

Table names match the SQLite file and the SQL script.

SELECT defect_type, COUNT(*) as defect_count FROM defects GROUP BY defect_type ORDER BY defect_count DESC LIMIT 5;

SELECT p.manufacturing_line, d.severity, COUNT(d.defect_id) as defect_count FROM defects d JOIN products p ON d.product_id = p.product_id GROUP BY p.manufacturing_line, d.severity ORDER BY p.manufacturing_line, defect_count DESC LIMIT 20;

SELECT d.defect_type, AVG(di.resolution_mp) as avg_resolution FROM defects d JOIN defect_images di ON d.defect_id = di.defect_id GROUP BY d.defect_type ORDER BY avg_resolution DESC LIMIT 20;

SELECT STRFTIME('%Y-%m', timestamp) as month, COUNT(defect_id) as defect_count FROM defects GROUP BY month ORDER BY month LIMIT 20;

SELECT p.material_type, d.defect_type, d.severity FROM defects d JOIN products p ON d.product_id = p.product_id WHERE d.severity = 'Critical' LIMIT 20;

Excel

Load each table into a separate Excel sheet. Use Power Query to join 'defects' and 'products' tables on product_id. Create a PivotTable from the joined data to analyze defect counts by manufacturing line and material type.

Power BI

Load all tables. Create relationships: defects.product_id to products.product_id, and defect_images.defect_id to defects.defect_id. Create measures for Total Defects (COUNT(defects[defect_id])) and Average Severity Score (e.g., mapping Minor=1, Moderate=2, Critical=3).

SQL

Load the SQLite or SQL dump into your preferred SQL environment. The primary join paths are defects.product_id -> products.product_id and defect_images.defect_id -> defects.defect_id. Use foreign keys to link related data.

Python

Use pandas to load CSV or SQLite data. Join tables using pd.merge(). Analyze defect distributions using value_counts() and group by operations. For ML, use libraries like scikit-learn to build predictive models.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file