-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathhrms_real.sql
121 lines (112 loc) · 7.76 KB
/
hrms_real.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE DATABASE `hrms` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
use hrms;
-- drop database hrms;
CREATE TABLE `job_position` (
`id` int NOT NULL AUTO_INCREMENT,
`job_name` varchar(100) DEFAULT NULL,
`job_explanation` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`corporation_name` varchar(200) DEFAULT NULL,
`web_site_name` varchar(300) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `corporation_name` (`corporation_name`),
UNIQUE KEY `web_site_name` (`web_site_name`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `password` (`password`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `job_advertisement` (
`id` int NOT NULL AUTO_INCREMENT,
`job_position_id` int DEFAULT NULL,
`employee_id` int DEFAULT NULL,
`advertisement_title` varchar(50) DEFAULT NULL,
`advertisement_context` text,
`min_salary` double DEFAULT NULL,
`max_salary` double DEFAULT NULL,
`position_amount` int DEFAULT NULL,
`is_active` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `job_advertisement_ibfk_01` (`job_position_id`),
KEY `job_advertisement_ibfk_02` (`employee_id`),
CONSTRAINT `job_advertisement_ibfk_01` FOREIGN KEY (`job_position_id`) REFERENCES `job_position` (`id`),
CONSTRAINT `job_advertisement_ibfk_02` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`),
CONSTRAINT `job_advertisement_chk_1` CHECK ((`min_salary` >= 0)),
CONSTRAINT `job_advertisement_chk_2` CHECK ((`max_salary` > 0)),
CONSTRAINT `job_advertisement_chk_3` CHECK ((`position_amount` > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `job_seeker` (
`id` int NOT NULL AUTO_INCREMENT,
`job_position_id` int DEFAULT NULL,
`name` varchar(100) NOT NULL,
`surname` varchar(100) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`birth_year` int NOT NULL,
`mail` varchar(255) DEFAULT NULL,
`tc_no` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `surname` (`surname`),
UNIQUE KEY `job_position_id` (`job_position_id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `password` (`password`),
CONSTRAINT `job_seeker_ibfk_01` FOREIGN KEY (`job_position_id`) REFERENCES `job_position` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `curriculum_vitae` (
`id` int NOT NULL AUTO_INCREMENT,
`job_seeker_id` int NOT NULL,
`about` varchar(250) NOT NULL,
`linkedin_link` varchar(100) DEFAULT NULL,
`github_link` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `job_seeker_id` (`job_seeker_id`),
CONSTRAINT `curriculum_vitae_ibfk_01` FOREIGN KEY (`job_seeker_id`) REFERENCES `job_seeker` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `job_experiences` (
`id` int NOT NULL AUTO_INCREMENT,
`curriculum_vitae_id` int NOT NULL,
`corporation_name` varchar(300) DEFAULT NULL,
`position_id` int DEFAULT NULL,
`beginning` varchar(8) DEFAULT NULL,
`finish` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `curriculum_vitae_id` (`curriculum_vitae_id`),
KEY `job_experiences_ibfk_02` (`position_id`),
CONSTRAINT `job_experiences_ibfk_01` FOREIGN KEY (`curriculum_vitae_id`) REFERENCES `curriculum_vitae` (`id`),
CONSTRAINT `job_experiences_ibfk_02` FOREIGN KEY (`position_id`) REFERENCES `job_position` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `language` (
`id` int NOT NULL AUTO_INCREMENT,
`curriculum_vitae_id` int NOT NULL,
`language_name` varchar(20) DEFAULT NULL,
`level` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `curriculum_vitae_id` (`curriculum_vitae_id`),
CONSTRAINT `language_ibfk_01` FOREIGN KEY (`curriculum_vitae_id`) REFERENCES `curriculum_vitae` (`id`),
CONSTRAINT `language_chk_1` CHECK (((`level` >= 0) and (`level` <= 5)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `school` (
`id` int NOT NULL AUTO_INCREMENT,
`curriculum_vitae_id` int NOT NULL,
`school_name` varchar(100) DEFAULT NULL,
`section` varchar(75) DEFAULT NULL,
`graduation_year` int DEFAULT NULL,
`beginning` varchar(8) DEFAULT NULL,
`finish` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `curriculum_vitae_id` (`curriculum_vitae_id`),
CONSTRAINT `school_ibfk_01` FOREIGN KEY (`curriculum_vitae_id`) REFERENCES `curriculum_vitae` (`id`),
CONSTRAINT `school_chk_1` CHECK ((`graduation_year` > 1900))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `tech_name` (
`id` int NOT NULL AUTO_INCREMENT,
`curriculum_vitae_id` int NOT NULL,
`tech_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `curriculum_vitae_id` (`curriculum_vitae_id`),
CONSTRAINT `tech_name_ibfk_01` FOREIGN KEY (`curriculum_vitae_id`) REFERENCES `curriculum_vitae` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;