CREATE TABLE Category
(
Category VARCHAR2(50),
Registration VARCHAR2(50),
CONSTRAINT pk_Category PRIMARY KEY (Category)
);

CREATE TABLE Breed
(
Category VARCHAR2(50),
Breed VARCHAR2(50),
CONSTRAINT pk_Breed PRIMARY KEY (Category,Breed),
CONSTRAINT fk_CategoryBreed FOREIGN KEY (Category)
REFERENCES Category(Category)
ON DELETE CASCADE
);

CREATE TABLE City
(
CityID INTEGER,
ZipCode VARCHAR2(50),
City VARCHAR2(50),
State VARCHAR2(50),
AreaCode VARCHAR2(50),
Population1990 INTEGER
DEFAULT 0,
Population1980 INTEGER
DEFAULT 0,
Country VARCHAR2(50)
DEFAULT '"USA"',
Latitude NUMBER
DEFAULT 0,
Longitude NUMBER
DEFAULT 0,
CONSTRAINT pk_City PRIMARY KEY (CityID)
);

CREATE TABLE Animal
(
AnimalID INTEGER,
Name VARCHAR2(50),
Category VARCHAR2(50),
Breed VARCHAR2(50),
DateBorn DATE,
Gender VARCHAR2(50)
CHECK (Gender='Male' Or Gender='Female' Or Gender='Unknown' Or Gender Is Null),
Registered VARCHAR2(50),
Color VARCHAR2(50),
ListPrice NUMBER(38,4)
DEFAULT 0,
Photo LONG RAW,
ImageFile VARCHAR2(250),
ImageHeight INTEGER,
ImageWidth INTEGER,
CONSTRAINT pk_Animal PRIMARY KEY (AnimalID),
CONSTRAINT fk_BreedAnimal FOREIGN KEY (Category,Breed)
REFERENCES Breed(Category,Breed)
ON DELETE CASCADE,
CONSTRAINT fk_CategoryAnimal FOREIGN KEY (Category)
REFERENCES Category(Category)
ON DELETE CASCADE
);

CREATE TABLE Employee
(
EmployeeID INTEGER,
LastName VARCHAR2(50),
FirstName VARCHAR2(50),
Phone VARCHAR2(50),
Address VARCHAR2(50),
ZipCode VARCHAR2(50),
CityID INTEGER
DEFAULT 0,
TaxPayerID VARCHAR2(50),
DateHired DATE,
DateReleased DATE,
ManagerID INTEGER
DEFAULT 0,
EmployeeLevel INTEGER
DEFAULT 0,
Title VARCHAR2(50),
CONSTRAINT pk_Employee PRIMARY KEY (EmployeeID),
CONSTRAINT fk_CityEmployee FOREIGN KEY (CityID)
REFERENCES City(CityID)
);

CREATE TABLE Customer
(
CustomerID INTEGER,
Phone VARCHAR2(50),
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
Address VARCHAR2(50),
ZipCode VARCHAR2(50),
CityID INTEGER
DEFAULT 0,
CONSTRAINT pk_Customer PRIMARY KEY (CustomerID),
CONSTRAINT fk_CityCustomer FOREIGN KEY (CityID)
REFERENCES City(CityID)
);

CREATE TABLE Supplier
(
SupplierID INTEGER,
Name VARCHAR2(50),
ContactName VARCHAR2(50),
Phone VARCHAR2(50),
Address VARCHAR2(50),
ZipCode VARCHAR2(50),
CityID INTEGER
DEFAULT 0,
CONSTRAINT pk_Supplier PRIMARY KEY (SupplierID),
CONSTRAINT fk_CitySupplier FOREIGN KEY (CityID) REFERENCES City(CityID)
);

CREATE TABLE AnimalOrder
(
OrderID INTEGER,
OrderDate DATE,
ReceiveDate DATE,
SupplierID INTEGER
DEFAULT 0,
ShippingCost NUMBER(38,4)
DEFAULT 0,
EmployeeID INTEGER
DEFAULT 0,
CONSTRAINT pk_AnimalOrder PRIMARY KEY (OrderID),
CONSTRAINT fk_EmployeeAnimalOrder FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
ON DELETE CASCADE,
CONSTRAINT fk_SupplierAnimalOrder FOREIGN KEY (SupplierID)
REFERENCES Supplier(SupplierID)
ON DELETE CASCADE
);

