Zoo_BD_EX1

CREATE TABLE Animals(
    ID SMALLINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Animals PRIMARY KEY,
    Spicie nvarchar(100) NOT NULL,
    Family nvarchar(100) NOT NULL,
    Sex nvarchar(20),
    Name nvarchar(100)
)

CREATE TABLE Paddocks(
    ID SMALLINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Paddocks PRIMARY KEY,
    NUMBER INT NOT NULL,
    Zone INT NOT NULL
)

CREATE TABLE Volonteers(
    ID SMALLINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Volonteers PRIMARY KEY,
    Surname nvarchar(100) NOT NULL,
    Name nvarchar(100) NOT NULL,
    FullName nvarchar(200) NOT NULL,
    ShortFullName nvarchar(101) NOT NULL,
    Phone nvarchar(8) NOT NULL,
    Email nvarchar(100) NOT NULL
    CONSTRAINT UQ_Volonteers_Email UNIQUE(Email),
    CONSTRAINT UQ_Volonteers_Phone UNIQUE(Phone)
)

CREATE TABLE Food(
    ID SMALLINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Food PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    Amount INT NOT NULL
)

CREATE TABLE Orders(
    ID SMALLINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    StartDate datetime NOT NULL,
    FinishDate datetime,
    CHECK(StartDate <= FinishDate)
)

CREATE TABLE OrderItems(
    OrderID SMALLINT NOT NULL,
    FoodID SMALLINT NOT NULL,
    Amount INT NOT NULL,
    CHECK (Amount > 0),

    CONSTRAINT FK_Order_ID FOREIGN KEY (OrderID) REFERENCES Orders(ID),
    CONSTRAINT FK_Food_ID FOREIGN KEY (FoodID) REFERENCES Food(ID),
    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderID, FoodID)
)

CREATE TABLE AnimalsPaddocks(
    AnimalsID SMALLINT NOT NULL,
    PaddocksID SMALLINT NOT NULL,

    CONSTRAINT FK_Animals_ID FOREIGN KEY (AnimalsID) REFERENCES Animals(ID),
    CONSTRAINT FK_Paddocks_ID FOREIGN KEY (PaddocksID) REFERENCES Paddocks(ID),
    CONSTRAINT PK_AnimalsPaddocks PRIMARY KEY (AnimalsID, PaddocksID)
)


CREATE TABLE VolonteersAnimals(
    VolonteersID SMALLINT NOT NULL,
    AnimalsID SMALLINT NOT NULL,

    CONSTRAINT FK_Volonteers_ID FOREIGN KEY (VolonteersID) REFERENCES Volonteers(ID),
    CONSTRAINT FK_Animals1_ID FOREIGN KEY (AnimalsID) REFERENCES Animals(ID),
    CONSTRAINT PK_VolonteersAnimals PRIMARY KEY (VolonteersID, AnimalsID)
)

CREATE TABLE VolonteeresOrders(
    VolonteersID SMALLINT NOT NULL,
    OrdersID SMALLINT NOT NULL,

    CONSTRAINT FK_Volonteers1_ID FOREIGN KEY (VolonteersID) REFERENCES Volonteers(ID),
    CONSTRAINT FK_Orders_ID FOREIGN KEY (OrdersID) REFERENCES Orders(ID),
    CONSTRAINT PK_VolonteersOrders PRIMARY KEY (VolonteersID, OrdersID)
)