schema.sql

/* Database schema to keep the structure of entire database. */
CREATE TABLE animals
(
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    escape_attempts INT NOT NULL,
    neutered BOOLEAN NOT NULL,
    weight_kg NUMERIC(5, 2) NOT NULL
);

-- Add a comment to the "animals" table
COMMENT ON TABLE animals IS 'Table containing information about animals in the vet clinic';

-- Add comments to the columns of the "animals" table
COMMENT ON COLUMN animals.id IS 'Unique identifier for each animal';
COMMENT ON COLUMN animals.name IS 'Name of the animal';
COMMENT ON COLUMN animals.date_of_birth IS 'Date of birth of the animal';
COMMENT ON COLUMN animals.escape_attempts IS 'Number of escape attempts made by the animal';
COMMENT ON COLUMN animals.neutered IS 'Indicates if the animal is neutered (true/false)';
COMMENT ON COLUMN animals.weight_kg IS 'Weight of the animal in kilograms';


/* =========== Update "animals" table =========== */

-- Add a column species of type string to the "animals" table
ALTER TABLE animals ADD COLUMN species VARCHAR(255);

-- Add comments to the "species" columns of the "animals" table
COMMENT ON COLUMN animals.species IS 'Specy of the animal';

/* =========== Create "onwers" table =========== */
CREATE TABLE owners
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL
);

-- Add comments to the table and columns
COMMENT ON TABLE owners IS 'Table containing information about animal owners in the vet clinic';
COMMENT ON COLUMN owners.id IS 'Unique identifier for each owner';
COMMENT ON COLUMN owners.full_name IS 'Full name of the owner';
COMMENT ON COLUMN owners.age IS 'Age of the owner';

/* =========== Update "owners" table =========== */
-- Add an email column to your owners table
ALTER TABLE owners ADD COLUMN email VARCHAR(120);

/* =========== Create "species" table =========== */
CREATE TABLE species
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Add comments to the table and columns
COMMENT ON TABLE species IS 'Table containing information about different animal species';
COMMENT ON COLUMN species.id IS 'Unique identifier for species';
COMMENT ON COLUMN species.name IS 'Name of the species';

/* =========== Update "animals" table =========== */
-- Remove the "species" column
ALTER TABLE animals
DROP COLUMN species;

-- Add the "species_id" column as a foreign key
ALTER TABLE animals
ADD COLUMN species_id INT REFERENCES species(id);

-- Add the "owner_id" column as a foreign key
ALTER TABLE animals
ADD COLUMN owner_id INT REFERENCES owners(id);

-- Add a primary key constraint to the "id" column
ALTER TABLE animals
ADD PRIMARY KEY (id);

/* =========== Create "vets" table =========== */
CREATE TABLE vets
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL,
    date_of_graduation DATE NOT NULL
);

-- Add comments to the table and columns
COMMENT ON TABLE vets IS 'Table containing information about veterinarians at the vet clinic';
COMMENT ON COLUMN vets.id IS 'Unique identifier for each vet';
COMMENT ON COLUMN vets.name IS 'Full name of the vet';
COMMENT ON COLUMN vets.age IS 'Age of the vet';
COMMENT ON COLUMN vets.date_of_graduation IS 'Date of graduation of the vet';


/* =========== Create "specializations" join table =========== */
CREATE TABLE specializations
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    vet_id INTEGER NOT NULL,
    species_id INTEGER NOT NULL,
    FOREIGN KEY (vet_id) REFERENCES vets(id),
    FOREIGN KEY (species_id) REFERENCES species(id)
);

-- Add comments to the table and columns
COMMENT ON TABLE specializations IS 'Join table for vet specializations in various species';
COMMENT ON COLUMN specializations.id IS 'Unique identifier for each specialization';
COMMENT ON COLUMN specializations.vet_id IS 'Foreign key referencing the vet specialized';
COMMENT ON COLUMN specializations.species_id IS 'Foreign key referencing the species of specialization';

/* =========== Create "visits" join table =========== */
CREATE TABLE visits
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    animal_id INTEGER NOT NULL,
    vet_id INTEGER NOT NULL,
    visit_date DATE NOT NULL,
    FOREIGN KEY (animal_id) REFERENCES animals(id),
    FOREIGN KEY (vet_id) REFERENCES vets(id)
);

-- Add comments to the table and columns
COMMENT ON TABLE visits IS 'Join table for animal visits to vets';
COMMENT ON COLUMN visits.id IS 'Unique identifier for each visit';
COMMENT ON COLUMN visits.animal_id IS 'Foreign key referencing the visiting animal';
COMMENT ON COLUMN visits.vet_id IS 'Foreign key referencing the veterinarian visited';
COMMENT ON COLUMN visits.visit_date IS 'Date of the animals visit';

/* =========== Update "visits" table =========== */
-- Rename visit_date column to date_of_visit
ALTER TABLE visits RENAME COLUMN visit_date TO date_of_visit;

/* ===========
 Create indexes on email, vet_id, and animal_id columns for optimization
 =========== */
--  Create an index on the `animal_id` column in the `visits` table
CREATE INDEX idx_animal_id
ON visits (animal_id);

--  Create an index on the `vet_id` column in the `visits` table
CREATE INDEX idx_vet_id
ON visits (vet_id);

--  Create an index on the `email` column of the `owners` table
CREATE INDEX idx_email
ON owners (email);