CREATE TABLE `employee-mgmt`.`employee` (`id` INT(20) NOT NULL , `name` VARCHAR(50) NOT NULL , `dept_id` INT(20) NOT NULL , `address` VARCHAR(50) NOT NULL , `telephone` INT(20) NOT NULL , `dateofbirth` DATE NOT NULL , `role` VARCHAR(50) NOT NULL , `dateofjoining` DATE NOT NULL , `gender` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;


CREATE TABLE `employee-mgmt`.`department` (`id` INT(20) NOT NULL , `name` VARCHAR(50) NOT NULL , `no_of_staff` INT(10) NOT NULL , `building_name` VARCHAR(50) NOT NULL , `no_of_rooms` INT(10) NOT NULL , `no_of_computing_labs` INT(10) NOT NULL , `no_of_lecture_hall` INT(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

CREATE TABLE `employee-mgmt`.`login` (`id` INT(20) NOT NULL , `password` VARCHAR(50) NOT NULL , `role` VARCHAR(50) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

ALTER TABLE login ADD FOREIGN KEY (id) REFERENCES employee(id);

ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department(id);


Insert Into department

INSERT INTO `department` (`id`, `name`, `no_of_staff`, `building_name`, `no_of_rooms`, `no_of_computing_labs`, `no_of_lecture_hall`) VALUES ('1', 'computing', '10', 'victoria', '10', '2', '3'), ('2', 'healthcare', '5', 'victoria', '5', '0', '2');

Insert into Employee

INSERT INTO `employee` (`id`, `name`, `dept_id`, `address`, `telephone`, `dateofbirth`, `role`, `dateofjoining`, `gender`) VALUES ('1234', 'sadhana', '1', 'birmingham', '123456789', '1990-01-01', 'staff', '2023-09-25', 'female'), ('1235', 'arya', '2', 'coventry', '123456788', '1989-01-01', 'admin', '2024-02-01', 'male');

Insert into login

INSERT INTO `login` (`id`, `password`, `role`) VALUES ('1235', '123456', 'admin'), ('1234', '1234', 'staff');


