DROP database SP;
CREATE DATABASE SP;
USE SP;

CREATE TABLE supplier(
sno varchar(20) NOT NULL PRIMARY KEY,
sname varchar(30),
city varchar(30),
phone varchar(10),
email varchar(30)
);

CREATE TABLE parts(
pno varchar(20) NOT NULL PRIMARY KEY,
pname varchar(30),
weight int,
color varchar(30)
);

CREATE TABLE sp(
sno varchar(20),
pno varchar(30),
qty int,
PRIMARY KEY (sno, pno),
CONSTRAINT FK_sno FOREIGN KEY (sno) REFERENCES
supplier(sno),
CONSTRAINT FK_pno FOREIGN KEY (pno) REFERENCES
parts(pno)
);


INSERT INTO supplier VALUES('s101', 'saga', 'CHN', '9999999999', 'asd@gmail.com');
INSERT INTO supplier VALUES('s102', 'nipo', 'CHN', '9999999999', 'asd@gmail.com');
INSERT INTO supplier VALUES('s103', 'tipo', 'CHN', '9999999999', 'asd@gmail.com');
INSERT INTO supplier VALUES('s104', 'poiuy', 'BOM', '9999999999', 'asd@gmail.com');
INSERT INTO supplier VALUES('s105', 'werfg', 'BOM', '9999999999', 'asd@gmail.com');
INSERT INTO supplier VALUES('s123', 'wiyy', 'BOM', '9999999999', 'asd@gmail.com');

INSERT INTO parts VALUES('p101', 'bread', 20, 'b');
INSERT INTO parts VALUES('p102', 'butter', 30, 'b');
INSERT INTO parts VALUES('p103', 'jam', 10, 'b');
INSERT INTO parts VALUES('p104', 'milk', 10, 'b');
INSERT INTO parts VALUES('p105', 'tea', 50, 'b');


INSERT INTO sp VALUES('s101', 'p101', 20);
INSERT INTO sp VALUES('s102', 'p102', 30);
INSERT INTO sp VALUES('s103', 'p103', 10);
INSERT INTO sp VALUES('s104', 'p104', 10);
INSERT INTO sp VALUES('s105', 'p105', 50);
INSERT INTO sp VALUES('s123', 'p105', 50);
INSERT INTO sp VALUES('s123', 'p102', 50);
INSERT INTO sp VALUES('s123', 'p103', 50);