SET SEARCH_PATH TO your_schema_name_goes_here;

/*
-- if you are re-running this script to get a fresh copy of the database
-- you'll want to include these commands
DROP TABLE listing;
DROP TABLE owner;
DROP TABLE book;
*/

CREATE TABLE book (
   isbn       VARCHAR(20),
   title      VARCHAR(500) NOT NULL,
	authors    VARCHAR(500) NOT NULL,
   publisher  VARCHAR(100),
	year       INTEGER,
	PRIMARY KEY(isbn)
);
INSERT INTO book (isbn,title,authors,publisher,year) VALUES
	('007050606X','A Tutorial Introduction to Occam Programming','Pountain, Dick; May, David','McGraw-Hill',1987);
INSERT INTO book (isbn,title,authors,publisher,year) VALUES
	('0262515520','The Stable Marriage Prolbem: Structure and Algorithms','Gusfield, Dan; Irving, Robert W.','The MIT Press',2003);
INSERT INTO book (isbn,title,authors,publisher,year) VALUES
	('0070512949','Parallel Computing: Theory and Practice','Quinn, Michael J.','McGraw Hill',1993);


CREATE TABLE listing (
	listing_id   SERIAL,
	owner_id     INTEGER,
	isbn         VARCHAR(20),
	price        NUMERIC(10,2),
	condition    SMALLINT,    -- 4=excellent, 3=good, 2=fair, 1=poor
	seller_notes VARCHAR(500),
	PRIMARY KEY (listing_id)
);

INSERT INTO listing (listing_id,owner_id,isbn,price,condition,seller_notes) VALUES
	(default,1,'007050606X',20.00,3,'Some notes written in margins but in good shape.');
INSERT INTO listing (listing_id,owner_id,isbn,price,condition,seller_notes) VALUES
	(default,1,'007050606X',15.00,2,'Showing signs of age but still easy to read/use.');
INSERT INTO listing (listing_id,owner_id,isbn,price,condition,seller_notes) VALUES
	(default,2,'0070512949',34.00,4,'Barely used.');
INSERT INTO listing (listing_id,owner_id,isbn,price,condition,seller_notes) VALUES
	(default,2,'007050606X',15.00,3,'Found this at an estate sale!');

SELECT * FROM listings;
