DBMS Lab4 [MySQL]

Exercise:  4

Patients  are  identified  by  an  SSN,  and  their  names, addresses,  and  ages  must  be  recorded. Doctors are identified by an SSN. For each doctor, the name, specialty, and years of experience must be recorded. Each pharmaceutical company is identified by name; it has an address and one phone number. For each drug, the trade name andformula must be recorded. Each drug issold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. Each pharmacy has a name, address, and phone number. Each  patient  is  checked  up  by  some  doctor.  Every  doctor  has  at  least  one  patient. Eachpharmacy  sells  several  drugs  and  has  a  price  for  each.  A  drug  could  be  sold  at  severalpharmacies, and the price could vary from one pharmacy to another. Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could

obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it. Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company  can  contract  with  several  pharmacies,  and  a pharmacy  can  contract  with  several pharmaceutical  companies.  For  each  contract,  you  have  to  store  a  start  date,  an  end  date, supervisor and the text of the contract.

                                     

ER-DIAGRAM: 

Relational schema:

Queries:

a)         List the details of patients who are 20 years old and have been checked by eye-specialist.

mysql> select p.name from patient p, prescription pr, doctor d where p.ssn=pr.p_ssn and d.d_ssn=pr.d_ssn and p.age>20 and d.speciality="eye";

b) List the details of doctors who have given the prescription to more than 20 patients in year 2013.

mysql> select d.name from doctor d,prescription pr where d.d_ssn=pr.d_ssn and pr.p_date>"20130101" group by pr.d_ssn having count(p_ssn)>=4;

c) List the details of pharmaceutical companies whosupply drug to more than 10 pharmacies in the same city where company is located.

mysql> select p.name,p.ph_no from pharm_comp p,contract c, pharmacy ph where ph.name=c.pname andp.name=c.pc_name and p.address=ph.address

having count(c.pname)>=4;

a)         List the details of drug supplied by only one pharmaceutical company.

mysql> select d.* from drug d,seller s,contract c where d.trade_name=s.trade_name and s.p_name=c.pname and c.pc_name="cipla";

b)         List the details of drug supplied by all pharmaceutical companies.

mysql> select d.*,c.pc_name from drug d, contract c, seller s where s.p_name=c.pname and d.trade_name=s.trade_name;

 

Posted in: II_DBMS_lab