1

I have this patient table which is an owner entity:

CREATE TABLE PATIENT 
(
    FILE_NO NUMBER(4) PRIMARY KEY, 
    NAME VARCHAR2(25), 
    SEX VARCHAR2 (7), 
    BLOOD_GROUP VARCHAR2(3),
    ALLERGIES VARCHAR (30), 
    BIRTH_DATE DATE, 
    INSURANCE_COMPANY VARCHAR (30)
);

and the appointment table is the weak entity

CREATE TABLE APPOINTMENT 
(
    APP_NO NUMBER(4),
    FILE_NO NUMBER (4),
    FOREIGN KEY (FILE_NO) REFERENCES PATIENT(FILE_NO),
    CONSTRAINT APPOINTMENT_PK PRIMARY KEY (APP_NO,FILE_NO),
    DATE_TIME TIMESTAMP, 
    DOCTOR_NAME VARCHAR2(25), 
    DEPARTMENT_NAME VARCHAR2(25)
);

they have a many to many relationship. Is this a correct table for the relationship?

CREATE TABLE APPOINTED_TO 
(
    APP_NO NUMBER(4),
    FILE_NO NUMBER (4),
    CONSTRAINT APPOINTED_TO_PK PRIMARY KEY (APP_NO,FILE_NO),
    CONSTRAINT APP_TO_FK FOREIGN KEY (APP_NO,FILE_NO)
        REFERENCES APPOINTMENT(APP_NO,FILE_NO)
);

My teacher insists I make a third table for the relationship. I can't take out the FILE_NO from the Appointment table because it's a weak entity.

It didn't make sense to me that's why I wanted to ask for an outside opinion.

0

The FOREIGN KEY is not correct. You must create 2 of them. One the PATIENT table for the FILE_NO field and one on the APPOINTMENT table for the APP_NO field.

PAT_TO_FK FOREIGN KEY APT_PATIENT_FK (FILE_NO) REFERENCES PATIENT(FILE_NO)
APP_TO_FK FOREIGN KEY APT_APPOINTMENT_FK (APP_NO) REFERENCES APPOINTMENT(APP_NO)
  • It gives me an error : ORA-02270: no matching unique or primary key for this column-list. Because the APPOINTMENT has a composite primary key – Sarah_b Dec 1 '15 at 14:00
  • Isn't the APP_NO a unique identifier for the APPOINTMENT table? Either you use the third table or you put FILE_NO in the APPOINTMENT table to create a unique key. Your solution is not logic (to me)? – Marco Dec 1 '15 at 14:26
  • @Sarah_b: I think your primary two tables should be PATIENT and DOCTOR, then APPOINTMENT would be the third table linking those two. – Andriy M Dec 1 '15 at 15:55
0

Why would you not just create a 3rd table (link table as they are called)

CREATE TABLE PATIENTAPPOINTS (
    PATAPP_NO NUMBER PRIMARY KEY,
    FILE_NO NUMBER(4) ,
    FOREIGN KEY ( FILE_NO ) REFERENCES PATIENT ( FILE_NO ) ,
    APP_NO NUMBER(4),
    FOREIGN KEY (APP_NO ) REFERENCES APPOINTMENT (APP_NO)
);

Not the answer you're looking for? Browse other questions tagged or ask your own question.