CREATE TABLE AnimalOrderItem
(
OrderID INTEGER
DEFAULT 0,
AnimalID INTEGER
DEFAULT 0,
Cost NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_AnimalOrderItem PRIMARY KEY (OrderID,AnimalID),
CONSTRAINT fk_AnimalAnimalOrderItem FOREIGN KEY (AnimalID)
REFERENCES Animal(AnimalID)
ON DELETE CASCADE,
CONSTRAINT fk_AnimalOrderAnOrdItem FOREIGN KEY (OrderID)
REFERENCES AnimalOrder(OrderID)
ON DELETE CASCADE
);

CREATE TABLE CustomerAccount
(
AccountID INTEGER,
CustomerID INTEGER
DEFAULT 0,
Balance NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_CustomerAccount PRIMARY KEY (AccountID)
);

CREATE TABLE Merchandise
(
ItemID INTEGER,
Description VARCHAR2(50),
QuantityOnHand INTEGER
DEFAULT 0,
ListPrice NUMBER(38,4)
DEFAULT 0,
Category VARCHAR2(50),
CONSTRAINT pk_Merchandise PRIMARY KEY (ItemID),
CONSTRAINT fk_CategoryMerchandise FOREIGN KEY (Category)
REFERENCES Category(Category)
ON DELETE CASCADE
);

CREATE TABLE MerchandiseOrder
(
PONumber INTEGER,
OrderDate DATE,
ReceiveDate DATE,
SupplierID INTEGER
DEFAULT 0,
EmployeeID INTEGER
DEFAULT 0,
ShippingCost NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_MerchandiseOrder PRIMARY KEY (PONumber),
CONSTRAINT fk_EmployeeOrder FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
ON DELETE CASCADE,
CONSTRAINT fk_SupplierOrder FOREIGN KEY (SupplierID)
REFERENCES Supplier(SupplierID)
ON DELETE CASCADE
);

CREATE TABLE OrderItem
(
PONumber INTEGER
DEFAULT 0 NOT NULL,
ItemID INTEGER
DEFAULT 0 NOT NULL,
Quantity NUMBER
DEFAULT 0,
Cost NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_OrderItem PRIMARY KEY (PONumber,ItemID),
CONSTRAINT fk_MerchandiseOrderItem FOREIGN KEY (ItemID)
REFERENCES Merchandise(ItemID)
ON DELETE CASCADE,
CONSTRAINT fk_OrderOrderItem FOREIGN KEY (PONumber)
REFERENCES MerchandiseOrder(PONumber)
ON DELETE CASCADE
);

CREATE TABLE Sale
(
SaleID INTEGER,
SaleDate DATE,
EmployeeID INTEGER
DEFAULT 0,
CustomerID INTEGER
DEFAULT 0,
SalesTax NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_Sale PRIMARY KEY (SaleID),
CONSTRAINT fk_CustomerSale FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
ON DELETE CASCADE,
CONSTRAINT fk_EmployeeSale FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
ON DELETE CASCADE
);

CREATE TABLE SaleAnimal
(
SaleID INTEGER
DEFAULT 0,
AnimalID INTEGER
DEFAULT 0,
SalePrice NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_SaleAnimal PRIMARY KEY (SaleID,AnimalID),
CONSTRAINT fk_AnimalSaleAnimal FOREIGN KEY (AnimalID)
REFERENCES Animal(AnimalID)
ON DELETE CASCADE,
CONSTRAINT fk_SaleSaleAnimal FOREIGN KEY (SaleID)
REFERENCES Sale(SaleID)
ON DELETE CASCADE
);

CREATE TABLE SaleItem
(
SaleID INTEGER
DEFAULT 0 NOT NULL,
ItemID INTEGER
DEFAULT 0 NOT NULL,
Quantity INTEGER
DEFAULT 0,
SalePrice NUMBER(38,4)
DEFAULT 0,
CONSTRAINT pk_SaleItem PRIMARY KEY (SaleID,ItemID),
CONSTRAINT fk_MerchandiseSaleItem FOREIGN KEY (ItemID)
REFERENCES Merchandise(ItemID)
ON DELETE CASCADE,
CONSTRAINT fk_SaleSaleItem FOREIGN KEY (SaleID)
REFERENCES Sale(SaleID)
ON DELETE CASCADE
);