-- A V Peterson   31-07-2002
--
-- COMP2400 2002 Assignment 1
-- Bookstore database
-- ===================================================================
-- Publisher (*PubId, PubName, City, Telephone)
CREATE TABLE Publisher
(
  PubId VARCHAR2(6),
  PubName VARCHAR2(20),
  City VARCHAR2(20),
  Telephone VARCHAR2(16),
  CONSTRAINT Publisher_pk PRIMARY KEY (PubId)
)
/  
-- ==================================================================
-- Author (*AId, Surname, Initials, City, Telephone, Birthdate)
CREATE TABLE Author
(
  AId VARCHAR2(5),
  Surname VARCHAR2(16) NOT NULL,
  Initials VARCHAR2(4),
  City VARCHAR2(20),
  Telephone VARCHAR2(16),
  Birthdate DATE,
  CONSTRAINT Author_pk PRIMARY KEY (AId)
)
/
-- ===================================================================
-- Book (*ISBN, Title, Edition, PubId, PubDate, PubPrice, RetPrice, QtyInStock)
CREATE TABLE Book
(
  ISBN VARCHAR2(16),
  Title VARCHAR2(100) NOT NULL,
  Edition NUMBER(2),
  PubId VARCHAR2(6),
  PubDate DATE,
  PubPrice NUMBER NOT NULL,
  RetPrice NUMBER NOT NULL,
  QtyInStock NUMBER,
  CONSTRAINT Book_pk PRIMARY KEY(ISBN),
  CONSTRAINT Book_Publisher_fk
    FOREIGN KEY(PubId) REFERENCES Publisher(PubId)
)
/
-- ==================================================================   
-- Authorship (*ISBN, *AId)
CREATE TABLE Authorship
(
  ISBN VARCHAR2(16),
  AId VARCHAR2(5),
  CONSTRAINT Authorship_pk PRIMARY KEY (ISBN, AId),
  CONSTRAINT Authorship_Book_fk FOREIGN KEY(ISBN) REFERENCES Book(ISBN),
  CONSTRAINT Authorship_Author_fk
    FOREIGN KEY(AId) REFERENCES Author(AId)
)
/
-- ==================================================================
-- Textbook (*CourseId, *ISBN, *DateReqd, NumReqd, NumArrd)
CREATE TABLE Textbook
(
  CourseId VARCHAR2(8),
  ISBN VARCHAR2(16),
  DateReqd DATE,
  NumReqd NUMBER,
  NumArrd NUMBER,
  CONSTRAINT Textbook_pk PRIMARY KEY (CourseId, ISBN, DateReqd),
  CONSTRAINT Textbook_Book_fk FOREIGN KEY(ISBN) REFERENCES Book(ISBN)
)
/
-- ===================================================================