/******************
PetOwners Database
INFO 1620
Written by Lisa Thoendel
Last Updated Summer 2022
******************/
/****
The below code will create a database for us to work with.
Copy and paste this code to the DDL pane of SQL Fiddle.
--<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>--
Creation Code. DO NOT EDIT!
****/
CREATE TABLE PetOwners
(ownerID integer NOT NULL,
ownerName VARCHAR(25),
CONSTRAINT PetOwners_PK PRIMARY KEY (ownerID));
CREATE TABLE Breeds
(breedID CHAR(8) NOT NULL,
type enum('dog','cat','bird','fish'),
breedName VARCHAR(35),
CONSTRAINT Breeds_PK PRIMARY KEY (breedID));
CREATE TABLE Pets
(petID integer NOT NULL,
breedID CHAR(8) NOT NULL,
petName CHAR(25),
CONSTRAINT Pets_PK PRIMARY KEY(petID),
CONSTRAINT Pets_FK FOREIGN KEY (breedID)
REFERENCES Breeds (breedID));
CREATE TABLE Owns
(ownerID integer NOT NULL,
petID integer NOT NULL,
CONSTRAINT Owns_PK PRIMARY KEY(ownerID, petID),
CONSTRAINT Owns_Pets_FK FOREIGN KEY(petID) REFERENCES Pets(petID),
CONSTRAINT Owns_PetOwners_FK FOREIGN KEY(ownerID) REFERENCES PetOwners(ownerID));
/***
Insert statements to give us data to work with.
DO NOT EDIT!
***/
insert
into PetOwners(ownerID, ownerName)
values(1, "Beth Anderson"),
(2, "Alison Jensen"),
(3, "Charlie Wilcox"),
(4, "Jo Yessen"),
(5, "Deb Donnelly"),
(6, "Kevin Thoendel");
insert into Breeds(breedID, type, breedName)
values(1, 'cat','tuxedo'),
(2, 'cat','russian blue'),
(3, 'dog','maltese'),
(4, 'dog','shih tzu'),
(5, 'fish','lionfish'),
(6, 'bird', 'parrot');
insert into Pets(petID, breedID, petName)
values(1, 1, 'Pengin'),
(2, 2, 'Kupo'),
(3, 3, 'Greta'),
(4, 4, 'Luma'),
(5, 4, 'Carson'),
(6, 5, 'Yoshi'),
(7, 5, 'Gizmo');
insert into Owns(ownerID, petID)
values(1,3),
(2, 6),
(3, 2),
(4, 4),
(5, 5),
(6, 1),
(1, 7);
select *
alter table pets
add column Age integer
/* Test Code: Execute in query pane */
select * from PetOwners;
select * from Breeds;
select * from Pets;
select * from Owns;
Telegram: DB developers channel