Using the SQL below for for Veterinary Services can someone copy and add this ne

Question

Using the SQL below for for Veterinary Services can someone copy and add this new update in the code.

Charitable organizations will sometimes offer aid or special services to animals needing to undergo certain procedures. Add table(s) to track these organizations and which services they partner with the veterinary clinic to provide.

CREATE TABLE Employee(EmployeeId VARCHAR(10),
EmployeeName VARCHAR(50),
Address VARCHAR(150),
Gender VARCHAR(1),
History VARCHAR(250),
NationalId VARCHAR(25),
Salary DOUBLE,
ContactNo VARCHAR(15),
PeriodFrom DATE,
PeriodTo DATE,
PRIMARY KEY (EmployeeId));

CREATE TABLE Animal(PatientID VARCHAR(25),
PatientName VARCHAR(50),
Breed VARCHAR(25),
AnimalType VARCHAR(25),
DoctorID VARCHAR(10),
PRIMARY KEY (PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctor(EmployeeID));

CREATE TABLE DoctorType(DTypeID VARCHAR(10),
DType VARCHAR(50),
PRIMARY KEY(DTypeID));
  

CREATE TABLE Doctor(EmployeeId VARCHAR(10),
DTypeID VARCHAR(10),
FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId),
FOREIGN KEY (DTypeID) REFERENCES DoctorType (DTypeID));

CREATE TABLE Treatment(TreatmentID VARCHAR(10),
TreatmentName VARCHAR(75),
Description VARCHAR(250),
PRIMARY KEY(TreatmentID));

CREATE TABLE Medicine(MCode VARCHAR(20),
MedicineName VARCHAR(50),
Price DOUBLE, PRIMARY KEY(MCode));

CREATE TABLE AdmissionDetails(PatientID VARCHAR(25),
DateAdmitted DATE,
DateDischarged DATE,
PRIMARY KEY (PatientID));

CREATE TABLE TreatmentTaken(PatientID VARCHAR(25),
TreatmentID VARCHAR(10),
FOREIGN KEY (PatientID) REFERENCES Animal (PatientID),
FOREIGN KEY (TreatmentID) REFERENCES Treatment (TreatmentID));

CREATE TABLE MedicineTaken(MCode VARCHAR(20),
PatientID VARCHAR(25),
Quantity INT,
FOREIGN KEY(MCode)REFERENCES Medicine (MCode),
FOREIGN KEY(PatientID)REFERENCES Animal (PatientID));

ALTER TABLE AdmissionDetails ADD COLUMN PatientName VARCHAR(50);
ALTER TABLE AdmissionDetails ADD COLUMN Breed VARCHAR(25);
ALTER TABLE AdmissionDetails ADD COLUMN AnimalType VARCHAR(25);
ALTER TABLE AdmissionDetails ADD COLUMN TreatmentID VARCHAR(10);
ALTER TABLE AdmissionDetails ADD COLUMN TreatmentName VARCHAR(75);
ALTER TABLE AdmissionDetails ADD COLUMN Description VARCHAR(250);
ALTER TABLE AdmissionDetails ADD COLUMN MCode VARCHAR(20);
ALTER TABLE AdmissionDetails ADD COLUMN Quantity INT;
ALTER TABLE AdmissionDetails ADD COLUMN MedicineName VARCHAR(50);
ALTER TABLE AdmissionDetails ADD COLUMN Price DOUBLE

SELECT AD.PatientID, AD.DateAdmitted, AD.DateDischarged, A.PatientName, A.Breed, A.AnimalType, TT. TreatmentID, T.TreatmentName, T.Description, MT.MCode, MT.Quantity, M.MedicineName, M.Price
FROM AdmissionDetails AD
JOIN Animal A ON AD.PatientID = A.PatientID
JOIN TreatmentTaken TT ON AD.PatientID = TT.PatientID
JOIN Treatment T ON TT. TreatmentID = T. TreatmentID
JOIN MedicineTaken MT ON AD.PatientID = MT.PatientID
JOIN Medicine M ON MT.MCode = M.MCode

Explanation / Answer

Hi, I have added two more tables to the given schema to specify the role of charitable organisation and the procedure they follow. Basically only one table can do the task but to add more info to the procedure i have added the second table.

First table is "CharitableOrganisation" and second table is "ProcedureFollowed" having below DDL::

CREATE TABLE CharitableOrganisations(
OrgId VARCHAR(10), OrgName VARCHAR(20),
ServiceOffered VARCHAR(20),
Benificiary VARCHAR(20),
Procedure VARCHAR(10),
PRIMARY KEY(OrgId),
FOREIGN KEY(ServiceOffered)REFERENCES Treatment (TreatmentId),
FOREIGN KEY(Benificiary)REFERENCES Animal (PatientId),
FOREIGN KEY(Procedure)REFERENCES ProcedureFollowed (ProcedureId)
);
CREATE TABLE ProcedureFollowed(
ProcedureId VARCHAR(10),
ProcedureDescription VARCHAR(50),
PRIMARY KEY (ProcedureId)
);

I have modified the given Select query, in case you need to track the charitable organisationOrganisation:

SELECT CO.OrgName, CO.Procedure, AD.PatientID, AD.DateAdmitted, AD.DateDischarged, A.PatientName, A.Breed, A.AnimalType, TT. TreatmentID, T.TreatmentName, T.Description, MT.MCode, MT.Quantity, M.MedicineName, M.Price
FROM AdmissionDetails AD
JOIN Animal A ON AD.PatientID = A.PatientID
JOIN TreatmentTaken TT ON AD.PatientID = TT.PatientID
JOIN Treatment T ON TT. TreatmentID = T. TreatmentID
JOIN MedicineTaken MT ON AD.PatientID = MT.PatientID
JOIN Medicine M ON MT.MCode = M.MCode
JOIN CharitableOrganisations CO ON TT. TreatmentID = CO. ServiceOffered;

If you get any doubt feel free to comment, i will try to respond asap. Happy chegging.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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