Database Normalization

Database saves lots of records and columns which might result in redundancy or duplication of the same record. Redundancy creates a lot of problems such as

• Duplicate records will take up extra space in the database
• Same data will be inserted, updated and deleted multiple times
• Increases inconsistency and unreliability of database.

Normalization is the process of reducing redundancy, efficiently organising and logically storing the data to reduce duplication and increase reusability. Normalization helps to reduce the amount of data and overall database size.

Normalization provides a set of guidelines to create and store data structurally. These guidelines are divided into normal forms also known as NF. Each step has some rules to follow, these steps are First Normal Form or NF1, Second Normal Form NF2, Third Normal Form or NF3 and so on.

let's look at all of them one by one.

Un Normalized Form or UNF

If a table has duplication and redundant data then it's called a Row table or Un-Normalized Form. The values which cannot be further decomposed are known as atomic values and values which can be further divided into smaller sets are called non-atomic values.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , vendor_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (1, "Macintosh", 'Y', 'vendor_A, vendor_B');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (2,"Fuji", 'N', 'vendor_B, vendor_C');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (3,"Gala",  'Y', 'vendor_C, vendor_D');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (4,"Jonagold", 'N', 'vendor_D, vendor_E');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (5,"GrannySmith", 'Y', 'vendor_E, vendor_F');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (6,"PinkLady", 'N', 'vendor_F, vendor_A');

select * from apple;

First Normal Form or NF1

A table is said to be First Normal Form or NF1 if each row can provide a unique combination of values. each column must contain atomic values which cannot be decomposed further.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , vendor_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (1, "Macintosh", 'Y', 'vendor_A, vendor_B');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (2,"Fuji", 'N', 'vendor_B, vendor_C');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (3,"Gala",  'Y', 'vendor_C, vendor_D');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (4,"Jonagold", 'N', 'vendor_D, vendor_E');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (5,"GrannySmith", 'Y', 'vendor_E, vendor_F');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (6,"PinkLady", 'N', 'vendor_F, vendor_A');

select * from apple;

in the above table, vendor_name is a multi-value column, its having multiple vendors' names, hence it's non-atomic and can be further decomposed. to make the table in 1NF, each row must contain only 1 vedor_name.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , vendor_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (1, "Macintosh", 'Y', 'vendor_A');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (2, "Macintosh", 'Y', 'vendor_B');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (3, "Fuji", 'N', 'vendor_B');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (4, "Fuji", 'N', 'vendor_C');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (5, "Gala",  'Y', 'vendor_C');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (6, "Gala",  'Y', 'vendor_D');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (7, "Jonagold", 'N', 'vendor_D');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (8, "Jonagold", 'N', 'vendor_E');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (9, "GrannySmith", 'Y', 'vendor_E');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (10, "GrannySmith", 'Y', 'vendor_F');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (11, "PinkLady", 'N', 'vendor_F');
INSERT INTO apple (apple_id, apple_name, available, vendor_name ) VALUES (12, "PinkLady", 'N', 'vendor_A');

select * from apple;

each row gives a unique value and cannot be decomposed further, hence table is now 1NF compliant.

Second Normal Form or 2NF

A table is said to be in Second Normal Form or 2NF if it's already in 1NF and each attribute is dependent on the primary key of the table. If the table has some attributes which are not dependent on the Primary key then the table is not in 2NF.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , vendor_id INT, vendor_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, vendor_id, vendor_name ) VALUES (1, "Macintosh", 'Y', 10, 'vendor_A');
INSERT INTO apple (apple_id, apple_name, available, vendor_id, vendor_name ) VALUES (2,"Fuji", 'Y', 20, 'vendor_B');
INSERT INTO apple (apple_id, apple_name, available, vendor_id, vendor_name ) VALUES (3,"Gala",  'Y', 30, 'vendor_C');
INSERT INTO apple (apple_id, apple_name, available, vendor_id, vendor_name ) VALUES (4,"Jonagold", 'N', 40, 'vendor_D');
INSERT INTO apple (apple_id, apple_name, available, vendor_id, vendor_name ) VALUES (5,"GrannySmith", 'Y', 50, 'vendor_E');
INSERT INTO apple (apple_id, apple_name, available, vendor_id, vendor_name ) VALUES (6,"PinkLady", 'N', 60, 'vendor_F');

