CREATE TABLE Employee(EmployeeId VARCHAR(10), EmployeeName VARCHAR(50), Address

Question

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)); 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)); hame attcnds DOCTOR vetTec

Explanation / Answer

Run the above two alter table queries which does the required work.

The first query adds the treatment and medice and time information to the table AdmissionDetails .

Second query create the foreign key relationship betweent the treatment and medicine tables so that you can get that information.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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