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

Reviews

Write a Review

 

Computer Engineering Questions & Answers

  Write down a class named testscores

Write down a class named TestScores. The class constructor should accept an array of test scores as its argument. The class should have a process that returns the average of the test scores.

  Define the best possible scenario for an organization

If in a hypothetical situation your company was capable to hire only either technically sound managers with weak leadership skills OR strong leaders with less knowledge/interest in routine management, that would you opt for and why.

  Weighted directed graph

Provided an example of the weighted directed graph G = (V, E) with weight function ω: E→R and source vertex s such that the G satisfies following property: For each edge (u,v)∈E,

  What is role of risk assessment

What is role of risk assessment

  Developing the java program

Write down a program in java to input the basic salary of 15 persons. Each one of them receives 25% of basic as hra (house rent allowance), 15 % of the basic as conveyance and 10 % of the basic as an entertainment allowance.

  Explain the difference between object-oriented programming

define the difference between object-oriented programming and procedural (or structural or processual) programming. What, if anything, does the OO model bring to the table and improve upon what was out there pre-OO.

  Xpath and xslt transformation

Explain the context in XPath. Explain at least three things you will require to perform an XSLT transformation.

  Find the waterfall project management methodologies

find the waterfall project management methodologies. If you are asked by your boss to start a specific new project, what are the possible questions you'll ask him/her or what information would you collect from him/her before you leave his/her offic..

  Write down an equation and draw a circuit

offer below is a truth table for a combinational logic circuit with three inputs and one output. Write an equation and draw a circuit which implements the function represented by this table.

  Contain what database applications are used

Analyzing the usage of databases in your organization. contain what database applications are used We use Oracle. Conclude by proposing improvements. For large organizations, restrict the scope of the paper to the department in which you work. I w..

  Web search engine

Utilizing a Web search engine, determine an article from the reputable source published in the past six months which reports on relative risk that comes.

  Point-to-point communication links

Suppose there is the wide-area network with N nodes, where N ≥ 2. Find out the smallest number of the point-to-point communication links such that each node within the network is able to talk to one another node?

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd