Untitled

CREATE DATABASE son;

USE son;

CREATE TABLE author (
    author_id INT AUTO_INCREMENT,
    a_name_surname NVARCHAR(100) NOT NULL,
    author_email VARCHAR(50) NOT NULL,
    PRIMARY KEY (author_id)
);

CREATE TABLE t_type (
    t_type_id INT AUTO_INCREMENT,
    t_type NVARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (t_type_id)
);

CREATE TABLE keywords (
    keyword_id INT AUTO_INCREMENT,
    keyword NVARCHAR(100) NOT NULL UNIQUE,
    PRIMARY KEY (keyword_id)
);

CREATE TABLE l_language (
    l_language_id INT AUTO_INCREMENT,
    l_language NVARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (l_language_id)
);

CREATE TABLE supervisor (
    supervisor_id INT AUTO_INCREMENT,
    author_id INT,
    s_name_surname NVARCHAR(100) NOT NULL,
    supervisor_email VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    PRIMARY KEY (supervisor_id),
    FOREIGN KEY (author_id) REFERENCES author(author_id)
);

CREATE TABLE university (
    university_id INT AUTO_INCREMENT,
    uni_name NVARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (university_id)
);

CREATE TABLE institutes (
    institutes_id INT AUTO_INCREMENT,
    university_id INT NOT NULL,
    in_name NVARCHAR(50) NOT NULL,
    PRIMARY KEY (institutes_id),
    FOREIGN KEY (university_id) REFERENCES university(university_id),
    CONSTRAINT UQ_Institute_Name_University UNIQUE (in_name, university_id)
);

CREATE TABLE subject_topics (
    subject_topics_id INT AUTO_INCREMENT,
    topic_name NVARCHAR(250) NOT NULL,
    PRIMARY KEY (subject_topics_id)
);

CREATE TABLE thesis (
    thesis_id INT AUTO_INCREMENT,
    title NVARCHAR(500) NOT NULL,
    abstract NVARCHAR(4000) NOT NULL,
    author_id INT NOT NULL,
    y_year DATE NOT NULL,
    t_type_id INT NOT NULL,
    institute_id INT NOT NULL,
    university_id INT NOT NULL,
    number_of_pages INT NOT NULL,
    l_language_id INT NOT NULL,
    submission_date DATE NOT NULL,
    PRIMARY KEY (thesis_id),
    CONSTRAINT CHK_Thesis_Id CHECK (thesis_id BETWEEN 1 AND 9999999),
    FOREIGN KEY (author_id) REFERENCES author(author_id),
    FOREIGN KEY (t_type_id) REFERENCES t_type(t_type_id),
    FOREIGN KEY (l_language_id) REFERENCES l_language(l_language_id),
    FOREIGN KEY (institute_id) REFERENCES institutes(institutes_id),
    FOREIGN KEY (university_id) REFERENCES university(university_id)
);

CREATE TABLE thesis_keywords (
    thesis_id INT NOT NULL,
    keyword_id INT NOT NULL,
    PRIMARY KEY (thesis_id, keyword_id),
    FOREIGN KEY (thesis_id) REFERENCES thesis(thesis_id),
    FOREIGN KEY (keyword_id) REFERENCES keywords(keyword_id)
);

CREATE TABLE thesis_subject_topics (
    thesis_id INT NOT NULL,
    subject_topic_id INT NOT NULL,
    FOREIGN KEY (thesis_id) REFERENCES thesis(thesis_id),
    FOREIGN KEY (subject_topic_id) REFERENCES subject_topics(subject_topics_id),
    PRIMARY KEY (thesis_id, subject_topic_id)
);

CREATE TABLE thesis_supervisor (
    thesis_id INT NOT NULL,
    supervisor_id INT NOT NULL,
    FOREIGN KEY (thesis_id) REFERENCES thesis(thesis_id),
    FOREIGN KEY (supervisor_id) REFERENCES supervisor(supervisor_id),
    PRIMARY KEY (thesis_id, supervisor_id)
);

CREATE TABLE thesis_cosupervisor (
    thesis_id INT NOT NULL,
    supervisor_id INT NOT NULL,
    FOREIGN KEY (thesis_id) REFERENCES thesis(thesis_id),
    FOREIGN KEY (supervisor_id) REFERENCES supervisor(supervisor_id),
    PRIMARY KEY (thesis_id, supervisor_id)
);

