conn system/manager

Create User nushy Identified by haha;
Grant unlimited Tablespace,Resource,Connect to nushy; 

conn nushy/haha


CREATE TABLE customers
(
custId number primary key,
fname varchar2(20) not null,
lname varchar2(20),
cardno varchar(16) unique not null,
pin number(4) not null,
address varchar2(60),
sex varchar(1),
email varchar2(30),
Dob date,
pic varchar2(60),
constraint sex_ck check (sex in ('m','f')) 
);



CREATE TABLE AccountTypes
(
accountTypeId number(2) primary key,
category varchar2(20),
interest number(2)
);

 
CREATE TABLE Accounts 
(
accountNo varchar2(12) primary key,
balance number(10,2),
createdOn date,
custId number,
accountTypeId number(2),
constraint cust_fk foreign key (custid) references customers(custid),
constraint acc_type_fk foreign key (accountTypeId) references AccountTypes(accountTypeId)
);



CREATE TABLE Transactions
(
tId number(6) primary key,
accountNo varchar2(12) ,
tDate date,
tAmount number(10,2),
tnote varchar2(120),
pBalance number(10,2),
constraint Trans_fk foreign key (accountNo) references Accounts (accountNo) 
);

create table bills
(
billid number primary key,
accountNo varchar2(12),
amount number(10,2),
receiver varchar2(20),
Constraint bills_fk Foreign key  (accountNo) references accounts(accountNo)
);

create sequence tId_seq
increment by 1
start with 1000
nomaxvalue 
nominvalue
nocache
nocycle;

create sequence custId_seq
increment by 1
start with 1000
nomaxvalue
nominvalue
nocache
nocycle;

create sequence bill_seq
increment by 1
start with 1000
nomaxvalue
nominvalue
nocache 
nocycle;