use cgt356inst;

CREATE TABLE Department(
DepartmentID    varchar(10)  PRIMARY KEY
);

CREATE TABLE Course(
CourseID        varchar(10),
DepartmentID    varchar(10)  REFERENCES Department(DepartmentID),
Instructor      varchar(60),
Constraint CoursePriKey      PRIMARY KEY(CourseID, DepartmentID)
);

CREATE TABLE BBUser(
UserID          varchar(15)  PRIMARY KEY,
Passwd          varchar(15)  NOT NULL,
FirstName       varchar(30)  NOT NULL,
LastName        varchar(30)  NOT NULL,
Address1        varchar(50)  NOT NULL,
Address2        varchar(50),
City            varchar(60)  NOT NULL,
State           varchar(3)   NOT NULL,
Zip             varchar(10)  NOT NULL,
Email           varchar(100) NOT NULL
);

CREATE TABLE Book(
ItemID          int          PRIMARY KEY,
ISBN            varchar(25)  NOT NULL,
SellerID        varchar(15)  REFERENCES BBUser(UserID),
Price           decimal      NOT NULL,
SoldTo          varchar(15)  REFERENCES BBUser(UserID),
SemesterUsed    varchar(20)  NOT NULL,
Author          varchar(60)  NOT NULL,
Title           varchar(60)  NOT NULL,
BookImage       varchar(120),
BookDescription varchar(200),
Condition       varchar(20)  NOT NULL,
Publisher       varchar(60),
DatePublished   datetime,
Edition         varchar(10),
Binding         varchar(20),
BookSize        varchar(20),
Weight          decimal
);

CREATE TABLE CourseBook(
ItemID          int          REFERENCES Book(ItemID),
CourseID        varchar(10),
DepartmentID    varchar(10),
FOREIGN KEY(CourseID, DepartmentID) REFERENCES Course(CourseID, DepartmentID),
Constraint CourseBookPriKey PRIMARY KEY(ItemID, CourseID, DepartmentID)
);

CREATE TABLE BookBag(
ItemID          int          REFERENCES Book(ItemID),
BuyerID         varchar(15)  REFERENCES BBUser(UserID),
Constraint BookBagPriKey     PRIMARY KEY(ItemID, BuyerID)
);

--DROP TABLE BookBag;
--DROP TABLE CourseBook;
--DROP TABLE Book;
--DROP TABLE Buyer;
--DROP TABLE Seller;
--DROP TABLE Course;
--DROP TABLE Department;