INSERT INTO author (a_name_surname, author_email) VALUES
('John Doe', 'john.doe@example.com'),
('Jane Smith', 'jane.smith@example.com'),
('Alice Brown', 'alice.brown@example.com'),
('Bob White', 'bob.white@example.com'),
('Emily Davis', 'emily.davis@example.com');

INSERT INTO t_type (t_type) VALUES
('Master'),
('Doctorate'),
('Specialization in Medicine'),
('Proficiency in Art');

INSERT INTO keywords (keyword) VALUES
('Machine Learning'),
('Data Science'),
('Artificial Intelligence'),
('Blockchain'),
('Renewable Energy'),
('Climate Change');

INSERT INTO l_language (l_language) VALUES
('English'),
('Turkish'),
('French'),
('German'),
('Spanish');

INSERT INTO supervisor (author_id, s_name_surname, supervisor_email, phone) VALUES
(1, 'Prof. Alan Turing', 'alan.turing@example.com', '123-456-7890'),
(2, 'Dr. Grace Hopper', 'grace.hopper@example.com', '234-567-8901'),
(3, 'Prof. Ada Lovelace', 'ada.lovelace@example.com', '345-678-9012'),
(4, 'Dr. Donald Knuth', 'donald.knuth@example.com', '456-789-0123'),
(5, 'Prof. Barbara Liskov', 'barbara.liskov@example.com', '567-890-1234');

INSERT INTO university (uni_name) VALUES
('Marmara University'),
('Boğaziçi University'),
('METU'),
('Istanbul University'),
('Hacettepe University');

INSERT INTO institutes (university_id, in_name) VALUES
(1, 'Institute of Pure and Applied Sciences'),
(1, 'Institute of Social Sciences'),
(2, 'School of Engineering'),
(3, 'Department of Computer Science'),
(4, 'Faculty of Medicine');

INSERT INTO subject_topics (topic_name) VALUES
('Big Data'),
('Cybersecurity'),
('Robotics'),
('Quantum Computing'),
('Renewable Energy');

INSERT INTO thesis (title, abstract, author_id, y_year, t_type_id, institute_id, university_id,
number_of_pages, l_language_id, submission_date) VALUES
('Machine Learning Applications', 'A detailed study on ML applications in healthcare.', 1, '2023-01-01', 1, 1, 2, 120, 1, '2023-05-15'),
('Blockchain Security', 'Analysis of secure blockchain implementations.', 2, '2022-01-01', 2, 3, 1, 140, 2, '2022-07-20'),
('Cybersecurity for IoT', 'Securing IoT devices in a connected world.', 3, '2021-01-01', 3, 5, 4, 110, 3, '2021-03-10'),
('Renewable Energy Systems', 'Innovative solutions for sustainable energy.', 4, '2024-01-01', 4, 2, 3, 180, 4, '2024-09-30'),
('Quantum Computing Algorithms', 'Developing efficient quantum algorithms.', 5, '2020-01-01', 1, 4, 5, 150, 5, '2020-12-15'),
('Genomic Data Analysis', 'An in-depth analysis of genomic datasets.', 3, '2023-01-01', 2, 5, 4, 175, 2, '2023-06-12'),
('Climate Modeling with AI', 'Using AI techniques to model climate systems.', 2, '2024-01-01', 3, 1, 1, 200, 1, '2024-08-25');

INSERT INTO thesis_keywords (thesis_id, keyword_id) VALUES
(1, 1), (1, 2), (1, 3),
(2, 3), (2, 4),
(3, 1), (3, 4), (3, 5), (3, 6),
(4, 2), (4, 3), (4, 6),
(5, 5);

INSERT INTO thesis_subject_topics (thesis_id, subject_topic_id) VALUES
(1, 1), (1, 2), (1, 3),
(2, 3), (2, 4),
(3, 2), (3, 3), (3, 5),
(4, 1), (4, 4), (4, 5),
(5, 5);

INSERT INTO thesis_supervisor (thesis_id, supervisor_id) VALUES
(1, 1), (1, 2),
(2, 2),
(3, 3), (3, 4),
(4, 4),
(5, 5);

INSERT INTO thesis_cosupervisor (thesis_id, supervisor_id) VALUES
(1, 3),
(2, 1), (2, 4),
(3, 5),
(4, 2), (4, 3),
(5, 1);