- CREATE TABLE doctor(doctor_id varchar(10),name varchar(25),speciality varchar(15),medical_school varchar(25),Primary Key(doctor_id));
- CREATE TABLE patient(patient_id varchar(10),name varchar(25),doctor_id varchar(10),insurance varchar(20),PRIMARY KEY(patient_id,doctor_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id))
- CREATE TABLE visit(visit_id varchar(10),check_in_date Date, check_out_date Date, doctor_id varchar(10),patient_id varchar(10), PRIMARY KEY(visit_id,doctor_id,patient_id),FOREIGN KEY(doctor_id) REFERENCES doctor.(doctorId), FOREIGN KEY (patient_id) REFERENCES patient(patient_id));
- CREATE TABLE test (test_id varchar(10), name varchar(25),test_date DATE,result varchar(50),PRIMARY KEY(test_id));
Problem 2
1. Give an E/R diagram for this database. List the primary keys, candidate keys, weak entities (if any), partial keys (if any), total participation and any key constraints
Primary Keys:
- reg_no ,
- SSN,
- FAA_NO
Weak entities: plane
Parital Keys:reg_no
Total participation:
Constraints: