--------------------------------------------------------------------------------
-- Drop Tables
--------------------------------------------------------------------------------

DROP TABLE IF EXISTS prescription;
DROP TABLE IF EXISTS appt_pet;
DROP TABLE IF EXISTS pet;
DROP TABLE IF EXISTS appointment;
DROP TABLE IF EXISTS vet_phone;
DROP TABLE IF EXISTS veterinarian;

--------------------------------------------------------------------------------
-- Create Tables
--------------------------------------------------------------------------------

CREATE TABLE veterinarian
(
    empid integer NOT NULL,
    name varchar(64) NOT NULL,
    email varchar(64) UNIQUE
);

ALTER TABLE veterinarian OWNER TO absent;

COMMENT ON TABLE veterinarian
    IS 'doctor who practices veterinary medicine';


CREATE TABLE vet_phone
(
    empid integer NOT NULL,
    sort integer NOT NULL,
    phone varchar(16) NOT NULL
);

ALTER TABLE vet_phone OWNER TO absent;

COMMENT ON TABLE vet_phone
    IS 'phone number (multi-valued) for a vet';


CREATE TABLE appointment
(
    datetime timestamp NOT NULL,
    room integer NOT NULL,
    notes text,
    empid integer
);

ALTER TABLE appointment OWNER TO absent;

COMMENT ON TABLE appointment
    IS 'meeting arranged in advance for treatment';


CREATE TABLE pet
(
    petid serial NOT NULL,
    name varchar(32) NOT NULL,
    breed varchar(32)
);

ALTER TABLE pet OWNER TO absent;

COMMENT ON TABLE pet
    IS 'animal for companionship or amusement';


CREATE TABLE appt_pet
(
    datetime timestamp NOT NULL,
    room integer NOT NULL,
    petid integer NOT NULL
);

ALTER TABLE appt_pet OWNER TO absent;

COMMENT ON TABLE appt_pet
    IS 'vet holds appointment that pet attends';


CREATE TABLE prescription
(
    empid integer NOT NULL,
    petid integer NOT NULL,
    datetime timestamp NOT NULL,
    drugname varchar(32) NOT NULL,
    dosage varchar(32) NOT NULL
);

ALTER TABLE prescription OWNER TO absent;

COMMENT ON TABLE prescription
    IS 'vet prescribes medicine given to a pet';