in the above table, the apple_id is the primary key hence apple_name, available, and vendor_id are dependent on apple_id but vendor_name has no relation with apple_id, its related to vendor_id and vendor_id is not a primary key of the table, hence table is not in 2NF.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), available CHAR(1) , vendor_id INT, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, vendor_id ) VALUES (1, "Macintosh", 'Y', 10);
INSERT INTO apple (apple_id, apple_name, available, vendor_id ) VALUES (2,"Fuji", 'Y', 20);
INSERT INTO apple (apple_id, apple_name, available, vendor_id ) VALUES (3,"Gala",  'Y', 30);
INSERT INTO apple (apple_id, apple_name, available, vendor_id ) VALUES (4,"Jonagold", 'N', 40);
INSERT INTO apple (apple_id, apple_name, available, vendor_id ) VALUES (5,"GrannySmith", 'Y', 50);
INSERT INTO apple (apple_id, apple_name, available, vendor_id ) VALUES (6,"PinkLady", 'N', 60);

CREATE TABLE vendor (vendor_id INT, vendor_name VARCHAR(20), PRIMARY KEY (vendor_id));
INSERT INTO vendor (vendor_id, vendor_name) VALUES (10, 'vendor_A');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (20, 'vendor_B');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (30, 'vendor_C');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (40, 'vendor_D');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (50, 'vendor_E');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (60, 'vendor_F');

now both table has their own data dependent on their primary key without redundancy, the data size has been reduced without losing any information and both tables are in relation.

Third Normal Form or 3NF

A table is said to be in the Third Normal Form or 3NF if it's already in 2NF and there is no transitive dependency between the columns.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), vendor_id INT, vendor_price INT, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (1, "Macintosh", 10, 100);
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (2, "Fuji", 20, 200);
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (3, "Gala", 30, 300);
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (4, "Jonagold", 10, 100);
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (5, "GrannySmith", 20, 200);
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (6, "PinkLady", 30, 300);
INSERT INTO apple (apple_id, apple_name, vendor_id, vendor_price ) VALUES (7, "Macintosh", 10, 100);

In the above table, the apple_id is the primary key and all non-key attributes such as apple_name, vendor_id, and vendor_price all are dependent on apple_id but the table is not in 3NF because vendor_id and vendor_price are having a transitive dependency, lets see how.

• a new vendor cannot be inserted into the table unless there is an apple_id corresponding to it.
• if apple_id 6 needs to be deleted then will lose all information about vendor_id 60 and its price.
• to update the price of Macintosh apple, changes are required in more than 1 place.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), vendor_id INT, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (1, "Macintosh", 10);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (2, "Fuji", 20);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (3, "Gala", 30);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (4, "Jonagold", 10);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (5, "GrannySmith", 20);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (6, "PinkLady", 30);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (7, "Macintosh", 10);

CREATE TABLE vendor (vendor_id INT, vendor_price INT, PRIMARY KEY (vendor_id));
INSERT INTO vendor (vendor_id, vendor_price) VALUES (10, 100);
INSERT INTO vendor (vendor_id, vendor_price) VALUES (20, 200);
INSERT INTO vendor (vendor_id, vendor_price) VALUES (30, 300);

Boyce-Code Normal Form or BCNF or 3.5NF

Boyce-Code Normal Form is an advanced version of 3NF hence also known as 3.5NF. A table is said to be in 3.5NF if it follows the below rules.

• the table already satisfies all requirements of 3NF and every column has a relation with the primary key of the table.
• A table should not have more than 1 candidate key in the table.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), vendor_id INT, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, vendor_name ) VALUES (1, "Macintosh", 'A');
INSERT INTO apple (apple_id, apple_name, vendor_name ) VALUES (2, "Fuji", 'B');
INSERT INTO apple (apple_id, apple_name, vendor_name ) VALUES (3, "Gala", 'C');
INSERT INTO apple (apple_id, apple_name, vendor_name ) VALUES (4, "Jonagold", 'A');
INSERT INTO apple (apple_id, apple_name, vendor_name ) VALUES (5, "GrannySmith", 'B');
INSERT INTO apple (apple_id, apple_name, vendor_name ) VALUES (6, "PinkLady", 'C');

