Untitled

avatar
unknown
plain_text
2 months ago
7.6 kB
2
Indexable
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `author` (
  `author_id` int(11) NOT NULL,
  `name_surname` varchar(100) NOT NULL,
  `author_email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `institutes` (
  `institutes_id` int(11) NOT NULL,
  `university_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `keywords` (
  `keyword_id` int(11) NOT NULL,
  `keyword` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `language` (
  `language_id` int(11) NOT NULL,
  `language` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `subject_topics` (
  `subject_topics_id` int(11) NOT NULL,
  `topic_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `supervisor` (
  `supervisor_id` int(11) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `name_surname` varchar(100) NOT NULL,
  `supervisor_email` varchar(50) NOT NULL,
  `phone` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `thesis` (
  `thesis_id` int(11) NOT NULL,
  `title` varchar(500) NOT NULL,
  `abstract` varchar(5000) NOT NULL,
  `author_id` int(11) NOT NULL,
  `year` year(4) NOT NULL,
  `type_id` int(11) NOT NULL,
  `institute_id` int(11) NOT NULL,
  `university_id` int(11) NOT NULL,
  `number_of_pages` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `submission_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `thesis_cosupervisor` (
  `thesis_id` int(11) NOT NULL,
  `supervisor_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `thesis_keywords` (
  `thesis_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `thesis_subject_topics` (
  `thesis_id` int(11) NOT NULL,
  `subject_topic_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `thesis_supervisor` (
  `thesis_id` int(11) NOT NULL,
  `supervisor_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `type` (
  `type_id` int(11) NOT NULL,
  `type` enum('Master','Doctorate','Specialization in Medicine','Proficiency in Art') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `university` (
  `university_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name_surname` varchar(100) NOT NULL,
  `user_email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `author`
  ADD PRIMARY KEY (`author_id`),
  ADD UNIQUE KEY `UQ_author_email_name` (`author_email`,`name_surname`);

ALTER TABLE `institutes`
  ADD PRIMARY KEY (`institutes_id`),
  ADD UNIQUE KEY `name` (`name`,`university_id`),
  ADD KEY `university_id` (`university_id`);

ALTER TABLE `keywords`
  ADD PRIMARY KEY (`keyword_id`),
  ADD UNIQUE KEY `keyword` (`keyword`);

ALTER TABLE `language`
  ADD PRIMARY KEY (`language_id`),
  ADD UNIQUE KEY `language` (`language`);

ALTER TABLE `subject_topics`
  ADD PRIMARY KEY (`subject_topics_id`);

ALTER TABLE `supervisor`
  ADD PRIMARY KEY (`supervisor_id`),
  ADD KEY `author_id` (`author_id`);

ALTER TABLE `thesis`
  ADD PRIMARY KEY (`thesis_id`),
  ADD KEY `author_id` (`author_id`),
  ADD KEY `type_id` (`type_id`),
  ADD KEY `language_id` (`language_id`),
  ADD KEY `institute_id` (`institute_id`),
  ADD KEY `university_id` (`university_id`);

ALTER TABLE `thesis_cosupervisor`
  ADD PRIMARY KEY (`thesis_id`,`supervisor_id`),
  ADD KEY `supervisor_id` (`supervisor_id`);

ALTER TABLE `thesis_keywords`
  ADD PRIMARY KEY (`thesis_id`,`keyword_id`),
  ADD KEY `keyword_id` (`keyword_id`);

ALTER TABLE `thesis_subject_topics`
  ADD PRIMARY KEY (`thesis_id`,`subject_topic_id`),
  ADD KEY `subject_topic_id` (`subject_topic_id`);

ALTER TABLE `thesis_supervisor`
  ADD PRIMARY KEY (`thesis_id`,`supervisor_id`),
  ADD KEY `supervisor_id` (`supervisor_id`);

ALTER TABLE `type`
  ADD PRIMARY KEY (`type_id`),
  ADD UNIQUE KEY `type` (`type`);

ALTER TABLE `university`
  ADD PRIMARY KEY (`university_id`),
  ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_email_2` (`user_email`,`name_surname`);


ALTER TABLE `author`
  MODIFY `author_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `institutes`
  MODIFY `institutes_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `keywords`
  MODIFY `keyword_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `language`
  MODIFY `language_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `subject_topics`
  MODIFY `subject_topics_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `supervisor`
  MODIFY `supervisor_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `thesis`
  MODIFY `thesis_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `type`
  MODIFY `type_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `university`
  MODIFY `university_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `institutes`
  ADD CONSTRAINT `institutes_ibfk_1` FOREIGN KEY (`university_id`) REFERENCES `university` (`university_id`);

ALTER TABLE `supervisor`
  ADD CONSTRAINT `supervisor_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`author_id`);

ALTER TABLE `thesis`
  ADD CONSTRAINT `thesis_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`author_id`),
  ADD CONSTRAINT `thesis_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `type` (`type_id`),
  ADD CONSTRAINT `thesis_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
  ADD CONSTRAINT `thesis_ibfk_4` FOREIGN KEY (`institute_id`) REFERENCES `institutes` (`institutes_id`),
  ADD CONSTRAINT `thesis_ibfk_5` FOREIGN KEY (`university_id`) REFERENCES `university` (`university_id`);

ALTER TABLE `thesis_cosupervisor`
  ADD CONSTRAINT `thesis_cosupervisor_ibfk_1` FOREIGN KEY (`supervisor_id`) REFERENCES `supervisor` (`supervisor_id`),
  ADD CONSTRAINT `thesis_cosupervisor_ibfk_2` FOREIGN KEY (`thesis_id`) REFERENCES `thesis` (`thesis_id`);

ALTER TABLE `thesis_keywords`
  ADD CONSTRAINT `thesis_keywords_ibfk_1` FOREIGN KEY (`thesis_id`) REFERENCES `thesis` (`thesis_id`),
  ADD CONSTRAINT `thesis_keywords_ibfk_2` FOREIGN KEY (`keyword_id`) REFERENCES `keywords` (`keyword_id`);

ALTER TABLE `thesis_subject_topics`
  ADD CONSTRAINT `thesis_subject_topics_ibfk_1` FOREIGN KEY (`thesis_id`) REFERENCES `thesis` (`thesis_id`),
  ADD CONSTRAINT `thesis_subject_topics_ibfk_2` FOREIGN KEY (`subject_topic_id`) REFERENCES `subject_topics` (`subject_topics_id`);

ALTER TABLE `thesis_supervisor`
  ADD CONSTRAINT `thesis_supervisor_ibfk_1` FOREIGN KEY (`thesis_id`) REFERENCES `thesis` (`thesis_id`),
  ADD CONSTRAINT `thesis_supervisor_ibfk_2` FOREIGN KEY (`supervisor_id`) REFERENCES `supervisor` (`supervisor_id`);

ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`user_email`,`name_surname`) REFERENCES `author` (`author_email`, `name_surname`) ON DELETE CASCADE;
COMMIT;
Editor is loading...
Leave a Comment