Dec
2005
Posted in sql unsere alte shop-datenbank
shop.sql man beachte das Datum im Script 
# Verbindung: local
# Rechner: localhost
# Gespeichert: 2003-12-19 12:06:57
#
#create database shop;
#use shop;
#
create table t_warengruppe
(wgnr tinyint not null auto_increment primary key,
warengruppe varchar(50) not null);
#
create table t_ware
(wnr int not null auto_increment primary key,
wgnr tinyint not null references t_warengruppe(wgnr),
ware varchar(100) not null);
# zeitspanne für lieferung
create table t_verfuegbar
(vnr tinyint not null auto_increment primary key,
spanne varchar(15));
# zeitspanne für ware
create table t_wareverfuegbar
(wnr int not null references t_ware(wnr),
vnr tinyint not null references t_verfuegbar(vnr),
datum date not null,
primary key(wnr,vnr,datum));
#
create table t_warepreis
(wpnr int not null auto_increment primary key,
wnr int not null references t_ware(wnr),
datum date not null,
preis_me numeric(8,2) not null);
#
create table t_bestellung
(bnr int not null auto_increment primary key,
zknr int not null references t_kundenzahlung(zknr),
datum datetime not null,
anr int not null references t_adresse(anr));
#
create table t_bestelldetail
(bnr int not null references t_bestellung(bnr),
wpnr int not null references t_warepreis(wpnr),
menge tinyint not null,
anr int not null references t_adresse(anr),
lanr int not null references t_lieferart(lanr),
primary key(bnr,wpnr,menge,anr));
#
create table t_lieferart
(lanr tinyint not null auto_increment primary key,
lieferart varchar(20));
#
create table t_zahlart
(zanr tinyint not null auto_increment primary key,
zahlart varchar(30) not null);
#
create table t_kundezahlung
(zknr int not null auto_increment primary key,
knr int not null references t_kunde(knr),
zanr tinyint not null references t_zahlart(zanr),
bank varchar(50),
blz char(12),
kk_nr varchar(16),
gueltig_bis char(5));
#
create table t_adresse
(anr int not null auto_increment primary key,
strasse varchar(50) not null,
plz char(5) not null,
ort varchar(30) not null,
knr int not null references t_kunde(knr),
amnr tinyint not null references t_adressmerkmal(amnr));
#
create table t_adressmerkmal
(amnr tinyint not null auto_increment primary key,
merkmal char(10) not null);
#
create table t_kunde
(knr int not null auto_increment primary key,
anrede enum(‘Frau’,'Herr’),
titel varchar(20),
vorname varchar(20),
name varchar(30) not null,
gebdat date not null,
mail varchar(60) not null,
telefon varchar(15) not null,
fax varchar(15),
login varchar(20) not null,
kennwort char(12) not null);
#end
show tables;
