create table Client(
bank_code varchar(10),
client_PIN varchar(10),
name varchar(100),
address varchar(100),
constraint Client_key primary key(bank_code,client_PIN));

create table Logical_card(
card_number varchar(10),
password varchar(4),
withdrawal_weekly_limit integer,
bank_code varchar(10) not null,
client_PIN varchar(10) not null,
constraint Logical_card_key primary key(card_number),
constraint Logical_card_unique unique(bank_code,client_PIN),
constraint Client_foreign_key foreign key(bank_code,client_PIN) references Client(bank_code,client_PIN));

create table Plastic_card(
serial_number varchar(10),
card_number varchar(10) not null,
constraint Plastic_card_key primary key(serial_number),
constraint Logical_card_foreign_key foreign key(card_number) references Logical_card(card_number));

create table Accessibility(
bank_code varchar(10),
account_number varchar(10),
card_number varchar(10) not null,
constraint Accessibility_key primary key(bank_code,account_number),
constraint Logical_card_foreign_key2 foreign key(card_number) references Logical_card(card_number));

create table Default_account(
bank_code varchar(10),
account_number varchar(10),
card_number varchar(10) not null,
constraint Default_account_key primary key(bank_code,account_number),
constraint Default_account_unique unique(card_number),
constraint Accessibility_foreign_key foreign key(bank_code,account_number) references Accessibility(bank_code,account_number) on delete cascade,
constraint Logical_card_foreign_key3 foreign key(card_number) references Logical_card(card_number) on delete cascade);

insert into Client values('B1','11-01-1963','Franck Barbier','www.PauWare.com');
insert into Client values('B1','06-12-1993','Oscar Barbier--Darnal','www.PauWare.com');
insert into Client values('B2','15-04-1996','Lena Barbier--Darnal','www.PauWare.com');
insert into Client values('B1','22-02-2001','Joseph Barbier--Darnal','www.PauWare.com');

insert into Logical_card values('LC FB','1101',3000,'B1','11-01-1963');
insert into Logical_card values('LC OBD','0612',1000,'B1','06-12-1993');
insert into Logical_card values('LC LBD','1504',1000,'B2','15-04-1996');
insert into Logical_card values('LC JBD','2202',1000,'B1','22-02-2001');

insert into Plastic_card values('PC1 FB','LC FB');
insert into Plastic_card values('PC2 FB','LC FB');
insert into Plastic_card values('PC OBD','LC OBD');
insert into Plastic_card values('PC LBD','LC LBD');
insert into Plastic_card values('PC JBD','LC JBD');

insert into Accessibility values('B1','0000000001','LC FB');
insert into Accessibility values('B1','0000000002','LC FB');
insert into Accessibility values('B1','0000000003','LC OBD');
insert into Accessibility values('B2','0000000001','LC LBD');
insert into Accessibility values('B1','0000000004','LC JBD');

insert into Default_account values('B1','0000000001','LC FB');
insert into Default_account values('B1','0000000003','LC OBD');
insert into Default_account values('B2','0000000001','LC LBD');
insert into Default_account values('B1','0000000004','LC JBD');

create table ATM(station_code varchar(10),
constraint ATM_key primary key(station_code));

create table GonyVA(station_code varchar(10),
                    serial_number varchar(10),
                    authorisation_date date,
constraint GonyVA_key primary key(station_code),
constraint GonyVA_unique unique(serial_number),
constraint ATM_foreign_key foreign key(station_code) references ATM(station_code),
constraint Plastic_card_foreign_key foreign key(serial_number) references Plastic_card(serial_number));

create table GoaVA(station_code varchar(10),
                   serial_number varchar(10),
constraint GoaVA_key primary key(station_code),
constraint GoaVA_unique unique(serial_number),
constraint ATM_foreign_key2 foreign key(station_code) references ATM(station_code),
constraint Plastic_card_foreign_key2 foreign key(serial_number) references Plastic_card(serial_number));

create table GonyPO(station_code varchar(10),
                    serial_number varchar(10),
                    operation varchar(100),
                    operation_date date,
constraint GonyPO_key primary key(station_code,serial_number,operation),
constraint GoaVA_foreign_key foreign key(station_code) references GoaVA(station_code) on delete cascade,
constraint GoaVA_foreign_key2 foreign key(serial_number) references GoaVA(serial_number) on delete cascade);

create table GoCO(station_code varchar(10),
                  serial_number varchar(10),
                  operation varchar(100),
constraint GoCO_key primary key(station_code,serial_number,operation));

insert into ATM values('0000000001');