Lehrstuhl für Praktische Informatik III - Database Management Systems

::  Zurück ::

 

:: Schema für TERRA

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

primary key (Name) 
);


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

primary key (Name) 
);


create table See
(
Name            CHAR(20)        NOT NULL,
Tiefe           DECIMAL(14,2),
Flaeche         INT, 

primary key (Name) 
);


create table Meer
(
Name            CHAR(25)        NOT NULL,
Tiefe           DECIMAL(14,2),

primary key (Name) 
);


create table Fluss
(
Name            CHAR(20)        NOT NULL,
Fluss           CHAR(20),
See             CHAR(20),
Meer            CHAR(25),
Laenge          DECIMAL(14,2),
LaengeU         DECIMAL(14,2),
BreiteU         DECIMAL(14,2), 
LaengeM         DECIMAL(14,2),
BreiteM         DECIMAL(14,2), 

primary key (Name) 
);


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

primary key (Name) 
);


create table Kontinent
(
Name            CHAR(10)        NOT NULL,
Flaeche         DECIMAL(4,2), 

primary key (Name) 
);


create table Stadt 
(
Name            CHAR(25)        NOT NULL,
L_ID            CHAR(4)         NOT NULL,
LT_ID           CHAR(4)         NOT NULL,
Einwohner       DECIMAL(14,2), 
Laenge          DECIMAL(14,2), 
Breite          DECIMAL(14,2), 

primary key (Name, L_ID, LT_ID) 
);


create table Landesteil
(
Name            CHAR(30)        NOT NULL,
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Einwohner       DECIMAL(14,2),
Lage            CHAR(2), 
Hauptstadt      CHAR(25), 
);


create table Land
(Name           CHAR(32)        NOT NULL,
L_Id            CHAR(4)         NOT NULL,
Einwohner       DECIMAL(14,2), 
Zuwachs         DECIMAL(14,2), 
Flaeche         DECIMAL(14,2), 
BSP             DECIMAL(14,2), 
Hauptstadt      CHAR(25)        NOT NULL,
LT_ID           CHAR(4)         NOT NULL,
Staatsform      CHAR(35),
Regierungschef  CHAR(70),

primary key (Name,L_ID, Hauptstadt, LT_ID) 
);


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

primary key (Abkuerzung) 
);


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


create table benachbart
(
Land1           CHAR(4)         NOT NULL, 
Land2           CHAR(4)         NOT NULL,
 
primary key(Land1,Land2) 
);


create table geht_ueber
(
Meer1           CHAR(25)        NOT NULL,
Meer2           CHAR(25)        NOT NULL,

primary key(Meer1,Meer2) 
);


create table geo_Berg
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Berg            CHAR(20)        NOT NULL,
 
primary key (LT_ID,L_ID,Berg) 
);


create table geo_Ebene
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Ebene           CHAR(25)        NOT NULL, 

primary key (LT_ID, L_ID, Ebene) 
);


create table geo_Fluss
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Fluss           CHAR(20)        NOT NULL, 

primary key (LT_ID ,L_ID, Fluss) 
);


create table geo_Insel
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Insel           CHAR(25)        NOT NULL, 

primary key (LT_ID ,L_ID, Insel) 
);


create table geo_Meer
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Meer            CHAR(25)        NOT NULL,

primary key (LT_ID, L_ID, Meer) 
);


create table geo_See
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
See             CHAR(20)        NOT NULL,

primary key(LT_ID, L_ID, See) 
);


create table geo_Wueste
(
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Wueste          CHAR(25)        NOT NULL,

primary key (LT_ID, L_ID, Wueste) 
);


create table ist_Mitglied
(
Land            CHAR(4)         NOT NULL,
Organisation    CHAR(20)        NOT NULL,
Art             CHAR(25),

primary key (Land,Organisation) 
);


create table hat_Sitz_in
(
Stadt           CHAR(25)        NOT NULL,
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Organisation    CHAR(20)        NOT NULL,

primary key (Stadt, LT_ID, L_ID, Organisation) 
);


create table liegt_an
(
Stadt           CHAR(25)        NOT NULL,
LT_ID           CHAR(4)         NOT NULL,
L_ID            CHAR(4)         NOT NULL,
Fluss           CHAR(20),
See             CHAR(20),
Meer            CHAR(25)
);


create table umfasst
(
Land            CHAR(4)         NOT NULL,
Kontinent       CHAR(10)        NOT NULL,
Prozent         DECIMAL(14,2)   NOT NULL
);