SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `J` (
  `j_num` varchar(15) NOT NULL,
  `jname` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `J` (`j_num`, `jname`, `city`) VALUES
('j1', 'Sorter', 'Paris'),
('j2', 'Punch', 'Rome'),
('j3', 'Reader', 'Athens'),
('j4', 'Console', 'Athens'),
('j5', 'Collator', 'London'),
('j6', 'Terminal', 'Oslo'),
('j7', 'Tape', 'London');

CREATE TABLE `P` (
  `p_num` varchar(45) NOT NULL,
  `pname` varchar(45) DEFAULT NULL,
  `color` varchar(45) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `P` (`p_num`, `pname`, `color`, `weight`, `city`) VALUES
('p1', 'Nut', 'Red', 12, 'London'),
('p2', 'Bolt', 'Green', 17, 'Paris'),
('p3', 'Screw', 'Blue', 17, 'Rome'),
('p4', 'Screw', 'Red', 14, 'London'),
('p5', 'Cam', 'Blue', 12, 'Paris'),
('p6', 'Cog', 'Red', 19, 'London');

CREATE TABLE `S` (
  `s_num` varchar(45) NOT NULL,
  `s_name` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `S` (`s_num`, `s_name`, `status`, `city`) VALUES
('s1', 'Smith', '20', 'Athens'),
('s2', 'Jones', '10', 'Paris'),
('s3', 'Blake', '30', 'Paris'),
('s4', 'Clark', '20', 'London'),
('s5', 'Adams', '30', 'Athens');

CREATE TABLE `SPJ` (
  `s_num` varchar(45) DEFAULT NULL,
  `p_num` varchar(45) DEFAULT NULL,
  `j_num` varchar(45) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `SPJ` (`s_num`, `p_num`, `j_num`, `qty`) VALUES
('s1', 'p1', 'j1', 200),
('s1', 'p1', 'j4', 700),
('s2', 'p3', 'j1', 400),
('s2', 'p3', 'j2', 200),
('s2', 'p3', 'j3', 200),
('s2', 'p3', 'j4', 500),
('s2', 'p3', 'j5', 600),
('s2', 'p3', 'j6', 400),
('s2', 'p3', 'j7', 800),
('s2', 'p5', 'j2', 100),
('s3', 'p3', 'j1', 200),
('s3', 'p4', 'j2', 500),
('s4', 'p6', 'j3', 300),
('s4', 'p6', 'j7', 300),
('s5', 'p1', 'j4', 100),
('s5', 'p2', 'j2', 200),
('s5', 'p2', 'j4', 100),
('s5', 'p3', 'j4', 200),
('s5', 'p4', 'j4', 800),
('s5', 'p5', 'j4', 400),
('s5', 'p5', 'j5', 500),
('s5', 'p5', 'j7', 100),
('s5', 'p6', 'j2', 200),
('s5', 'p6', 'j4', 500);


ALTER TABLE `J`
  ADD PRIMARY KEY (`j_num`);

ALTER TABLE `P`
  ADD PRIMARY KEY (`p_num`);

ALTER TABLE `S`
  ADD PRIMARY KEY (`s_num`);

ALTER TABLE `SPJ`
  ADD KEY `snum_idx` (`s_num`),
  ADD KEY `pnum_idx` (`p_num`),
  ADD KEY `jnum_idx` (`j_num`);


ALTER TABLE `SPJ`
  ADD CONSTRAINT `jnum` FOREIGN KEY (`j_num`) REFERENCES `J` (`j_num`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `pnum` FOREIGN KEY (`p_num`) REFERENCES `P` (`p_num`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `snum` FOREIGN KEY (`s_num`) REFERENCES `S` (`s_num`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;