schema.sql

CREATE TABLE animals(id SMALLINT GENERATED ALWAYS AS IDENTITY, name VARCHAR(100), date_of_birth DATE, escape_attempts SMALLINT, neutered BOOLEAN, weight_kg DECIMAL(4,2), PRIMARY KEY(id));
ALTER TABLE animals ADD species VARCHAR(100);
CREATE TABLE owners(id SMALLINT GENERATED ALWAYS AS IDENTITY, full_name VARCHAR(100), age SMALLINT, PRIMARY KEY(id));
CREATE TABLE species(id SMALLINT GENERATED ALWAYS AS IDENTITY, name VARCHAR(100), PRIMARY KEY(id));
ALTER TABLE animals DROP species;
ALTER TABLE animals ADD species_id SMALLINT;
ALTER TABLE animals ADD CONSTRAINT species FOREIGN KEY(species_id) REFERENCES species(id);
ALTER TABLE animals ADD owner_id SMALLINT;
ALTER TABLE animals ADD CONSTRAINT owners FOREIGN KEY(owner_id) REFERENCES owners(id);
CREATE TABLE vets(id SMALLINT GENERATED ALWAYS AS IDENTITY, name VARCHAR(100), age SMALLINT, date_of_graduation DATE, PRIMARY KEY(id));
CREATE TABLE specializations(species_id SMALLINT, vet_id SMALLINT, PRIMARY KEY(species_id, vet_id));
ALTER TABLE specializations ADD CONSTRAINT species FOREIGN KEY(species_id) REFERENCES species(id);
ALTER TABLE specializations ADD CONSTRAINT vets FOREIGN KEY(vet_id) REFERENCES vets(id);
CREATE TABLE visits(animal_id SMALLINT, vet_id SMALLINT, date_of_visit DATE, PRIMARY KEY(animal_id, vet_id, date_of_visit));
ALTER TABLE visits ADD CONSTRAINT animals FOREIGN KEY(animal_id) REFERENCES animals(id);
ALTER TABLE visits ADD CONSTRAINT vets FOREIGN KEY(vet_id) REFERENCES vets(id);
ALTER TABLE owners ADD COLUMN email VARCHAR(120);
CREATE INDEX animal ON visits(animal_id);
CREATE INDEX vet ON visits(vet_id);
CREATE INDEX email ON owners(email);