CREATE DATABASE IF NOT EXISTS `user217` 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
USE `user217`;

CREATE TABLE `employees` (
  `emp_id`    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `surname`   VARCHAR(50) NOT NULL,
  `room_no`   SMALLINT      NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB;

CREATE TABLE `items` (
  `item_id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB;

CREATE TABLE `issuance` (
  `issuance_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `emp_id`      INT UNSIGNED NOT NULL,
  `item_id`     INT UNSIGNED NOT NULL,
  `issue_date`  DATE          NOT NULL,
  PRIMARY KEY (`issuance_id`),
  FOREIGN KEY (`emp_id`)  REFERENCES `employees`(`emp_id`)  ON DELETE CASCADE,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`item_id`)    ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO `employees` (`surname`, `room_no`) VALUES
  ('Иванов', 101),
  ('Петрова', 202),
  ('Сидоров', 101);

INSERT INTO `items` (`item_name`) VALUES
  ('Ноутбук Lenovo'),
  ('Монитор Samsung'),
  ('Клавиатура Logitech');

INSERT INTO `issuance` (`emp_id`, `item_id`, `issue_date`) VALUES
  (1, 1, '2025-04-01'),
  (2, 2, '2025-04-10'),
  (3, 3, '2025-04-15');