Lehrstuhl für Praktische Informatik III - Database Management Systems

::  Zurück ::

 

:: Schema für UNIDB

CREATE TABLE Studenten (  
 MatrNr         INTEGER  NOT NULL PRIMARY KEY,  
 Name           VARCHAR(30) NOT NULL,  
 Semester       INTEGER)

CREATE TABLE Professoren  
 (PersNr	INTEGER PRIMARY KEY NOT NULL,  
 Name		VARCHAR(30) NOT NULL,  
 Rang    	CHAR(2) CHECK (Rang in ('C2', 'C3', 'C4')),  
 Raum		INTEGER)

CREATE TABLE Assistenten  
 (PersNr	INTEGER PRIMARY KEY NOT NULL,  
 Name           VARCHAR(30) NOT NULL,  
 Fachgebiet     VARCHAR(30),  
 Boss           INTEGER,  
 FOREIGN KEY    (Boss) REFERENCES Professoren)

CREATE TABLE Vorlesungen  
 (VorlNr	INTEGER PRIMARY KEY NOT NULL,  
 Titel		VARCHAR(30),  
 SWS		INTEGER,  
 gelesenVon	INTEGER REFERENCES Professoren)

CREATE TABLE hoeren  
 (MatrNr	INTEGER REFERENCES Studenten ON DELETE CASCADE NOT NULL,  
 VorlNr		INTEGER REFERENCES Vorlesungen ON DELETE CASCADE NOT NULL,  
 PRIMARY KEY    (MatrNr, VorlNr))
 
CREATE TABLE voraussetzen  
 (Vorgaenger	INTEGER REFERENCES Vorlesungen ON DELETE CASCADE NOT NULL,  
 Nachfolger	INTEGER REFERENCES Vorlesungen ON DELETE CASCADE NOT NULL,  
 PRIMARY KEY    (Vorgaenger, Nachfolger))

CREATE TABLE pruefen  
 (MatrNr	INTEGER REFERENCES Studenten ON DELETE CASCADE NOT NULL,  
 VorlNr		INTEGER REFERENCES Vorlesungen NOT NULL,  
 PersNr    	INTEGER REFERENCES Professoren,  
 Note           NUMERIC(2,1) CHECK (Note between 0.7 and 5.0),  
 PRIMARY KEY    (MatrNr, VorlNr))