Use the following tables and sample data for your answers to the questions in th

ID: 3736178 • Letter: U

Question

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

Explanation / Answer

Answers)

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)

Create table with OwnerID as a surrogate key.

create table PET_OWNER (OwnerID integer primary key, OwnerLastName varchar, OwnerFirstName varchar, OwnerPhone varchar, OwnerEmail varchar);

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)

CREATE TABLE PET creating a referential integrity constraint on OwnerID in PET. OwnerID is the foreign key.

create table PET (PetID integer primary key, PetName varchar, PetType varchar, PetBreed varchar, PetDOB varchar, OwnerID integer, FOREIGN KEY (OwnerID) REFERENCES PET_OWNER(OwnerID));

SQL INSERT STATEMENTS (4 points)

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

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

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

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

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

INSERT INTO PET(PetName, PetType, PetBreed, OwnerID) VALUES('Woolley', 'Cat', 'Unknown', 2);

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

select * from PET;

Output:
1|King|Dog|Std. Poodle|27-Feb-11|1
2|Teddy|Cat|Cashmier|01-Feb-12|2
3|Fido|Dog|Std. Poodle|17-Jul-10|1
4|AJ|Dog|Collie Mix|05-May-11|3
5|Cedro|Cat|Unknown|06-Jun-09|2
6|Woolley|Cat|Unknown||2
7|Buster|Dog|BorderCollie|11-Dec-08|4

INSERT INTO PET_OWNER VALUES(1,'Downs','Marsha','555-537-8765','Marsha.Downs@somewhere.com');
INSERT INTO PET_OWNER VALUES(2,'James','Richard','555-537-7654','James.Richard@somewhere.com');
INSERT INTO PET_OWNER VALUES(3,'Frier','Liz','555-537-6543','Liz.Frier@somewhere.com');
INSERT INTO PET_OWNER(OwnerLastName, OwnerFirstName,OwnerEmail) VALUES('Trent','Miles','Miles.Trent@somewhere.com');

select * from PET_OWNER;

Output:
1|Downs|Marsha|555-537-8765|Marsha.Downs@somewhere.com
2|James|Richard|555-537-7654|James.Richard@somewhere.com
3|Frier|Liz|555-537-6543|Liz.Frier@somewhere.com
4|Trent|Miles||Miles.Trent@somewhere.com

Navigate

Use the following tables and sample data for your answers to the questions in th

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

Use the following tables and sample data for your answers to the questions in th

Question

Explanation / Answer

Related Questions

Navigate