in the above table, the apple_id and apple_name makes a primary key or are a candidate for the primary key similar manner, apple_name and vendor_name are other candidates for the primary key. To make the table 3.5NF compliance, decompose the table into 2 separate tables and each will have its own primary key.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), vendor_id INT, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (1, "Macintosh", 10);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (2, "Fuji", 20);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (3, "Gala", 30);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (4, "Jonagold", 10);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (5, "GrannySmith", 20);
INSERT INTO apple (apple_id, apple_name, vendor_id ) VALUES (6, "PinkLady", 30);

CREATE TABLE vendor (vendor_id INT, vendor_name VARCHAR(20), PRIMARY KEY (vendor_id));
INSERT INTO vendor (vendor_id, vendor_name) VALUES (10, 'A');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (20, 'B');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (30, 'C');

Forth Normal Form or 4NF

A table is said to be in Forth Normal Form if it satisfies the following conditions

• the table already satisfies all requirements of the 3NF
• there must be only one multivalue dependency

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name) VALUES (1, "Macintosh");
INSERT INTO apple (apple_id, apple_name) VALUES (2, "Fuji");
INSERT INTO apple (apple_id, apple_name) VALUES (3, "Gala");
INSERT INTO apple (apple_id, apple_name) VALUES (4, "Jonagold");
INSERT INTO apple (apple_id, apple_name) VALUES (5, "GrannySmith");
INSERT INTO apple (apple_id, apple_name) VALUES (6, "PinkLady");

CREATE TABLE vendor (vendor_id INT, vendor_name VARCHAR(20), PRIMARY KEY (vendor_id));
INSERT INTO vendor (vendor_id, vendor_name) VALUES (10, 'vendor_A');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (20, 'vendor_B');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (30, 'vendor_C');

CREATE TABLE market (market_id INT, market_name VARCHAR(20), PRIMARY KEY (market_id));
INSERT INTO market (market_id, market_name) VALUES (100, 'market_X');
INSERT INTO market (market_id, market_name) VALUES (200, 'market_Y');
INSERT INTO market (market_id, market_name) VALUES (300, 'market_Z');

CREATE TABLE vendor_apple_market_mapping (mapping_id INT, apple_id INT, vendor_id INT, market_id INT, PRIMARY KEY (mapping_id));
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (1, 1 ,10  ,100);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (2, 6 ,10  ,100);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (3, 2 ,10  ,300);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (4, 5 ,10  ,300);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (5, 3, 20  ,100);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (6, 1 ,10  ,300);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (7, 3 ,20  ,200);
INSERT INTO vendor_apple_market_mapping (mapping_id, apple_id, vendor_id, market_id) VALUES (8, 4 ,30  ,300);

4NF tries to solve the issues with the multi value table , ie apple_vendor_market_mapping table,

• if a vendor wants to sell an apple but market is not decided yet, then there will be a new row with blank market_id.
• if a vendor wants to sell all 6 apples into all 3 markets, then there will be vendor * apple * market row inserted.
• if a vendor needs to be removed then there will be a lot of deletion required
• if vendor_id needs to be updated, then it will require more then 1 updation.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name) VALUES (1, "Macintosh");
INSERT INTO apple (apple_id, apple_name) VALUES (2, "Fuji");
INSERT INTO apple (apple_id, apple_name) VALUES (3, "Gala");
INSERT INTO apple (apple_id, apple_name) VALUES (4, "Jonagold");
INSERT INTO apple (apple_id, apple_name) VALUES (5, "GrannySmith");
INSERT INTO apple (apple_id, apple_name) VALUES (6, "PinkLady");

CREATE TABLE vendor (vendor_id INT, vendor_name VARCHAR(20), PRIMARY KEY (vendor_id));
INSERT INTO vendor (vendor_id, vendor_name) VALUES (10, 'vendor_A');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (20, 'vendor_B');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (30, 'vendor_C');

