-- Sample database to illustrate the concept of view and its use

drop table if exists sneakers;
CREATE TABLE Sneakers (
	name	CHAR(20) PRIMARY KEY,
	manf	CHAR(20)
);

drop table if exists buyers;
CREATE TABLE Buyers (
	name	CHAR(20) PRIMARY KEY,
	addr    CHAR(20),
	phone   CHAR(15)
);

drop table if exists stores;
CREATE TABLE Stores (
	name	CHAR(20) PRIMARY KEY,
	addr    CHAR(20),
	owner   CHAR(20)
);

drop table if exists sells;
CREATE TABLE Sells (
	store	CHAR(20),
	sneaker	CHAR(20) check ( sneaker in ('Nike', 'Adidas', 'AirJordan', 'LiNing')),
	price	REAL check (store='Joe''s Store' or price <= 50.00),
	FOREIGN KEY(sneaker) REFERENCES Sneakers(name)
	on delete set null
	on update cascade,
	FOREIGN KEY(store) REFERENCES Stores(name)
	on delete set null
	on update cascade,
	primary key (store, sneaker)
);

drop table if exists Likes;
create table Likes (
   buyer char(20),
   sneaker char(20),
   primary key (buyer, sneaker)
);

drop table if exists BuysAt;
create table BuysAt (
   buyer char(20),
   store char(20),
   primary key (buyer, store)
);


