--
-- Product-Laptop-PC-Printer Database 
-- CS 4750: Database Systems

-- -----------------------------------------------------------
-- Table: Product

CREATE TABLE `Product` (
  `maker` char(30) NOT NULL,
  `model` char(10) NOT NULL,
  `type` char(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `Product`
  ADD PRIMARY KEY (`maker`,`model`);
COMMIT;

INSERT INTO `Product` (`maker`, `model`, `type`) VALUES
('AAA', '1001', 'pc'),
('AAA', '1002', 'pc'),
('AAA', '1003', 'pc'),
('AAA', '2004', 'laptop'),
('AAA', '2005', 'laptop'),
('AAA', '2006', 'laptop'),
('BBB', '1004', 'pc'),
('BBB', '1005', 'pc'),
('BBB', '1006', 'pc'),
('BBB', '2006', 'laptop'),
('CCC', '1007', 'pc'),
('DDD', '1007', 'pc'),
('DDD', '1008', 'pc'),
('DDD', '1009', 'pc'),
('DDD', '3004', 'printer'),
('DDD', '3005', 'printer'),
('EEE', '1008', 'pc'),
('EEE', '2001', 'laptop'),
('EEE', '2002', 'laptop'),
('EEE', '2003', 'laptop'),
('EEE', '3001', 'Printer'),
('EEE', '3002', 'Printer'),
('FFF', '3007', 'Printer'),
('GGG', '2004', 'laptop'),
('GGG', '3006', 'Printer'),
('HHH', '2005', 'laptop'),
('HHH', '2006', 'laptop');

-- -----------------------------------------------------------
-- Table: _Laptop

CREATE TABLE `Laptop` (
  `model` char(30) NOT NULL,
  `speed` decimal(4,2) DEFAULT NULL,
  `ram` int(11) DEFAULT NULL,
  `hd` int(11) DEFAULT NULL,
  `screen` decimal(3,1) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `Laptop`
  ADD PRIMARY KEY (`model`);
COMMIT;


INSERT INTO `Laptop` (`model`, `speed`, `ram`, `hd`, `screen`, `price`) VALUES
('2001', '2.00', 2048, 240, '20.1', '3673.00'),
('2002', '1.73', 1024, 80, '17.0', '949.00'),
('2003', '1.80', 512, 60, '15.4', '549.00'),
('2004', '2.00', 512, 60, '13.3', '1150.00'),
('2005', '2.16', 1024, 120, '17.0', '2500.00'),
('2006', '2.00', 2048, 80, '15.4', '1700.00');

-- -----------------------------------------------------------
-- Table: PC

CREATE TABLE `PC` (
  `model` char(30) NOT NULL,
  `speed` decimal(4,2) DEFAULT NULL,
  `ram` int(11) DEFAULT NULL,
  `hd` int(11) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `PC`
  ADD PRIMARY KEY (`model`);
COMMIT;

INSERT INTO `PC` (`model`, `speed`, `ram`, `hd`, `price`) VALUES
('1001', '2.66', 1024, 250, '2114.00'),
('1002', '2.10', 512, 250, '995.00'),
('1003', '1.42', 512, 80, '478.00'),
('1004', '2.80', 1024, 250, '649.00'),
('1005', '3.20', 512, 250, '630.00'),
('1006', '3.20', 1024, 320, '1049.00'),
('1007', '2.20', 1024, 200, '510.00'),
('1008', '2.20', 2048, 250, '770.00'),
('1009', '2.00', 1024, 250, '650.00'),
('1010', '2.80', 2048, 300, '770.00'),
('1011', '1.86', 2048, 160, '956.00'),
('1012', '2.80', 1024, 160, '649.00'),
('1013', '3.06', 512, 80, '529.00');

-- -----------------------------------------------------------
-- Table: Printer

CREATE TABLE `Printer` (
  `model` char(30) NOT NULL,
  `color` tinyint(1) DEFAULT NULL,
  `type` char(10) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `Printer`
  ADD PRIMARY KEY (`model`);
COMMIT;

INSERT INTO `Printer` (`model`, `color`, `type`, `price`) VALUES
('3001', 1, 'ink-jet', '99.00'),
('3002', 0, 'laser', '239.00'),
('3003', 1, 'laser', '899.00'),
('3004', 1, 'ink-jet', '120.00'),
('3005', 0, 'laser', '120.00'),
('3006', 1, 'ink-jet', '100.00'),
('3007', 1, 'laser', '200.00');

