/****************************************************************************/
/*																			*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																			*/
/*	Pet_Database Create Tables												*/
/*																			*/
/*	These are the Microsoft SQL Server 2016 SQL code solutions			*/
/*																			*/
/****************************************************************************/

USE Pet_Database;
GO

CREATE TABLE PET_OWNER(
	OwnerID					Int				NOT NULL IDENTITY(1, 1),
	OwnerLastName			Char(25)		NOT NULL,
	OwnerFirstName			Char(25)		NOT NULL,
	OwnerPhone				Char(12)		NULL,
	OwnerEmail				VarChar(100)	NULL,
	CONSTRAINT				OWNER_PK		PRIMARY KEY(OwnerID)
	);

CREATE TABLE BREED(
	BreedName				VarChar(100)	NOT NULL,
	MinWeight				Numeric(4,1)	NULL,
	MaxWeight				Numeric(4,1)	NULL,
	AverageLifeExpectancy	Numeric(4,1)	NULL,
	CONSTRAINT				BREED_PK		PRIMARY KEY(BreedName)
	);

CREATE TABLE PET (
	PetID					Int				NOT NULL IDENTITY(1,1),
	PetName					Char (50)		NOT NULL,
	PetType					Char (25)		NOT NULL,
	PetBreed				VarChar(100)	NULL,
	PetDOB					Date			NULL,
	OwnerID					Int				NOT NULL,
	CONSTRAINT				PET_PK			PRIMARY KEY(PetID),
	CONSTRAINT 				PET_OWNER_FK 	FOREIGN KEY(OwnerID)
									REFERENCES PET_OWNER(OwnerID)
											ON DELETE CASCADE
	);

CREATE TABLE PET_3(
	PetID					Int				NOT NULL IDENTITY(1,1),
	PetName					Char (50)		NOT NULL,
	PetType					Char (25)		NOT NULL,
	PetBreed				VarChar(100)	NULL,
	PetDOB					Date			NULL,
	PetWeight				Numeric(4,1)	NULL,
	OwnerID					Int				NOT NULL,
	CONSTRAINT				PET_3_PK		PRIMARY KEY(PetID),
	CONSTRAINT 				PET_3_OWNER_FK	FOREIGN KEY(OwnerID)
									REFERENCES PET_OWNER(OwnerID)
											ON DELETE CASCADE
	);

ALTER TABLE PET
	ADD CONSTRAINT PET_BREED_FK FOREIGN KEY(PetBreed)
		REFERENCES BREED(BreedName)
			ON UPDATE CASCADE;

ALTER TABLE PET_3
	ADD CONSTRAINT PET_3_BREED_FK FOREIGN KEY(PetBreed)
		REFERENCES BREED(BreedName)
			ON UPDATE CASCADE;



/********************************************************************************/