Veterinarian Office Case: Part 5 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. A primary key needs to be added to the Owner table. Select the best field for the primary key. Also add a check constraint to ensure only NV can be added. Select the correct SQL. a. ALTER TABLE Owner ADD PRIMARY KEY (OWNER_ID) ADD CHECK (OWNER_STATE = 'NV'); b. ALTER TABLE Owner ADD PRIMARY KEY (OWNER_EMAIL) ADD CHECK (OWNER_STATE = 'NV'); c. ALTER TABLE Owner ADD PRIMARY KEY (OWNER_ID) CHECK (OWNER_STATE = 'NV'); 2. Create a procedure to add a reason to the Appointments table if the APPOINTMENT_REASON is blank. Ensure you output a message that the change was completed. Select the correct SQL. a. CREATE OR REPLACE PROCEDURE CHECK_APPOINTMENT_REASON AS BEGIN UPDATE Appointments SET APPOINTMENT_REASON = 'Not given' WHERE APPOINTMENT_REASON 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 APPOINTMENT_REASON = 'Not given' Where APPOINTMENT_REASON IS NULL; 3. Change the Appointments table so when a APPOINTMENT_ID is added a PET_ID must be added. Select the correct SQL. a. CHANGE TABLE Appointments MODIFY (PET_ID INTEGER NOT NULL); b. MODIFY TABLE Appointments MODIFY (PET_ID INTEGER NOT NULL); c. ALTER TABLE Appointments MODIFY (PET_ID INTEGER NOT NULL); 4. Add the appropriate foreign key(s) to the Treatment_Appt table. Select the correct SQL. a. ALTER TABLE Treatment_Appt ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID); ALTER TABLE Treatment_Appt ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID); b. ALTER TABLE Treatment_Appt ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID); c. ALTER TABLE Treatment_Appt ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID);

Veterinarian Office Case: Part 5 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. A primary key needs to be added to the Owner table. Select the best field for the primary key. Also add a check constraint to ensure only NV can be added. Select the correct SQL. a. ALTER TABLE Owner ADD PRIMARY KEY (OWNER_ID) ADD CHECK (OWNER_STATE = 'NV'); b. ALTER TABLE Owner ADD PRIMARY KEY (OWNER_EMAIL) ADD CHECK (OWNER_STATE = 'NV'); c. ALTER TABLE Owner ADD PRIMARY KEY (OWNER_ID) CHECK (OWNER_STATE = 'NV'); 2. Create a procedure to add a reason to the Appointments table if the APPOINTMENT_REASON is blank. Ensure you output a message that the change was completed. Select the correct SQL. a. CREATE OR REPLACE PROCEDURE CHECK_APPOINTMENT_REASON AS BEGIN UPDATE Appointments SET APPOINTMENT_REASON = 'Not given' WHERE APPOINTMENT_REASON 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 APPOINTMENT_REASON = 'Not given' Where APPOINTMENT_REASON IS NULL; 3. Change the Appointments table so when a APPOINTMENT_ID is added a PET_ID must be added. Select the correct SQL. a. CHANGE TABLE Appointments MODIFY (PET_ID INTEGER NOT NULL); b. MODIFY TABLE Appointments MODIFY (PET_ID INTEGER NOT NULL); c. ALTER TABLE Appointments MODIFY (PET_ID INTEGER NOT NULL); 4. Add the appropriate foreign key(s) to the Treatment_Appt table. Select the correct SQL. a. ALTER TABLE Treatment_Appt ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID); ALTER TABLE Treatment_Appt ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID); b. ALTER TABLE Treatment_Appt ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID); c. ALTER TABLE Treatment_Appt ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID);

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 5

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. A primary key needs to be added to the Owner table. Select the best field for the primary key. Also add a check constraint to ensure only NV can be added. Select the correct SQL.

a. ALTER TABLE Owner

ADD PRIMARY KEY (OWNER_ID)

ADD CHECK (OWNER_STATE = 'NV');

b. ALTER TABLE Owner

ADD PRIMARY KEY (OWNER_EMAIL)

ADD CHECK (OWNER_STATE = 'NV');

c. ALTER TABLE Owner

ADD PRIMARY KEY (OWNER_ID)

CHECK (OWNER_STATE = 'NV');

2. Create a procedure to add a reason to the Appointments table if the APPOINTMENT_REASON is blank. Ensure you output a message that the change was completed. Select the correct SQL.

a. CREATE OR REPLACE PROCEDURE CHECK_APPOINTMENT_REASON

AS BEGIN

UPDATE Appointments

SET APPOINTMENT_REASON = 'Not given'

WHERE APPOINTMENT_REASON 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 APPOINTMENT_REASON = 'Not given'

Where APPOINTMENT_REASON IS NULL;

3. Change the Appointments table so when a APPOINTMENT_ID is added a PET_ID must be added. Select the correct SQL.

a. CHANGE TABLE Appointments

MODIFY (PET_ID INTEGER NOT NULL);

b. MODIFY TABLE Appointments

MODIFY (PET_ID INTEGER NOT NULL);

c. ALTER TABLE Appointments

MODIFY (PET_ID INTEGER NOT NULL);

4. Add the appropriate foreign key(s) to the Treatment_Appt table. Select the correct SQL.

a. ALTER TABLE Treatment_Appt

ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID);

ALTER TABLE Treatment_Appt

ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID);

b. ALTER TABLE Treatment_Appt

ADD FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments (APPOINTMENT_ID);

c. ALTER TABLE Treatment_Appt

ADD FOREIGN KEY (TREATMENT_ID) REFERENCES Treatment (TREATMENT_ID);

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 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.