Convert an address table to 3NF

Convert an address table to 3NF

Medium SQL Normalization 16 views
Explanation Complexity

Problem Statement

CustomerProfile(customer_id, name, city, state, state_code). state_code depends on state. Show 3NF tables.

Input Format

SQL DDL

Output Format

DDL statement(s)

Example

CustomerProfile
Decomposition

Constraints

Remove transitive dependency

Input / Output Format

Input Format
SQL DDL
Output Format
DDL statement(s)
Constraints
Remove transitive dependency

Examples

Input:
CustomerProfile
Output:
Decomposition

Example Solution (Public)

SQL 
CREATE TABLE States (state VARCHAR(100) PRIMARY KEY, state_code VARCHAR(10) NOT NULL); CREATE TABLE Customers (customer_id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100), state VARCHAR(100), FOREIGN KEY (state) REFERENCES States(state));

Official Solution Code

CREATE TABLE States (state VARCHAR(100) PRIMARY KEY, state_code VARCHAR(10) NOT NULL); CREATE TABLE Customers (customer_id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100), state VARCHAR(100), FOREIGN KEY (state) REFERENCES States(state));
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.