Write SQL query to find patients who have not had any consultation. Explain the impact of adding a new entity Room with a one-to-many relationship to Consultation.

To find patients without consultations, we need the PATIENT table and the CONSULTATION table.

A LEFT JOIN from PATIENT to CONSULTATION will return all patients, and any matching consultations. If a patient has no consultations, the CONSULTATION columns will be NULL.

We filter the results where the ConsulNo from the CONSULTATION table is NULL, indicating no consultation record for that patient.

8. The SQL query to display all patients who have not had any consultation is:

SELECT P.Name, P.Surname
FROM PATIENT P
LEFT JOIN CONSULTATION C ON P.SSNo = C.SSNo
WHERE C.ConsulNo IS NULL;

The new entity is ROOM with attributes RoomID (unique), RoomLocation, and RoomSize. The relationship states: "A room can be used for many consultations but a consultation can make use of only one room." This is a one-to-many relationship from ROOM to CONSULTATION.

The database model will be affected by the addition of a new entity ROOM and a new relationship between ROOM and CONSULTATION. This relationship means that the CONSULTATION entity will need to include a foreign key referencing the ROOM entity's primary key.

The relations affected are the new ROOM table and the existing CONSULTATION table.

9. The database model will be affected by the addition of a new entity, ROOM, and a one-to-many relationship between ROOM and CONSULTATION. This means that each consultation will now be associated with a specific room.

The relations (tables) that will be affected are:

• ROOM (RoomID*, RoomLocation, RoomSize)
  • This is a new table to store information about each room. RoomID is the primary key.
• CONSULTATION (ConsulNo*, Date, Time, Matricle, SSNo, RoomID)
  • The CONSULTATION table will be modified to include RoomID as a foreign key, referencing the RoomID in the ROOM table. This links each consultation to a specific room.