CST 363: Database Management
Course Objective:
This course provides balanced coverage of database use and design, focusing on relational databases. Students will learn to design relational schemas, write SQL queries, access a DB programmatically, and perform database administration. Students will gain a working knowledge of the algorithms and data structures used in query evaluation and transaction processing. Students will also learn to apply newer database technologies such as XML, NoSQL, and Hadoop
Project Documentation for Running Database and Website:
SQL File Used in Database Generation:
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema bigpharma
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `bigpharma` DEFAULT CHARACTER SET utf8 ;
USE `bigpharma` ;
-- -----------------------------------------------------
-- Table `bigpharma`.`doctor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`doctor` (
`id` INT NOT NULL AUTO_INCREMENT,
`ssn` VARCHAR(9) NOT NULL,
`name` VARCHAR(70) NOT NULL,
`specialty` VARCHAR(70) NOT NULL,
`practice_since_year` INT NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`patient`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`patient` (
`patientID` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(70) NOT NULL,
`birthdate` DATE NOT NULL,
`ssn` VARCHAR(9) NOT NULL,
`street` VARCHAR(70) NOT NULL,
`city` VARCHAR(70) NOT NULL,
`state` VARCHAR(45) NOT NULL,
`zipcode` VARCHAR(10) NOT NULL,
`primaryID` INT NOT NULL,
PRIMARY KEY (`patientID`),
INDEX `fk_patient_doctor_idx` (`primaryID` ASC) VISIBLE,
CONSTRAINT `fk_patient_doctor`
FOREIGN KEY (`primaryID`)
REFERENCES `bigpharma`.`doctor` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`company`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`company` (
`companyID` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(70) NOT NULL,
`phone_number` VARCHAR(10) NOT NULL,
PRIMARY KEY (`companyID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`drug`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`drug` (
`drug_id` INT(11) NOT NULL,
`trade_name` VARCHAR(45) NULL,
`formula` VARCHAR(45) NULL,
`companyID` INT NULL,
PRIMARY KEY (`drug_id`),
INDEX `fk_drug_company1_idx` (`companyID` ASC) VISIBLE,
CONSTRAINT `fk_drug_company1`
FOREIGN KEY (`companyID`)
REFERENCES `bigpharma`.`company` (`companyID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`pharmacy`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`pharmacy` (
`pharmacyID` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(70) NOT NULL,
`address` VARCHAR(70) NOT NULL,
`phone_number` VARCHAR(10) NOT NULL,
PRIMARY KEY (`pharmacyID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`prescription`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`prescription` (
`rxid` INT NOT NULL AUTO_INCREMENT,
`drug_id` INT(11) NOT NULL,
`quantity` INT NOT NULL,
`prescribed_date` DATE NOT NULL,
`patientID` INT NOT NULL,
`doctor_id` INT NOT NULL,
`pharmacyID` INT NULL,
`date_filled` DATE NULL,
`cost` DECIMAL(7,2) NULL,
PRIMARY KEY (`rxid`),
INDEX `fk_prescription_drug1_idx` (`drug_id` ASC) VISIBLE,
INDEX `fk_prescription_patient1_idx` (`patientID` ASC) VISIBLE,
INDEX `fk_prescription_doctor1_idx` (`doctor_id` ASC) VISIBLE,
INDEX `fk_prescription_pharmacy1_idx` (`pharmacyID` ASC) VISIBLE,
CONSTRAINT `fk_prescription_drug1`
FOREIGN KEY (`drug_id`)
REFERENCES `bigpharma`.`drug` (`drug_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_prescription_patient1`
FOREIGN KEY (`patientID`)
REFERENCES `bigpharma`.`patient` (`patientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_prescription_doctor1`
FOREIGN KEY (`doctor_id`)
REFERENCES `bigpharma`.`doctor` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_prescription_pharmacy1`
FOREIGN KEY (`pharmacyID`)
REFERENCES `bigpharma`.`pharmacy` (`pharmacyID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`supervisor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`supervisor` (
`supervisorID` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(70) NOT NULL,
`ssn` VARCHAR(9) NOT NULL,
PRIMARY KEY (`supervisorID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `bigpharma`.`contract`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bigpharma`.`contract` (
`contractID` INT NOT NULL AUTO_INCREMENT,
`start_date` DATE NOT NULL,
`end_date` DATE NOT NULL,
`text` VARCHAR(255) NOT NULL,
`price` DECIMAL(7,2) NOT NULL,
`supervisorID` INT NOT NULL,
`pharmacyID` INT NOT NULL,
`drug_id` INT(11) NOT NULL,
PRIMARY KEY (`contractID`),
INDEX `fk_contract_supervisor1_idx` (`supervisorID` ASC) VISIBLE,
INDEX `fk_contract_pharmacy1_idx` (`pharmacyID` ASC) VISIBLE,
INDEX `fk_contract_drug1_idx` (`drug_id` ASC) VISIBLE,
CONSTRAINT `fk_contract_supervisor1`
FOREIGN KEY (`supervisorID`)
REFERENCES `bigpharma`.`supervisor` (`supervisorID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_contract_pharmacy1`
FOREIGN KEY (`pharmacyID`)
REFERENCES `bigpharma`.`pharmacy` (`pharmacyID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_contract_drug1`
FOREIGN KEY (`drug_id`)
REFERENCES `bigpharma`.`drug` (`drug_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- INSERTS - Use DataGenerate for Patients, Docs, & Prescriptions
-- -----------------------------------------------------
INSERT INTO pharmacy (name, address, phone_number) VALUES ('CVS', '231 Roadwiler Ave', '555112111');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Hornsynders Pharmacy', '21 Jump Street', '5552221297');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('By the Bay Medicine', '233 Bay Ave', '5551233363');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Rite Aid', '14 Right Lane', '5554441234');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Walgreens', '235 41st Ave', '5555512855');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Kind Peoples Pharmacy', '42 Blaze Ave', '5556667166');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Walmart', '12 Walmart Lane', '5557777777');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Costco', 'Costco Street', '5558888888');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Amazon Online Pharmacy', '123 Amazon Way', '5551239999');
INSERT INTO pharmacy (name, address, phone_number) VALUES ('Safeway', '11 Safe Way', '5550617000');
INSERT INTO `drug` (drug_id, trade_name, formula) VALUES
(1,'Tylenol with Codeine','acetaminophen and codeine'),
(2,'Proair Proventil','albuterol aerosol'),
(3,'Accuneb','albuterol HFA'),
(4,'Fosamax','alendronate'),
(5,'Zyloprim','allopurinol'),
(6,'Xanax','alprazolam'),
(7,'Elavil','amitriptyline'),
(8,'Augmentin','amoxicillin and clavulanate K+'),
(9,'Amoxil','amoxicillin'),
(10,'Adderall XR','amphetamine and dextroamphetamine XR'),
(11,'Tenormin','atenolol'),
(12,'Lipitor','atorvastatin'),
(13,'Zithromax','azithromycin'),
(14,'Lotrel','benazepril and amlodipine'),
(15,'Soma','carisoprodol'),
(16,'Coreg','carvedilol'),
(17,'Omnicef','cefdinir'),
(18,'Celebrex','celecoxib'),
(19,'Keflex','cephalexin'),
(20,'Cipro','ciprofloxacin'),
(21,'Celexa','citalopram'),
(22,'Klonopin','clonazepam'),
(23,'Catapres','clonidine HCl'),
(24,'Plavix','clopidogrel'),
(25,'Premarin','conjugated estrogens'),
(26,'Flexeril','cyclobenzaprine'),
(27,'Valium','diazepam'),
(28,'Voltaren','diclofenac sodium'),
(29,'Yaz','drospirenone and ethinyl estradiol'),
(30,'Cymbalta','Duloxetine'),
(31,'Vibramycin','doxycycline hyclate'),
(32,'Vasotec','enalapril'),
(33,'Lexapro','escitalopram'),
(34,'Nexium','esomeprazole'),
(35,'Zetia','ezetimibe'),
(36,'Tricor','fenofibrate'),
(37,'Allegra','fexofenadine'),
(38,'Diflucan','fluconozole'),
(39,'Prozac','fluoxetine HCl'),
(40,'Advair','fluticasone and salmeterol inhaler'),
(41,'Flonase','fluticasone nasal spray'),
(42,'Folic Acid','folic acid'),
(43,'Lasix','furosemide'),
(44,'Neurontin','gabapentin'),
(45,'Amaryl','glimepiride'),
(46,'Diabeta','glyburide'),
(47,'Glucotrol','glipizide'),
(48,'Microzide','hydrochlorothiazide'),
(49,'Lortab','hydrocodone and acetaminophen'),
(50,'Motrin','ibuprophen'),
(51,'Lantus','insulin glargine'),
(52,'Imdur','isosorbide mononitrate'),
(53,'Prevacid','lansoprazole'),
(54,'Levaquin','levofloxacin'),
(55,'Levoxl','levothyroxine sodium'),
(56,'Zestoretic','lisinopril and hydrochlorothiazide'),
(57,'Prinivil','lisinopril'),
(58,'Ativan','lorazepam'),
(59,'Cozaar','losartan'),
(60,'Mevacor','lovastatin'),
(61,'Mobic','meloxicam'),
(62,'Glucophage','metformin HCl'),
(63,'Medrol','methylprednisone'),
(64,'Toprol','metoprolol succinate XL'),
(65,'Lopressor','metoprolol tartrate'),
(66,'Nasonex','mometasone'),
(67,'Singulair','montelukast'),
(68,'Naprosyn','naproxen'),
(69,'Prilosec','omeprazole'),
(70,'Percocet','oxycodone and acetaminophen'),
(71,'Protonix','pantoprazole'),
(72,'Paxil','paroxetine'),
(73,'Actos','pioglitazone'),
(74,'Klor-Con','potassium Chloride'),
(75,'Pravachol','pravastatin'),
(76,'Deltasone','prednisone'),
(77,'Lyrica','pregabalin'),
(78,'Phenergan','promethazine'),
(79,'Seroquel','quetiapine'),
(80,'Zantac','ranitidine'),
(81,'Crestor','rosuvastatin'),
(82,'Zoloft','sertraline HCl'),
(83,'Viagra','sildenafil HCl'),
(84,'Vytorin','simvastatin and ezetimibe'),
(85,'Zocor','simvastatin'),
(86,'Aldactone','spironolactone'),
(87,'Bactrim DS','sulfamethoxazole and trimethoprim DS'),
(88,'Flomax','tamsulosin'),
(89,'Restoril','temezepam'),
(90,'Topamax','topiramate'),
(91,'Ultram','tramadol'),
(92,'Aristocort','triamcinolone Ace topical'),
(93,'Desyrel','trazodone HCl'),
(94,'Dyazide','triamterene and hydrochlorothiazide'),
(95,'Valtrex','valaciclovir'),
(96,'Diovan','valsartan'),
(97,'Effexor XR','venlafaxine XR'),
(98,'Calan SR','verapamil SR'),
(99,'Ambien','zolpidem');