CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE VETS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30))
CREATE INDEX VETS_LAST_NAME ON VETS(LAST_NAME)
CREATE MEMORY TABLE SPECIALTIES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(80))
CREATE INDEX SPECIALTIES_NAME ON SPECIALTIES(NAME)
CREATE MEMORY TABLE VET_SPECIALTIES(VET_ID INTEGER NOT NULL,SPECIALTY_ID INTEGER NOT NULL,CONSTRAINT FK_VET_SPECIALTIES_VETS FOREIGN KEY(VET_ID) REFERENCES VETS(ID),CONSTRAINT FK_VET_SPECIALTIES_SPECIALTIES FOREIGN KEY(SPECIALTY_ID) REFERENCES SPECIALTIES(ID))
CREATE MEMORY TABLE TYPES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(80))
CREATE INDEX TYPES_NAME ON TYPES(NAME)
CREATE MEMORY TABLE OWNERS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30),ADDRESS VARCHAR(255),CITY VARCHAR(80),TELEPHONE VARCHAR(20))
CREATE INDEX OWNERS_LAST_NAME ON OWNERS(LAST_NAME)
CREATE MEMORY TABLE PETS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(30),BIRTH_DATE DATE,TYPE_ID INTEGER NOT NULL,OWNER_ID INTEGER NOT NULL,CONSTRAINT FK_PETS_OWNERS FOREIGN KEY(OWNER_ID) REFERENCES OWNERS(ID),CONSTRAINT FK_PETS_TYPES FOREIGN KEY(TYPE_ID) REFERENCES TYPES(ID))
CREATE INDEX PETS_NAME ON PETS(NAME)
CREATE MEMORY TABLE VISITS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,PET_ID INTEGER NOT NULL,VISIT_DATE DATE,DESCRIPTION VARCHAR(255),CONSTRAINT FK_VISITS_PETS FOREIGN KEY(PET_ID) REFERENCES PETS(ID))
CREATE INDEX VISITS_PET_ID ON VISITS(PET_ID)
ALTER TABLE VETS ALTER COLUMN ID RESTART WITH 7
ALTER TABLE SPECIALTIES ALTER COLUMN ID RESTART WITH 4
ALTER TABLE TYPES ALTER COLUMN ID RESTART WITH 7
ALTER TABLE OWNERS ALTER COLUMN ID RESTART WITH 11
ALTER TABLE PETS ALTER COLUMN ID RESTART WITH 14
ALTER TABLE VISITS ALTER COLUMN ID RESTART WITH 5
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
INSERT INTO VETS VALUES(1,'James','Carter')
INSERT INTO VETS VALUES(2,'Helen','Leary')
INSERT INTO VETS VALUES(3,'Linda','Douglas')
INSERT INTO VETS VALUES(4,'Rafael','Ortega')
INSERT INTO VETS VALUES(5,'Henry','Stevens')
INSERT INTO VETS VALUES(6,'Sharon','Jenkins')
INSERT INTO SPECIALTIES VALUES(1,'radiology')
INSERT INTO SPECIALTIES VALUES(2,'surgery')
INSERT INTO SPECIALTIES VALUES(3,'dentistry')
INSERT INTO VET_SPECIALTIES VALUES(2,1)
INSERT INTO VET_SPECIALTIES VALUES(3,2)
INSERT INTO VET_SPECIALTIES VALUES(3,3)
INSERT INTO VET_SPECIALTIES VALUES(4,2)
INSERT INTO VET_SPECIALTIES VALUES(5,1)
INSERT INTO TYPES VALUES(1,'cat')
INSERT INTO TYPES VALUES(2,'dog')
INSERT INTO TYPES VALUES(3,'lizard')
INSERT INTO TYPES VALUES(4,'snake')
INSERT INTO TYPES VALUES(5,'bird')
INSERT INTO TYPES VALUES(6,'hamster')
INSERT INTO OWNERS VALUES(1,'George','Franklin','110 W. Liberty St.','Madison','6085551023')
INSERT INTO OWNERS VALUES(2,'Betty','Davis','638 Cardinal Ave.','Sun Prairie','6085551749')
INSERT INTO OWNERS VALUES(3,'Eduardo','Rodriquez','2693 Commerce St.','McFarland','6085558763')
INSERT INTO OWNERS VALUES(4,'Harold','Davis','563 Friendly St.','Windsor','6085553198')
INSERT INTO OWNERS VALUES(5,'Peter','McTavish','2387 S. Fair Way','Madison','6085552765')
INSERT INTO OWNERS VALUES(6,'Jean','Coleman','105 N. Lake St.','Monona','6085552654')
INSERT INTO OWNERS VALUES(7,'Jeff','Black','1450 Oak Blvd.','Monona','6085555387')
INSERT INTO OWNERS VALUES(8,'Maria','Escobito','345 Maple St.','Madison','6085557683')
INSERT INTO OWNERS VALUES(9,'David','Schroeder','2749 Blackhawk Trail','Madison','6085559435')
INSERT INTO OWNERS VALUES(10,'Carlos','Estaban','2335 Independence La.','Waunakee','6085555487')
INSERT INTO PETS VALUES(1,'Leo','2000-09-07',1,1)
INSERT INTO PETS VALUES(2,'Basil','2002-08-06',6,2)
INSERT INTO PETS VALUES(3,'Rosy','2001-04-17',2,3)
INSERT INTO PETS VALUES(4,'Jewel','2000-03-07',2,3)
INSERT INTO PETS VALUES(5,'Iggy','2000-11-30',3,4)
INSERT INTO PETS VALUES(6,'George','2000-01-20',4,5)
INSERT INTO PETS VALUES(7,'Samantha','1995-09-04',1,6)
INSERT INTO PETS VALUES(8,'Max','1995-09-04',1,6)
INSERT INTO PETS VALUES(9,'Lucky','1999-08-06',5,7)
INSERT INTO PETS VALUES(10,'Mulligan','1997-02-24',2,8)
INSERT INTO PETS VALUES(11,'Freddy','2000-03-09',5,9)
INSERT INTO PETS VALUES(12,'Lucky','2000-06-24',2,10)
INSERT INTO PETS VALUES(13,'Sly','2002-06-08',1,10)
INSERT INTO VISITS VALUES(1,7,'1996-03-04','rabies shot')
INSERT INTO VISITS VALUES(2,8,'1996-03-04','rabies shot')
INSERT INTO VISITS VALUES(3,8,'1996-06-04','neutered')
INSERT INTO VISITS VALUES(4,7,'1996-09-04','spayed')