CREATE TABLE market (market_id INT, market_name VARCHAR(20), PRIMARY KEY (market_id));
INSERT INTO market (market_id, market_name) VALUES (100, 'market_X');
INSERT INTO market (market_id, market_name) VALUES (200, 'market_Y');
INSERT INTO market (market_id, market_name) VALUES (300, 'market_Z');

CREATE TABLE vendor_apple_mapping (mapping_id INT, apple_id INT, vendor_id INT, PRIMARY KEY (mapping_id));
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (1, 1 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (2, 6 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (3, 2 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (4, 5 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (5, 3, 20);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (8, 4 ,30);

CREATE TABLE vendor_market_mapping (mapping_id INT, vendor_id INT, market_id INT, PRIMARY KEY (mapping_id));
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (1, 10  ,100); 
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (2, 10  ,300); 
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (3, 20  ,100); 
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (4, 30  ,200);
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (5, 30  ,300);

Fifth Normal Form or 5NF

Fifth Normal Form or 5NF also known as Project-Join Normal Form or PJNF. A table is said to be in 5NF if

• its alreyd satisfy all the properties of 4NF
• It should have no join dependency and also the joining must be lossless.

5NF decompose all the sub-relations as much as possible. in 4NF, the vendor_apple_market_mapping table was broke down into vendor_apple_mapping and vendor_market_mapping but in the process, the relation ship between vendor_id and market_id was lost.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id INT, apple_name VARCHAR(20), PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name) VALUES (1, "Macintosh");
INSERT INTO apple (apple_id, apple_name) VALUES (2, "Fuji");
INSERT INTO apple (apple_id, apple_name) VALUES (3, "Gala");
INSERT INTO apple (apple_id, apple_name) VALUES (4, "Jonagold");
INSERT INTO apple (apple_id, apple_name) VALUES (5, "GrannySmith");
INSERT INTO apple (apple_id, apple_name) VALUES (6, "PinkLady");

CREATE TABLE vendor (vendor_id INT, vendor_name VARCHAR(20), PRIMARY KEY (vendor_id));
INSERT INTO vendor (vendor_id, vendor_name) VALUES (10, 'vendor_A');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (20, 'vendor_B');
INSERT INTO vendor (vendor_id, vendor_name) VALUES (30, 'vendor_C');

CREATE TABLE market (market_id INT, market_name VARCHAR(20), PRIMARY KEY (market_id));
INSERT INTO market (market_id, market_name) VALUES (100, 'market_X');
INSERT INTO market (market_id, market_name) VALUES (200, 'market_Y');
INSERT INTO market (market_id, market_name) VALUES (300, 'market_Z');

CREATE TABLE vendor_apple_mapping (mapping_id INT, apple_id INT, vendor_id INT, PRIMARY KEY (mapping_id));
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (1, 1 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (2, 6 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (3, 2 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (4, 5 ,10);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (5, 3, 20);
INSERT INTO vendor_apple_mapping (mapping_id, apple_id, vendor_id) VALUES (6, 4 ,30);

CREATE TABLE vendor_market_mapping (mapping_id INT, vendor_id INT, market_id INT, PRIMARY KEY (mapping_id));
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (1, 10  ,100); 
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (2, 10  ,300); 
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (3, 20  ,100); 
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (4, 30  ,200);
INSERT INTO vendor_market_mapping (mapping_id, vendor_id, market_id) VALUES (5, 30  ,300);

CREATE TABLE apple_market_mapping (mapping_id INT, apple_id INT, market_id INT, PRIMARY KEY (mapping_id));
INSERT INTO apple_market_mapping (mapping_id, apple_id, market_id) VALUES (1, 1  ,100); 
INSERT INTO apple_market_mapping (mapping_id, apple_id, market_id) VALUES (2, 6  ,100); 
INSERT INTO apple_market_mapping (mapping_id, apple_id, market_id) VALUES (3, 2  ,300); 
INSERT INTO apple_market_mapping (mapping_id, apple_id, market_id) VALUES (4, 3  ,100);
INSERT INTO apple_market_mapping (mapping_id, apple_id, market_id) VALUES (5, 3  ,200);
INSERT INTO apple_market_mapping (mapping_id, apple_id, market_id) VALUES (6, 4  ,300);