Project 28: Daily Invoice Foothills Animal Hospital is a full-service small anim

Question

Project 28: Daily Invoice Foothills Animal Hospital is a full-service small animal veterinary hospital located in Morrison, Colorado, specializing in routine medical care, vaccinations, laboratory testing, and surgery. The hospital has experienced tremendous growth over the past six months due to customer referrals. While Foothills Animal Hospital has typically kept its daily service records in a workbook format, it feels the need to expand its reporting capabilities to develop a relational database as a more functional structure. Page AYK18 Foothills Animal Hospital needs help developing a database, specifically: Create a customer table-name, address, phone, and date of entrance. Create a pet table-pet name, type of animal, breed, gender, color, neutered/spayed, weight, and comments Create a medications table-medication code, name of medication, and cost of medication Create a visit table-details of treatments performed, medications dispensed, and date of the visit. Produce a daily invoice report Figure AYK.2 displays a sample daily invoice report that the Foothills Animal Hospital accountants have requested. Foothills Animal Hospital organizes its treatments using the codes displayed in Figure AYK.3. The entities and primary keys for the database have been identified in Figure AYK.4

Explanation / Answer

Please find the Table Structure below:-

CREATE TABLE CUSTOMER(

CUST_ID NUMBER,

NAME VARCHAR2(50),

ADDRESS VARCHAR2(50),

PHONE VARCHAR2(10),

ENTRANCE_DATE DATE,

CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUST_ID));

CREATE TABLE PET(

PET_ID NUMBER,

PET_NAME VARCHAR2(100),

PET_TYPE VARCHAR2(50),

BREED VARCHAR2(50),

GENDER CHAR(1),

COLOR VARCHAR2(20),

WEIGHT NUMBER(3),

NEUTERED_SPAYED CHAR(1),

COMMENTS VARCHAR2(2000),

CONSTRAINT PET_PK PRIMARY KEY (PET_ID));

CREATE TABLE MEDICATIONS(

MEDICATION_CODE NUMBER,

MEDICATION_NAME VARCHAR2(100),

MEDICATION_COST NUMBER(7,3),

CONSTRAINT MEDICATION_PK PRIMARY KEY (MEDICATION_CODE));

CREATE TABLE VISIT(

VISIT_ID NUMBER,

CUST_ID NUMBER,

PET_ID NUMBER,

MEDICATION_CODE NUMBER,

VISIT_DATE DATE,

CONSTRAINT VISIT_PK PRIMARY KEY (VISIT_ID),

CONSTRAINT VISIT_FK1 FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID),

CONSTRAINT VISIT_FK2 FOREIGN KEY (PET_ID) REFERENCES PET(PET_ID),

CONSTRAINT VISIT_FK3 FOREIGN KEY (MEDICATION_CODE) REFERENCES MEDICATIONS (MEDICATION_CODE));

Produce a Daily Invoice Report

SELECT CUST_ID, VISIT_DATE, SUM(MEDICATION_COST) AS 'TOTAL_BILL' FROM VISIT JOIN CUSTOMER ON CUSTOMER.CUST_ID = VISIT.CUST_ID JOIN MEDICATIONS ON MEDICATIONS.MEDICATION_CODE = VISIT.MEDICATION_CODE WHERE VISIT_DATE = SYSDATE();

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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