Hospital Outpatient Pharmacy Database Management System

Topic - Hospital Outpatient Pharmacy Database Management System

Requirements -

Drug attributes: ID, name, generic, brand, strength, quantity, price, quantity order, order date, directions, controlled substances, drugs in mixtures

Pharmaceutical companies attributes: name, street, city, state, zip code, preferred brand

1. Create relationship based on your design (primary and foreign keys). Use one to one, one to many and many to many relationships
2. Draw E-R diagram using appropriate symbols
3. Based on your E-R diagram create tables and relationships into SQL 5.7
4. Enter minimum 10 observations in each table using SQL 5.7
5. Make sure you add maximum functionality of SQL 5.7 for example if you delete one record from the master table, it should delete automatically from child tables (based on your relationships)
6. Explain ER diagram
7. Have a screenshot of your table, SQL statement and results
8. Define your goal

Program

CREATE DATABASE  IF NOT EXISTS `hospitaloutpatientpharma` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hospitaloutpatientpharma`;
-- MySQL dump 10.13  Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: localhost    Database: hospitaloutpatientpharma
-- ------------------------------------------------------
-- Server version    5.7.20-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `doctors`
--

DROP TABLE IF EXISTS `doctors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `doctors` (
  `idDoctors` int(11) NOT NULL,
  `DoctorName` varchar(45) NOT NULL,
  `DoctorCity` varchar(45) NOT NULL,
  `DoctorState` varchar(45) NOT NULL,
  `DoctorZipCode` varchar(6) NOT NULL,
  `DoctorSpeciality` varchar(45) NOT NULL,
  `DoctorPhone` varchar(45) NOT NULL,
  `DoctorEmail` varchar(63) NOT NULL,
  PRIMARY KEY (`idDoctors`),
  UNIQUE KEY `idDoctors_UNIQUE` (`idDoctors`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `doctors`
--

LOCK TABLES `doctors` WRITE;
/*!40000 ALTER TABLE `doctors` DISABLE KEYS */;
INSERT INTO `doctors` VALUES (1,'Doc 1','DocCity1','DocState1','12345','Heart','12121212','mail1@doc.dr'),(2,'Doc 2','DocCity2','DocState2','12345','Lungs','23232323','mail2@doc.dr'),(3,'Doc 3','DocCity3','DocState3','12345','Eyes','24234234','mail3@doc.dr'),(4,'Doc 4','DocCity4','DocState4','12345','Liver','23525543','mail4@doc.dr'),(5,'Doc 5','DocCity5','DocState5','12345','Stomach','23413123','mail5@doc.dr'),(6,'Doc 6','DocCity6','DocState6','12345','Head','23423423','mail6@doc.dr'),(7,'Doc 7','DocCity7','DocState7','12345','Skin','34623423','mail7@doc.dr'),(8,'Doc 8','DocCity8','DocState8','12345','Hair','23467783','mail8@doc.dr'),(9,'Doc 9','DocCity9','DocState9','12345','ENT','23466746','mail9@doc.dr'),(10,'Doc 10','DocCity10','DocState10','12345','Ortho','12121221','mail10@doc.dr');
/*!40000 ALTER TABLE `doctors` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `genericdruginfo`
--

DROP TABLE IF EXISTS `genericdruginfo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `genericdruginfo` (
  `idGenericDrugInfo` int(11) NOT NULL,
  `DrugName` varchar(127) NOT NULL,
  `DrugStrength` varchar(45) NOT NULL,
  `DrugPrice` decimal(10,2) NOT NULL,
  `DrugQuantity` int(11) NOT NULL,
  PRIMARY KEY (`idGenericDrugInfo`),
  UNIQUE KEY `idGenericDrugInfo_UNIQUE` (`idGenericDrugInfo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `genericdruginfo`
--

LOCK TABLES `genericdruginfo` WRITE;
/*!40000 ALTER TABLE `genericdruginfo` DISABLE KEYS */;
INSERT INTO `genericdruginfo` VALUES (1,'Drug 1','10mg',12.10,100),(2,'Drug 2','20mg',3.40,100),(3,'Drug 3','15mg',5.30,210),(4,'Drug 4','10mg',2.60,113),(5,'Drug 5','1mg',12.60,125),(6,'Drug 6','15mg',16.60,16),(7,'Drug 7','5mg',23.60,194),(8,'Drug 8','10mg',23.70,523),(9,'Drug 9','10mg',623.20,52),(10,'Drug 10','20mg',23.32,226);
/*!40000 ALTER TABLE `genericdruginfo` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patients`
--

DROP TABLE IF EXISTS `patients`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patients` (
  `idPatients` int(11) NOT NULL,
  `PatientName` varchar(45) NOT NULL,
  `PatientStreet` varchar(45) NOT NULL,
  `PatientCity` varchar(45) NOT NULL,
  `PatientState` varchar(45) NOT NULL,
  `PatientZipCode` varchar(45) NOT NULL,
  `PatientPhone` varchar(45) NOT NULL,
  `PatientGender` varchar(8) NOT NULL,
  `PatientsDOB` date NOT NULL,
  `PatientDoctorId` int(11) NOT NULL,
  PRIMARY KEY (`idPatients`),
  UNIQUE KEY `idDoctors_UNIQUE` (`idPatients`),
  KEY `PatientHasDoctor_idx` (`PatientDoctorId`),
  CONSTRAINT `PatientHasDoctor` FOREIGN KEY (`PatientDoctorId`) REFERENCES `doctors` (`idDoctors`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patients`
--

LOCK TABLES `patients` WRITE;
/*!40000 ALTER TABLE `patients` DISABLE KEYS */;
INSERT INTO `patients` VALUES (1,'Patient 1','1st Main','City 1','State 1','121341','6634234623','Male','2000-09-09',2),(2,'Patient 2','2nd Main','City 2','State 2','412445','1235325634','Female','1994-02-13',3),(3,'Patient 3','3rd Main','City 3','State 3','234356','1234414562','Male','1989-09-12',5),(4,'Patient 4','4th Main','City 4','State 4','634523','3634534566','Male','1990-12-12',1),(5,'Patient 5','5th Main','City 5','State 5','663452','3467756574','Male','2004-01-09',4),(6,'Patient 6','6th Main','City 6','State 6','235673','4757645767','Female','2006-10-10',1),(7,'Patient 7','7th Main','City 7','State 7','234663','7456774678','Female','2009-10-09',4),(8,'Patient 8','8th Main','City 8','State 8','236765','4576756756','Male','1998-01-01',5),(9,'Patient 9','9th Main','City 9','State 9','234667','4565467575','Male','1990-09-07',10),(10,'Patient 10','10th Main','City 10','State 10','236345','5465674564','Male','1999-10-20',9);
/*!40000 ALTER TABLE `patients` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pharmacompany`
--

DROP TABLE IF EXISTS `pharmacompany`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pharmacompany` (
  `idPharmaCompany` int(11) NOT NULL,
  `CompanyName` varchar(45) NOT NULL,
  `CompanyStreet` varchar(45) NOT NULL,
  `CompanyCity` varchar(45) NOT NULL,
  `CompanyState` varchar(45) NOT NULL,
  `CompanyZipCode` varchar(6) NOT NULL,
  `CompanyPreferredBrand` varchar(45) NOT NULL,
  PRIMARY KEY (`idPharmaCompany`),
  UNIQUE KEY `idPharmaCompany_UNIQUE` (`idPharmaCompany`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pharmacompany`
--

LOCK TABLES `pharmacompany` WRITE;
/*!40000 ALTER TABLE `pharmacompany` DISABLE KEYS */;
INSERT INTO `pharmacompany` VALUES (1,'Pharma1','1st Cross','PharmaCity1','State1','121212','Brand1'),(2,'Pharma2','2nd Cross','PharmaCity2','State2','122323','Brand2'),(3,'Pharma3','3rd Cross','PharmaCity3','State3','434212','Brand3'),(4,'Pharma4','4th Cross','PharmaCity4','State4','123524','Brand4'),(5,'Pharma5','5th Cross','PharmaCity5','State5','235352','Brand5'),(6,'Pharma6','6th Cross','PharmaCity6','State6','234523','Brand6'),(7,'Pharma7','7th Cross','PharmaCity7','State7','632345','Brand7'),(8,'Pharma8','8th Cross','PharmaCity8','State8','234662','Brand8'),(9,'Pharma9','9th Cross','PharmaCity9','State9','234663','Brand9'),(10,'Pharma10','10th Cross','PharmaCity10','State10','234623','Brand10');
/*!40000 ALTER TABLE `pharmacompany` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `prescription`
--

DROP TABLE IF EXISTS `prescription`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `prescription` (
  `idPrescription` int(11) NOT NULL,
  `DrugId` int(11) NOT NULL,
  `PrescriptionDate` date NOT NULL,
  `PrescriptionQuantity` int(11) NOT NULL,
  `PrescriptionPrice` decimal(10,2) NOT NULL,
  `PrescriptionRefills` int(11) NOT NULL,
  `prescriptionPatientId` int(11) NOT NULL,
  PRIMARY KEY (`idPrescription`),
  UNIQUE KEY `idPrescription_UNIQUE` (`idPrescription`),
  KEY `PrescriptionForDrug_idx` (`DrugId`),
  KEY `PrescriptionForPatient_idx` (`prescriptionPatientId`),
  CONSTRAINT `PrescriptionForDrug` FOREIGN KEY (`DrugId`) REFERENCES `genericdruginfo` (`idGenericDrugInfo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `PrescriptionForPatient` FOREIGN KEY (`prescriptionPatientId`) REFERENCES `patients` (`idPatients`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `prescription`
--

LOCK TABLES `prescription` WRITE;
/*!40000 ALTER TABLE `prescription` DISABLE KEYS */;
INSERT INTO `prescription` VALUES (1,4,'2018-09-09',2,2.60,2,3),(2,5,'2018-09-10',2,12.60,2,2),(3,3,'2018-09-11',3,5.30,10,4),(4,4,'2018-09-12',4,2.60,1,5),(5,3,'2018-09-14',2,5.30,1,1),(6,4,'2018-09-15',5,2.60,10,4),(7,5,'2018-09-18',5,12.60,5,4),(8,7,'2018-09-28',4,23.60,3,5),(9,8,'2018-09-29',5,23.70,12,8),(10,9,'2018-10-01',6,623.20,1,7);
/*!40000 ALTER TABLE `prescription` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `storedrug`
--

DROP TABLE IF EXISTS `storedrug`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `storedrug` (
  `idStoreDrug` int(11) NOT NULL,
  `DrugName` varchar(45) NOT NULL,
  `DrugIsGeneric` tinyint(4) NOT NULL,
  `DrugBrand` varchar(45) NOT NULL,
  `DrugStrength` varchar(45) NOT NULL,
  `DrugQuantity` int(11) NOT NULL,
  `DrugPrice` decimal(10,2) NOT NULL,
  `DrugOrderQuantity` int(11) NOT NULL,
  `DrugOrderDate` date NOT NULL,
  `DrugDirections` varchar(127) NOT NULL,
  `DrugCtrlSubs` tinyint(4) NOT NULL,
  `DrugMix` varchar(127) NOT NULL,
  `DrugSupplier` int(11) NOT NULL,
  PRIMARY KEY (`idStoreDrug`),
  UNIQUE KEY `idStoreDrug_UNIQUE` (`idStoreDrug`),
  KEY `DrugHasSupplier_idx` (`DrugSupplier`),
  CONSTRAINT `DrugHasSupplier` FOREIGN KEY (`DrugSupplier`) REFERENCES `pharmacompany` (`idPharmaCompany`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `storedrug`
--

LOCK TABLES `storedrug` WRITE;
/*!40000 ALTER TABLE `storedrug` DISABLE KEYS */;
INSERT INTO `storedrug` VALUES (1,'Drug 1',0,'Brand 1','10mg',10,1.90,100,'2018-09-02','With Milk',0,'None',1),(2,'Drug 2',0,'Brand 4','20mg',10,2.80,10,'2018-09-03','With Water',0,'None',4),(3,'Drug 3',0,'Brand 6','20mg',2,2.33,200,'2018-09-03','With Cold Water',0,'None',6),(4,'Drug 4',1,'Brand 8','10mg',4,5.23,230,'2018-09-04','With Warm Water',0,'None',8),(5,'Drug 5',1,'Brand 7','5mg',10,6.54,120,'2018-09-04','With Warm Water',1,'None',7),(6,'Drug 6',1,'Brand 2','2mg',8,9.90,220,'2018-09-05','With Water',1,'None',2),(7,'Drug 7',1,'Brand 1','4mg',20,110.20,530,'2018-09-10','With Warm Water',1,'None',1),(8,'Drug 8',1,'Brand 9','10mg',10,12.32,580,'2018-10-01','With Milk',0,'None',9),(9,'Drug 9',0,'Brand 6','20mg',20,1.98,940,'2018-11-11','With Water',1,'None',6),(10,'Drug 10',1,'Brand 9','10mg',10,123.12,1000,'2018-12-19','With Water',0,'None',9);
/*!40000 ALTER TABLE `storedrug` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `thirdparty`
--

DROP TABLE IF EXISTS `thirdparty`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `thirdparty` (
  `idThirdParty` int(11) NOT NULL,
  `ClaimNumber` varchar(45) NOT NULL,
  `ClaimDate` date NOT NULL,
  `ClaimPolicyNumber` varchar(45) NOT NULL,
  `ClaimCompanyName` varchar(127) NOT NULL,
  `ClaimAmountCovered` decimal(10,2) NOT NULL,
  `ClaimAmountOwe` decimal(10,2) NOT NULL,
  `ClaimInsuredName` varchar(64) NOT NULL,
  `ClaimPatientId` int(11) DEFAULT NULL,
  PRIMARY KEY (`idThirdParty`),
  UNIQUE KEY `idThirdParty_UNIQUE` (`idThirdParty`),
  KEY `ClaimForPatient_idx` (`ClaimPatientId`),
  CONSTRAINT `ClaimForPatient` FOREIGN KEY (`ClaimPatientId`) REFERENCES `patients` (`idPatients`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `thirdparty`
--

LOCK TABLES `thirdparty` WRITE;
/*!40000 ALTER TABLE `thirdparty` DISABLE KEYS */;
INSERT INTO `thirdparty` VALUES (1,'123','2018-10-09','AS12312','ATG Life',12000.00,2121.00,'Patient 2',2),(2,'124','2018-10-12','AR12312','POOL Insurance',12000.00,124.00,'Patient 1',1),(3,'125','2018-10-14','AT12313','ATG Life',10000.00,1253.00,'Patient 4',4),(4,'126','2018-10-16','TY12312','LifeSecure',100000.00,2344.00,'Patient 3',3),(5,'127','2018-10-17','YG12342','Secure You',25000.00,5343.00,'Patient 5',5),(6,'128','2018-10-20','GR22442','Health Security',50000.00,3534.00,'Patient 9',9),(7,'129','2018-10-22','TF12311','Healthy Life',10000.00,3443.00,'Patient 8',8),(8,'130','2018-10-25','TK12524','Secure You',20000.00,3452.00,'Patient 6',6),(9,'131','2018-10-28','ES12345','ATG Life',12000.00,3334.00,'Patient 7',7),(10,'132','2018-10-30','YF12312','ATG Life',65000.00,40043.00,'Patient 10',10);
/*!40000 ALTER TABLE `thirdparty` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-05-04  2:20:10