Korvpalliga seotud andmebaas

Andmeplokid
  Mängija
     isikuandmed
	 meditsiinilised andmed
	 sportlikud mõõtmised
	 kohalkäimised
  Klubi
     mängijad
	 rollid
	 treenerid
	 varustus
	 harjutuskohad
	 kohatyybid
	 klubiinfo
	 sündmused
	 maakonnad
	 
  Turniir
     klubid
	 mängud
	 edetabel

tunnused(kood, yhik, miinimum, maksimum)
isikud(id, perekonnanimi, synniaeg)
isik_tunnus(id, isiku_id, tunnuse_kood, vaartus, aeg)
rollid(rollinimi)
syndmusetyybid(tyybinimi)
syndmused(id, tyybinimi, aeg, harjutuskoha_id, klubinimi)
klubid(nimetus, maakond)
harjutuskohad(id, kohatyyp, kommentaar)
kohatyybid(tyybinimi)
sponsoreerimine(id, sponsor, klubinimi, aasta, summa, kommentaar)
sponsorid(sponsorinimi)
kutsutud_rollid_syndmusel(id, syndmuse_id, rollinimi)
osalenud_isikud_syndmusel(id, isiku_id, syndmuse_id)
isik_klubis(id, isiku_id, roll, klubinimi)
	 
CREATE TABLE rollid(rollinimi VARCHAR(50) NOT NULL PRIMARY KEY);
CREATE TABLE syndmusetyybid(tyybinimi VARCHAR(50) NOT NULL PRIMARY KEY);
CREATE TABLE kohatyybid(tyybinimi VARCHAR(50) NOT NULL PRIMARY KEY);
CREATE TABLE sponsorid(sponsorinimi VARCHAR(50) NOT NULL PRIMARY KEY);
CREATE TABLE tunnused(kood VARCHAR(20) PRIMARY KEY, 
   yhik VARCHAR(20), miinimum FLOAT, maksimum FLOAT);
CREATE TABLE isikud(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  perekonnanimi VARCHAR(20), synniaeg DATE);
CREATE TABLE klubid(nimetus VARCHAR(50) NOT NULL PRIMARY KEY, maakond VARCHAR(50));
CREATE TABLE harjutuskohad(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  kohatyyp VARCHAR(50), kommentaar VARCHAR(255), 
  FOREIGN KEY(kohatyyp) REFERENCES kohatyybid(tyybinimi));
CREATE TABLE sponsoreerimine(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   sponsor VARCHAR(50), klubinimi VARCHAR(50), aasta INT, summa DECIMAL(8, 2),
   kommentaar TEXT,
   FOREIGN KEY(sponsor) REFERENCES sponsorid(sponsorinimi),
   FOREIGN KEY(klubinimi) REFERENCES klubid(nimetus));
CREATE TABLE isik_tunnus(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  isiku_id INT, tunnuse_kood VARCHAR(20), vaartus FLOAT, aeg DATETIME,
  FOREIGN KEY(isiku_id) REFERENCES isikud(id),
  FOREIGN KEY(tunnuse_kood) REFERENCES tunnused(kood));
CREATE TABLE syndmused(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  tyybinimi VARCHAR(50), aeg DATETIME, harjutuskoha_id INT, klubinimi VARCHAR(50),
  FOREIGN KEY(tyybinimi) REFERENCES syndmusetyybid(tyybinimi),
  FOREIGN KEY(harjutuskoha_id) REFERENCES harjutuskohad(id),
  FOREIGN KEY(klubinimi) REFERENCES klubid(nimetus));
CREATE TABLE kutsutud_rollid_syndmusel(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   syndmuse_id INT, rollinimi VARCHAR(50),
 FOREIGN KEY(syndmuse_id) REFERENCES syndmused(id), 
 FOREIGN KEY(rollinimi) REFERENCES rollid(rollinimi));
CREATE TABLE osalenud_isikud_syndmusel(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   
 isiku_id INT, syndmuse_id INT,
 FOREIGN KEY(isiku_id) REFERENCES isikud(id),
 FOREIGN KEY(syndmuse_id) REFERENCES syndmused(id));
CREATE TABLE isik_klubis(id INT NOT NULL auto_increment PRIMARY KEY, 
  isiku_id INT, roll VARCHAR(50), klubinimi VARCHAR(50),
  FOREIGN KEY (isiku_id) REFERENCES isikud(id),
  FOREIGN KEY (roll) REFERENCES rollid(rollinimi),
  FOREIGN KEY (klubinimi) REFERENCES klubid(nimetus));
  
  
INSERT INTO rollid VALUES('treener'); 
INSERT INTO rollid VALUES('mängija'); 
INSERT INTO rollid VALUES('koristaja');

INSERT INTO klubid VALUES('Tamme klubi', 'Saaremaa');
INSERT INTO klubid VALUES('Saare klubi', 'Saaremaa');
INSERT INTO klubid VALUES('Kase klubi', 'Harjumaa');

INSERT INTO isikud VALUES(DEFAULT, 'Jalakas', '2001-10-03');
INSERT INTO isikud VALUES(DEFAULT, 'Saar', '2001-10-03');
INSERT INTO isikud VALUES(DEFAULT, 'Mänd', '2002-10-03');
INSERT INTO isikud VALUES(DEFAULT, 'Kuusk', '2003-10-03');
INSERT INTO isikud VALUES(DEFAULT, 'Kivi', '1983-10-03');
INSERT INTO isikud VALUES(DEFAULT, 'Uba', '1984-10-03');
INSERT INTO isikud VALUES(DEFAULT, 'Ahi', '1954-10-03');

INSERT INTO isik_klubis VALUES (DEFAULT, 5, 'treener', 'Tamme klubi');
INSERT INTO isik_klubis VALUES (DEFAULT, 6, 'treener', 'Kase klubi');
INSERT INTO isik_klubis VALUES (DEFAULT, 1, 'mängija', 'Kase klubi');
INSERT INTO isik_klubis VALUES (DEFAULT, 2, 'mängija', 'Kase klubi');
INSERT INTO isik_klubis VALUES (DEFAULT, 3, 'mängija', 'Kase klubi');
INSERT INTO isik_klubis VALUES (DEFAULT, 7, 'koristaja', 'Tamme klubi');

INSERT INTO kohatyybid VALUES ('staadion');
INSERT INTO kohatyybid VALUES ('võimla');
INSERT INTO kohatyybid VALUES ('klassiruum');

INSERT INTO harjutuskohad VALUES(DEFAULT, 'staadion', 'Tammega staadion');
INSERT INTO harjutuskohad VALUES(DEFAULT, 'staadion', 'Saarega staadion');

INSERT INTO syndmusetyybid VALUES ('treening');
INSERT INTO syndmusetyybid VALUES ('võistlus');
INSERT INTO syndmusetyybid VALUES ('koosolek');

INSERT INTO syndmused VALUES(DEFAULT, 'treening', '2016-12-31 19:00', 1, 'Tamme klubi');
INSERT INTO syndmused VALUES(DEFAULT, 'treening', '2017-01-01 10:00', 2, 'Saare klubi');

INSERT INTO kutsutud_rollid_syndmusel (syndmuse_id, rollinimi) VALUES (1, 'mängija');
INSERT INTO kutsutud_rollid_syndmusel (syndmuse_id, rollinimi) VALUES (1, 'treener');

-- Koosta päring leidmaks isikute perekonnanimed, kel on vaja tulla 2016 aasta vana-aasta-õhtul Tammega staadionile

SELECT * FROM harjutuskohad WHERE kommentaar LIKE '%Tamme%';

SELECT * FROM harjutuskohad 
  JOIN syndmused ON syndmused.harjutuskoha_id=harjutuskohad.id
  WHERE kommentaar LIKE '%Tamme%' AND DATE(aeg)='2016-12-31';

SELECT * FROM harjutuskohad 
  JOIN syndmused ON syndmused.harjutuskoha_id=harjutuskohad.id
  JOIN klubid ON syndmused.klubinimi=klubid.nimetus
  WHERE kommentaar LIKE '%Tamme%' AND DATE(aeg)='2016-12-31';


SELECT * FROM harjutuskohad 
  JOIN syndmused ON syndmused.harjutuskoha_id=harjutuskohad.id
  JOIN klubid ON syndmused.klubinimi=klubid.nimetus
  JOIN isik_klubis ON isik_klubis.klubinimi=klubid.nimetus
  WHERE kommentaar LIKE '%Tamme%' AND DATE(aeg)='2016-12-31';
  
SELECT * FROM harjutuskohad 
  JOIN syndmused ON syndmused.harjutuskoha_id=harjutuskohad.id
  JOIN klubid ON syndmused.klubinimi=klubid.nimetus
  JOIN isik_klubis ON isik_klubis.klubinimi=klubid.nimetus
  JOIN isikud ON isik_klubis.isiku_id=isikud.id
  WHERE kommentaar LIKE '%Tamme%' AND DATE(aeg)='2016-12-31';

SELECT perekonnanimi FROM harjutuskohad 
  JOIN syndmused ON syndmused.harjutuskoha_id=harjutuskohad.id
  JOIN klubid ON syndmused.klubinimi=klubid.nimetus
  JOIN isik_klubis ON isik_klubis.klubinimi=klubid.nimetus
  JOIN isikud ON isik_klubis.isiku_id=isikud.id
  WHERE kommentaar LIKE '%Tamme%' AND DATE(aeg)='2016-12-31'
   AND roll IN 
   (SELECT rollinimi FROM kutsutud_rollid_syndmusel 
     WHERE syndmuse_id=syndmused.id);