DROP DATABASE IF EXISTS `university`;

CREATE DATABASE `university`;

CREATE TABLE `university`.`university` (
	`id` TINYINT NOT NULL  AUTO_INCREMENT ,
	`name` VARCHAR( 255 ) NOT NULL ,
	`founded` DATE NULL DEFAULT NULL ,
	PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

CREATE TABLE `university`.`faculties` (
	`id` TINYINT NOT NULL  AUTO_INCREMENT ,
	`name` VARCHAR( 255 ) NOT NULL ,
	`univ_id` TINYINT NOT NULL ,
	`dekan_id` INT NULL DEFAULT NULL ,
	PRIMARY KEY ( `id` ) ,
	FOREIGN KEY ( `univ_id` )
		REFERENCES `university`.`university`( `id` )
		ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `university`.`professors` (
	`id` INT NOT NULL AUTO_INCREMENT ,
	`firstname` TINYTEXT NOT NULL ,
	`middlename` TINYTEXT NULL DEFAULT NULL ,
	`lastname` TINYTEXT NOT NULL ,
	`phone` VARCHAR( 32 ) NULL DEFAULT NULL ,
	`address` TEXT NULL DEFAULT NULL ,
	`EGN` BIGINT( 10 ) NULL ,
	`faculty_id` TINYINT NOT NULL,
	PRIMARY KEY ( `id` ),
	FOREIGN KEY ( `faculty_id` )
		REFERENCES `university`.`faculties`( `id` )
		ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `university`.`subjects` (
	`id` TINYINT NOT NULL  AUTO_INCREMENT ,
	`name` VARCHAR( 255 ) NOT NULL ,
	`horarium_hr` SMALLINT NOT NULL ,
	`lead_professor_id` INT NOT NULL ,
	PRIMARY KEY ( `id` ) ,
	FOREIGN KEY ( `lead_professor_id` )
		REFERENCES `university`.`professors`( `id` )
		ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `university`.`students` (
	`firstname` TINYTEXT NOT NULL ,
	`middlename` TINYTEXT NULL DEFAULT NULL ,
	`lastname` TINYTEXT NOT NULL ,
	`phone` VARCHAR( 32 ) NULL DEFAULT NULL ,
	`address` TEXT NULL DEFAULT NULL ,
	`faknum` BIGINT( 9 ) NOT NULL ,
	`sr_uspeh` FLOAT( 3,2 ) NULL DEFAULT NULL,
	PRIMARY KEY ( `faknum` )
) ENGINE = InnoDB;

CREATE TABLE `university`.`zapisani_studenti` (
	`student_fnum` BIGINT( 9 ) NOT NULL ,
	`subject_id` TINYINT NOT NULL ,
	FOREIGN KEY ( `student_fnum` )
		REFERENCES `university`.`students`( `faknum` )
		ON DELETE CASCADE,
	FOREIGN KEY ( `subject_id` )
		REFERENCES `university`.`subjects`( `id` )
		ON DELETE CASCADE,
	PRIMARY KEY (`student_fnum`, `subject_id`)
) ENGINE = InnoDB;

CREATE TABLE `university`.`zadochnici` (
	`student_fnum` BIGINT( 12 ) NOT NULL ,
	`firma` VARCHAR( 255 ) NULL DEFAULT NULL ,
	UNIQUE( `student_fnum` ),
	FOREIGN KEY ( `student_fnum` )
		REFERENCES `university`.`students`( `faknum` )
		ON DELETE CASCADE
) ENGINE = InnoDB;



INSERT INTO `university`.`university` (`id` , `name` , `founded`)
VALUES ( NULL , 'Sofia University', '1889-01-29' );

INSERT INTO `university`.`university` (`id` , `name` , `founded`)
VALUES ( NULL , 'Technical University', '1941-06-12' );

INSERT INTO `university`.`faculties` (`id` ,`name` ,`univ_id` ,`dekan_id`)
VALUES 	( NULL , 'Avtomatika', '2', NULL ),
	( NULL , 'Elektronna Tehnika i Tehnologii', '2', NULL ),
	( NULL , 'Elektrotehnicheski', '2', NULL ),
	( NULL , 'Energo-mashinostroitelen', '2', NULL ),
	( NULL , 'Kompiutarni sistemi i upravlenie', '2', NULL ),
	( NULL , 'Mashino-tehnologichen', '2', NULL );

INSERT INTO `university`.`faculties` (`id` ,`name` ,`univ_id` ,`dekan_id` )
VALUES 	( NULL , 'Biologicheski', '1', NULL ),
	( NULL , 'Himicheski', '1', NULL ),
	( NULL , 'Matematika i informatika', '1', NULL ),
	( NULL , 'Istoricheski', '1', NULL ),
	( NULL , 'Fizicheski', '1', NULL );

INSERT INTO `university`.`professors` (`id` ,`firstname` ,`middlename` ,`lastname` ,
					`phone` ,`address` ,`EGN` ,`faculty_id` )
VALUES 	( NULL , 'Todor', NULL , 'Ionkov', NULL , NULL , NULL , '1'), 
	( NULL , 'Emil', NULL , 'Nikolov', NULL , NULL , NULL , '1'), 
	( NULL , 'Plamen', NULL , 'Tzvetkov', NULL , NULL , NULL , '1'), 
	( NULL , 'Emil', NULL , 'Garipov', NULL , NULL , NULL , '1'), 
	( NULL , 'Valeri', NULL , 'Mladenov', NULL , NULL , NULL , '1'),
	( NULL , 'Marin', 'H', 'Hristov', '9652220', NULL , NULL , '2'), 
	( NULL , 'Dimitar', 'G', 'Todorov', '9652140', NULL , NULL , '2'), 
	( NULL , 'Stela', 'Iv', 'Mileva', '9653661 ', NULL , NULL , '2'), 
	( NULL , 'Emil', 'D', 'Manolov', '9653269', NULL , NULL , '2'), 
	( NULL , 'Philip', 'T', 'Koparanov', '9653269', NULL , NULL , '2'),
	( NULL , 'Stefcho', 'Georgiev', 'Guninski', '9652318', NULL , NULL , '3'), 
	( NULL , 'Liubomir', 'Borisov', 'Balgaranov', '9652167', NULL , NULL , '3'), 
	( NULL , 'Nadejda', 'Alexandrova', 'Peeva', '9652161', NULL , NULL , '3'), 
	( NULL , 'Petar', NULL , 'Nakov', '9652107', NULL , NULL , '3'), 
	( NULL , 'Snejana', NULL , 'Evtimova', '9652109', NULL , NULL , '3'),
	( NULL , 'Boncho', NULL , 'Bonev', '9652295', NULL , NULL , '4'), 
	( NULL , 'Hristina', NULL , 'Antonova', '9652359', NULL , NULL , '4'), 
	( NULL , 'Ivailo', NULL , 'Banov', '9652209', NULL , NULL , '4'), 
	( NULL , 'Emanuil', NULL , 'Agoncev', '9652436', NULL , NULL , '4'), 
	( NULL , 'Hristo', NULL , 'Petkov', '9653629', NULL , NULL , '4'),
	( NULL , 'Ognian', NULL , 'Nakov', '9653613', NULL , NULL , '5'), 
	( NULL , 'Daniela', NULL , 'Gotceva', NULL, NULL , NULL , '5'),
	( NULL , 'Valentin', NULL , 'Kamburov', '9653691', NULL , NULL , '6'), 
	( NULL , 'Anelia', NULL , 'Ivanova', NULL , NULL , NULL , '6'), 
	( NULL , 'Georgi', 'Todorov', 'Popov', '9653539', NULL , NULL , '6'), 
	( NULL , 'Daniela', NULL , 'Peneva', '9652504', NULL , NULL , '6'), 
	( NULL , 'Nikolai', 'Liubenov', 'Nikolov', '9652770', NULL , NULL , '6'),
	( NULL , 'Bojidar', NULL , 'Galucov', '8167300', NULL , NULL , '7'), 
	( NULL , 'Mariela', NULL , 'Ojdakova', '8167362', NULL , NULL , '7'), 
	( NULL , 'Iana', NULL , 'Topalova', '8167250', NULL , NULL , '7'), 
	( NULL , 'Natasha', NULL , 'Tzanova', NULL , NULL , NULL , '7'),
	( NULL , 'Toni', NULL , 'Spasov', NULL , NULL , NULL , '8'), 
	( NULL , 'Ivan', NULL , 'Petkov', NULL , NULL , NULL , '8'),
	( NULL , 'Ivan', NULL , 'Soskov', '8161575', NULL , NULL , '9'), 
	( NULL , 'Ivan', NULL , 'Gantchev', NULL , NULL , NULL , '9'),
	( NULL , 'Liudmil', NULL , 'Vasilev', NULL , NULL , NULL , '11'), 
	( NULL , 'Ivan', NULL , 'Lalov', NULL , NULL , NULL , '11');



INSERT INTO `university`.`subjects` (`id` ,`name` ,`horarium_hr` ,`lead_professor_id`)
VALUES 	( NULL , 'Bazi Danni', '30', '22'), 
	( NULL , 'Programni Sredi', '40', '21');