/******************
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);
ALTER table
Pets
ADD
column petAge integer NOT NULL;
Telegram: DB developers channel