CreateQueriesNEW.txt

PRAGMA foreign_keys=ON;

CREATE TABLE Person ( 
	PersonID VARCHAR(20) PRIMARY KEY, 
	FirstName VARCHAR NOT NULL, 
	LastName VARCHAR NOT NULL, 
  	DOB DATE NOT NULL,  
  	Ethnicity VARCHAR
  	
); 

CREATE TABLE EmergencyContact ( 
	EContactID VARCHAR(20), 
	PatientID VARCHAR(20), 
  	RelationToPatient VARCHAR NOT NULL, 
	PRIMARY KEY (PatientID, EContactID), 
  	FOREIGN KEY (PatientID) REFERENCES Person(PersonID) 
  	ON DELETE CASCADE
  	ON UPDATE CASCADE 
  
); 

CREATE TABLE Patient ( 
	PersonID VARCHAR(20) PRIMARY KEY,  
  	Weight INTEGER NOT NULL,  
  	Height INTEGER NOT NULL, 
  	PolicyID VARCHAR(20),  
  	FOREIGN KEY (PersonID) 
  	REFERENCES Person(PersonID)
  	ON DELETE CASCADE
  	ON UPDATE CASCADE,
  	FOREIGN KEY (PolicyID) 
  	REFERENCES INSURANCE_POLICY (PolicyID) 
  	
); 

 CREATE TABLE Employee ( 
	PersonID VARCHAR(20) PRIMARY KEY,  
	Degree VARCHAR NOT NULL, 
	YrsOfExp INTEGER NOT NULL,  
	Salary INTEGER NOT NULL,  
	RoleID VARCHAR(20) NOT NULL, 
	FOREIGN KEY (PersonID) 
	REFERENCES Person (PersonID)
   	ON UPDATE CASCADE
   	ON DELETE CASCADE,
	FOREIGN KEY (RoleID) 
	REFERENCES Roles (RoleID) 
); 

CREATE TABLE Roles ( 
	RoleID VARCHAR(20) PRIMARY KEY,  
	Type VARCHAR NOT NULL 
); 

CREATE TABLE INSURANCE_POLICY(  
  PolicyID VARCHAR(20) PRIMARY KEY,   
  PolicyName VARCHAR(20) NOT NULL,  
  PolicyType VARCHAR(20) NOT NULL,  
  InsuranceCompany VARCHAR(20) NOT NULL,  
  Contact INT NOT NULL,  
  CoveragePeriod DATE NOT NULL,  
  CoverageLimit INT NOT NULL,  
  PlanID INR NOT NULL  
);  

CREATE TABLE MedicalEmp ( 
	EmployeeID VARCHAR(20) PRIMARY KEY,  
  	Specialization VARCHAR NOT NULL,  
  	FOREIGN KEY (EmployeeID) 
  	REFERENCES Person (PersonID)
  	ON DELETE CASCADE
  	ON UPDATE CASCADE 
  
); 

CREATE TABLE CardInfo ( 
	CardNo INTEGER PRIMARY KEY, 
	NameOnCard VARCHAR(20) NOT NULL,  
	ExpDate DATE NOT NULL,  
	CVV INTEGER NOT NULL 
);  

CREATE TABLE Payment ( 
  PayID VARCHAR(15) NOT NULL, 
  PayType VARCHAR(15) NOT NULL, 
  PatientID VARCHAR(20) NOT NULL, 
  PRIMARY KEY (PayID), 
  FOREIGN KEY (PatientID) REFERENCES Patient(PersonID)
  ON DELETE CASCADE
  ON UPDATE CASCADE 
); 

CREATE TABLE Card ( 
	PayID VARCHAR(20) PRIMARY KEY,  
	CardNo INTEGER NOT NULL,  
	FOREIGN KEY (PayID) 
	REFERENCES Payment (PayID)
  	ON DELETE CASCADE,
	FOREIGN KEY (CardNo) 
	REFERENCES CardInfo (CardNo) 
  	ON DELETE CASCADE
  	ON UPDATE CASCADE 
); 

CREATE TABLE Cheque ( 
	PayID VARCHAR(20) PRIMARY KEY,
  	CheckNo INT NOT NULL,  
  	AcctNo INT NOT NULL,  
  	RoutingNo INT NOT NULL, 
  	FOREIGN KEY (PayID) 
  	REFERENCES Payment (PayID)
  	ON DELETE CASCADE
); 

CREATE TABLE ProcedureInfo ( 
	ProcedureName VARCHAR(20) PRIMARY KEY,  
	Description VARCHAR(20) NOT NULL,  
	Duration TIME NOT NULL,  
	Cost INTEGER NOT NULL 
); 

CREATE TABLE Procedure ( 
	ProcedureID VARCHAR(20) PRIMARY KEY,  
	ProcedureName VARCHAR(20) NOT NULL, 
 	FOREIGN KEY (ProcedureName) 
	REFERENCES ProcedureInfo (ProcedureName) 
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
); 

