CREATE TABLE Employee(EmployeeId VARCHAR(10), EmployeeName VARCHAR(50), Address
ID: 3585168 • Letter: C
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.