patient (PCode, Name, Addr, Disease) Bed (Bed_No, RoomNo, loc) Relationship: - There is one-one relationship between patient and bed. Constraints: - Primary key, RoomNo must be greater than Bed_No, Addr should not be null.

 

Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints.

Patient (PCode, Name, Addr, Disease)

Bed (Bed_No, RoomNo, loc)

 Relationship: - There is one-one relationship between patient and bed.

Constraints: - Primary key, RoomNo must be greater than Bed_No, Addr should not be null.

 SQL>Create table Patient (Pcode int Primary Key,Name varchar(10),Addr varchar(10),Disease varchar(10));

SQL>insert into Patient values(101,'Ram','Pune','Cancer');

 SQL>Create table Bed(Bed_No int Primary Key,Room_No int,loc varchr(10),pcode int);

SQL>insert into Bed values(101,301,'rastapeth',101);

 Consider the above tables and execute the following queries:

 1. Display the details of patients who are from “Pimple Gurav”.

SQL>select * from patient where addr='pimple_gurav';

2. Delete the details of patient whose Bed_No is 1 and RoomNo is 105.

SQL>delete from Bed where Bed_No = 1 and Room_No = 105.

Q4. Consider the above tables and execute the following queries: 

 1. Display the count of patient room wise.

SQL>select count(patient.pcode) from patient,bed  where patient.pcode=bed.pcode group by Room_No;

 2. Display the names of patients who are admitted in room no 101.

SQL>select name from patient,bed where patient.pcode=bed.pcode and Room_No=101;

  3. Display the disease of patient whose bed_No is 1.

SQL>select disease from patient ,bed where patient.pcode=bed.pcode and Bed_No=1;

 4. Display the room_no and bed_no of patient whose name is “Mr Roy”.

SQL>select rno,bno from patient,bed where patient.pcode=bed.pcode  and name='Mr.Roy';

  5. Give the details of Patient who is admitted on 2nd flr in roomno 102.

SQL>select * from patient,bed  where patient.pcode=bed.pcode  and loc='second_floor' and Room_No=102;