ISI CSB 2017

Question

Consider the following relational schema:

$\text{DOCTOR}(\textit{DoctorName}, \textbf{RegistrationNumber}$)

$\text{PATIENT}(\textbf{PatientID}, \textit{PatientName})$

$\text{ASSIGNED-TO}(\textit{ PatientID, DoctorName})$.

The primary keys for the DOCTOR and PATIENT relations are in $\textbf{bold}$. Write the tuple relational calculus expression(or an equivalent SQL query) to find the names of all patients who are assigned to more than one doctor.

Comments

Select P.Name

from Assign_to A1,Assign_to A2,Patient P

where A1.DoctorName <> A2.DoctorName and A1.PatientId = A2.PatientId and A1.PatientId= P.PatientId

---

@shivanisrivarshini you can't compare two DoctorName because they are not the primary key. It may be a case in your query that a patient P1 visits two Doctors D1 and D2, but unfortunately, both have the same Name as Dr. Mike. Then your query will not retrive P1. 

I think above Relational Schema does not follow the Referential Integrity constraints because DoctorName is the foreign key referencing to DoctorName of Doctor relation which is not a primary key. Thus it is violating the rules.

select P.PatientName
from Assigned_To as A1, Assigned_To as A2, Doctor as D1, Doctor as D2, Patient as P
where A1.PatientID = A2.PatientID AND A1.DoctorName = D1.DoctorName 
        AND A2.DoctorName = D2.DoctorName 
        AND D1.RegistrationNumber <> D2.RegistrationNumber 
        AND A1.PatientID = P.PatientID

---

Ya Your correct I didnt think in that way Thank You