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.

closed as unclear what you're asking by ypercubeᵀᴹ, Marco, McNets, mustaccio, joanolo May 3 '17 at 20:48

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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.