XV8RNMSB

1DROP DATABASE if exists VereinsDB;
2CREATE DATABASE VereinsDB;
3USE VereinsDB;
4
5SET FOREIGN_KEY_CHECKS=0;
6
7CREATE TABLE s_sportler (
8	s_sportler_nr decimal(6,0) PRIMARY KEY NOT NULL,
9	s_name varchar(20) NOT NULL,
10	s_vorname varchar(20) NOT NULL,
11	s_geb_datum date,
12	s_geschlecht varchar(1),
13	s_t_team_nr integer(8) NOT NULL
14) engine = InnoDB;
15
16
17CREATE TABLE t_teams (
18	t_team_nr Integer(8) PRIMARY KEY NOT NULL,
19    t_teambezeichnung Varchar(20),
20    t_s_teamkaptain decimal(6,0) NOT NULL
21) engine = InnoDB;
22
23
24CREATE TABLE w_wettkaempfe(
25	w_t_team_nr_hf integer(8) NOT NULL,
26	w_t_team_nr_hg integer(8) NOT NULL,
27	w_datum date,
28	ergebnis varchar(6),
29
30	PRIMARY KEY(w_t_team_nr_hf, w_t_team_nr_hg, w_datum)
31) engine = InnoDB;
32
33ALTER TABLE s_sportler ADD FOREIGN KEY (s_t_team_nr) REFERENCES t_teams(t_team_nr);
34ALTER TABLE t_teams ADD FOREIGN KEY(t_s_teamkaptain) REFERENCES d_dept(s_sportler_nr);
35ALTER TABLE w_wettkaempfe ADD FOREIGN KEY(w_t_team_nr_hf) REFERENCES t_teams(t_team_nr);
36ALTER TABLE w_wettkaempfe ADD FOREIGN KEY(w_t_team_nr_hg) REFERENCES t_teams(t_team_nr);
37
38
39INSERT INTO s_sportler VALUES (127369, 'SCHMIDT', 'KLAUS', '1988-12-17', 'm', 44553022);
40INSERT INTO s_sportler VALUES (217499, 'SAMMER', 'MARTINA', '1982-1-25', 'w', 56243640);
41INSERT INTO s_sportler VALUES (257521, 'GEIER', 'PAUL', '1983-4-18', 'm', 12658400);
42INSERT INTO s_sportler VALUES (327566, 'HEILER', 'LUKAS', '1980-8-6', 'm', 56243640);
43INSERT INTO s_sportler VALUES (117654, 'BERNDT', 'MARIA', '1981-3-5', 'w', 56243640);
44INSERT INTO s_sportler VALUES (147698, 'SCHMIDT', 'MARIUS', '1982-2-11', 'm', 62321233);
45INSERT INTO s_sportler VALUES (207782, 'WALLA', 'HERBERT','1981-6-29', 'm', 12658400);
46INSERT INTO s_sportler VALUES (317788, 'KOLLER', 'CLAUDIA', '1983-7-31', 'w', 62321233);
47INSERT INTO s_sportler VALUES (156829, 'PETRI', 'WALTER', '1987-6-18', 'm', 44553022);
48INSERT INTO s_sportler VALUES (436512, 'BARI', 'JOSEFA', '1986-4-24', 'w', 44553022);
49INSERT INTO s_sportler VALUES (137839, 'SCHMOLL', 'TINA', '1982-9-17', 'w', 12658400);
50INSERT INTO s_sportler VALUES (167844, 'BERGER', 'PETER', '1981-10-7', 'm', 62321233);
51INSERT INTO s_sportler VALUES (207876, 'SCHLAGER', 'HANS', '1980-11-23', 'm', 62321233);
52
53INSERT INTO t_teams VALUES
54	(12658400, 'Flinke Haudegen', 137839),
55    (56243640, 'Kavallerie', 117654),
56    (63221233, 'Die vier Musketiere', 167844),
57    (44553022, 'Florettfreunde', 436512);
58    
59INSERT INTO w_wettkaempfe VALUES
60	(56243640, 44553022, '2011-11-20', '45:28'),
61    (56243640, 63221233, '2011-11-20', '39:45'),
62    (63221233, 12658400, '2011-11-12', '45:43'),
63    (12658400, 44553022, '2011-10-25', '33:45'),
64    (56243640, 12658400, '2011-12-14', '45:42');