Nachricht

  • EU e-Privacy Directive

    This website uses cookies to manage authentication, navigation, and other functions. By using our website, you agree that we can place these types of cookies on your device.

    View e-Privacy Directive Documents

    View GDPR Documents

    You have declined cookies. This decision can be reversed.

SQL-Skript für Kapitel 13 und 14

create table reihen(
  id int generated always as identity primary key,
  name varchar(30) not null unique
);

create table alben( 
  reihe int references reihen, 
  titel varchar(30), 
  band int check(band>=0), 
  preis decimal(4,2) check(preis>=0), 
  jahr int, 
  primary key(reihe, band)
);
create table autoren(
  id int generated always as identity primary key,
  name varchar(20) not null unique
);

create table albenautoren(
  autor int not null references autoren,
  reihe int not null,
  band int not null,
  foreign key(reihe, band) references alben,
  primary key(autor, reihe, band)
);

 

insert into reihen(name) values('Gespenster Geschichten');
insert into reihen(name) values('Asterix');
insert into reihen(name) values('Tim und Struppi');  
insert into reihen(name) values('Franka');
insert into reihen(name) values('Prinz Eisenherz');  

insert into alben values( 1,    'Gespenster Geschichten',    1    ,1.20    ,1974 );
insert into alben values( 2,    'Asterix der Gallier',    1        ,2.80    ,1968 );
insert into alben values( 2,    'Asterix und Kleopatra',    2    ,2.80    ,1968 );
insert into alben values( 2,    'Asterix als Legionär',    10        ,3.00    ,null );
insert into alben values( 2,    'Die Trabantenstadt',    17,        3.80,    1974 );
insert into alben values(2,   'Der große Graben',    25,        5.00,    1980 );
insert into alben values( 3,     'Der geheimnisvolle Stern',    1,        null,    1972 );
insert into alben values( 3,    'Tim und der Haifischsee',    23    ,null,    1973 );
insert into alben values( 4, 'Das Kriminalmuseum',    1,8.80,1985 );
insert into alben values( 4, 'Das Meisterwerk',    2,        8.80,1986 );

insert into autoren(name) values('Uderzo');
insert into autoren(name) values('Goscinny');
insert into autoren(name) values('Hergé');
insert into autoren(name) values('Kuijpers');
insert into autoren(name) values('Franquin');    

insert into albenautoren values( 1,2,1);
insert into albenautoren values( 1,2,2);
insert into albenautoren values( 1,2,10);
insert into albenautoren values( 1,2,17);    
insert into albenautoren values( 1,2,25);    

insert into albenautoren values( 2,2,1);
insert into albenautoren values( 2,2,2);
insert into albenautoren values( 2,2,10);
insert into albenautoren values( 2,2,17);

insert into albenautoren values( 3,3,1);
insert into albenautoren values( 3,3,23);

insert into albenautoren values( 4,4,1);
insert into albenautoren values( 4,4,2);