Veterinarian Office Case: Part 4 The veterinarian office created a database to keep track of owner and pet information. It also includes tracking appointments and treatments for each pet. Unfortunately, the veterinarian database was not created correctly and needs modification. Read the question and select the correct dropdown to answer the question. Owner OWNER_ID OWNER_FNAME OWNER_LNAME OWNER_ST_ADDRESS OWNER_CITY OWNER_STATE OWNERE_POSTAL_CODE OWNER_PHONE OWNER_EMAIL 1 Melissa Canton NULL NULL NV NULL NULL NULL 2 Clay Sheperd Lousetown Road VC Highlands NV 89521 3456789 chsep@gmail.com 3 Helen Harvey 345 Pine Road Reno NV 89521 3451234 hharvey@gmail.com 4 April Windon 123 Doughton Road Reno NV 89521 3454879 awindon@gmail.com 5 Emily Minden 540 Fandango Pass Road VC Highlands NV 89521 3455190 emindn@gmail.com Pet PET_ID PET_BREED PET_NAME OWNER_ID 1 Shepherd Bailey 1 2 Husky Sam 2 3 Terrier Bentley 3 4 Shorthair Kiki 1 5 Burmese Boo 4 6 Shorthair Tigger 5 7 Parakeet Bob 5 8 Macaw Biranna 2 Appointments APPOINTMENT_ID PET_ID APPOINTMENT_DATE APPOINTMENT_REASON PAID INVOICE 1 1 2019-10-19 No feathers N 99.99 2 7 2019-10-11 Has temperature Y 53.34 3 5 2019-10-30 Weighs a lot Y 25.11 4 3 2019-11-05 Yearly checkup Y 99.99 5 5 2019-11-05 Biting people N 125.67 6 1 2010-10-30 Vaccinations N 125.55 7 3 2019-11-07 Chasing tail Y 99.99 8 7 2019-10-30 Vaccinations N 125.55 Treatment_Appt TREATMENT_ID APPOINTMENT_ID 1 2 2 1 9 6 6 3 7 3 7 4 8 5 7 7 9 8 Treatment TREATMENT_ID TREATMENT_PLAN DIAGNOSIS 1 Rest and do not run around Exhaustion 2 Put cream on feathers Feathers falling out 3 Put medicine in ears Ear mites 4 Change diet Losing weight 5 Use ointment Fur falling out 6 Get more exercise Gaining too much weight 7 Change diet and provide more exercise opportunities Sleeps all the time 8 Take to training class Bites people 9 Gave vaccination Time for vaccinations 1. In the TREATMENT table, a new row of data needs to be added using PL/SQL. Include a message indicating the row was added successfully. Select the correct SQL. a. INSERT INTO Treatment VALUES (9, 'Give more greens', 'Eating grass'; b. SET SERVEROUTPUT ON BEGIN INSERT INTO Treatment VALUES (9, 'Give more greens', 'Eating grass'; DBMS_OUTPUT.PUTLINE('New row added to Treatment'); END; c. SET SERVEROUTPUT ON BEGIN INSERT INTO Treatment VALUES (9, 'Give more greens', 'Eating grass'; END; 2. Create a procedure to add 0 to the invoice column if it is null. Select the correct SQL. a. CREATE OR REPLACE PROCEDURE CHECK_INVOICE AS BEGIN UPDATE Appointments SET INVOICE = 0 WHERE INVOICE IS NULL; DBMS_OUTPUT.PUTLINE("Update completed"); END; b. CREATE OR REPLACE PROCEDURE CHECK_INVOICE AS BEGIN UPDATE Appointments SET INVOICE = 0 WHERE INVOICE = NULL; DBMS_OUTPUT.PUTLINE('Update completed'); END; c. UPDATE Appointments SET INVOICE = 0; 3. Add a check constraint to the Appointments table. Ensure that the INVOICE is greater than or equal to 0. Select the correct SQL. a. MODIFY Appointments CONSTRAINT INVOICE > 0; b. ALTER TABLE Appointments ADD CHECK (INVOICE >=0); c. ALTER TABLE Appointments ADD CHECK (INVOICE>0); 4. Add a foreign key to Pet referencing Owner. Also, add a check constraint ensuring PET_AGE is greater than 0. Select the correct SQL. a. ALTER TABLE Pet ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID) ADD CHECK (PET_AGE >0); b. ALTER TABLE Pet ADD FOREIGN KEY (OWNER_ID) ADD CHECK (PET_ID >0); c. MODIFY TABLE Pet ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID) ADD CHECK (PET_ID >0)

Veterinarian Office Case: Part 4 The veterinarian office created a database to keep track of owner and pet information. It also includes tracking appointments and treatments for each pet. Unfortunately, the veterinarian database was not created correctly and needs modification. Read the question and select the correct dropdown to answer the question. Owner OWNER_ID OWNER_FNAME OWNER_LNAME OWNER_ST_ADDRESS OWNER_CITY OWNER_STATE OWNERE_POSTAL_CODE OWNER_PHONE OWNER_EMAIL 1 Melissa Canton NULL NULL NV NULL NULL NULL 2 Clay Sheperd Lousetown Road VC Highlands NV 89521 3456789 chsep@gmail.com 3 Helen Harvey 345 Pine Road Reno NV 89521 3451234 hharvey@gmail.com 4 April Windon 123 Doughton Road Reno NV 89521 3454879 awindon@gmail.com 5 Emily Minden 540 Fandango Pass Road VC Highlands NV 89521 3455190 emindn@gmail.com Pet PET_ID PET_BREED PET_NAME OWNER_ID 1 Shepherd Bailey 1 2 Husky Sam 2 3 Terrier Bentley 3 4 Shorthair Kiki 1 5 Burmese Boo 4 6 Shorthair Tigger 5 7 Parakeet Bob 5 8 Macaw Biranna 2 Appointments APPOINTMENT_ID PET_ID APPOINTMENT_DATE APPOINTMENT_REASON PAID INVOICE 1 1 2019-10-19 No feathers N 99.99 2 7 2019-10-11 Has temperature Y 53.34 3 5 2019-10-30 Weighs a lot Y 25.11 4 3 2019-11-05 Yearly checkup Y 99.99 5 5 2019-11-05 Biting people N 125.67 6 1 2010-10-30 Vaccinations N 125.55 7 3 2019-11-07 Chasing tail Y 99.99 8 7 2019-10-30 Vaccinations N 125.55 Treatment_Appt TREATMENT_ID APPOINTMENT_ID 1 2 2 1 9 6 6 3 7 3 7 4 8 5 7 7 9 8 Treatment TREATMENT_ID TREATMENT_PLAN DIAGNOSIS 1 Rest and do not run around Exhaustion 2 Put cream on feathers Feathers falling out 3 Put medicine in ears Ear mites 4 Change diet Losing weight 5 Use ointment Fur falling out 6 Get more exercise Gaining too much weight 7 Change diet and provide more exercise opportunities Sleeps all the time 8 Take to training class Bites people 9 Gave vaccination Time for vaccinations 1. In the TREATMENT table, a new row of data needs to be added using PL/SQL. Include a message indicating the row was added successfully. Select the correct SQL. a. INSERT INTO Treatment VALUES (9, 'Give more greens', 'Eating grass'; b. SET SERVEROUTPUT ON BEGIN INSERT INTO Treatment VALUES (9, 'Give more greens', 'Eating grass'; DBMS_OUTPUT.PUTLINE('New row added to Treatment'); END; c. SET SERVEROUTPUT ON BEGIN INSERT INTO Treatment VALUES (9, 'Give more greens', 'Eating grass'; END; 2. Create a procedure to add 0 to the invoice column if it is null. Select the correct SQL. a. CREATE OR REPLACE PROCEDURE CHECK_INVOICE AS BEGIN UPDATE Appointments SET INVOICE = 0 WHERE INVOICE IS NULL; DBMS_OUTPUT.PUTLINE("Update completed"); END; b. CREATE OR REPLACE PROCEDURE CHECK_INVOICE AS BEGIN UPDATE Appointments SET INVOICE = 0 WHERE INVOICE = NULL; DBMS_OUTPUT.PUTLINE('Update completed'); END; c. UPDATE Appointments SET INVOICE = 0; 3. Add a check constraint to the Appointments table. Ensure that the INVOICE is greater than or equal to 0. Select the correct SQL. a. MODIFY Appointments CONSTRAINT INVOICE > 0; b. ALTER TABLE Appointments ADD CHECK (INVOICE >=0); c. ALTER TABLE Appointments ADD CHECK (INVOICE>0); 4. Add a foreign key to Pet referencing Owner. Also, add a check constraint ensuring PET_AGE is greater than 0. Select the correct SQL. a. ALTER TABLE Pet ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID) ADD CHECK (PET_AGE >0); b. ALTER TABLE Pet ADD FOREIGN KEY (OWNER_ID) ADD CHECK (PET_ID >0); c. MODIFY TABLE Pet ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID) ADD CHECK (PET_ID >0)

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

Veterinarian Office Case: Part 4

The veterinarian office created a database to keep track of owner and pet information. It also includes tracking appointments and treatments for each pet.

Unfortunately, the veterinarian database was not created correctly and needs modification. Read the question and select the correct dropdown to answer the question.

Owner

OWNER_ID	OWNER_FNAME	OWNER_LNAME	OWNER_ST_ADDRESS	OWNER_CITY	OWNER_STATE	OWNERE_POSTAL_CODE	OWNER_PHONE	OWNER_EMAIL
1	Melissa	Canton	NULL	NULL	NV	NULL	NULL	NULL
2	Clay	Sheperd	Lousetown Road	VC Highlands	NV	89521	3456789	chsep@gmail.com
3	Helen	Harvey	345 Pine Road	Reno	NV	89521	3451234	hharvey@gmail.com
4	April	Windon	123 Doughton Road	Reno	NV	89521	3454879	awindon@gmail.com
5	Emily	Minden	540 Fandango Pass Road	VC Highlands	NV	89521	3455190	emindn@gmail.com

Pet

PET_ID	PET_BREED	PET_NAME	OWNER_ID
1	Shepherd	Bailey	1
2	Husky	Sam	2
3	Terrier	Bentley	3
4	Shorthair	Kiki	1
5	Burmese	Boo	4
6	Shorthair	Tigger	5
7	Parakeet	Bob	5
8	Macaw	Biranna	2

Appointments

APPOINTMENT_ID	PET_ID	APPOINTMENT_DATE	APPOINTMENT_REASON	PAID	INVOICE
1	1	2019-10-19	No feathers	N	99.99
2	7	2019-10-11	Has temperature	Y	53.34
3	5	2019-10-30	Weighs a lot	Y	25.11
4	3	2019-11-05	Yearly checkup	Y	99.99
5	5	2019-11-05	Biting people	N	125.67
6	1	2010-10-30	Vaccinations	N	125.55
7	3	2019-11-07	Chasing tail	Y	99.99
8	7	2019-10-30	Vaccinations	N	125.55

Treatment_Appt

TREATMENT_ID	APPOINTMENT_ID
1	2
2	1
9	6
6	3
7	3
7	4
8	5
7	7
9	8

Treatment

TREATMENT_ID	TREATMENT_PLAN	DIAGNOSIS
1	Rest and do not run around	Exhaustion
2	Put cream on feathers	Feathers falling out
3	Put medicine in ears	Ear mites
4	Change diet	Losing weight
5	Use ointment	Fur falling out
6	Get more exercise	Gaining too much weight
7	Change diet and provide more exercise opportunities	Sleeps all the time
8	Take to training class	Bites people
9	Gave vaccination	Time for vaccinations

1. In the TREATMENT table, a new row of data needs to be added using PL/SQL. Include a message indicating the row was added successfully. Select the correct SQL.

a. INSERT INTO Treatment

VALUES (9, 'Give more greens', 'Eating grass';

b. SET SERVEROUTPUT ON

BEGIN

INSERT INTO Treatment

VALUES (9, 'Give more greens', 'Eating grass';

DBMS_OUTPUT.PUTLINE('New row added to Treatment');

END;

c. SET SERVEROUTPUT ON

BEGIN

INSERT INTO Treatment

VALUES (9, 'Give more greens', 'Eating grass';

END;

2. Create a procedure to add 0 to the invoice column if it is null. Select the correct SQL.

a. CREATE OR REPLACE PROCEDURE CHECK_INVOICE

AS BEGIN

UPDATE Appointments

SET INVOICE = 0

WHERE INVOICE IS NULL;

DBMS_OUTPUT.PUTLINE("Update completed");

END;

b. CREATE OR REPLACE PROCEDURE CHECK_INVOICE

AS BEGIN

UPDATE Appointments

SET INVOICE = 0

WHERE INVOICE = NULL;

DBMS_OUTPUT.PUTLINE('Update completed');

END;

c. UPDATE Appointments

SET INVOICE = 0;

3. Add a check constraint to the Appointments table. Ensure that the INVOICE is greater than or equal to 0. Select the correct SQL.

a. MODIFY Appointments

CONSTRAINT INVOICE > 0;

b. ALTER TABLE Appointments

ADD CHECK (INVOICE >=0);

c. ALTER TABLE Appointments

ADD CHECK (INVOICE>0);

4. Add a foreign key to Pet referencing Owner. Also, add a check constraint ensuring PET_AGE is greater than 0. Select the correct SQL.

a. ALTER TABLE Pet

ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID)

ADD CHECK (PET_AGE >0);

b. ALTER TABLE Pet

ADD FOREIGN KEY (OWNER_ID) ADD CHECK (PET_ID >0);

c. MODIFY TABLE Pet

ADD FOREIGN KEY (OWNER_ID) REFERENCES Owner (OWNER_ID)

ADD CHECK (PET_ID >0);

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.