bAvnESj1

1-- phpMyAdmin SQL Dump
2-- version 4.4.13.1deb1
3-- http://www.phpmyadmin.net
4--
5-- Host: localhost
6-- Generation Time: Jan 09, 2017 at 12:39 PM
7-- Server version: 5.6.31-0ubuntu0.15.10.1
8-- PHP Version: 5.6.11-1ubuntu3.4
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8mb4 */;
18
19--
20-- Database: `Quartalsnoten`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Table structure for table `Fach`
27--
28
29CREATE TABLE IF NOT EXISTS `Fach` (
30  `FachID` int(11) NOT NULL DEFAULT '0',
31  `Fachname` char(25) DEFAULT NULL
32) ENGINE=InnoDB DEFAULT CHARSET=latin1;
33
34-- --------------------------------------------------------
35
36--
37-- Table structure for table `Klasse`
38--
39
40CREATE TABLE IF NOT EXISTS `Klasse` (
41  `KlassenID` int(11) NOT NULL DEFAULT '0',
42  `Ausbildungsberuf` char(30) DEFAULT NULL,
43  `LehrerID` int(11) NOT NULL DEFAULT '0'
44) ENGINE=InnoDB DEFAULT CHARSET=latin1;
45
46-- --------------------------------------------------------
47
48--
49-- Table structure for table `Lehrer`
50--
51
52CREATE TABLE IF NOT EXISTS `Lehrer` (
53  `LehrerID` int(11) NOT NULL DEFAULT '0',
54  `Nachname` char(20) DEFAULT NULL,
55  `Mail` char(40) DEFAULT NULL
56) ENGINE=InnoDB DEFAULT CHARSET=latin1;
57
58-- --------------------------------------------------------
59
60--
61-- Table structure for table `Notenliste`
62--
63
64CREATE TABLE IF NOT EXISTS `Notenliste` (
65  `SchülerID` int(11) NOT NULL DEFAULT '0',
66  `FachID` int(11) NOT NULL DEFAULT '0',
67  `Note` int(11) DEFAULT NULL
68) ENGINE=InnoDB DEFAULT CHARSET=latin1;
69
70-- --------------------------------------------------------
71
72--
73-- Table structure for table `Schüler`
74--
75
76CREATE TABLE IF NOT EXISTS `Schüler` (
77  `SchülerID` int(11) NOT NULL DEFAULT '0',
78  `Nachname` char(20) DEFAULT NULL,
79  `Vorname` char(20) DEFAULT NULL,
80  `Geburtdatum` date DEFAULT NULL,
81  `Geburtsort` char(40) DEFAULT NULL,
82  `Telefonnummer` char(20) DEFAULT NULL,
83  `KlassenID` int(11) NOT NULL DEFAULT '0'
84) ENGINE=InnoDB DEFAULT CHARSET=latin1;
85
86-- --------------------------------------------------------
87
88--
89-- Table structure for table `Unterricht`
90--
91
92CREATE TABLE IF NOT EXISTS `Unterricht` (
93  `KlassenID` int(11) NOT NULL DEFAULT '0',
94  `LehrerID` int(11) NOT NULL DEFAULT '0',
95  `FachID` int(11) NOT NULL DEFAULT '0'
96) ENGINE=InnoDB DEFAULT CHARSET=latin1;
97
98--
99-- Indexes for dumped tables
100--
101
102--
103-- Indexes for table `Fach`
104--
105ALTER TABLE `Fach`
106  ADD PRIMARY KEY (`FachID`);
107
108--
109-- Indexes for table `Klasse`
110--
111ALTER TABLE `Klasse`
112  ADD PRIMARY KEY (`KlassenID`,`LehrerID`),
113  ADD KEY `LehrerID` (`LehrerID`);
114
115--
116-- Indexes for table `Lehrer`
117--
118ALTER TABLE `Lehrer`
119  ADD PRIMARY KEY (`LehrerID`);
120
121--
122-- Indexes for table `Notenliste`
123--
124ALTER TABLE `Notenliste`
125  ADD PRIMARY KEY (`SchülerID`,`FachID`),
126  ADD KEY `FachID` (`FachID`);
127
128--
129-- Indexes for table `Schüler`
130--
131ALTER TABLE `Schüler`
132  ADD PRIMARY KEY (`SchülerID`,`KlassenID`),
133  ADD KEY `KlassenID` (`KlassenID`);
134
135--
136-- Indexes for table `Unterricht`
137--
138ALTER TABLE `Unterricht`
139  ADD PRIMARY KEY (`KlassenID`,`LehrerID`,`FachID`),
140  ADD KEY `LehrerID` (`LehrerID`),
141  ADD KEY `FachID` (`FachID`);
142
143--
144-- Constraints for dumped tables
145--
146
147--
148-- Constraints for table `Klasse`
149--
150ALTER TABLE `Klasse`
151  ADD CONSTRAINT `Klasse_ibfk_1` FOREIGN KEY (`LehrerID`) REFERENCES `Lehrer` (`LehrerID`);
152
153--
154-- Constraints for table `Notenliste`
155--
156ALTER TABLE `Notenliste`
157  ADD CONSTRAINT `Notenliste_ibfk_1` FOREIGN KEY (`SchülerID`) REFERENCES `Schüler` (`SchülerID`),
158  ADD CONSTRAINT `Notenliste_ibfk_2` FOREIGN KEY (`FachID`) REFERENCES `Fach` (`FachID`);
159
160--
161-- Constraints for table `Schüler`
162--
163ALTER TABLE `Schüler`
164  ADD CONSTRAINT `Schüler_ibfk_1` FOREIGN KEY (`KlassenID`) REFERENCES `Klasse` (`KlassenID`);
165
166--
167-- Constraints for table `Unterricht`
168--
169ALTER TABLE `Unterricht`
170  ADD CONSTRAINT `Unterricht_ibfk_1` FOREIGN KEY (`LehrerID`) REFERENCES `Lehrer` (`LehrerID`),
171  ADD CONSTRAINT `Unterricht_ibfk_2` FOREIGN KEY (`KlassenID`) REFERENCES `Klasse` (`KlassenID`),
172  ADD CONSTRAINT `Unterricht_ibfk_3` FOREIGN KEY (`FachID`) REFERENCES `Fach` (`FachID`);
173
174/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
175/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
176/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;