3.7 Write a SQL CREATE TABLE statement to create the PET_OWNER table, with
OwnerID as a surrogate key. Justify your choices of column properties.
Assumptions:
1. OwnerID is a surrogate key (starts at 1 and increments by 1)
For SQL Server:
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)
);
INSERT INTO PET_OWNER
INSERT INTO PET_OWNER
INSERT INTO PET_OWNER
INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerEmail)
VALUES('Trent', 'Miles', 'Miles.Trent@somewhere.com');
3.9 Create a referential integrity constraint on OwnerID in PET. Assume that deletions
should not cascade.
CREATE TABLE PET(
For SQL Server:
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 OWNER_FK FOREIGN KEY(OwnerID)
REFERENCES PET_OWNER(OwnerID)
ON DELETE NO ACTION
);
3.11 Write the required SQL statements to create the PET_2 table.
PET_2 (PetName, PetType, PetBreed, PetDOB, OwnerID)
For Access, SQL Server Oracle Database and MySQL:
CREATE TABLE PET_2(
PetName Char(50) NOT NULL,
PetType Char(25) NOT NULL,