Write queries to analyze data normalization levels and demonstrate converting an unnormalized transactional table into Third Normal Form (3NF).
Use the provided unnormalized_transactions relation and create a 3NF representation with separate customer, product, and sales relations. Also report whether the source satisfies 1NF, 2NF, and 3NF.
normalization_level, status, source_row_count, and normalized_relation_count.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction identifier |
| customer_id | INTEGER | Customer identifier repeated in the source |
| customer_name | VARCHAR(100) | Customer name |
| customer_email | VARCHAR(255) | Customer email address |
| customer_address | VARCHAR(255) | Customer shipping address |
| product_id | INTEGER | Product identifier repeated in the source |
| product_name | VARCHAR(150) | Product name |
| product_category | VARCHAR(100) | Product category |
| transaction_date | DATE | Date of purchase |
| quantity | INTEGER | Quantity purchased |
| unit_price | NUMERIC(12,2) | Price per unit at transaction time |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INTEGER | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| customer_email | VARCHAR(255) | Customer email address |
| customer_address | VARCHAR(255) | Customer shipping address |
| Column | Type | Description |
|---|---|---|
| product_idPK | INTEGER | Unique product identifier |
| product_name | VARCHAR(150) | Product name |
| product_category | VARCHAR(100) | Product category |
| standard_price | NUMERIC(12,2) | Product standard price |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction identifier |
| customer_id | INTEGER | Referenced customer |
| product_id | INTEGER | Referenced product |
| transaction_date | DATE | Date of purchase |
| quantity | INTEGER | Quantity purchased |
| unit_price | NUMERIC(12,2) | Price per unit at transaction time |