CREATE TABLE people (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	name VARCHAR(50) NOT NULL,
	surname VARCHAR(100) NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE users (
	login VARCHAR(100) NOT NULL,
	password VARCHAR(64) NOT NULL,
	role VARCHAR(5) NOT NULL,
	PRIMARY KEY (login)
);

CREATE TABLE pet (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
    name VARCHAR(50) NOT NULL,
    animalType VARCHAR(50) NOT NULL,
    owner_id int NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (owner_id) REFERENCES people(id) ON DELETE CASCADE
);