Untitled

CREATE DATABASE son
GO
USE son
create table author(
	author_id int identity(1,1),
	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 identity(1,1),
	t_type nvarchar(50) not null unique,

	primary key (t_type_id)
);

create table keywords(
	keyword_id int identity(1,1),
	keyword nvarchar(100) not null unique,

	primary key (keyword_id)
);

create table l_language(
	l_language_id int identity(1,1),
	l_language nvarchar(50) not null unique,

	primary key (l_language_id)
);

create table supervisor(
	supervisor_id int identity(1,1),
	author_id int, --if the supervisor is also a user, author_id will show who is.
	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 identity(1,1),
	uni_name nvarchar(50) not null unique,

	primary key (university_id)
);
create table institutes(
	institutes_id int identity(1,1),
	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 identity(1,1),
	topic_name nvarchar(250) not null,

	primary key (subject_topics_id)
);

create table thesis(
	thesis_id int identity(1,1),
	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,

	constraint PK_Thesis_Keywords 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),
	constraint PK_Thesis_Subject 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),
	constraint PK_Thesis_Supervisor primary key (thesis_id, supervisor_id)
);

create table thesis_cosupervisor(
	thesis_id int not null,
	supervisor_id int not null,

	foreign key (supervisor_id) references supervisor(supervisor_id),
	foreign key (thesis_id) references thesis(thesis_id),
	constraint PK_Thesis_Cosupervisor 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', 1, 1, 2, 120, 1, '2023-05-15'),
('Blockchain Security', 'Analysis of secure blockchain implementations.', 2, '2022', 2, 3, 1,
140, 2, '2022-07-20'),
('Cybersecurity for IoT', 'Securing IoT devices in a connected world.', 3, '2021', 3, 5, 4, 110, 3,
'2021-03-10'),
('Renewable Energy Systems', 'Innovative solutions for sustainable energy.', 4, '2024', 4, 2, 3,
180, 4, '2024-09-30'),
('Quantum Computing Algorithms', 'Developing efficient quantum algorithms.', 5, '2020', 1,
4, 5, 150, 5, '2020-12-15'),
('Genomic Data Analysis', 'An in-depth analysis of genomic datasets.', 3, '2023', 2, 5, 4, 175,
2, '2023-06-12'),
('Climate Modeling with AI', 'Using AI techniques to model climate systems.', 2, '2024', 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);