GoIt_Homework_SQL/initDB.sql at master · hhlTer/GoIt_Homework_SQL · GitHub

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
#--------------------------------------------------------------
#------------------ create DEVELOPERS table -------------------
#--------------------------------------------------------------
CREATE DATABASE dvlop_3;
USE dvlop_3;
CREATE TABLE developers (
  id          SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  first_name  VARCHAR(50)       NOT NULL,
  second_name VARCHAR(50)       NOT NULL,
  skills      VARCHAR(255)      NOT NULL
);

#--------------------------------------------------------------
#------------------------ SKILLS ------------------------------
#--------------------------------------------------------------
CREATE TABLE skills (
  id    SMALLINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  skill VARCHAR(50)                   NOT NULL
);

#--------------------------------------------------------------
#------------------------ PROJECTS ----------------------------
#--------------------------------------------------------------
CREATE TABLE projects (
  id           SMALLINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name_project VARCHAR(50)                   NOT NULL,
  descryption  VARCHAR(255)
);

#--------------------------------------------------------------
#----------------------- COMPANIES ----------------------------
#--------------------------------------------------------------

CREATE TABLE companies (
  id             SMALLINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name_company   VARCHAR(50)                   NOT NULL,
  addres_company VARCHAR(50)
);

#--------------------------------------------------------------
#----------------------- CUSTOMERS ----------------------------
#--------------------------------------------------------------

CREATE TABLE customers (
  id          SMALLINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name_cstmer VARCHAR(50)                   NOT NULL
);

#--------------------------------------------------------------
#-------------------------- MTM -------------------------------
#--------------------------------------------------------------

#--------------------------------------------------------------
#------------------ DEVELOPER / SKILLS ------------------------
#--------------------------------------------------------------

CREATE TABLE mtm_developers_skills(
  mtm_id_developer SMALLINT UNSIGNED NOT NULL,
  mtm_id_skills    SMALLINT UNSIGNED NOT NULL,

  FOREIGN KEY (mtm_id_developer) REFERENCES developers(id),
  FOREIGN KEY (mtm_id_skills)    REFERENCES skills(id)
);

#--------------------------------------------------------------
#------------------ PROJECT / DEVELOPERS ----------------------
#--------------------------------------------------------------

CREATE TABLE mtm_project_developers (
  mtm_id_project   SMALLINT UNSIGNED NOT NULL,
  mtm_id_developer SMALLINT UNSIGNED NOT NULL,

  FOREIGN KEY (mtm_id_developer) REFERENCES developers (id),
  FOREIGN KEY (mtm_id_project)   REFERENCES projects (id)
);

#--------------------------------------------------------------
#----------------- COMPANIES / DEVELOPERS ---------------------
#--------------------------------------------------------------

CREATE TABLE mtm_companies_developers (
  mtm_id_company   SMALLINT UNSIGNED NOT NULL,
  mtm_id_developer SMALLINT UNSIGNED NOT NULL,

  FOREIGN KEY (mtm_id_company)   REFERENCES companies (id),
  FOREIGN KEY (mtm_id_developer) REFERENCES developers (id)
);

#--------------------------------------------------------------
#------------------ COMPANIES / PROJECTS ----------------------
#--------------------------------------------------------------

CREATE TABLE mtm_companies_projects (
  mtm_id_company  SMALLINT UNSIGNED NOT NULL,
  mtm_id_projects SMALLINT UNSIGNED NOT NULL,

  FOREIGN KEY (mtm_id_company)  REFERENCES companies (id),
  FOREIGN KEY (mtm_id_projects) REFERENCES projects (id)
);

#--------------------------------------------------------------
#------------------ CUSTOMERS / PROJECTS ----------------------
#--------------------------------------------------------------

CREATE TABLE mtm_customer_projects (
  mtm_customer_id SMALLINT UNSIGNED NOT NULL,
  mtm_projects_id  SMALLINT UNSIGNED NOT NULL,

  FOREIGN KEY (mtm_customer_id) REFERENCES customers(id),
  FOREIGN KEY (mtm_projects_id)  REFERENCES projects(id)

);