create table Company
(
   id int not null auto_increment primary key,
   name varchar(20),
   address varchar(50),
   web varchar(50),
   total int,
)
create table User
(
   Id int not null auto_increment primary key,
   login varchar(10),
   pass varchar(20),
   name varchar(50),
   address varchar(50),
   age int,
   cash float
 )
create table Stock
(
   id int not null auto_increment primary key,
   ticker varchar(20),
   company int,
   foreign key (company) references Company(Id)
)
create table Sell
(
   id int not null auto_increment primary key,
   price float,
   volume int,
   stock int,
   user int,
   foreign key (stock) references Stock(id),
   foreign key (user) references User(id)
)
create table Buy
(
   id int not null auto_increment primary key,
   price float,
   volume int,
   stock int,
   user int,
   foreign key (stock) references Stock(id),
   foreign key (user) references User(id)
)
create table Ownership
(
   id int not null auto_increment primary key,
   user int,
   stock int,
   volume int,
   foreign key (stock) references Stock(id),
   foreign key (user) references User(id)
)
create table History
(
   id int not null auto_increment primary key,
   time datetime,
   price float,
   buyer int,
   seller int,
   stock int,
   foreign key (buyer) references User(id),
   foreign key (seller) references User(id),
   foreign key (stock) references Stock(id)
)
insert into Company(name, address, web, total) values ("Barclays", "London Road 3","www.barclays.com", 100); 
insert into Company(name, address, web, total) values ("Scotland Bank", "London Road 1","www.scotlandbank.com", 100);
insert into Company(name, address, web, total) values ("Lloyds", "Abbey Road 3", "www.lloyds.com", 100);
insert into Company(name, address, web, total) values ("Abbey", "Abbey Road 1", "www.abbey.com", 100);
insert into Company(name, address, web, total) values ("Orange", "Welford Road 3", "www.orange.com", 100);
insert into Company(name, address, web, total) values ("Vodafone", "Welford Road 1","www.vodafone.com", 100);
insert into Company(name, address, web, total) values ("O2", "Welford Road 2", "www.o2.com", 100);
insert into User(login, pass, name, address, age, cash) values ("merc", "huecija", "Mercedes Garcia Martinez", 1000);
insert into User(login, pass, name, address, age, cash) values ("stanley", "fung", "Stanley Fung", 1000);
insert into Stock(ticker, company) values ("ABB", 4);
insert into Stock(ticker, company) values ("ORA", 5);
insert into Stock(ticker, company) values ("VOD", 6);
insert into Stock(ticker, company) values ("O2", 7);
insert into Ownership(user, stock, volume) values (1, 1, 10);
insert into Ownership(user, stock, volume) values (1, 2, 5);
insert into Ownership(user, stock, volume) values (1, 3, 10);
insert into Ownership(user, stock, volume) values (2, 4, 10);
insert into Ownership(user, stock, volume) values (2, 5, 10);
insert into Ownership(user, stock, volume) values (2, 6, 10);
insert into Ownership(user, stock, volume) values (2, 1, 5);
insert into Ownership(user, stock, volume) values (2, 7, 5);


