Find out all doctors who went to harvard

Assignment Help Computer Engineering

Reference no: EM1333306

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)

Reference no: EM1333306

Questions Cloud

Computation of absorption costing gross profit : The computation of absorption costing gross profit always involves subtracting.

Finding examples for mode : What examples can you find for mode?

Write down the sql command to add math scope : Write down the SQL command to add SUBJECT to TUTOR. The only values allowed for SUBJECT will be "Reading", "Math", and "ESL".

Discussing uniform commercial code : What is the purpose of the Uniform Commercial Code (UCC)? What types of transactions are covered by the UCC?

Find out all doctors who went to harvard : 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, might this constraint have been implemented.

Explain environmental policy : Explain Environmental Policy and Discuss the relationship between the Interstate Commerece Clause and environmental law

Financial information management evaluate in making decision : Which one of the following is non financial information that management might evaluate in making a decision?

Government actions that create barriers to exit : Government actions that create barriers to exit can have the unintended effect of retarding industrial development.

Necessary variables to form contract : What are the four elements necessary to form a contract? What is the effect of legal capacity on any of the three elements?

User Account

All Pages