Assignment:
Refer to the "CREATE TABLE" commands to answer the following questions.
Q1 a) Did the SQL programmer enforce the constraint that every doctor practices in a unique hospital? If so, how? If not, how do you know, and how, if at all, could this constraint have been implemented?
b) Did the SQL programmer enforce the constraint that every patient has at least one doctor? If so, how? If not, how do you know, and how, if at all, could this constraint have been implemented?
c) Did the SQL programmer enforce the constraint that every patient can be hospitalized in at most one hospital? If so, how? If not, how do you know, and how, if at all, could this constraint have been implemented?
Q2. Write SQL queries to response the following questions. Try to use a simple SELECT without sub-queries.
(a) Find all doctors who went to Harvard as their medical school and who have been practicing for at least 10 years.
(b) Find the names and SSNs of all doctors who have diagnosed anyone with an incurable disease.
(c) Find the patients who have made an appointment with a doctor who does not treat them.
CREATE TABLE Hospital
(
name VARCHAR(50),
location VARCHAR(100),
affiliation VARCHAR(25),
CONSTRAINT PK_Hospital PRIMARY KEY(name, location)
)
CREATE TABLE Doctor
(
SSN char(9),
name varchar(50),
med_school varchar(50),
years_in_practice int,
practicing_hospital_name VARCHAR(50),
practicing_hospital_location VARCHAR(100),
practicing_title VARCHAR(100),
CONSTRAINT PK_Doctor PRIMARY KEY(SSN)
)
CREATE TABLE Disease
(
name VARCHAR(50),
contagious bit,
curable bit,
severity INT,
CONSTRAINT PK_Disease PRIMARY KEY(name)
)
CREATE TABLE Patient
(
SSN char(9),
name VARCHAR(50),
birth_date DATETIME,
gender CHAR(1),
weight float,
CONSTRAINT PK_Patient PRIMARY KEY(SSN)
)
CREATE TABLE Treats
(
Patient_SSN CHAR(9),
Doctor_SSN CHAR(9),
CONSTRAINT PK_Treats PRIMARY KEY(Patient_SSN, Doctor_SSN),
CONSTRAINT FK_Treats_Patient FOREIGN KEY(Patient_SSN)
REFERENCES Patient(SSN),
CONSTRAINT FK_Treats_Doctor FOREIGN KEY(Doctor_SSN)
REFERENCES Doctor(SSN)
)
CREATE TABLE Appointment
(
location VARCHAR(100),
daytime DATETIME,
Patient_SSN CHAR(9),
Doctor_SSN CHAR(9),
CONSTRAINT PK_Appointment
PRIMARY KEY(location, daytime, Patient_SSN, Doctor_SSN),
CONSTRAINT FK_Appointment_Patient FOREIGN KEY(Patient_SSN)
REFERENCES Patient(SSN),
CONSTRAINT FK_Appointment_Doctor FOREIGN KEY(Doctor_SSN)
REFERENCES Doctor(SSN)
)
CREATE TABLE Diagnose
(
Patient_SSN CHAR(9),
Doctor_SSN CHAR(9),
disease_name VARCHAR(50),
CONSTRAINT PK_Diagnose PRIMARY KEY(Patient_SSN, Doctor_SSN,
disease_name),
CONSTRAINT FK_Diagnose_Patient FOREIGN KEY(Patient_SSN)
REFERENCES Patient(SSN),
CONSTRAINT FK_Diagnose_Doctor FOREIGN KEY(Doctor_SSN)
REFERENCES Doctor(SSN),
CONSTRAINT FK_Diagnose_Disease FOREIGN KEY(disease_name)
REFERENCES Disease(name)
)
CREATE TABLE Hospitalized
(
Patient_SSN CHAR(9),
Hospital_name VARCHAR(50),
Hospital_location VARCHAR(100),
ward VARCHAR(25),
room INT,
CONSTRAINT PK_Hospitalized PRIMARY KEY(Patient_SSN,
Hospital_name, Hospital_location),
CONSTRAINT FK_Hospitalized_Patient FOREIGN KEY(Patient_SSN)
REFERENCES Patient(SSN),
CONSTRAINT FK_Hospitalized_Hospital FOREIGN KEY(Hospital_name,
Hospital_location) REFERENCES Hospital(name, location)
)