Lehrstuhl für Praktische Informatik III - Database Management Systems

::  Zurück ::

 

:: Schema für TERRA2


create table Berg
(
B_ID		SMALLINT	NOT NULL,
Name 		CHAR(20) 	NOT NULL,
Gebirge         CHAR(25),
Hoehe           DECIMAL(14,2),
Jahr            SMALLINT, 
Laenge          DECIMAL(14,2), 
Breite          DECIMAL(14,2), 

primary key(B_ID)
);


create table Ebene
(
E_ID		SMALLINT	NOT NULL,
Name            CHAR(25)        NOT NULL,
Hoehe           DECIMAL(14,2), 
Flaeche         DECIMAL(14,2), 

primary key(E_ID)
);


create table Gewaesser
(
G_ID		SMALLINT	NOT NULL,
Name		CHAR(25)	NOT NULL,

primary key(G_ID)
);


create table See
(
G_ID		SMALLINT	NOT NULL,
Tiefe           DECIMAL(14,2),
Flaeche         DECIMAL(14,2), 

primary key(G_ID),
foreign key(G_ID) references Gewaesser(G_ID) on delete cascade
);


create table Meer
(
G_ID		SMALLINT	NOT NULL,
Tiefe           DECIMAL(14,2),

primary key(G_ID),
foreign key(G_ID) references Gewaesser(G_ID) on delete cascade
);


create table Fluss
(
G_ID		SMALLINT	NOT NULL,
Laenge          DECIMAL(14,2),

primary key(G_ID),
foreign key(G_ID) references Gewaesser(G_ID) on delete cascade
);


create table Insel
(
I_ID		SMALLINT	NOT NULL,
Name            CHAR(25)        NOT NULL,
Inselgruppe     varchar(25),
Flaeche         DECIMAL(14,2), 
Laenge          DECIMAL(14,2),
Breite          DECIMAL(14,2),

primary key(I_ID) 
);


create table Kontinent
(
K_ID		SMALLINT	NOT NULL,
Name            CHAR(10)        NOT NULL,
Flaeche         DECIMAL(14,2), 

primary key(K_ID)  
);


create table Organisation
(
O_ID		SMALLINT	NOT NULL,
Name            varchar(70)     NOT NULL,
Abkuerzung      CHAR(20)        NOT NULL, 

primary key(O_ID) 
);


create table Wueste
(
W_ID		SMALLINT	NOT NULL,
Name            CHAR(25)        NOT NULL,
Flaeche         DECIMAL(14,2),
Wuestenart      CHAR(17),
 
primary key(W_ID) 
);


create table Stadt 
(
S_ID		SMALLINT	NOT NULL,
Name            CHAR(25)        NOT NULL,
Einwohner       INT,
Laenge          DECIMAL(14,2), 
Breite          DECIMAL(14,2), 

primary key(S_ID)
);


create table Land
(
L_ID		SMALLINT	NOT NULL,
Name		CHAR(32)        NOT NULL,
Einwohner       INT,
Zuwachs         DECIMAL(14,2), 
Flaeche         DECIMAL(14,2), 
BSP             DECIMAL(14,2), 
Staatsform      CHAR(35),
Regierungschef  CHAR(70),
Hauptstadt	SMALLINT	NOT NULL,

primary key(L_ID),
foreign key(Hauptstadt) references Stadt(S_ID)
);


create table Landesteil
(
LT_ID		SMALLINT	NOT NULL,
Name            CHAR(30)        NOT NULL,
L_ID            SMALLINT	NOT NULL,
Einwohner       INT,
Lage            CHAR(2), 
Hauptstadt	SMALLINT,

primary key(LT_ID),
foreign key(L_ID) references Land(L_ID)
);



create table Flughafen
(
FH_ID		SMALLINT	NOT NULL,
Code            CHAR(3)         NOT NULL,
S_ID            SMALLINT	NOT NULL,

primary key(FH_ID),
foreign key(S_ID) references Stadt(S_ID)
);


