8FgT3ppy

1DROP DATABASE IF EXISTS Tracking;
2CREATE DATABASE Tracking;
3USE Tracking;
4
5CREATE TABLE ethnic_origins
6(
7	eth_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
8	name VARCHAR(255) NOT NULL,
9	description TEXT NULL
10);
11
12CREATE TABLE People
13(
14	person_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
15	first_name VARCHAR(200) NOT NULL,
16	middle_name VARCHAR(200) NOT NULL,
17	last_name VARCHAR(200) NOT NULL,
18	personal_id VARCHAR(10) UNIQUE NOT NULL,
19	email VARCHAR(200) UNIQUE NOT NULL,
20	phone VARCHAR(50) UNIQUE NOT NULL,
21	marital_status ENUM('married', 'single', 'divorced', 'widowed') NOT NULL,
22	car_type VARCHAR(250) NULL,
23	ethnic_origins INT NOT NULL,
24	gender ENUM('male', 'female'),
25	country_of_birth VARCHAR(255) NOT NULL,
26	country_of_citizenship VARCHAR(255) NOT NULL,
27	country_of_residence VARCHAR(255) NOT NULL,
28	additional_details TEXT NOT NULL,
29	
30	FOREIGN KEY (ethnic_origins) REFERENCES ethnic_origins(eth_id)
31);
32
33CREATE TABLE colours
34(
35	colour_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
36	colour_description VARCHAR(255) NOT NULL
37);
38
39CREATE TABLE Colour_of_eyes
40(
41	person_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
42	right_eye INT NOT NULL,
43	left_eye INT NOT NULL,
44	
45	FOREIGN KEY(person_id) REFERENCES people(person_id),
46	FOREIGN KEY(right_eye) REFERENCES colours(colour_id),
47	FOREIGN KEY(left_eye) REFERENCES colours(colour_id)
48);
49
50CREATE TABLE Colour_of_hair
51(
52	person_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
53	colour_id INT NOT NULL,
54	
55	FOREIGN KEY(person_id) REFERENCES people(person_id),
56	FOREIGN KEY(colour_id) REFERENCES colours(colour_id)
57);
58
59CREATE TABLE coordinates
60(
61	coordinates_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
62	latitude DECIMAL(10,8) NOT NULL,
63	longitude DECIMAL(11,8) NOT NULL
64);
65
66CREATE TABLE people_most_visited
67(
68	pmv_id INT AUTO_INCREMENT NOT NULL,
69	person_id INT NOT NULL,
70	city VARCHAR(255) NOT NULL,
71	country VARCHAR(255) NOT NULL,
72	address VARCHAR(255) NOT NULL,
73	coordinates_id INT NOT NULL,
74	place_type VARCHAR(255) NOT NULL,
75	time_visited DATETIME NOT NULL,
76	time_left DATETIME NOT NULL,
77	
78	PRIMARY KEY (pmv_id, person_id),
79	FOREIGN KEY (person_id) REFERENCES people(person_id),
80	FOREIGN KEY (coordinates_id) REFERENCES coordinates(coordinates_id)
81);
82
83CREATE TABLE people_most_visited_company
84(
85	pmv_id INT NOT NULL,
86	person_id INT NOT NULL,
87	time_visited DATETIME NOT NULL,
88	time_left DATETIME NOT NULL,
89	
90	FOREIGN KEY (pmv_id) REFERENCES people_most_visited(pmv_id),
91	FOREIGN KEY (person_id) REFERENCES people(person_id)
92);
93
94CREATE TABLE characteristics
95(
96	char_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
97	name VARCHAR(255) NOT NULL,
98	description TEXT NULL
99);
100
101CREATE TABLE distinguishing_characteristics
102(
103	char_id INT NOT NULL,
104	person_id INT NOT NULL,
105	
106	PRIMARY KEY (char_id, person_id),
107	FOREIGN KEY (char_id) REFERENCES characteristics(char_id),
108	FOREIGN KEY (person_id) REFERENCES people(person_id)
109);
110
111CREATE TABLE height_weight
112(
113	person_id INT NOT NULL,
114	date_recorded DATE NOT NULL,
115	height_cm FLOAT(5,2) NOT NULL,
116	weight_kg FLOAT(5,2) NOT NULL,
117	
118	PRIMARY KEY (person_id, date_recorded),
119	FOREIGN KEY (person_id) REFERENCES people(person_id)
120);
121
122#####################################################
123
124INSERT INTO `characteristics` (`char_id`, `name`, `description`) VALUES
125	(1, 'char_1', 'char_description_1'),
126	(2, 'char_2', 'char_description_2'),
127	(3, 'char_3', 'char_description_3'),
128	(4, 'char_4', 'char_description_4'),
129	(5, 'char_5', 'char_description_5');
130	
131INSERT INTO `ethnic_origins` (`eth_id`, `name`, `description`) VALUES
132	(1, 'eth_1', 'eth_desc_1'),
133	(2, 'eth_2', 'eth_desc_2'),
134	(3, 'eth_3', 'eth_desc_3'),
135	(4, 'eth_4', 'eth_desc_4'),
136	(5, 'eth_5', 'eth_desc_5');
137	
138INSERT INTO `people` (`person_id`, `first_name`, `middle_name`, `last_name`, `personal_id`, `email`, `phone`, `marital_status`, `car_type`, `ethnic_origins`, `gender`, `country_of_birth`, `country_of_citizenship`, `country_of_residence`, `additional_details`) VALUES
139	(1, 'fname_1', 'mname_1', 'lname_1', 'id_1', 'email_1', 'phone_1', 'single', NULL, 3, 'male', 'Bulgaria', 'Bulgaria', 'Bulgaria', 'shoe_number:40;pants_size:32'),
140	(2, 'fname_2', 'mname_2', 'lname_2', 'id_2', 'email_2', 'phone_2', 'divorced', NULL, 3, 'male', 'Bulgaria', 'Bulgaria', 'Bulgaria', 'shoe_number:40;pants_size:32'),
141	(3, 'fname_3', 'mname_3', 'lname_3', 'id_3', 'email_3', 'phone_3', 'married', NULL, 2, 'female', 'Bulgaria', 'Bulgaria', 'Bulgaria', 'shoe_number:40;pants_size:32'),
142	(4, 'fname_4', 'mname_4', 'lname_4', 'id_4', 'email_4', 'phone_4', 'widowed', NULL, 2, 'female', 'Bulgaria', 'Bulgaria', 'Bulgaria', 'shoe_number:40;pants_size:32'),
143	(5, 'fname_5', 'mname_5', 'lname_5', 'id_5', 'email_5', 'phone_5', 'single', NULL, 5, 'male', 'Bulgaria', 'Bulgaria', 'Bulgaria', 'shoe_number:40;pants_size:32');
144		
145
146INSERT INTO `colours` (`colour_id`, `colour_description`) VALUES
147	(1, 'blue'),
148	(2, 'red'),
149	(3, 'yellow'),
150	(4, 'black'),
151	(5, 'white'),
152	(6, 'green');
153
154	
155INSERT INTO `colour_of_eyes` (`person_id`, `right_eye`, `left_eye`) VALUES
156	(1, 4, 4),
157	(2, 1, 1),
158	(3, 6, 6),
159	(4, 2, 2),
160	(5, 3, 3);
161	
162INSERT INTO `colour_of_hair` (`person_id`, `colour_id`) VALUES
163	(1, 1),
164	(3, 2),
165	(5, 3),
166	(4, 5),
167	(2, 6);
168	
169INSERT INTO `coordinates` (`coordinates_id`, `latitude`, `longitude`) VALUES
170	(1, 40.74189500, -73.98930800),
171	(2, 50.74189500, 23.98930800),
172	(3, 60.74189500, -33.98930800),
173	(4, 70.74189500, -53.98930800),
174	(5, 40.74189500, 63.98930800);
175
176INSERT INTO `distinguishing_characteristics` (`char_id`, `person_id`) VALUES
177	(2, 1),
178	(1, 2),
179	(4, 3),
180	(3, 4),
181	(5, 5);
182	
183INSERT INTO `height_weight` (`person_id`, `date_recorded`, `height_cm`, `weight_kg`) VALUES
184	(1, '2018-05-15', 123.00, 60.00),
185	(2, '2018-04-15', 123.00, 70.00),
186	(3, '2018-07-15', 125.00, 80.00),
187	(4, '2018-05-15', 188.00, 90.00);
188
189INSERT INTO `people_most_visited` (`pmv_id`, `person_id`, `city`, `country`, `address`, `coordinates_id`, `place_type`, `time_visited`, `time_left`) VALUES
190	(1, 1, 'city_1', 'country_1', 'address_1', 1, 'place_1', '2018-05-15 17:34:56', '2018-05-15 18:34:59'),
191	(2, 2, 'city_2', 'country_2', 'address_2', 2, 'place_2', '2018-05-15 17:34:56', '2018-05-15 18:34:59'),
192	(3, 3, 'city_3', 'country_3', 'address_3', 3, 'place_3', '2018-05-15 17:34:56', '2018-05-15 18:34:59'),
193	(4, 4, 'city_4', 'country_4', 'address_4', 4, 'place_4', '2018-05-15 17:34:56', '2018-05-15 18:34:59'),
194	(5, 5, 'city_5', 'country_5', 'address_5', 5, 'place_5', '2018-05-15 17:34:56', '2018-05-15 18:34:59');
195	
196INSERT INTO `people_most_visited_company` (`pmv_id`, `person_id`, `time_visited`, `time_left`) VALUES
197	(2, 4, '2018-05-15 17:35:50', '2018-05-15 17:35:51'),
198	(2, 1, '2018-05-15 17:36:05', '2018-05-15 17:36:06');
199#####################################################
200
201#2
202SELECT * FROM people WHERE marital_status='single';
203
204#3
205SELECT gender, COUNT(person_id) AS count FROM people GROUP BY gender; 
206
207#4
208SELECT CONCAT(p.first_name, '   ', p.last_name) AS Name, pmv.country, pmv.city, pmv.address, pmv.time_visited, pmv.time_left
209FROM people p
210JOIN people_most_visited pmv ON pmv.person_id=p.person_id;
211
212#FULL OUTER JOIN - people - height-weight
213(SELECT * 
214FROM height_weight hw
215LEFT JOIN people p ON p.person_id=hw.person_id)
216UNION
217(SELECT * 
218FROM height_weight hw
219RIGHT JOIN people p ON p.person_id=hw.person_id);
220
221#5
222SELECT eo.name AS 'Ethic origin name', COUNT(eo.eth_id) AS 'Count of people'
223FROM people p
224JOIN ethnic_origins eo ON eo.eth_id=p.ethnic_origins
225GROUP BY ethnic_origins;