---------------------------------------------------------------------------------
-- Abschnitt 8.1
create database EineDB ;
go
use EineDB ;	

-- SQL-Server:
create table buch (
	katalognr int identity (1, 1) primary key ,
	isbn varchar (50) null ,
	titel varchar (50) not null unique,
	kurz text null ,
	autor int null references autor (id)
		on update cascade
		on delete cascade ,
	seiten int null ,
	copyright int null ,
	verlag int null references verlag (id)
		on update cascade
		on delete cascade ,
	preis decimal(8,2) null ,
	kategorie int null references kategorie (id)
		on update cascade
		on delete no action ,
	sprache int null references sprache (id)
		on update cascade
		on delete no action ,
	taschenbuch bit null ) ;
go

create table buch (
	katalognr int identity (1, 1) ,
	isbn varchar (50) null ,
	titel varchar (50) not null ,
	kurz text null ,
	autor int null ,
	seiten int null ,
	copyright int null ,
	verlag int null ,
	preis decimal(8,2) null ,
	kategorie int null ,
	sprache int null ,
	taschenbuch bit null,
	constraint pkbuch primary key ( katalognr ) ,
	constraint uqbuch unique ( titel ) ,
	constraint fkautorbuch foreign key ( autor )
		references autor (id) 
		on update cascade
		on delete cascade ,
	constraint fkverlagbuch foreign key ( verlag )
		references verlag (id)
		on update cascade
		on delete cascade ,
	constraint fkkategoriebuch foreign key ( kategorie )
		references kategorie (id)
		on update cascade
		on delete no action ,
	constraint fksprachebuch foreign key ( sprache )
		references sprache (id)
		on update cascade
		on delete no action ) ;
go

-- PostgreSQL:
create seqids
	increment by 1
	start with 1 cache 25 no cycle
go

create table buch (
	katalognr int primary key default ( nextval ( seqids ) ),
	isbn varchar (50) null ,
	titel varchar (50) not null unique,
	kurz text null ,
	autor int null references autor (id)
		match simple
		on update cascade
		on delete cascade ,
	seiten int null ,
	copyright int null ,
	verlag int null references verlag (id)
		match full
		on update cascade
		on delete cascade ,
	preis decimal(8,2) null ,
	kategorie int null references kategorie (id)
		on update cascade
		on delete no action ,
	sprache int null references sprache (id)
		on update cascade
		on delete no action ,
	taschenbuch bit null ) ;
go

---------------------------------------------------------------------------------
-- Abschnitt 8.5
drop table buch ;

use AdventureWorks2008R2 ;

---------------------------------------------------------------------------------
-- Abschnitt 8.6
create database EineDB ;	
go

use AdventureWorks2012 ;
drop database EineDB ;	
go

alter table wrkBikesAccessories
	add description varchar(max) null,
		year int;
go

create table wrkBikesAccessories (
	ProductID integer primary key,
	Category varchar(100) not null,
	Subcategory varchar(100) not null,
	Product varchar(100) not null unique,
	ProductNumber varchar(100) not null unique,
	ListPrice decimal (8,2) null ,
	constraint FK_Prod_Prod foreign key ( ProductID )
		references Production.Product ( ProductID )
			on delete cascade
			on update cascade
);
go

alter table wrkBikesAccessories
	add constraint ck_wrk_Desc check ( 
			ltrim(description) != '' and year > 1900 ) ,
	    constraint fk_prod_prod foreign key ( ProductID )
	    		references Production.Product ( ProductID )
	    					on delete cascade
	    					on update cascade ;
go

alter table wrkBikesAccessories
	drop  constraint ck_wrk_Desc,
		constraint fk_prod_prod,
		column description,
		column year;
go

alter table  wrkBikesAccessories
	add description varchar(max) null,
		year int,
		constraint ck_wrk_Desc check ( 
			ltrim(description) != '' and year > 1900 ) ,
	    constraint fk_prod_prod foreign key ( ProductID )
	    		references Production.Product ( ProductID )
	    					on delete cascade
	    					on update cascade ;

---------------------------------------------------------------------------------
-- Abschnitt 8.7.3
TRUNCATE TABLE wrkBikesAccessories ;
delete wrkBikesAccessories ;
drop table wrkBikesAccessories ;
go

---------------------------------------------------------------------------------
-- Abschnitt 8.7.4
UPDATE STATISTICS Person.Person ;
go