CREATE TABLE Appointment ( 
	AppointmentID VARCHAR(15) NOT NULL, 
	Status VARCHAR(25) NOT NULL, 
	ApptType VARCHAR(25) NOT NULL, 
	Location VARCHAR(25) NOT NULL, 
	Date DATE NOT NULL, 
	Time TIME NOT NULL, 
	PatientID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (AppointmentID), 
	FOREIGN KEY (PatientID) REFERENCES Patient(personid)
); 

CREATE TABLE INVOICE ( 
	InvoiceNo INT NF NULL, 
	BillingAddress VARCHAR(50) NOT NULL, 
	Date DATE NOT NULL, 
	PaymentAmount DECIMAL(10,2) NOT NULL, 
	Payment Date DATE NOT NULL, 
	PolicyID VARCHAR(20) NOT NULL, 
	AppointmentID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (InvoiceNo), 
	FOREIGN KEY (AppointmentID) REFERENCES APPOINTMENT(AppointmentID), 
	FOREIGN KEY (PolicyID) REFERENCES INSURANCE_POLICY(PolicyID)
  	ON UPDATE CASCADE 
); 

CREATE TABLE SUPPLY(  
  SupplyID VARCHAR(20) PRIMARY KEY,   
  LastPurchaseDate DATE NOT NULL,  
  Quantity INT NOT NULL,  
  Cost INT NOT NULL,  
  ExpiryDate DATE NOT NULL,  
  SupplyType VARCHAR(20) NOT NULL 
);  


CREATE TABLE USES ( 
	SupplyID VARCHAR(20) NOT NULL, 
	ProcedureID VARCHAR(20) NOT NULL, 
	Qty INT NOT NULL, 
  	PRIMARY KEY (SupplyID, ProcedureID), 
	FOREIGN KEY (SupplyID) REFERENCES SUPPLY(SupplyID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE, 
	FOREIGN KEY (ProcedureID) REFERENCES Procedure(ProcedureID) 
  	ON UPDATE CASCADE
  	ON DELETE CASCADE 
); 

CREATE TABLE CHARGES (  
	ProcedureID VARCHAR(20) NOT NULL, 
	InvoiceNo INT NOT NULL,   
	Qty INT NOT NULL,  
	PRIMARY KEY (InvoiceNo, ProcedureID),  
	FOREIGN KEY (InvoiceNo) REFERENCES INVOICE(InvoiceNo)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE,  
	FOREIGN KEY (ProcedureID) REFERENCES PROCEDURE(ProcedureID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
); 

CREATE TABLE GOES_TO ( 
	InvoiceNo INT NOT NULL, 
	PayID VARCHAR(20) NOT NULL, 
	PayDate DATE NOT NULL, 
	PayAmt DECIMAL(10,2) NOT NULL, 
	PRIMARY KEY (InvoiceNo, PayID), 
	FOREIGN KEY (InvoiceNo) REFERENCES INVOICE(InvoiceNo)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE, 
	FOREIGN KEY (PayID) REFERENCES Payment(PayID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
); 

CREATE TABLE ASSOCIATED ( 
	PolicyID VARCHAR(20) NOT NULL, 
	ProcedureID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (PolicyID, ProcedureID), 
	FOREIGN KEY (PolicyID) REFERENCES INSURANCE_POLICY(PolicyID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE, 
	FOREIGN KEY (ProcedureID) REFERENCES PROCEDURE(ProcedureID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
); 

CREATE TABLE ADDRESS(  
  AddressID VARCHAR(20) PRIMARY KEY,   
  City VARCHAR(20) NOT NULL,  
  State VARCHAR(20) NOT NULL,  
  StreetAddr VARCHAR(20) NOT NULL,  
  Zip INT NOT NULL 

);

CREATE TABLE HAS_ADDRESS ( 
	AddressID VARCHAR(20) NOT NULL, 
	PersonID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (AddressID, PersonID), 
	FOREIGN KEY (AddressID) REFERENCES ADDRESS(AddressID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE, 
	FOREIGN KEY (PersonID) REFERENCES Person(PersonID) 
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
); 

CREATE TABLE COMPLETES_PROCEDURE ( 
	ProcedureID VARCHAR(20) NOT NULL, 
	LicenseID VARCHAR(20) NOT NULL, 
	Date DATE NOT NULL, 
	Time TIME NOT NULL, 
  	PRIMARY KEY (ProcedureID, LicenseID, Date, Time), 
	FOREIGN KEY (ProcedureID) REFERENCES Procedure(ProcedureID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE, 
	FOREIGN KEY (LicenseID) REFERENCES Licensure(LicenseNo) 
); 

CREATE TABLE Licensure ( 
	LicenseNo VARCHAR(10) NOT NULL, 
	LicenseType VARCHAR(25) NOT NULL, 
	Expiration DATE NOT NULL, 
	MedEmployeeID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (LicenseNo), 
	FOREIGN KEY (MedEmployeeID) REFERENCES Person(PersonID)
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
); 

CREATE TABLE Prescription ( 
  PrescriptionID VARCHAR(15) NOT NULL, 
  Medicine VARCHAR(25) NOT NULL, 
  Dosage VARCHAR(25) NOT NULL, 
  Directions VARCHAR(50) NOT NULL, 
  PatientID VARCHAR(20) NOT NULL, 
  PRIMARY KEY (PrescriptionID), 
  FOREIGN KEY (PatientID) REFERENCES Patient(personid)
); 

CREATE TABLE Assigned ( 
	AppointmentID VARCHAR(15) NOT NULL, 
	MedEmployeeID VARCHAR(20) NOT NULL, 
  	TreatmentNotes VARCHAR(100) NOT NULL, 
	PRIMARY KEY (AppointmentID), 
	FOREIGN KEY (MedEmployeeID) REFERENCES Person(PersonID)
); 

CREATE TABLE PRESCRIBES (  
  EmployeeID VARCHAR(20) NOT NULL,  
  PrescriptionID VARCHAR(20) NOT NULL,  
  Date DATE NOT NULL,  
  PRIMARY KEY (EmployeeID, PrescriptionID),  
  FOREIGN KEY (EmployeeID) REFERENCES PERSON(personid),  
  FOREIGN KEY (PrescriptionID) REFERENCES PRESCRIPTION(PrescriptionID) 
);

CREATE TABLE ALLERGY(  
  AllergyID VARCHAR(20) PRIMARY KEY,   
  Medication VARCHAR(20) NOT NULL,  
  Symptoms VARCHAR(20) NOT NULL,  
  DiagnosisDate DATE NOT NULL 
);  

CREATE TABLE ALLERGIC_TO (  
  AllergyID VARCHAR(20) NOT NULL,  
  PatientID VARCHAR(20) NOT NULL,  
  PRIMARY KEY (AllergyID, PatientID),  
  FOREIGN KEY (AllergyID) REFERENCES ALLERGY(AllergyID),  
  FOREIGN KEY (PatientID) REFERENCES PERSON(personid)  
);  

CREATE TABLE MEDICATION(  
  MedicationID VARCHAR(20) PRIMARY KEY,   
  Name VARCHAR(20) NOT NULL,  
  Dosage VARCHAR(20) NOT NULL,  
  Quantity INT NOT NULL,
  PrescribedDate DATE NOT NULL 

);  

cREATE TABLE USES_MEDICATION (  
  MedicationID VARCHAR(20) NOT NULL,  
  PatientID VARCHAR(20) NOT NULL,  
  PRIMARY KEY (MedicationID, PatientID),  
  FOREIGN KEY (MedicationID) REFERENCES MEDICATION(MedicationID),  
  FOREIGN KEY (PatientID) REFERENCES PERSON(personid)  
);  

CREATE TABLE IS_RELATED_TO (  
  EmergencyContactID VARCHAR(20) NOT NULL,  
  PatientID VARCHAR(20) NOT NULL,  
  PRIMARY KEY (EmergencyContactID, PatientID),  
  FOREIGN KEY (EmergencyContactID) REFERENCES Person(personid),  
  FOREIGN KEY (PatientID) REFERENCES Person(personid)  
);  

CREATE TABLE MEDICAL_HISTORY(  
  PatientID VARCHAR(20) NOT NULL,   
  HIPPA VARCHAR(20) NOT NULL,  
  LastXray DATE NOT NULL,  
  LastCheck DATE NOT NULL,  
  PRIMARY KEY (PatientID),  
  FOREIGN KEY (PatientID) REFERENCES Patient(personid)
  ON DELETE CASCADE
  ON UPDATE CASCADE

); 

CREATE TABLE PERSON_TYPE ( 
	Type VARCHAR(20) NOT NULL, 	
  	PersonID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (Type, PersonID), 
	FOREIGN KEY (PersonID) REFERENCES Person(personid) 
); 

CREATE TABLE PERSON_EMAIL ( 
	Email VARCHAR(20) NOT NULL, 
	PersonID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (Email, PersonID), 
  	FOREIGN KEY (PersonID) REFERENCES Person(personid) 
); 

CREATE TABLE Person_Phone ( 
	Phone INT(10) NOT NULL, 
	PersonID VARCHAR(20) NOT NULL, 
	PRIMARY KEY (Phone), 
	FOREIGN KEY (PersonID) REFERENCES Person(personid)
); 

CREATE TABLE PastOperation ( 
	OperationID VARCHAR(15) NOT NULL, 
	PatientID VARCHAR(20), 
	PRIMARY KEY (OperationID), 
	FOREIGN KEY (PatientID) REFERENCES Patient(personid)
  	ON DELETE CASCADE 
  	ON UPDATE CASCADE
); 

CREATE TABLE MedicalConditions ( 
	ConditionID VARCHAR(15) NOT NULL, 
	PatientID VARCHAR(20), 
	PRIMARY KEY (ConditionID), 
	FOREIGN KEY (PatientID) REFERENCES Patient(personid)
);