8.   We know that patients suffer illnesses. A patient can suffer many illnesses, and an illness can be suffered by many patients. The illness table need only contain the attributes illcode (the primary key) and illname (the name given to the illness, which, again, should not be left blank). Now, of course, we know that a patient CAN have the same illness at two different points in time. Draw the section of the ERD which illustrates this situation and then show the SQL commands necessary to create the tables.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATE TABLE illness

   

  ( illcode          CHAR(10),

     illname          CHAR(30)  NOT NULL,

  PRIMARY KEY (illcode));

 

CREATE TABLE suffers

   

  ( patid            CHAR(9),                              

     illcode          CHAR(30),

  illdate          DATE,

  PRIMARY KEY (patid, illcode, illdate),

  FOREIGN KEY (patid) REFERENCES patient(patid),

  FOREIGN KEY (illcode) REFERENCES illness(illcode))