Question

1. Write SQL CREATE Table statement to create the following table with Owner ID as a surrogate key. Owner ID is a surrogate key (starts at 1 and increments by 1) hint: IDENTITY (1, 1) is syntax for surrogate key. PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) You can try inserting some data into your tables too! 2. Write SQL CREATE Table statement to create the following table with Pet ID as a surrogate key. Pet ID is a surrogate key (starts at 1 and increments by 1) hint: IDENTITY (1, 1) syntax for surrrogate key.. PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID) You don't need to create referential integrity constraint on OwnerID in PET table. 3. Now create the referential integrity constraint on OwnerID in PET. You can use ALTER TABLE command.

Answer 1

ANSWER

1. SQL Statement

create table pet_owner(ownerID int not null auto_increment,
ownerlastname varchar(10),ownerfirstname varchar(10),
ownerphone varchar(15), owneremail varchar(20),
primary key(ownerID));

Output Screen

MySQL [(none)]> use pet; Database changed MySQL [pet]> create table pet_owner(ownerID int not null auto_increment, ownerlastname varchar(10), ownerfirstname varchar(10), -> ownerphone varchar(15), owneremail varchar(20), -> primary key (ownerID)); Query OK, o rows affected (0.114 sec)

Records are inserted as follows.

insert into pet_owner(ownerlastname,ownerfirstname,ownerphone,owneremail)
values("Jack","Smith","98764521","abc@hmail.com");
insert into pet_owner(ownerlastname,ownerfirstname,ownerphone,owneremail)
values("Johk","Adams","98799521","klm@hmail.com");
insert into pet_owner(ownerlastname,ownerfirstname,ownerphone,owneremail)
values("Joe","Mark","98964521","mnp@hmail.com");

MySQL [pet]> insert into pet_owner(ownerlastname, ownerfirstname, ownerphone, owneremail) -> values("Jack", "Smith", "98764521", "abc@hmail.com"); Query OK, 1 row affected (0.011 sec) MySQL [pet]> insert into pet_owner(ownerlastname, ownerfirstname, ownerphone, owneremail) -> values("Johk", "Adams", "98799521", "kim@hmail.com"); Query OK, 1 row affected (0.001 sec) MySQL [pet]> insert into pet_owner(ownerlastname, ownerfirstname, ownerphone, owneremail) -> values("Joe", "Mark", "98964521", "mnp@hmail.com"); Query OK, 1 row affected (0.006 sec)

2. SQL statement given below.

create table pet(petId int not null auto_increment,
petname varchar(20), pettype varchar(20), petbreed varchar(20),
petdob date, ownerid int, primary key(petid));

Output screen

MySQL [pet]> create table pet(petId int not null auto_increment, -> petname varchar(20), pettype varchar(20), petbreed varchar(20), -> petdob date, ownerid int, primary key (petid)); Query OK, o rows affected (0.041 sec)

3. SQL statement is given below.

Alter table pet add foreign key(ownerid) references pet_owner(ownerid);

Output screen

MySQL [pet]> Alter table pet add foreign key (ownerid) references pet_owner(ownerid); Query OK, o rows affected (0.158 sec) Records: 0 Duplicates: Warnings: 0