Operating System Schema

-- -----------------------------------------------------
-- Schema operatingsystem
-- -----------------------------------------------------
CREATE DATABASE operatingsystem;
USE operatingsystem;

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Table `operatingsystem`.`roles_bt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`roles_bt` (
  `role_id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`role_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`groups` (
  `role_id` INT NOT NULL,
  `label` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`role_id`),
  CONSTRAINT `fk_groups_roles_bt1`
    FOREIGN KEY (`role_id`)
    REFERENCES `operatingsystem`.`roles_bt` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`storage_objects_bt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`storage_objects_bt` (
  `storage_object_id` INT NOT NULL AUTO_INCREMENT,
  `partition` INT NOT NULL,
  `memory_allocation` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `parent_dir_id` INT NOT NULL,
  PRIMARY KEY (`storage_object_id`),
  INDEX `fk_storage_objects_bt_directories1_idx` (`parent_dir_id` ASC) VISIBLE,
  CONSTRAINT `fk_storage_objects_bt_directories1`
    FOREIGN KEY (`parent_dir_id`)
    REFERENCES `operatingsystem`.`directories` (`storage_object_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`directories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`directories` (
  `storage_object_id` INT NOT NULL,
  PRIMARY KEY (`storage_object_id`),
  CONSTRAINT `fk_directories_storage_objects_bt1`
    FOREIGN KEY (`storage_object_id`)
    REFERENCES `operatingsystem`.`storage_objects_bt` (`storage_object_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`users_bt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`users_bt` (
  `role_id` INT NOT NULL,
  `user_name` VARCHAR(30) NOT NULL,
  `psw` VARCHAR(255) NOT NULL,
  `main_group_id` INT NOT NULL,
  `home_directories_id` INT NOT NULL,
  PRIMARY KEY (`role_id`),
  INDEX `fk_users_groups1_idx` (`main_group_id` ASC) VISIBLE,
  INDEX `fk_users_bt_directories1_idx` (`home_directories_id` ASC) VISIBLE,
  CONSTRAINT `fk_users_roles`
    FOREIGN KEY (`role_id`)
    REFERENCES `operatingsystem`.`roles_bt` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_groups1`
    FOREIGN KEY (`main_group_id`)
    REFERENCES `operatingsystem`.`groups` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_bt_directories1`
    FOREIGN KEY (`home_directories_id`)
    REFERENCES `operatingsystem`.`directories` (`storage_object_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`groups_has_users_jt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`groups_has_users_jt` (
  `groupe_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`groupe_id`, `user_id`),
  INDEX `fk_groups_has_users_users1_idx` (`user_id` ASC) VISIBLE,
  INDEX `fk_groups_has_users_groups1_idx` (`groupe_id` ASC) VISIBLE,
  CONSTRAINT `fk_groups_has_users_groups1`
    FOREIGN KEY (`groupe_id`)
    REFERENCES `operatingsystem`.`groups` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_groups_has_users_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `operatingsystem`.`users_bt` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`admins`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`admins` (
  `admin_id` INT NOT NULL,
  PRIMARY KEY (`admin_id`),
  CONSTRAINT `fk_admins_users1`
    FOREIGN KEY (`admin_id`)
    REFERENCES `operatingsystem`.`users_bt` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`files` (
  `storage_object_id` INT NOT NULL,
  PRIMARY KEY (`storage_object_id`),
  CONSTRAINT `fk_files_storage_objects_bt1`
    FOREIGN KEY (`storage_object_id`)
    REFERENCES `operatingsystem`.`storage_objects_bt` (`storage_object_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`permissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`permissions` (
  `permission_code` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`permission_code`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `operatingsystem`.`allocations_jt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operatingsystem`.`allocations_jt` (
  `permission_code` VARCHAR(10) NOT NULL,
  `storage_object_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  `created_at` DATE NOT NULL,
  `expired_at` DATE NOT NULL,
  `admin_id` INT NOT NULL,
  PRIMARY KEY (`permission_code`, `storage_object_id`, `role_id`, `admin_id`, `created_at`),
  INDEX `fk_allocations_jt_storage_objects_bt1_idx` (`storage_object_id` ASC) VISIBLE,
  INDEX `fk_allocations_jt_roles_bt1_idx` (`role_id` ASC) VISIBLE,
  INDEX `fk_allocations_jt_admins1_idx` (`admin_id` ASC) VISIBLE,
  CONSTRAINT `fk_allocations_permissions1`
    FOREIGN KEY (`permission_code`)
    REFERENCES `operatingsystem`.`permissions` (`permission_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_allocations_jt_storage_objects_bt1`
    FOREIGN KEY (`storage_object_id`)
    REFERENCES `operatingsystem`.`storage_objects_bt` (`storage_object_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_allocations_jt_roles_bt1`
    FOREIGN KEY (`role_id`)
    REFERENCES `operatingsystem`.`roles_bt` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_allocations_jt_admins1`
    FOREIGN KEY (`admin_id`)
    REFERENCES `operatingsystem`.`admins` (`admin_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;





-- ------------------------------
-- DATA
-- ------------------------------

INSERT INTO roles_bt (role_id) VALUES
(1), (2), (3), (4), (5), (6), (7), (8);

-- 2) Groups
INSERT INTO `groups` (role_id, label, description) VALUES
(1, 'root', 'System administrators group'),
(2, 'students', 'Group for all student accounts'),
(3, 'teachers', 'Group for teaching staff'),
(4, 'guests', 'Temporary guest accounts');

-- 3) Users
INSERT INTO users_bt (role_id, user_name, psw, main_group_id, home_directories_id) VALUES
(5, 'alice', 'pass123', 2, 101),
(6, 'bob', 'hunter2', 2, 102),
(7, 'charlie', 'letmein', 3, 103),
(8, 'david', 'adminpw', 1, 104);

-- 4) Groups-Users memberships (secondary groups)
INSERT INTO groups_has_users_jt (groupe_id, user_id) VALUES
(2, 5),  -- alice in students
(2, 6),  -- bob in students
(3, 7),  -- charlie in teachers
(1, 8),  -- david in root
(3, 5);  -- alice also in teachers (special)

-- 5) Admins
INSERT INTO admins (admin_id) VALUES
(8); -- david is admin

-- 6) Storage Objects (files + directories)
-- Root directories
INSERT INTO storage_objects_bt (storage_object_id, `partition`, memory_allocation, name, parent_dir_id) VALUES
(100, 1, '4KB', '/', 100),
(200, 1, '4KB', '/home', 100),
(300, 1, '4KB', '/etc', 100),
(400, 1, '4KB', '/var', 100),
(500, 1, '4KB', '/tmp', 100),
(600, 1, '4KB', '/usr', 100),

-- Home directories for users
(201, 1, '4KB', '/home/alice', 200),
(202, 1, '4KB', '/home/bob', 200),
(203, 1, '4KB', '/home/charlie', 200),
(204, 1, '4KB', '/home/david', 200),
(205, 1, '4KB', '/home/erin', 200),
(206, 1, '4KB', '/home/frank', 200),
(207, 1, '4KB', '/home/grace', 200),
(208, 1, '4KB', '/home/henry', 200),

-- Alice subdirs
(210, 1, '4KB', 'docs', 201),
(211, 1, '4KB', 'projects', 201),
(212, 1, '4KB', 'photos', 201),
(213, 1, '4KB', 'music', 201),
(214, 1, '4KB', 'games', 201),

-- Alice files
(220, 1, '2KB', 'notes.txt', 210),
(221, 1, '8KB', 'resume.pdf', 210),
(222, 1, '12KB', 'paper.docx', 210),
(223, 1, '1KB', 'todo.md', 210),
(224, 1, '16KB', 'game.c', 211),
(225, 1, '10KB', 'app.java', 211),
(226, 1, '4KB', 'project1.py', 211),
(227, 1, '3KB', 'script.sh', 211),
(228, 1, '20KB', 'holiday1.jpg', 212),
(229, 1, '25KB', 'holiday2.png', 212),
(230, 1, '30KB', 'family.png', 212),
(231, 1, '15KB', 'song1.mp3', 213),
(232, 1, '18KB', 'song2.mp3', 213),
(233, 1, '5KB', 'savegame1.dat', 214),
(234, 1, '6KB', 'savegame2.dat', 214),

-- Bob subdirs
(240, 1, '4KB', 'docs', 202),
(241, 1, '4KB', 'projects', 202),
(242, 1, '4KB', 'music', 202),
(243, 1, '4KB', 'downloads', 202),

-- Bob files
(250, 1, '3KB', 'cv.pdf', 240),
(251, 1, '7KB', 'essay.docx', 240),
(252, 1, '2KB', 'list.txt', 240),
(253, 1, '14KB', 'game2.c', 241),
(254, 1, '9KB', 'assignment.py', 241),
(255, 1, '5KB', 'experiment.cpp', 241),
(256, 1, '12KB', 'track1.mp3', 242),
(257, 1, '13KB', 'track2.mp3', 242),
(258, 1, '20KB', 'installer.exe', 243),
(259, 1, '50KB', 'ebook.pdf', 243),

-- Charlie subdirs
(260, 1, '4KB', 'lectures', 203),
(261, 1, '4KB', 'research', 203),
(262, 1, '4KB', 'assignments', 203),

-- Charlie files
(270, 1, '30KB', 'lecture1.ppt', 260),
(271, 1, '32KB', 'lecture2.ppt', 260),
(272, 1, '28KB', 'paper1.pdf', 261),
(273, 1, '35KB', 'paper2.pdf', 261),
(274, 1, '10KB', 'assignment1.docx', 262),
(275, 1, '15KB', 'assignment2.docx', 262),

-- David
(280, 1, '4KB', 'admin_docs', 204),
(281, 1, '4KB', 'scripts', 204),
(282, 1, '10KB', 'policy.pdf', 280),
(283, 1, '2KB', 'backup.sh', 281),
(284, 1, '1KB', 'monitor.sh', 281),

-- System directories (/etc)
(310, 1, '4KB', 'network', 300),
(311, 1, '4KB', 'security', 300),
(312, 1, '4KB', 'init.d', 300),

-- System files
(320, 1, '1KB', 'passwd', 300),
(321, 1, '2KB', 'hosts', 300),
(322, 1, '3KB', 'fstab', 300),
(323, 1, '5KB', 'interfaces', 310),
(324, 1, '6KB', 'firewall.conf', 311),
(325, 1, '4KB', 'startup.sh', 312),

-- Var
(330, 1, '4KB', 'log', 400),
(331, 1, '4KB', 'cache', 400),
(332, 1, '4KB', 'lib', 400),
(340, 1, '12KB', 'syslog', 330),
(341, 1, '14KB', 'kern.log', 330),
(342, 1, '8KB', 'error.log', 330),
(343, 1, '22KB', 'pagecache.bin', 331),

-- Tmp
(350, 1, '2KB', 'tmpfile1', 500),
(351, 1, '2KB', 'tmpfile2', 500),
(352, 1, '1KB', 'tmpfile3', 500),

-- /usr structure
(610, 1, '4KB', 'bin', 600),
(611, 1, '4KB', 'lib', 600),
(612, 1, '4KB', 'share', 600),

-- /usr/bin files
(620, 1, '7KB', 'ls', 610),
(621, 1, '9KB', 'cat', 610),
(622, 1, '15KB', 'grep', 610),
(623, 1, '13KB', 'awk', 610),
(624, 1, '20KB', 'sed', 610),
(625, 1, '18KB', 'python3', 610),
(626, 1, '25KB', 'java', 610),

-- /usr/lib
(630, 1, '50KB', 'libc.so', 611),
(631, 1, '40KB', 'libm.so', 611),
(632, 1, '60KB', 'libssl.so', 611),
(633, 1, '35KB', 'libcrypto.so', 611),

-- /usr/share
(640, 1, '4KB', 'man', 612),
(641, 1, '4KB', 'doc', 612),
(642, 1, '10KB', 'README', 612),
(650, 1, '2KB', 'man1', 640),
(651, 1, '2KB', 'man2', 640),
(652, 1, '5KB', 'ls.1', 650),
(653, 1, '6KB', 'cat.1', 650),
(654, 1, '8KB', 'grep.1', 650),
(655, 1, '3KB', 'doc1.txt', 641),
(656, 1, '4KB', 'doc2.txt', 641);


-- Directories (mark all directory storage_objects)
INSERT INTO directories (storage_object_id) VALUES
(100), (200), (300), (400), (500), (600),
(201), (202), (203), (204), (205), (206), (207), (208),
(210), (211), (212), (213), (214),
(240), (241), (242), (243),
(260), (261), (262),
(280), (281),
(310), (311), (312),
(330), (331), (332),
(610), (611), (612),
(640), (641), (650), (651);

-- Files (mark all file storage_objects)
INSERT INTO files (storage_object_id) VALUES
(220), (221), (222), (223), (224), (225), (226), (227),
(228), (229), (230), (231), (232), (233), (234),
(250), (251), (252), (253), (254), (255), (256), (257),
(258), (259),
(270), (271), (272), (273), (274), (275),
(282), (283), (284),
(320), (321), (322), (323), (324), (325),
(340), (341), (342), (343),
(350), (351), (352),
(620), (621), (622), (623), (624), (625), (626),
(630), (631), (632), (633),
(642), (652), (653), (654), (655), (656);

-- 9) Permissions
INSERT INTO permissions (permission_code) VALUES
('r'), ('w'), ('x');

-- 10) Allocations (permissions given by admin david)
INSERT INTO allocations_jt (permission_code, storage_object_id, role_id, created_at, expired_at, admin_id) VALUES
-- Alice (role_id 5)
('r', 220, 5, '2025-01-01', '2025-12-31', 8), -- notes.txt
('w', 220, 5, '2025-01-01', '2025-12-31', 8),
('r', 224, 5, '2025-01-01', '2025-12-31', 8), -- game.c
('x', 224, 5, '2025-01-01', '2025-12-31', 8),
('r', 228, 5, '2025-01-01', '2025-12-31', 8), -- holiday1.jpg
('r', 231, 5, '2025-01-01', '2025-12-31', 8), -- song1.mp3
('r', 233, 5, '2025-01-01', '2025-12-31', 8), -- savegame1.dat
('w', 233, 5, '2025-01-01', '2025-12-31', 8),

-- Bob (role_id 6)
('r', 250, 6, '2025-01-01', '2025-12-31', 8), -- cv.pdf
('w', 250, 6, '2025-01-01', '2025-12-31', 8),
('r', 253, 6, '2025-01-01', '2025-12-31', 8), -- game2.c
('x', 253, 6, '2025-01-01', '2025-12-31', 8),
('r', 256, 6, '2025-01-01', '2025-12-31', 8), -- track1.mp3
('r', 259, 6, '2025-01-01', '2025-12-31', 8), -- ebook.pdf

-- Charlie (role_id 7)
('r', 270, 7, '2025-01-01', '2025-12-31', 8), -- lecture1.ppt
('r', 271, 7, '2025-01-01', '2025-12-31', 8),
('w', 274, 7, '2025-01-01', '2025-12-31', 8), -- assignment1.docx
('r', 272, 7, '2025-01-01', '2025-12-31', 8), -- paper1.pdf
('r', 273, 7, '2025-01-01', '2025-12-31', 8), -- paper2.pdf

-- David (admin but also user, role_id 8)
('r', 282, 8, '2025-01-01', '2025-12-31', 8), -- policy.pdf
('w', 283, 8, '2025-01-01', '2025-12-31', 8), -- backup.sh
('x', 284, 8, '2025-01-01', '2025-12-31', 8), -- monitor.sh

-- System allocations to groups
('r', 320, 1, '2025-01-01', '2025-12-31', 8), -- root -> /etc/passwd
('r', 321, 1, '2025-01-01', '2025-12-31', 8), -- root -> /etc/hosts
('r', 340, 1, '2025-01-01', '2025-12-31', 8), -- root -> syslog

-- Teachers group (role_id 3)
('r', 272, 3, '2025-01-01', '2025-12-31', 8), -- teachers -> paper1.pdf
('r', 273, 3, '2025-01-01', '2025-12-31', 8), -- teachers -> paper2.pdf
('r', 270, 3, '2025-01-01', '2025-12-31', 8), -- teachers -> lecture1.ppt

-- Students group (role_id 2)
('r', 221, 2, '2025-01-01', '2025-12-31', 8), -- students -> resume.pdf
('r', 222, 2, '2025-01-01', '2025-12-31', 8), -- students -> paper.docx
('r', 254, 2, '2025-01-01', '2025-12-31', 8), -- students -> assignment.py
('r', 255, 2, '2025-01-01', '2025-12-31', 8), -- students -> experiment.cpp

-- Executables in /usr/bin
('x', 620, 2, '2025-01-01', '2025-12-31', 8), -- students -> ls
('x', 621, 2, '2025-01-01', '2025-12-31', 8), -- students -> cat
('x', 622, 2, '2025-01-01', '2025-12-31', 8), -- students -> grep
('x', 625, 2, '2025-01-01', '2025-12-31', 8), -- students -> python3
('x', 626, 2, '2025-01-01', '2025-12-31', 8); -- students -> java

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;