rem
rem  +---------------------------------------------------------------------+
rem  |  COUNTYLIBRARY.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the County Library Database	   	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Library(
libraryNo NUMBER(8) NOT NULL, 
libStreet VARCHAR2(40) NOT NULL, 
libCity VARCHAR2(15) NOT NULL, 
libState VARCHAR2(15) NOT NULL, 
libZipCode VARCHAR2(9) NOT NULL, 
libTelNo VARCHAR2(15) NOT NULL UNIQUE, 
libFaxNo VARCHAR2(15) NOT NULL UNIQUE, 
libWebAddress VARCHAR2(100), 
managerEmployeeNo NUMBER(8),
PRIMARY KEY (libraryNo)
);

CREATE TABLE Employee(
employeeNo NUMBER(8) NOT NULL, 
title VARCHAR2(10) NOT NULL, 
firstName VARCHAR2(30) NOT NULL, 
middleName VARCHAR2(30), 
lastName VARCHAR2(30) NOT NULL, 
address VARCHAR2(80) NOT NULL, 
workTelExt VARCHAR2(4) NOT NULL, 
homeTelNo VARCHAR2(20) NOT NULL, 
empEmailAddress VARCHAR2(100), 
socialSecurityNumber VARCHAR2(12) NOT NULL UNIQUE, 
DOB DATE NOT NULL, 
position VARCHAR2(20) NOT NULL, 
sex CHAR NOT NULL, 
salary NUMBER(8,2) NOT NULL, 
dateStarted DATE NOT NULL,
libraryNo NUMBER(8) NOT NULL,
PRIMARY KEY (employeeNo),
CONSTRAINT librarynumber FOREIGN KEY (libraryNo) REFERENCES Library(libraryNo)
);

ALTER TABLE Library
ADD CONSTRAINT manageremployeenumber FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo);

CREATE TABLE Publisher(
publisherNo NUMBER(8) NOT NULL, 
publisherName VARCHAR2(50) NOT NULL UNIQUE, 
publisherStreet VARCHAR2(40) NOT NULL, 
publisherCity VARCHAR2(15) NOT NULL, 
publisherState VARCHAR2(15) NOT NULL, 
publisherZipCode VARCHAR2(9) NOT NULL, 
pubTelNo VARCHAR2(20) NOT NULL UNIQUE, 
pubFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
pubWebAddress VARCHAR2(100), 
contactName VARCHAR2(50), 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100), 
creditRating CHAR,
PRIMARY KEY (publisherNo)
);

CREATE TABLE Artist(
artistNo NUMBER(8) NOT NULL, 
name VARCHAR2(50) NOT NULL,
PRIMARY KEY (artistNo)
);

CREATE TABLE Author(
authorNo NUMBER(8) NOT NULL, 
name VARCHAR2(50) NOT NULL,
PRIMARY KEY (authorNo)
);

CREATE TABLE BookCategory(
bookCategoryNo NUMBER(2) NOT NULL, 
bookCatDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (bookCategoryNo)
);

CREATE TABLE Book(
ISBN VARCHAR2(13) NOT NULL, 
title VARCHAR2(100) NOT NULL, 
year NUMBER(4) NOT NULL, 
publisherNo NUMBER(8) NOT NULL, 
bookCategoryNo NUMBER(2) NOT NULL,
PRIMARY KEY (ISBN),
CONSTRAINT publishernumber FOREIGN KEY (publisherNo) REFERENCES Publisher(publisherNo),
CONSTRAINT bookcategorynumber FOREIGN KEY (bookCategoryNo) REFERENCES BookCategory(bookCategoryNo) 
);

CREATE TABLE BookAuthor(
ISBN VARCHAR2(13) NOT NULL, 
authorNo NUMBER(8) NOT NULL,
PRIMARY KEY (ISBN, authorNo),
CONSTRAINT isbnnumber FOREIGN KEY (ISBN) REFERENCES Book(ISBN),
CONSTRAINT authornumber FOREIGN KEY (authorNo) REFERENCES Author(authorNo) 
);

CREATE TABLE BookCopy(
catalogNo NUMBER(8) NOT NULL, 
bookShelfNo NUMBER(8) NOT NULL UNIQUE, 
ISBN VARCHAR2(13) NOT NULL, 
dateInStock DATE NOT NULL, 
libraryNo NUMBER(8) NOT NULL,
PRIMARY KEY (catalogNo),
CONSTRAINT isbnnumber1 FOREIGN KEY (ISBN) REFERENCES Book(ISBN),
CONSTRAINT librarynumber1 FOREIGN KEY (libraryNo) REFERENCES Library(libraryNo)
);

CREATE TABLE CDCategory(
cdCategoryNo NUMBER(2) NOT NULL, 
cdCatDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (cdCategoryNo)
);

CREATE TABLE CD(
cdNo NUMBER(8) NOT NULL, 
title VARCHAR2(100) NOT NULL, 
releaseDate DATE NOT NULL, 
cdCategoryNo NUMBER(2) NOT NULL,
PRIMARY KEY (cdNo),
CONSTRAINT cdcategorynumber FOREIGN KEY (cdCategoryNo) REFERENCES CDCategory(cdCategoryNo) 
);

CREATE TABLE CDArtist(
cdNo NUMBER(8) NOT NULL, 
artistNo NUMBER(8) NOT NULL,
PRIMARY KEY (cdNo, artistNo),
CONSTRAINT cdnumber FOREIGN KEY (cdNo) REFERENCES CD(cdNo),
CONSTRAINT artistnumber FOREIGN KEY (artistNo) REFERENCES Artist(artistNo) 
);

CREATE TABLE CDCopy(
catalogNo NUMBER(8) NOT NULL, 
cdRackNo NUMBER(8) NOT NULL UNIQUE, 
cdNo NUMBER(8) NOT NULL, 
dateInStock DATE NOT NULL, 
libraryNo NUMBER(8) NOT NULL,
PRIMARY KEY (catalogNo), 
CONSTRAINT cdnumber1 FOREIGN KEY (cdNo) REFERENCES CD(cdNo),
CONSTRAINT librarynumber2 FOREIGN KEY (libraryNo) REFERENCES Library(libraryNo)
);

CREATE TABLE Member(
memberNo NUMBER(8) NOT NULL, 
memTitle VARCHAR2(10) NOT NULL, 
memFirstName VARCHAR2(30) NOT NULL, 
memMiddleName VARCHAR2(30) NOT NULL, 
memLastName VARCHAR2(30) NOT NULL, 
memAddress VARCHAR2(80) NOT NULL, 
memWorkTelExt VARCHAR2(4), 
memHomeTelNo VARCHAR2(20), 
memDOB DATE NOT NULL, 
memSex CHAR NOT NULL, 
dateJoined DATE NOT NULL, 
libraryNo NUMBER(8) NOT NULL,
PRIMARY KEY (memberNo),
CONSTRAINT librarynumber3 FOREIGN KEY (libraryNo) REFERENCES Library(libraryNo)
);

CREATE TABLE Loan(
catalogNo NUMBER(8) NOT NULL, 
memberNo NUMBER(8) NOT NULL, 
dateOut DATE NOT NULL, 
dateReturn DATE NOT NULL,
PRIMARY KEY (catalogNo, memberNo),
CONSTRAINT catalognumber FOREIGN KEY (catalogNo) REFERENCES BookCopy(catalogNo), 
CONSTRAINT catalognumber1 FOREIGN KEY (catalogNo) REFERENCES CDCopy(catalogNo),
CONSTRAINT membernumber FOREIGN KEY (memberNo) REFERENCES Member(memberNo)
);