· 8 years ago · Mar 05, 2018, 10:20 PM
1drop database if exists schuldatenbank;
2create database schuldatenbank;
3
4use schuldatenbank;
5
6#CREATE TABLES#
7 create table s_schueler(
8 s_schnr int not null primary key,
9 s_name varchar(15),
10 s_vorname varchar(10),
11 s_gebdat date,
12 s_adresse varchar(15),
13 s_k_klasse varchar(5) #foreign key
14 )engine = InnoDB;
15
16 create table k_klasse(
17 k_id varchar(5) not null primary key,
18 k_bez varchar(15),
19 k_s_klaspr int, #foreign key
20 k_s_klasprstv int, #foreign key
21 k_l_klavst varchar(5) #foreign key
22 )engine = InnoDB;
23
24 create table l_lehrer(
25 l_id varchar(2) not null primary key,
26 l_name varchar(15),
27 l_vorname varchar(10),
28 l_gebDat date,
29 l_gehalt int,
30 l_l_chef varchar(2) #foreign key
31 )engine = InnoDB;
32
33 create table st_stunde(
34 st_k_klasse varchar(5) not null, #foreign key
35 st_l_lehrer varchar(2), #foreign key
36 st_g_fach varchar(5), #foreign key
37 st_stunde varchar(5) not null,
38 st_r_raum varchar(5),
39 primary key(st_k_klasse,st_stunde)
40 )engine = InnoDB;
41
42 create table p_pruefung(
43 p_datum date not null,
44 p_s_kandidat int not null,
45 p_l_pruefer varchar(2) not null,
46 p_g_fach varchar(5) not null,
47 p_art varchar(3),
48 p_noe int,
49 primary key(p_datum,p_s_kandidat,p_l_pruefer,p_g_fach)
50 )engine = InnoDB;
51
52 create table r_raum(
53 r_id varchar(5) not null primary key,
54 r_plaetze int
55 );
56
57 create table g_gegenstand(
58 g_id varchar(5),
59 g_bez varchar(20)
60 );
61
62 create table v_vorgesetzter(
63 v_l_vorg varchar(2) not null, #foreign key
64 v_art varchar(10) not null,
65 v_l_unt varchar(2) not null,
66 primary key(v_l_vorg,v_art,v_l_unt)
67 )engine = InnoDB;
68
69
70#INSERT VALUES#
71Insert into s_schueler(s_schnr,s_name,s_vorname,s_gebdat,s_adresse,s_k_klasse)
72VALUES('1','Adler','Richard','1966-03-12','Gumpendorf','03TA'),
73('4','Kneippp','Sebastian','1967-12-24','Fuenfhaus','03TA'),
74('16','Geyer','Walli','1966-07-23','Simmering','03TA'),
75('19','Sitzenbleiber','Eusebius','1951-02-22','Schweiz','03TA'),
76('22','Huber','Seppl','1968-02-02','Margareten','03TA'),
77('55','Schulz','Xandl','1964-09-03','Doebling','03TB'),
78('74','Hundertwasser','Friederike','1961-02-02','Landstrasse','03TA'),
79('77','Berger','Balduin','1964-03-13','Favoriten','03TB'),
80('84','Feuerstein','Bebbles','1966-06-12','Steintal','03TB'),
81('88','Mayer','Barbara','1965-05-03','Ottakring','03TB'),
82('111','Sandler','Eberhard','1959-04-19','Hoechststaettpl','03TB'),
83('122','Graf','Bobby','1962-01-01','Doebling','03TA'),
84('1266','Schlager','Ronnie','1963-04-23','Ottakring','03TB');
85
86Insert into k_klasse(k_id,k_bez,k_s_klaspr,k_s_klasprstv,k_l_klavst)
87VALUES('01VL','Vorber.LG','0','0',''),
88('03TA','Kolleg 03ta','1','122','BE'),
89('03TB','Kolleg 03tb','1266','111','BI');
90
91Insert into l_lehrer(l_id,l_name,l_vorname,l_gebDatum,s_adresse,s_k_klasse)
92VALUES('AS','Aschauer','Anton','1961-08-09','180','PI'),
93('AU','Auwald','Herbert','1946-09-07','150','LN'),
94('B1','Berger','Alfred','1945-02-03','400',''),
95('BE','Beringer','Alfred','1961-07-15','220','HA'),
96('BI','Bilek','Hans','1932-03-03','280','HA'),
97('HA','Hanke','Gustav','1950-12-12','300','B1'),
98('LI','Lindner','Kristine','1958-12-12','300','HA'),
99('LN','Lenau','Nikolaus','1938-02-22','180','HA'),
100('MY','Mayer','Walter','1947-04-01','120','LN'),
101('PI','Pirkner','Walter','1955-06-26','220','B1'),
102('PS','Prei','Johann','1956-07-05','82','HA'),
103('SG','Siegel','Heinz','1954-10-05','0',''),
104('WA','Walter','Hans','1949-11-11','115','PI');
105
106Insert into st_stunde(st_k_klasse,st_l_lehrer,st_g_fach,st_stunde,st_r_raum)
107VALUES('03TA','BI','BO','DI1','B1'),
108('03TA','BI','BO','DI2','B1'),
109('03TA','HA','PR','DI3','LA3'),
110('03TA','HA','PR','DI4','LA3'),
111('03TA','HA','PR','DI5','LA3'),
112('03TA','B1','AM','DO1','B1'),
113('03TA','B1','AM','DO2','B1'),
114('03TA','BI','BO','DO3','B1'),
115('03TA','BE','PR','DO4','LA1'),
116('03TA','BE','PR','DO5','LA1'),
117('03TA','PS','TDO','MI1','LA2'),
118('03TA','PS','TDO','MI2','LA2'),
119('03TA','PS','TDO','MI3','B1'),
120('03TA','BI','RWO','MI4','B1'),
121('03TA','BI','RWO','MI5','B1'),
122('03TA','LI','TDO','MO1','B1'),
123('03TA','LI','TDO','MO2','B1'),
124('03TA','LI','TDO','MO3','B1'),
125('03TA','BE','SEP','MO4','B1'),
126('03TA','BE','SEP','MO5','B1'),
127('03TA','PS','PR','MO7','LA1'),
128('03TA','PS','PR','MO8','LA1'),
129('03TB','PS','PR','DI1','LA1'),
130('03TB','PS','PR','DI2','LA1'),
131('03TB','HA','PR','DI3','LA2'),
132('03TB','HA','PR','DI4','LA2'),
133('03TB','HA','PR','DI5','LA2'),
134('03TB','PS','TDO','DO1','B2'),
135('03TB','PS','TDO','DO2','B2'),
136('03TB','B1','AM','DO3','B2'),
137('03TB','B1','AM','DO4','B2'),
138('03TB','BI','RW','MI1','B2'),
139('03TB','BI','RW','MI2','B2'),
140('03TB','BI','RW','MI3','B2'),
141('03TB','LI','TDO','MI4','B2'),
142('03TB','LI','TDO','MI5','B2'),
143('03TB','BI','BO','MO1','B2'),
144('03TB','BI','BO','MO2','B2'),
145('03TB','BI','BO','MO3','B2'),
146('03TB','BE','SEP','MO4','B2'),
147('03TB','BE','SEP','MO5','B2');
148
149Insert into p_pruefung(p_datum,p_s_kandidat,p_l_pruefer,p_g_fach,p_art,p_noe)
150VALUES('1','1988-01-01','PS','TDO','M','1'),
151('1','1989-03-15','AU','BO','121','2'),
152('1','1989-04-01','AS','AM','','2'),
153('1','1989-04-01','HA','AM','M','4'),
154('3','1988-01-12','SG','TDO','M','1'),
155('4','1989-04-01','AS','AM','','5'),
156('19','1988-04-15','SG','TDO','M','5'),
157('22','1988-01-12','PS','TDO','M','2'),
158('22','1989-03-15','AU','BO','121','4'),
159('55','1987-12-24','LI','TDO','M','5'),
160('111','1988-02-06','HA','AM','M','4'),
161('111','1988-06-07','SG','TDO','M','1'),
162('111','1989-03-15','SG','TDO','F','4'),
163('1266','1988-01-04','BI','BO','M','3');
164
165Insert into r_raum(r_id,r_plaetze)
166VALUES('B1','22'),
167('B2','24'),
168('LA1','10'),
169('LA2','18'),
170('LA3','2');
171
172
173#SET FOREIGNKEYS#
174alter table s_schueler add foreign key(s_k_klasse) references k_klasse(k_id)
175
176alter table k_klasse add foreign key (k_s_klspr, k_s_klasprstv) references s_schueler(s_id)
177alter table k_klasse add foreign key(k_s_klspr, k_s_klsprvt) references s_schueler(s_id)
178
179alter table l_lehrer add foreign key(l_l_chef) references lehrer(l_id)
180
181alter table st_stunde add foreign key (st_k_klasse) references k_klasse(k_id);
182alter table st_stunde add foreign key (st_l_lehrer) references l_lehrer(l_id);
183alter table st_stunde add foreign key (st_g_fach) references g_gegenstand(g_id)
184
185
186
187#DELETE/LOAD DATA#
188
189delete from s_schueler;
190delete from k_klasse;
191delete from l_lehrer;
192delete from st_stunde;
193delete from p_pruefung;
194delete from r_raum;
195delete from g_gegenstand;
196delete from v_vorgesetzter;
197
198load data infile 'Data/s_schueler.txt' into table s_schueler;
199load data infile 'Data/k_klasse.txt' into table k_klasse;
200load data infile 'Data/l_lehrer' into table l_lehrer;
201load data infile 'Data/s_stunde' into table st_stunde;
202load data infile 'Data/p_pruefung.txt' into table p_pruefung;
203load data infile 'Data/r_raum.txt' into table r_raum;
204load data infile 'Data/g_gegenstand.txt' into table g_gegenstand;
205load data infile 'Data/v_vorgesetzter' into table v_vorgesetzter;