Use the following tables for your answers to questions 3.7 through 3.51: PET_OWN

Question

Use the following tables for your answers to questions 3.7 through 3.51:

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID)

Sample data for these tables are shown in Figures 3-17 and 3-18.

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 SQL Server, Oracle Database, or MySQL data types shown in Figure 3-4.

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.

3.8 Write an SQL CREATE TABLE statement to create the PET table without a referential integrity constraint on OwnerID in PET. Justify your choices of column properties. Why not make every column NOT NULL?

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: PET_2 (PetName, PetType, PetBreed, PetDOB, OwnerID)

3.11 Write the required SQL statements to create the PET_2 table

Explanation / Answer

Tables can be created in SQL using the CREATE TABLE command. Ex:

CREATE TABLE table name (

col name data type,

col name data type ,

create table "table-name" -> exclude the quotes when creating the table
after this a message will come : table created
(row_name data type(limit of characters),... )
for example
(name varchar2(20)).
This will make a column(attribute) in your table with the name "name" and data type varchar with character limit of 20.
you can further add more attributes in the same manner.
to insert values in the table you need this:
insert into "table name" values(123,qwew,wsd,2342)
the data in the brackets above depends on the attributes of your table.
and now you have created a simple table.
you can update, delete, alter, drop the table.

CREATE TABLE PET_OWNER(
OwnerID Int NOT NULL IDENTITY(1001, 1),
LastName Char(25) NOT NULL,
FirstName Char(25) NOT NULL,
Phone Char(12) NULL,
Email VarChar(100) NULL,
CONSTRAINT OWNER_PK PRIMARY KEY(OwnerID)
);

CREATE TABLE BREED(
BreedName VarChar(100) NOT NULL,
MinWeight Numeric(4,1) NULL,
MaxWeight Numeric(4,1) NULL,
AverageLifeExpectancy Numeric(4,1) NULL,
CONSTRAINT BREED_PK PRIMARY KEY(BreedName)
);

CREATE TABLE PET(
PetID Int NOT NULL IDENTITY(101,1),
[Name] Char (50) NOT NULL,
[Type] Char (25) NOT NULL,
Breed VarChar(100) NULL,
DOB DateTime NULL,
OwnerID Int NOT NULL,
CONSTRAINT PET_PK PRIMARY KEY(PetID),
CONSTRAINT PET_OWNER_FK FOREIGN KEY(OwnerID)
REFERENCES PET_OWNER(OwnerID) ON DELETE CASCADE
);

CREATE TABLE PET_3(
PetID Int NOT NULL IDENTITY(101,1),
[Name] Char (50) NOT NULL,
[Type] Char (25) NOT NULL,
Breed VarChar(100)NULL,
DOB DateTime NULL,
[Weight] Numeric(4,1) NULL,
OwnerID Int NOT NULL,
CONSTRAINT PET_3_PK PRIMARY KEY(PetID),
CONSTRAINT PET_3_OWNER_FK FOREIGN KEY(OwnerID)
REFERENCES PET_OWNER(OwnerID) ON DELETE CASCADE
);

ALTER TABLE PET
ADD CONSTRAINT PET_BREED_FK FOREIGN KEY(Breed)
REFERENCES BREED(BreedName) ON UPDATE CASCADE
;

ALTER TABLE PET_3
ADD CONSTRAINT PET_3_BREED_FK FOREIGN KEY(Breed)
REFERENCES BREED(BreedName) ON UPDATE CASCADE
;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.