---------------------------------------------------------------
-- Grade Book Database; Create Tables Script
-- Chapter 2; Oracle 9i Programming -- A Primer
--            by R. Sunderraman
---------------------------------------------------------------

drop table member cascade constraints;
create table member (
  mid             varchar2 (7),
  password        varchar2 (8) not null,
  fname           varchar2 (15) not null,
  lname           varchar2 (15) not null,
  address         varchar2 (50),
  email           varchar2(30),
  cash_balance    number (10,2) not null,
  primary key (mid)
);

drop sequence m_seq;
create sequence m_seq start with 10000;

drop table security cascade constraints;
create table security (
  symbol              varchar2 (8),
  cname               varchar2 (30),
  current_price       number (7,3)  check (current_price >= 0.0) not null,
  ask_price           number (7,3)  check (ask_price >= 0.0),
  bid_price           number (7,3)  check (bid_price >= 0.0),
  primary key (symbol)
);

drop table analyst cascade constraints;
create table analyst (
  aid             varchar2 (4),
  name            varchar2 (15)  not null,
  password        varchar2 (8),
  primary key (aid)
);

drop sequence a_seq;
create sequence a_seq start with 100;

drop table rating cascade constraints;
create table rating (
  aid             varchar2(4),
  symbol          varchar2 (8),
  rating          number  check (rating in (1,2,3,4,5)) not null,
  primary key (aid, symbol),
  foreign key (aid) references analyst,
  foreign key (symbol) references security
);

drop table transaction cascade constraints;
create table transaction (
  mid              varchar2 (7),
  symbol           varchar2 (8),
  trans_date       date,
  trans_type       varchar2 (20) not null,
  quantity         number (7,2) not null,
  price_per_share  number (7,3)  check (price_per_share >= 0.0) not null,
  commission       number (5,2)  check (commission >= 0.0) not null,
  amount           number (8,2)  check (amount >= 0.0) not null,
  primary key (mid, symbol, trans_date),
  foreign key (mid) references member,
  foreign key (symbol) references security
);


drop view buy_transaction;
create view buy_transaction as
  select mid, symbol, sum(quantity) total
  from transaction 
  where trans_type = 'buy'
  group by mid, symbol;

drop view sell_transaction;
create view sell_transaction as
  select mid, symbol, sum(quantity) total
  from transaction 
  where trans_type = 'sell'
  group by mid, symbol;

drop view portfolio;
create view portfolio as
  (select b.mid, b.symbol, (b.total - s.total) quantity
   from buy_transaction b, sell_transaction s
   where b.mid = s.mid and 
         b.symbol = s.symbol and
         (b.total - s.total) > 0)
  union
  (select b.mid, b.symbol, b.total  quantity
   from buy_transaction b
   where not exists (
                select * 
                from sell_transaction s
                where b.mid = s.mid and 
                      b.symbol = s.symbol));

