drop database if exists doctors;
create database doctors;
use doctors;

create table doctor (
  doctor_id int primary key auto_increment,
  first_name varchar(16),
  last_name varchar(16),
  specialty varchar(16)
);

create table patient (
  patient_id int primary key auto_increment,
  first_name varchar(16),
  last_name varchar(16),
  doctor_id int not null, -- must have a primary care provider

  foreign key (doctor_id) references doctor(doctor_id)
);

create table visit (
  doctor_id int,
  patient_id int,
  visit_date date,

  primary key (doctor_id, patient_id, visit_date),
  foreign key (doctor_id) references doctor(doctor_id),
  foreign key (patient_id) references patient(patient_id)
);

-- You may assume:

-- A doctor may have many patients
-- A patient will have only one primary care provider
-- A patient only visits a doctor once on a given date
-- Doctors are never patients
