schema.sql

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

CREATE TABLE animals(
id INT GENERATED BY DEFAULT AS IDENTITY,
name VARCHAR(250),
date_of_birth DATE,
escape_attempts INT,
neutered BOOLEAN,
weight_kg FLOAT,
species VARCHAR (50)
);

CREATE TABLE owners (
id SERIAL PRIMARY KEY,
 full_name VARCHAR(255)
 age INT
  );

CREATE TABLE species (
id SERIAL PRIMARY KEY,
 name VARCHAR(255)
  );

ALTER TABLE animals ADD PRIMARY KEY(id);
ALTER TABLE animals DROP COLUMN species;

ALTER TABLE animals ADD COLUMN species_id INT REFERENCES species(id);
ALTER TABLE animals ADD COLUMN owner_id INT REFERENCES owners(id);

create table vets(
	id serial primary key,
	name varchar(255),
	age INT,
	date_of_graduation date
)

create table specializations (
	id serial primary key,
	vet_id INT,
	species_id INT,
	foreign key (vet_id) references vets(id),
	foreign key (species_id) references species(id)
)

create table visits (
id serial primary key,
	animals_id int,
	vet_id int,
	visit_date date,
	foreign key (animals_id) references animals(id),
	foreign key (vet_id) references vets(id)
)

create index on visits (animals_id);

 CREATE TABLE visits_clustered (
  id SERIAL PRIMARY KEY,
  animals_id INTEGER,
  vet_id INTEGER,
  visit_date TIMESTAMP
);


CREATE INDEX visits_clustered_vet_id_idx ON visits_clustered (vet_id);
CLUSTER visits_clustered USING visits_clustered_vet_id_idx;
DROP TABLE visits;
ALTER TABLE visits_clustered RENAME TO visits;


CREATE INDEX email_idx ON owners (email);
ANALYZE owners;
CLUSTER owners USING email_idx;