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.