schema.sql

/* Database schema to keep the structure of entire database. */

CREATE TABLE animals(
	id INT GENERATED BY DEFAULT AS IDENTITY, 
	name VARCHAR(20), 
	date_of_birth DATE, 
	escape_attempts INT, 
	neutered BIT, 
	weight_kg DECIMAL
);

ALTER TABLE animals ADD species varchar(255);

CREATE TABLE owners(id INT GENERATED BY DEFAULT AS IDENTITY, full_name VARCHAR(20), age INT, PRIMARY KEY(id));
CREATE TABLE species(id INT GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(20), PRIMARY KEY(id));
ALTER TABLE animals ADD PRIMARY KEY(id);
ALTER TABLE animals DROP COLUMN species;
ALTER TABLE animals ADD species_id INT;
ALTER TABLE animals ADD CONSTRAINT fk_species FOREIGN KEY(species_id) REFERENCES species(id) ON DELETE CASCADE;
ALTER TABLE animals ADD owner_id INT;
ALTER TABLE animals ADD CONSTRAINT fk_owners FOREIGN KEY(owner_id) REFERENCES owners(id) ON DELETE CASCADE;

CREATE TABLE vets(
	id INT GENERATED BY DEFAULT AS IDENTITY, 
	name VARCHAR(20), 
	age INT, 
	date_of_graduation date, 
	PRIMARY KEY (id)
);

CREATE TABLE specializations(
	species_id INT, 
	vets_id INT, 
	CONSTRAINT fk_speciesJoin FOREIGN KEY (species_id) REFERENCES species(id), 
	CONSTRAINT fk_vetsJoin FOREIGN KEY (vets_id) REFERENCES vets(id)
);

CREATE TABLE visits(
	animals_id INT, 
	vets_id INT, CONSTRAINT fk_animalsJoin FOREIGN KEY (animals_id) REFERENCES animals(id), 
	CONSTRAINT fk_vetsJoinA FOREIGN KEY (vets_id) REFERENCES vets(id)
); 

ALTER TABLE visits ADD date_of_visit date;

ALTER TABLE owners ADD COLUMN email VARCHAR(120);
CREATE INDEX visits_animals_id ON visits(animals_id ASC);
CREATE INDEX visits_vets_id ON visits(vets_id ASC);
CREATE INDEX owners_email ON owners(email DESC);