-- replace 'barbier' by your own account id. within Oracle
create schema authorization barbier
create table ATM(station_code char(10),
constraint ATM_key primary key(station_code))
create table Client(bank_code char(10),
                    client_PIN char(10),
                    name char(100),
                    address char(100),
constraint Client_key primary key(bank_code,client_PIN))
create table Logical_card(card_number char(10),
                          password char(4),
                          withdrawal_weekly_limit number(5),
                          bank_code char(10),
                          client_PIN char(10),
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) on delete cascade)
create table Plastic_card(serial_number char(10),
                          card_number char(10),
constraint Plastic_card_key primary key(serial_number),
constraint Logical_card_foreign_key foreign key(card_number) references Logical_card(card_number) on delete cascade)
create table Accessibility(bank_code char(10),
                           account_number char(10),
                           card_number char(10),
constraint Accessibility_key primary key(bank_code,account_number),
constraint Logical_card_foreign_key2 foreign key(card_number) references Logical_card(card_number) on delete cascade)
create table Default_account(bank_code char(10),
                             account_number char(10),
constraint Default_account_key primary key(bank_code,account_number),
constraint Accessibility_foreign_key foreign key(bank_code,account_number) references Accessibility(bank_code,account_number) on delete cascade)
create table GonyVA(station_code char(10),
                    serial_number char(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 char(10),
                   serial_number char(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 char(10),
                    serial_number char(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 char(10),
                  serial_number char(10),
                  operation varchar(100),
constraint GoCO_key primary key(station_code,serial_number,operation));
/* create view XXX as select... */
/* grant select on XXX to YYY */

insert into ATM values('0000000001');

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

insert into Logical_card values('0000000001','1101',3000,'0000000000','11-01-1963');
insert into Logical_card values('0000000002','0612',1000,'0000000001','06-12-1993');
insert into Logical_card values('0000000003','1504',1000,'0000000002','15-04-1996');
insert into Logical_card values('0000000004','2202',1000,'0000000003','22-02-2001');

insert into Plastic_card values('0000000001','0000000001');
insert into Plastic_card values('0000000002','0000000001');
insert into Plastic_card values('0000000003','0000000002');
insert into Plastic_card values('0000000004','0000000003');
insert into Plastic_card values('0000000005','0000000004');

insert into Accessibility values('0000000000','0000000001','0000000001');
insert into Accessibility values('0000000000','0000000002','0000000001');
insert into Accessibility values('0000000001','0000000003','0000000002');
insert into Accessibility values('0000000002','0000000001','0000000003');
insert into Accessibility values('0000000002','0000000002','0000000004');

insert into Default_account values('0000000000','0000000002');
insert into Default_account values('0000000001','0000000003');
insert into Default_account values('0000000002','0000000001');
insert into Default_account values('0000000002','0000000002');

commit;


