Share
Explore BrainMass

Write SQL queries to answer the following questions

Kindly refer to the "CREATE TABLE" commands given at the end to answer the following questions.

(1a) 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?
(1b) 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?
(1c) 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?

2. Write SQL queries to answer 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)
)

Solution Preview

(1a) No. This is not enforced.
I interpret this as, each doctor in the doctor table must practice at a different hospital, that is, no two doctors can practice at the same hospital.

For this, we want to add a UNIQUE constraint to the Doctor table, since no Doctor can practice at the same hospital, no row in the table can have the same practicing_hospital_name and practicing_hospital_location [we need both since there may be multiple locations for the same hospital name]. This is because each doctor will get one entry since SSN is the primary key and must be unique. So we add in the following constraint to table Doctor:

CONSTRAINT uc_Hospital UNIQUE (practicing_hospital_name, practicing_hospital_location)

Table Doctor then becomes:

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),
CONSTRAINT UC_Hospital UNIQUE (practicing_hospital_name, practicing_hospital_location)
)

For sake of completeness, I would actually add a foreign key constraint to make sure that each hospital in the Doctor table must come from a hospital defined in table Hospital.

Table Doctor would then become:

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),
CONSTRAINT UC_Hospital UNIQUE (practicing_hospital_name, practicing_hospital_location),
CONSTRAINT FK_Hospital FOREIGN KEY(practicing_hospital_name, practicing_hospital_location) REFERENCES Hospital(name, location)
)

The foreign key is formed with both ...

Solution Summary

The expert writes SQL queries. SQL programmers enforce for constraints are determined.

$2.19