Question

In: Operations Management

Project 28: Daily Invoice Foothills Animal Hospital is a full-service small animal veterinary hospital located in...

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. 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 . The following business rules have been identified: Project Focus Your job is to complete the following tasks: 1. Develop and describe the entity-relationship diagram.2.create an Access database, populate it, and create any queries, forms, and/or reports as per the instructions in AYK 28

(All tables are created and populated,All Relationships are set between tables,query has been created to pull the specific data from the tables to create the report (Customer Name, Pet Name, Type of Animal, Treatment, and Price),This criterion is linked to a Learning OutcomeCreate a report that is something similar to Figure AYK.2 on page AYK.16 of the textbook)

Solutions

Expert Solution


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 ();


Related Solutions

Multiple Product Break-Even Analysis TPG Tax & Accounting is a full-service CPA firm located in Apache...
Multiple Product Break-Even Analysis TPG Tax & Accounting is a full-service CPA firm located in Apache Junction, Arizona. Assume that tax return services are classified into one of three categories: standard, complex, and full-service (includes end-of-year bookkeeping with tax return preparation). Assume that TPG’s fixed costs (rent, utilities, wages, and so forth) totaled $180,000 last year. Additional information from the prior year follows. Standard Complex Full-Service Billing rate $125 $250 $150 Average variable costs (45) (65) (50) Average contribution margin...
In this service learning based project, you are expected to identify a local small business (for...
In this service learning based project, you are expected to identify a local small business (for example, a grocery store) in your community, and apply your knowledge about IT and business to analyze the role of IT for this business, and develop an IT Plan for it. The plan would specify (at a high level) the key IT applications that the business should focus on, the underlying infrastructure technologies necessary to support these applications, how to go about achieving this...
Question 3 Small Town hospital splits its service into two categories: general care and obstetrics. Medicare...
Question 3 Small Town hospital splits its service into two categories: general care and obstetrics. Medicare will pay the hospital 120% of the total cost incurred to treat Medicare patients. The hospital has two service departments: general records and dietary. The general records’ costs are allocated to departments based upon a log of hours spent for each department. Dietary costs are allocated on the basis of the number of meals served. Results for the past period are summarized below Records...
RWE Enterprises: Expansion Project Analysis RWE Enterprises, Inc. (RWE) is a small manufacturing firm located in...
RWE Enterprises: Expansion Project Analysis RWE Enterprises, Inc. (RWE) is a small manufacturing firm located in the hills just outside of Nashville, TN. The firm is engaged in the manufacture and sale of feed supplements used by cattle raisers. The product has a molasses base but is supplemented with minerals and vitamins that are generally thought to be essential to the health and growth of beef cattle. The final product is put in 125-pound or 200-pound tubs that are then...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT