Designing a Database Schema for Healthcare

Database Schema
Author Name
Institution Affiliation
Entities
All of the tables/entities have been associated with several relations and are purely based on the E-R Diagram. The attributes of these tables/entities are precisely shown in the table below.
Entities

Attributes

Doctor

D_Name, D_Ssn, Gender, Age, Position, Phone, Address, Office, City, Zip

Diagnosis

Diagnosis_Id, Category

Patient

P_Name, P_Ssn, Gender, Age, Position, Phone, Address, Office, City, Zip

Invoice

Invoice_Num, Amount, Invoice_Date, Due_Date

Medicine

MInventory_Id, M_Name, Manufacturer, Price, Quantity, Exp_Date

Payment

Invoice_Num, PayTrans_Num, Pay_Method, Pay_Status, Paid_Date

Prescription

Prescription_Id, Medicine_Quantity

ER-Diagram
Mapping on a Database Schema
Depending on certain functional dependencies of these relations, the mapping can be done by normalizing the relations and then converting the E-R model into a relational model. Here, we have shown it precisely.
Doctor (D_Ssn, D_Name, Gender, Age, Position, Office, Phone, Address, City, Zip)
Patient (D_Ssn, P_Ssn, P_Name, Gender, Age, Position, Phone, Address, City, Zip)
Invoice (P_Ssn, Invoice_Num, CPT_Id, Diagnosis_Id, Prescription_Id, Amount, Invoice_Date, Due_Date)
Payment (Invoice_Num, PayTrans_Num, Pay_Method, Pay_Status, Paid_Date)
Diagnosis (Diagnosis_Id, Category)
Prescription and Medicine (Prescription_Id, Med_Quantity)
(MedInventory_Id, Med_Name, Manufacturer, Price, Quantity, Exp_Date)
A Data Dictionary for All Entity Attributes
The data dictionary is as follows.
Patient
CREATE TABLE [dbo].[Patient] (
PatientID [int] IDENTITY (1, 1) NOT NULL,
FirstName [varchar] (50) NOT NULL,
MidName [varchar] (50) NULL,
LastName [varchar] (50) NOT NULL,
Address1 [varchar] (100) NOT NULL,
Address2 [varchar] (100) NULL,
City [varchar] (100) NOT NULL,
State [varchar] (2) NOT NULL,
Zip [varchar] (5) NOT NULL,
Constraint [PK_Patient1] PRIMARY KEY CLUSTERED
([PatientID] ASC))
INSERT INTO PATIENT (PATIENTID, FIRSTNAME, MIDNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP) VALUES (1, ‘June’, ‘Cleaver’, ‘201 E. Oak’, ‘’, ‘Mayberry’, ‘VA’, 85201)
INSERT INTO PATIENT (PATIENTID, FIRSTNAME, MIDNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP) VALUES (2, ‘Ward’, ‘Cleaver’, ‘201 E. Oak’, ‘’, ‘Mayberry’, ‘VA’, 85201)
INSERT INTO PATIENT (PATIENTID, FIRSTNAME, MIDNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP) VALUES (3, ‘Beaver’, ‘Cleaver’, ‘201 E. Oak’, ‘’, ‘Mayberry’, ‘VA’, 85201)
INSERT INTO PATIENT (PATIENTID, FIRSTNAME, MIDNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP) VALUES (4, ‘Wally’, ‘Cleaver’, ‘201 E. Oak’, ‘’, ‘Mayberry’, ‘VA’, 85201)
Diagnosis
INSERT INTO DIAGNOSIS (DIAGNOSISID, PATIENTID, DIAGNOSISDATE, ICD10CODE, DIAGNOSIS, DOCTORID) VALUES (1, 1, ‘05/07/2012’, &lsqu...