Copy select statements below and update with: Add or alter table(s) to track all
ID: 3591369 • Letter: C
Question
Copy select statements below and update with: Add or alter table(s) to track all treatments and medicine given to an animal in a visit (addmissionDetails table). Include the time the treatment or medicine was given.
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));
Explanation / Answer
NOTE: With understanding that we need to alter the table AdmissionDetails, i have written ALTER SQLs.
Already provided table:
CREATE TABLE AdmissionDetails(PatientID VARCHAR(25),
DateAdmitted DATE,
DateDischarged DATE,
PRIMARY KEY (PatientID));
ALTER SQLs:
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 Query to fetch Treatment, Medicine and time details:
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