Flock

The Flock table has three columns:

1. Flock_ID (PK), it is set as a SERIAL type to allow for auto-incrementing the PK.
2. Starting_Count, it is an INTEGER with the constraint that it is not zero, negative, or null. This defines the total number of chickens in the Flock at the start. This value is not updated and does not change for the entire lifetime of the column.
3. Starting_Date, is a unique DATE that is also not null. This defines the date when the Flock was received by DB Poultry. The Starting_Date is unique since there is only one Flock in the farm at a time.

Below is the database definition of the table:

CREATE TABLE
    Flock (
        Flock_ID SERIAL PRIMARY KEY,
        Starting_Count INTEGER CHECK (Starting_Count > 0) NOT NULL,
        Starting_Date DATE UNIQUE NOT NULL
    );

Flock_Details

The Flock_Details table has five columns:

1. Flock_Details_ID (PK; usually abbreviated as fd_ID), it is set as a SERIAL type to allow for auto-incrementing the PK.
2. Flock_ID, is an integer FK pointing to the Flock this Flock_Details table belongs to.
3. FD_Date, is a unique DATE that is also not null. This defines the date when the Flock_Details was recorded. This date is unique as there can only be one Flock_Details per day; that is, they can only record the Flock_Details once a day.
4. Depleted_Count, is an INTEGER with the constraint that it is non-negative. This defines how many chickens died during that day. This value is not updated and does not change for the entire lifetime of the column.

Special specifications regarding the insertion of Flock_Details is discussed in detail in Maintaining Integrity -> Flock_Details.

Below is the database definition of the table:

CREATE TABLE
    Flock_Details (
        Flock_Details_ID SERIAL PRIMARY KEY,
        Flock_ID INTEGER NOT NULL,
        FD_Date DATE UNIQUE NOT NULL,
        Depleted_Count INTEGER CHECK (Depleted_Count >= 0),
        FOREIGN KEY (Flock_ID) REFERENCES Flock (Flock_ID) ON DELETE CASCADE
    );

Supply_Record

The Supply_Record has six columns:

1. Supply_ID (PK; usually abbreviated as sr_ID), it is set as SERIAL to allow for auto-incrementing the PK.
2. Supply_Type_ID, it is an integer FK pointing to a Supply_Type column, this denotes what type of supply this Supply_Record is tracking.
3. Added, is a numerical value that takes any value in the range [0, 999999999] with four points of precision. Denotes how much of that supply was added (given by partner businesses, bought, among others).
4. Consumed, is a numerical value that takes any value in the range [0, 999999999] with four points of precision. Denotes how much of that supply were consumed or used.
5. Retrieved is a Boolean value that denotes if the remaining (unused) supplies were given back (retrieved by) the partner business.

CREATE TABLE
    Supply_Record (
        Supply_ID SERIAL PRIMARY KEY,
        Supply_Type_ID INT NOT NULL,
        SR_Date DATE,
        Added NUMERIC(9, 4),
        Consumed NUMERIC(9, 4),
        Retrieved BOOLEAN,
        FOREIGN KEY (Supply_Type_ID) REFERENCES Supply_Type (Supply_Type_ID) ON DELETE CASCADE
    );

Supply_Type

The Supply_Type allows for dynamically typing a Supply_Record; it allows the user to add a new Supply_Type when needed. The Supply_Type has three columns:

1. Sypply_Type_ID (PK; usually abbreviated as st_ID), it is set as a SERIAL to allow for auto-incrementing the PK.
2. Supply_Name is a TEXT field that denotes the type of supply (e.g., Feed, Medicine, among others). This value is not updated and does not change for the entire lifetime of the column. Each Supply_Name is unique; that is, for every Supply_Type in the database, that is only for a single supply. For instance, if we have a Supply_Type with the name "Rice" and another with the name "Chicken Feed" then for every data insert that is a chicken feed, every data insert of chicken feed will be using the Supply_Type with the name "Chicken Feed"
3. Unit is a VARCHAR(12) field that denotes the unit of measurement used (S.I. units cm or ml; discrete units like sacks or bottles). This value is not updated and does not change for the entire lifetime of the column.

CREATE TABLE
    Supply_Type (
        Supply_Type_ID SERIAL PRIMARY KEY,
        Supply_Name TEXT UNIQUE NOT NULL CHECK (Supply_Name <> ''),
        Unit VARCHAR(12) NOT NULL CHECK (Unit <> '') 
    );