CvL507Pj

1DROP DATABASE IF EXISTS PlayerStats;
2CREATE DATABASE PlayerStats CHARSET 'utf8';
3
4use PlayerStats;
5
6CREATE TABLE StatTypes(
7	StatCode CHAR(4) NOT NULL PRIMARY KEY,
8    Name CHAR(20) NOT NULL
9);
10
11INSERT INTO StatTypes(StatCode, Name) VALUES('G', 'Гол');
12INSERT INTO StatTypes(StatCode, Name) VALUES('A', 'Асистенция');
13INSERT INTO StatTypes(StatCode, Name) VALUES('R', 'Червен картон');
14INSERT INTO StatTypes(StatCode, Name) VALUES('Y', 'Жълт картон');
15INSERT INTO StatTypes(StatCode, Name) VALUES('OG', 'Автогол');
16INSERT INTO StatTypes(StatCode, Name) VALUES('IN', 'Смяна влиза');
17INSERT INTO StatTypes(StatCode, Name) VALUES('OUT', 'Смяна излиза');
18
19CREATE TABLE Positions(
20	PositionCode CHAR(3) NOT NULL PRIMARY KEY,
21    Name CHAR(25) NOT NULL
22);
23
24INSERT INTO Positions(PositionCode, Name) VALUES('GK', 'Вратар');
25INSERT INTO Positions(PositionCode, Name) VALUES('RB', 'Десен защитник');
26INSERT INTO Positions(PositionCode, Name) VALUES('LB', 'Ляв защитник');
27INSERT INTO Positions(PositionCode, Name) VALUES('CB', 'Централен защитник');
28INSERT INTO Positions(PositionCode, Name) VALUES('RM', 'Десен защитник');
29INSERT INTO Positions(PositionCode, Name) VALUES('LM', 'Ляв полузащитник');
30INSERT INTO Positions(PositionCode, Name) VALUES('CM', 'Полузащитник');
31INSERT INTO Positions(PositionCode, Name) VALUES('CF', 'Централен нападател');
32
33CREATE TABLE Players (
34	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
35    Name CHAR(25) NOT NULL,
36    Num INTEGER NOT NULL,
37    PositionCode CHAR(3) NOT NULL,
38    
39	FOREIGN KEY (PositionCode) REFERENCES Positions(PositionCode)
40);
41
42INSERT INTO Players(Name, Num, PositionCode) VALUES('Ivaylo Trifonov', 1, 'GK');
43INSERT INTO Players(Name, Num, PositionCode) VALUES('Valko Trifonov', 2, 'RB');
44INSERT INTO Players(Name, Num, PositionCode) VALUES('Ognyay Yanev', 3, 'CB');
45INSERT INTO Players(Name, Num, PositionCode) VALUES('Zahari Dragomirov', 4, 'CB');
46INSERT INTO Players(Name, Num, PositionCode) VALUES('Bozhidar Chilikov', 5, 'LB');
47INSERT INTO Players(Name, Num, PositionCode) VALUES('Timotei Zahariev', 6, 'CM');
48INSERT INTO Players(Name, Num, PositionCode) VALUES('Marin Valentinov', 7, 'CM');
49INSERT INTO Players(Name, Num, PositionCode) VALUES('Mitre Cvetkov', 99, 'CF');
50INSERT INTO Players(Name, Num, PositionCode) VALUES('Zlatko Genov', 9, 'CF');
51INSERT INTO Players(Name, Num, PositionCode) VALUES('Matey Goranov', 10, 'RM');
52INSERT INTO Players(Name, Num, PositionCode) VALUES('Sergei Zhivkov', 11, 'LM');
53
54CREATE TABLE Tournaments(
55	Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
56    Name CHAR(30) NOT NULL
57    );
58    
59INSERT INTO Tournaments(Name) Values('Шампионска лига');
60INSERT INTO Tournaments(Name) Values('Първа лига');
61INSERT INTO Tournaments(Name) Values('Купа за България');
62INSERT INTO Tournaments(Name) Values('Суперкупа на България');
63
64CREATE TABLE Matches(
65	Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
66    MatchDate DATE NOT NULL,
67    TournamentId INT NOT NULL,
68    FOREIGN KEY (TournamentId) REFERENCES Tournaments(Id)
69	);
70    
71INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-04-08',2);
72INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-04-13',2);
73INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-04-21',2);
74INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-04-28',2);
75INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-05-06',2);
76INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-05-11',2);
77INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-09-21',3);
78INSERT INTO Matches(MatchDate, TournamentId) VALUES ('2018-10-26',3);
79
80CREATE TABLE MatchStats(
81	Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
82	MatchId INT NOT NULL,
83    PlayerId INT NOT NULL,
84    EventMinute INT NOT NULL,
85    StatCode CHAR(3) NOT NULL,
86    FOREIGN KEY (MatchId) REFERENCES Matches(Id),
87    FOREIGN KEY (PlayerId) REFERENCES Players(Id),
88    FOREIGN KEY (StatCode) REFERENCES StatTypes(StatCode)
89	);
90
91INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(8, 9, 14, 'G');
92INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(8, 8, 14, 'A');
93INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(8, 3, 43, 'Y');
94INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(7, 2, 28, 'Y');
95INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(7, 10, 45, 'Y');
96INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(7, 10, 65, 'R');
97INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(1, 10, 23, 'G');
98INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(1, 9, 23, 'A');
99INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(1, 9, 43, 'G');
100INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(2, 4, 33, 'OG');
101INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(2, 9, 68, 'G');
102INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(2, 1, 68, 'A');
103INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(3, 3, 35, 'G');
104INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(3, 4, 35, 'A');
105INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(3, 8, 55, 'G');
106INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(3, 11, 55, 'A');
107INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(4, 3, 9, 'G');
108INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(4, 8, 9, 'G');
109INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(4, 8, 56, 'OG');
110INSERT INTO MatchStats(MatchId, PlayerId, EventMinute, StatCode) VALUES(5, 8, 67, 'G');