Question

Sample data for these tables are shown in Figures 3-26 and 3-27. For each SQL statement you write, show the results based on these data. If possible, run the statements you write for the questions that follow in an actual DBMS, as appropriate, to obtain results. Use data types that are consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types by using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. 3.7 Write an SQL CREATE TABLE statement to create the PET_OWNER table, with OwnerID as a surrogate key. Justify your choices of column properties. If you are using an actual DBMS, also insert the data using SQL. 3.8 Write an SQL CREATE TABLE statement to create the PET table without a ref- erential integrity constraint on OwnerID in PET. Justify your choices of column properties. Why not make every column NOT NULL? If you are using an actual DBMS, also insert the data using SQL. 3.9 Create a referential integrity constraint on OwnerID in PET. Assume that deletions should not cascade. 3.10 Create a referential integrity constraint on OwnerID in PET. Assume that deletions should cascade. The following table schema for the PET_2 table is an alternate version of the PET table- use it to answer review questions 3.11 and 3.12: PET_2 (PetName, PetType, PetBreed, PetDOB, OwnerID)

Answer 1

3.7)

Table Creation:

create table PET_OWNER (OwnerID INTEGER AUTO_INCREMENT PRIMARY KEY,OwnerLastName CHAR(10),OwnerFirstName CHAR(10),OwnerPhone VARCHAR(15),OwnerEmail VARCHAR(30));

Insert Data into Table:

INSERT INTO PET_OWNER VALUES (1,"Downs","Marsha","555-537-8765","[email protected]"),(2,"James","Richard","555-537-7654","[email protected]"),(3,"Frier","Liz","555-537-6543","[email protected]"),(4,"Trent","Miles",NULL,"[email protected]");

Output Screenshot:

mysql> create table PET_OWNER (OwnerID INTEGER AUTO_INCREMENT PRIMARY KEY, OwnerLastName CHAR (10), OwnerFirstName CHAR(10), Owner Phone VARCHAR(15), Owner Email VARCHAR(30)); Query OK, O rows affected (0.56 sec) mysql> INSERT INTO PET_OWNER VALUES(1,"Downs", "Marsha","555-537-8765", "Marsha. Downs@somewher e.com"),(2,"James", "Richard","555-537-7654", "Richard. [email protected]"),(3,"Frier","Liz", "555-537-6543","[email protected]"),(4,"Trent", "Miles", NULL, "Miles. [email protected] "); Query OK, 4 rows affected (0.18 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select *from PET_OWNER; | OwnerID | Owner LastName | Owner FirstName Owner Phone | Owner Email 1 | Downs | Marsha | 555-537-8765 | [email protected] ! 2 | James | Richard | 555-537-7654 | [email protected] | 1 3 | Frier | Liz | 555-537-6543 | [email protected] | 4 | Trent | Miles | NULL | Miles. [email protected] I 4 rows in set (0.00 sec)

3.8)

Create Table:

create table PET(PetID integer primary key, PetName char(10),PetType char(5),PetBreed varchar(20),PetDOB varchar(15),OwnerID integer);

Insert Date into Table:

insert into PET VALUES(1,"King","Dog","Std.Poodle","27-Feb-16",1),(2,"Teddy","Cat","Cashmere","01-Feb-17",2),(3,"Fido","Dog","Std.Poodle","17-Jul-15",1),(4,"AJ","Dog","Collie Mix","05-May-16",3),(5,"Cedro","Cat","Unknown","06-Jun-14",2),(6,"Wooley","Cat","Unknown",NULL,2),(7,"Buster","Dog","Border Collie","11-Dec-13",4);

Output Screenshot:

mysql> create table PET(PetID integer primary key, PetName char(10), PetType char(5), PetBreed varchar(20), PetDOB varchar(15), Owner ID integer); Query OK, o rows affected (0.51 sec) mysql> insert into PET VALUES(1, "King", "Dog", "Std. Poodle","27-Feb-16",1),(2,"Teddy", "Cat","C ashmere", "01-Feb-17",2),(3,"Fido", "Dog", "std. Poodle","17-Jul-15",1),(4,"AJ", "Dog", "Collie Mi x","05-May-16",3),(5,"Cedro", "Cat", "Unknown","06-Jun-14",2),(6,"Wooley","Cat", "Unknown", NULL ,2), (7,"Buster","Dog", "Border Collie","11-Dec-13",4); Query OK, 7 rows affected (0.11 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select*from PET; | PetID | PetName | PetType | PetBreed | PetDOB | OwnerID | I I I 1 | King | Dog 2 | Teddy | Cat 3 | Fido | Dog 4 | AJ | Dog 5 | Cedro | Cat 6 | Wooley | Cat 7 | Buster | Dog | Std. Poodle | 27-Feb-16 | Cashmere | 01-Feb-17 | Std. Poodle | 17-Jul-15 | Collie Mix | 05-May-16 | | Unknown | 06-Jun-14 | | Unknown | NULL | | Border Collie | 11-Dec-13 11 2 1 | 3 | 21 21 4 | | 7 rows in set (0.00 sec)

Why not make every column NOT NULL?

Because in PetDOB Column there is no value so we considered that as NULL value.

3.9)

ALTER TABLE PET ADD FOREIGN KEY (OwnerID) REFERENCES PET_OWNER (OwnerID);

3.10)

ALTER TABLE PET ADD FOREIGN KEY (OwnerID) REFERENCES PET_OWNER (OwnerID) ON DELETE CASCADE;

3.11)

Create Table:

create table PET_2 (Pet_Name char(20) primary key,PetType char(5),PetBreed varchar(20),PetDOB varchar(15),OwnerID integer references PET_OWNER (OwnerID));

Output Screenshot:

mysql> create table PET_2 (Pet_Name char(20) primary key, PetType char(5), PetBreed varchar(20 ), PetDOB varchar(15), OwnerID integer references PET_OWNER (OwnerID)); Query OK, o rows affected (0.64 sec) mysql> desc PET_2; | Field | Type | Null | Key | Default | Extra | | Pet_Name | char(20) | NO | PRI | NULL | PetType | char(5) | YES I | NULL PetBreed | varchar(20) | YES | NULL PetDOB | varchar(15) | YES | | NULL || OwnerID | int(11) | YES 1 | NULL 5 rows in set (0.00 sec)

3.12) Answer:

PET table is a better design. because it have more information.