Use the following tables and sample data for your answers to the questions in this assignment. (Please note that the tables have already been normalized.) Use MS SQL Server Management Studio to write and test your SQL statements. When you are finished, put all of your SQL statements into a single .sql script file. You may use comments to mark Question 1, etc.. PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID) SQL CREATE STATEMENTS (8 points)

1. Write an SQL CREATE TABLE statement to create the PET_OWNER table, with OwnerID as a surrogate key. Use appropriate data types and column properties as you see fit. (3 pts)

2. Write an SQL CREATE TABLE statement to create the PET table, creating a referential integrity constraint on OwnerID in PET. Assume that deletions should not cascade. (3 pts)

3. Create a database diagram in MS SQL Server Management Studio showing the relationship between the tables. Copy and paste to a Word or PDF document (in Management Studio, hold down Ctrl and select both tables, then in the Edit menu, select Copy Diagram to Clipboard; then in Microsoft Word paste the diagram; alternatively you can do a screenshot). (2 pts)

SQL INSERT STATEMENTS (4 points)

4. Write a series of 4 SQL statements to populate the PET_OWNER table, based on Figure 3-18 above. (2 pts) Open the attached Pet-Insert-Data.sql file and run it (Test first to make sure it matches with your created tables!! If not, make adjustments as necessary so that it runs properly!) to populate the PET table.

ADDITIONAL SQL SKILLS (8 points)

5. Write an SQL ALTER statement to add a new column to the PET table, called Location (this attribute represents the primary location where the pet is groomed). (2 pts)

6. Write an SQL UPDATE statement so that all dogs have a listed location of Fullerton. (1 pt) 7. Write an SQL UPDATE statement so that all cats have a listed location of Brea. (1 pt)

8. Write an SQL statement that deletes all values of Unknown in the PetBreed attribute of the PET table (note: you may need to change this field to allow null values first). (2 pts)

9. Write an SQL statement to delete the PET table not just the data, but the whole table. (2 pts)

Formatting and submission Put all SQL statements in a single SQL script file. At the bottom of the file, in a comment, type the following: By typing this statement, I am indicating that I completed this assignment individually and honestly. I did not copy answers from another person or source. Submit your SQL script and your Word/PDF file with your diagram through TITANium.

/***** PET DATA *************************************************************/

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

INSERT INTO PET VALUES('Teddy', 'Cat', 'Cashmier', '01-Feb-12', 2);

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

INSERT INTO PET VALUES('AJ', 'Dog', 'Collie Mix', '05-May-11', 3);

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

INSERT INTO PET(PetName, PetType, PetBreed, OwnerID)

VALUES('Woolley', 'Cat', 'Unknown', 2);

INSERT INTO PET VALUES('Buster', 'Dog', 'BorderCollie', '11-Dec-08', 4);

PET OWNER (OwnerlD, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerlD) FIGURE 3- PET OWNER Data OwnerID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail 555-537-8765 555-537-764 Richard.James@somewhere.com 555-537-6543 Liz Frier@somewhere.com Frier Trent Liz Miles.Trent@somewhere.com FIGURE 3-19 PET Data PetName PetType PetBreed Std. Poodle PetID PetDOB 27-Feb-11 01-Feb-12 17-Jul-10 05-May-11 06-Jun-09 OwnerID Teddy Fido AJ Cedro Wooley Buster Std. Poodle Collie Mix Border Collie 11-Dec-08