Question

You should try this code in Microsoft SQL Server to get the practice of using the command line and getting syntax errors. You can submit to me your sql files or just copy and paste of your code in a word document. I may try to run it, so make sure it is error free. Or you can show screen shots of code running.

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

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.

CREATE TABLE PET_OWNER(

OwnerID Int IDENTITY(1, 1) NOT NULL,

OwnerLastName Char(25) NOT NULL,

OwnerFirstName Char(25) NOT NULL,

OwnerPhone Char(12) NULL,

OwnerEmail VarChar(100) NULL,

CONSTRAINT OWNER_PK PRIMARY KEY(OwnerID)

);

Inserting some data into your tables :

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone,OwnerEmail) VALUES('Downs', 'Marsha', '555-537-8765', '[email protected]');

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName,OwnerPhone, OwnerEmail) VALUES('James', 'Richard', '555-537-7654', '[email protected]');

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone,OwnerEmail) VALUES('Frier', 'Liz', '555-537-6543', '[email protected]');

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName,OwnerPhone, OwnerEmail) VALUES('Trent', 'Miles', ' ','[email protected]');

SELECT * FROM PET_OWNER;

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..

CREATE TABLE PET(

PetID Int IDENTITY(1,1) NOT NULL,

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)

);

1. create the referential integrity constraint on OwnerID in PET. You can use ALTER TABLE command.

ALTER TABLE PET

ADD CONSTRAINT OWNER_FK FOREIGN KEY(OwnerID) REFERENCES PET_OWNER(OwnerID);

Inserting some data into your tables :

INSERT INTO PET VALUES( 'King', 'Dog', 'Std.Poodle','2011-02-27',1);

INSERT INTO PET VALUES( 'Teddy', 'Dog', 'Std.Poodle','2012-02-01',2);

INSERT INTO PET VALUES( 'Fido', 'Dog', 'Std.Poodle','2010-07-17',1);

INSERT INTO PET VALUES( 'Aj', 'Dog', 'Collie Mix','2011-05-05',3);

INSERT INTO PET VALUES( 'Cedro', 'Cat', 'Unknown','2009-06-06',2);

INSERT INTO PET VALUES( 'Wolley', 'Cat', 'Unknown',' ',2);

INSERT INTO PET VALUES( 'Buster', 'Dog', 'BorderCollie','2008-12-11',4);

SELECT * FROM PET;