CREATE TABLE PUBLISHER
(
  publisher_id int,
  publisher_name char (50),
  CONSTRAINT pk_publisher PRIMARY KEY (publisher_id)
);

CREATE TABLE Sale
(
  sale_id int,
  customer_numb int,
  sale_date date,
  sale_total_amt decimal (8,2),
  credit_card_numb char (20),
  exp_month int,
  exp_year int,
  CONSTRAINT pk_sale PRIMARY KEY (sale_id),
	CONSTRAINT SaleCustomer FOREIGN KEY (customer_numb) REFERENCES Customer
);

CREATE TABLE CUSTOMER
(
  customer_numb int,
  first_name varchar (30),
  last_name varchar (30),
  street varchar (50),
  city varchar (30),
  state_province char (2),
  zip_postcode char (10),
  contact_phone char (12),
  CONSTRAINT pk_customer PRIMARY KEY (customer_numb)
);

CREATE TABLE BOOK
(
  ISBN char (17),
  work_numb int,
  publisher_id int,
  edition int,
  binding char (20),
  copyright_year char (4),
  CONSTRAINT pk_book PRIMARY KEY (ISBN),
  CONSTRAINT PublisherBook FOREIGN KEY (publisher_id) REFERENCES Publisher,
CONSTRAINT workBook FOREIGN KEY (work_numb) REFERENCES Work
);

CREATE TABLE VOLUME
(
  inventory_id int,
  ISBN char (17),
  condition_code int,
  date_acquired date,

  asking_price decimal (7,2),
  selling_price decimal (7,2),
  sale_id int,
  CONSTRAINT pk_volume PRIMARY KEY (inventory_id),
  CONSTRAINT BookVolume FOREIGN KEY (ISBN) REFERENCES Book,
CONSTRAINT ConditionVolume FOREIGN KEY (condition_code) REFERENCES Condition_codes,
CONSTRAINT SaleVolume FOREIGN KEY (sale_id) REFERENCES Sale
);

CREATE TABLE CONDITION_CODES
(
  condition_code int,
  condition_description varchar (128),
  CONSTRAINT pk_condition_codes PRIMARY KEY (condition_code)
);

CREATE TABLE WORK
(
  work_numb int,
  author_numb int,
  title char (50),
  CONSTRAINT pk_work PRIMARY KEY (work_numb),
CONSTRAINT AuthorWork FOREIGN KEY (author_numb) REFERENCES Author

);

CREATE TABLE author
(
author_numb int,
author_last_first char (128),
CONSTRAINT pk_author PRIMARY KEY (author_numb)
);