Problem:
Identify a few of the entities that may be identified in your data model for the data management project.
Background Information
A doctor’s office in Delaware offers superb care to their patients. In order to keep all information organized, the doctor has tasked the secretary to create a database.
Purpose
This database will keep track of a patient’s information, doctor’s information, insurance, appointments, location, prescription, payments, bills, and visits.. The ID numbers for all tables are different and will be specific to each entity. The location may vary, depending upon the schedule of the doctor, and the appointment may vary depending upon the schedule of the doctor and patient.
Business Goals
The goal of this database is to organize the information of each patient, so the appointment schedule will run smoothly and take up a minimal amount of time from the patient.
System Overview and User Requirements
A secretary will log into the computer, using their given office credentials, and access the database containing the information. Once logged in, the secretary will fill in the information, accordingly, which will then be stored into a database. The secretary will be able to see and modify the information in the database. The database is accessible by all secretaries, working for that specific doctor.
Data Dictionary
| Patient | Doctor | Insurance | Appointment |
| PatientID (PK) | DoctorID (PK) | InsuranceID (PK) | AppointmentID (PK) |
| FirstName | DoctorName | InsuranceName | Date |
| LastName | SSN | Copay | Time |
| SSN | DoctorAddress | Phone | Reason |
| PatientAddress | DoctorEmail | Address | |
| BirthDate | WorkPhone | ||
| CellPhone | |||
| HomePhone | |||
| Age | |||
| Gender | |||
| Location | Prescription | Visits | Bills |
| LocationID (PK) | PescriptionID (PK) | VisitID (PK) | BillNumber (PK) |
| Street | Name | DoctorID (FK) | Amount |
| City | Refills | PatientID (FK) | Bill Date |
| State | Location | Date | Due Date |
| Zip | Time | ||
| PhoneNumber | |||
| Payments | |||
| PayNumber (PK) | |||
| PayAmount | |||
| PayDate | |||
| PayTime | |||
Reporting requirements
Patients will be given receipts, that will show the copay (how much was paid or how much is owed) and cards, specifying the next appointment date. Initial prescriptions will be called in by the doctor and refills will be called in by the patient.
Project Data Model
Solution:
SQL STATEMENTS
CREATE TABLE PATIENT
(
PATIENTID VARCHAR2(4) NOT NULL,
FIRSTNAME VARCHAR2(15) NOT NULL,
LASTNAME VARCHAR2(15) NOT NULL,
SSN VARCHAR2(11) NOT NULL,
PATIENTADDRESS VARCHAR2(50) NOT NULL,
BIRTHDATE DATE NOT NULL,
CELLPHONE VARCHAR2(13) NOT NULL,
HOMEPHONE VARCHAR2(13),
AGE NUMBER(2),
GENDER CHAR(1) NOT NULL,
CONSTRAINT PATIENT_PK PRIMARY KEY(PATIENTID)
);
CREATE TABLE DOCTOR
(
DOCTORID VARCHAR2(4) NOT NULL,
DOCTORNAME VARCHAR2(30) NOT NULL,
SSN VARCHAR2(11) NOT NULL,
DOCTORADDRESS VARCHAR2(50) NOT NULL,
DOCTOREMAIL VARCHAR2(40) NOT NULL,
WORKPHONE VARCHAR2(13) NOT NULL,
CONSTRAINT DOCTOR_PK PRIMARY KEY(DOCTORID)
);
CREATE TABLE INSURANCE
(
INSURANCEID VARCHAR2(4) NOT NULL,
INSURANCENAME VARCHAR2(50) NOT NULL,
COPAY NUMBER,
PHONE VARCHAR2(13) NOT NULL,
ADDRESS VARCHAR2(50) NOT NULL,
CONSTRAINT INSURANCE_PK PRIMARY KEY(INSURANCEID)
);
CREATE TABLE LOCATION
(
LOCATIONID VARCHAR2(4) NOT NULL,
STREET VARCHAR2(20) NOT NULL,
CITY VARCHAR2(12) NOT NULL,
STATE VARCHAR2(4) NOT NULL,
ZIP VARCHAR2(5) NOT NULL,
PHONENUMBER VARCHAR2(13),
CONSTRAINT LOCATION_PK PRIMARY KEY(LOCATIONID)
);
CREATE TABLE APPOINTMENT
(
APPOINTMENTID VARCHAR2(4) NOT NULL,
APPOINTMENTDATE DATE NOT NULL,
APPOINTMENTTIME DATE NOT NULL,
PATIENT VARCHAR2(4) NOT NULL,
DOCTOR VARCHAR2(4) NOT NULL,
REASON VARCHAR2(20),
CONSTRAINT APPOINTMENT_PK PRIMARY KEY(APPOINTMENTID),
CONSTRAINT APPOINTMENT_FK_PATIENT FOREIGN KEY(PATIENT) REFERENCES PATIENT(PATIENTID),
CONSTRAINT APPOINTMENT_FK_DOCTOR FOREIGN KEY(DOCTOR) REFERENCES DOCTOR(DOCTORID)
);
CREATE TABLE VISITS
(
VISITID VARCHAR2(4) NOT NULL,
DOCTOR VARCHAR2(4) NOT NULL,
PATIENT VARCHAR2(4) NOT NULL,
VISITDATE DATE NOT NULL,
VISITTIME DATE NOT NULL,
CONSTRAINT VISIT_PK PRIMARY KEY(VISITID),
CONSTRAINT VISIT_FK_DOCTOR FOREIGN KEY(DOCTOR) REFERENCES DOCTOR(DOCTORID),
CONSTRAINT VISIT_FK_PATIENT FOREIGN KEY(PATIENT) REFERENCES PATIENT(PATIENTID)
);
CREATE TABLE PRESCRIPTION
(
PRESCRIPTIONID VARCHAR2(4) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
REFILLS NUMBER,
LOCATION VARCHAR2(4),
VISIT VARCHAR2(4) NOT NULL,
CONSTRAINT PRESCRIPTION_PK PRIMARY KEY(PRESCRIPTIONID),
CONSTRAINT PRESCRIPTION_FK_LOCATION FOREIGN KEY(LOCATION) REFERENCES LOCATION(LOCATIONID),
CONSTRAINT PRESCRIPTION_FK_VISIT FOREIGN KEY(VISIT) REFERENCES VISITS(VISITID)
);
CREATE TABLE BILLS
(
BILLNUMBER NUMBER(8) NOT NULL,
AMOUNT NUMBER(8,2) NOT NULL,
BILLDATE DATE NOT NULL,
DUEDATE DATE NOT NULL,
VISIT VARCHAR2(4) NOT NULL,
CONSTRAINT BILLS_PK PRIMARY KEY(BILLNUMBER),
CONSTRAINT BILLS_FK_VISIT FOREIGN KEY(VISIT) REFERENCES VISITS(VISITID)
);
CREATE TABLE PAYMENTS
(
PAYNUMBER NUMBER(8) NOT NULL,
PAYAMOUNT NUMBER(8,2) NOT NULL,
PAYDATE DATE NOT NULL,
PAYTIME DATE NOT NULL,
BILLNUMBER NUMBER(8) NOT NULL,
CONSTRAINT PAYMENTS_PK PRIMARY KEY(PAYNUMBER),
CONSTRAINT PAYMENTS_FK FOREIGN KEY(BILLNUMBER) REFERENCES BILLS(BILLNUMBER)
);