create table flug
(
Flug_ID		SMALLINT	NOT NULL,
FlugNr          CHAR(6)         NOT NULL,
AbflugFH_ID     SMALLINT	NOT NULL,
AnkunftFH_ID    SMALLINT	NOT NULL,
Preis           DECIMAL(10,2)   NOT NULL,

primary key(Flug_ID),
foreign key(AbflugFH_ID) references Flughafen(FH_ID) on delete cascade,
foreign key(AnkunftFH_ID) references Flughafen(FH_ID) on delete cascade
);


create table Flussquelle
(
G_ID		SMALLINT	NOT NULL,
Q_ID		SMALLINT	NOT NULL,
LaengeU		DECIMAL(14,2),
BreiteU		DECIMAL(14,2),

primary key(G_ID, Q_ID),
foreign key(G_ID) references Fluss(G_ID)	
);


create table benachbart
(
L_ID1           SMALLINT         NOT NULL references Land on delete cascade, 
L_ID2           SMALLINT	 NOT NULL references Land on delete cascade,
 
primary key(L_ID1, L_ID2) 
);


create table geht_ueber
(
G_ID1		SMALLINT        NOT NULL references Gewaesser on delete cascade,
G_ID2		SMALLINT        NOT NULL references Gewaesser on delete cascade,

primary key(G_ID1, G_ID2) 
);


create table geo_Berg
(
LT_ID		SMALLINT        NOT NULL references Landesteil on delete cascade,
B_ID            SMALLINT        NOT NULL references Berg on delete cascade,
 
primary key (LT_ID, B_ID) 
);


create table geo_Ebene
(
LT_ID		SMALLINT        NOT NULL references Landesteil on delete cascade,
E_ID		SMALLINT        NOT NULL references Ebene on delete cascade, 

primary key (LT_ID, E_ID) 
);


create table geo_Insel
(
LT_ID           SMALLINT	NOT NULL references Landesteil on delete cascade,
I_ID            SMALLINT        NOT NULL references Insel on delete cascade, 

primary key (LT_ID, I_ID)        
);


create table geo_Gewaesser
(
LT_ID           SMALLINT        NOT NULL references Landesteil on delete cascade,
G_ID            SMALLINT        NOT NULL references Gewaesser on delete cascade,

primary key(LT_ID, G_ID)    
);


create table geo_Wueste
(
LT_ID           SMALLINT        NOT NULL references Landesteil on delete cascade,
W_ID            SMALLINT        NOT NULL references Wueste on delete cascade,

primary key (LT_ID, W_ID)
);


create table ist_Mitglied
(
L_ID		SMALLINT	NOT NULL references Land on delete cascade,
O_ID		SMALLINT	NOT NULL references Organisation on delete cascade,
Art             CHAR(25),

primary key (L_ID, O_ID)
);


create table hat_Sitz_in
(
S_ID		SMALLINT	NOT NULL references Stadt on delete cascade,
O_ID		SMALLINT	NOT NULL references Organisation on delete cascade,

primary key (S_ID, O_ID)
);


create table liegt_an
(
S_ID		SMALLINT	NOT NULL references Stadt on delete cascade,
G_ID		SMALLINT	NOT NULL references Gewaesser on delete cascade,

primary key (S_ID, G_ID)
);


create table umfasst
(
L_ID		SMALLINT	NOT NULL references Land on delete cascade,
K_ID		SMALLINT	NOT NULL references Kontinent on delete cascade,
Prozent         DECIMAL(14,2)   NOT NULL,

primary key (L_ID, K_ID)
);


create table muendet
(
G_ID		SMALLINT	NOT NULL references Gewaesser on delete cascade,
Muendung_G_ID	SMALLINT	NOT NULL references Gewaesser on delete cascade,
LaengeM		DECIMAL(14,2),	
BreiteM		DECIMAL(14,2),

primary key (G_ID,Muendung_G_ID)
);

create table gehoert_LT
(
S_ID		SMALLINT	NOT NULL references Stadt on delete cascade,
LT_ID		SMALLINT	NOT NULL references Landesteil on delete cascade,

primary key (S_ID, LT_ID)
);