DatabaseCreation: MedicalSystem DML.txt

File MedicalSystem DML.txt, 42.9 KB (added by 231561, 13 days ago)

this is the dml

Line 
1MedicalSystem DML
2
3CREATE TEMP TABLE tmp_names (rn INT PRIMARY KEY, name TEXT, gender TEXT);
4CREATE TEMP TABLE tmp_surnames (rn INT PRIMARY KEY, surname TEXT);
5
6INSERT INTO tmp_names (rn, name, gender) VALUES
7(1,'James','MALE'),(2,'John','MALE'),(3,'Robert','MALE'),(4,'Michael','MALE'),(5,'William','MALE'),
8(6,'David','MALE'),(7,'Richard','MALE'),(8,'Joseph','MALE'),(9,'Thomas','MALE'),(10,'Charles','MALE'),
9(11,'Christopher','MALE'),(12,'Daniel','MALE'),(13,'Matthew','MALE'),(14,'Anthony','MALE'),(15,'Mark','MALE'),
10(16,'Donald','MALE'),(17,'Steven','MALE'),(18,'Paul','MALE'),(19,'Andrew','MALE'),(20,'Kenneth','MALE'),
11(21,'Kevin','MALE'),(22,'Brian','MALE'),(23,'George','MALE'),(24,'Timothy','MALE'),(25,'Ronald','MALE'),
12(26,'Edward','MALE'),(27,'Jason','MALE'),(28,'Jeffrey','MALE'),(29,'Ryan','MALE'),(30,'Jacob','MALE'),
13(31,'Gary','MALE'),(32,'Nicholas','MALE'),(33,'Eric','MALE'),(34,'Jonathan','MALE'),(35,'Stephen','MALE'),
14(36,'Larry','MALE'),(37,'Justin','MALE'),(38,'Scott','MALE'),(39,'Brandon','MALE'),(40,'Benjamin','MALE'),
15(41,'Samuel','MALE'),(42,'Raymond','MALE'),(43,'Gregory','MALE'),(44,'Frank','MALE'),(45,'Alexander','MALE'),
16(46,'Patrick','MALE'),(47,'Jack','MALE'),(48,'Dennis','MALE'),(49,'Jerry','MALE'),(50,'Tyler','MALE'),
17(51,'Mary','FEMALE'),(52,'Patricia','FEMALE'),(53,'Jennifer','FEMALE'),(54,'Linda','FEMALE'),(55,'Barbara','FEMALE'),
18(56,'Elizabeth','FEMALE'),(57,'Susan','FEMALE'),(58,'Jessica','FEMALE'),(59,'Sarah','FEMALE'),(60,'Karen','FEMALE'),
19(61,'Lisa','FEMALE'),(62,'Nancy','FEMALE'),(63,'Betty','FEMALE'),(64,'Margaret','FEMALE'),(65,'Sandra','FEMALE'),
20(66,'Ashley','FEMALE'),(67,'Dorothy','FEMALE'),(68,'Kimberly','FEMALE'),(69,'Emily','FEMALE'),(70,'Donna','FEMALE'),
21(71,'Michelle','FEMALE'),(72,'Carol','FEMALE'),(73,'Amanda','FEMALE'),(74,'Melissa','FEMALE'),(75,'Deborah','FEMALE'),
22(76,'Stephanie','FEMALE'),(77,'Rebecca','FEMALE'),(78,'Sharon','FEMALE'),(79,'Laura','FEMALE'),(80,'Cynthia','FEMALE'),
23(81,'Kathleen','FEMALE'),(82,'Amy','FEMALE'),(83,'Angela','FEMALE'),(84,'Shirley','FEMALE'),(85,'Anna','FEMALE'),
24(86,'Brenda','FEMALE'),(87,'Pamela','FEMALE'),(88,'Emma','FEMALE'),(89,'Nicole','FEMALE'),(90,'Helen','FEMALE'),
25(91,'Samantha','FEMALE'),(92,'Katherine','FEMALE'),(93,'Christine','FEMALE'),(94,'Rachel','FEMALE'),(95,'Lauren','FEMALE'),
26(96,'Kelly','FEMALE'),(97,'Christina','FEMALE'),(98,'Joan','FEMALE'),(99,'Evelyn','FEMALE'),(100,'Judith','FEMALE');
27
28INSERT INTO tmp_surnames (rn, surname) VALUES
29(1,'Smith'),(2,'Johnson'),(3,'Williams'),(4,'Brown'),(5,'Jones'),
30(6,'Garcia'),(7,'Miller'),(8,'Davis'),(9,'Rodriguez'),(10,'Martinez'),
31(11,'Hernandez'),(12,'Lopez'),(13,'Gonzalez'),(14,'Wilson'),(15,'Anderson'),
32(16,'Thomas'),(17,'Taylor'),(18,'Moore'),(19,'Jackson'),(20,'Martin'),
33(21,'Lee'),(22,'Perez'),(23,'Thompson'),(24,'White'),(25,'Harris'),
34(26,'Sanchez'),(27,'Clark'),(28,'Ramirez'),(29,'Lewis'),(30,'Robinson'),
35(31,'Walker'),(32,'Young'),(33,'Allen'),(34,'King'),(35,'Wright'),
36(36,'Scott'),(37,'Torres'),(38,'Nguyen'),(39,'Hill'),(40,'Flores'),
37(41,'Green'),(42,'Adams'),(43,'Nelson'),(44,'Baker'),(45,'Hall'),
38(46,'Rivera'),(47,'Campbell'),(48,'Mitchell'),(49,'Carter'),(50,'Roberts'),
39(51,'Gomez'),(52,'Phillips'),(53,'Evans'),(54,'Turner'),(55,'Diaz'),
40(56,'Parker'),(57,'Cruz'),(58,'Edwards'),(59,'Collins'),(60,'Reyes'),
41(61,'Stewart'),(62,'Morris'),(63,'Morales'),(64,'Murphy'),(65,'Cook'),
42(66,'Rogers'),(67,'Gutierrez'),(68,'Ortiz'),(69,'Morgan'),(70,'Cooper'),
43(71,'Peterson'),(72,'Bailey'),(73,'Reed'),(74,'Kelly'),(75,'Howard'),
44(76,'Ramos'),(77,'Kim'),(78,'Cox'),(79,'Ward'),(80,'Richardson'),
45(81,'Watson'),(82,'Brooks'),(83,'Chavez'),(84,'Wood'),(85,'James'),
46(86,'Bennett'),(87,'Gray'),(88,'Mendoza'),(89,'Ruiz'),(90,'Hughes'),
47(91,'Price'),(92,'Alvarez'),(93,'Castillo'),(94,'Sanders'),(95,'Patel'),
48(96,'Myers'),(97,'Long'),(98,'Ross'),(99,'Foster'),(100,'Jimenez');
49
50INSERT INTO Specialization (spec_id, spec_name) VALUES
51(1,'Cardiology'),(2,'Neurology'),(3,'Orthopedics'),(4,'Pediatrics'),
52(5,'Dermatology'),(6,'Oncology'),(7,'Gastroenterology'),(8,'Pulmonology'),
53(9,'Endocrinology'),(10,'Nephrology'),(11,'Ophthalmology'),(12,'Psychiatry'),
54(13,'Rheumatology'),(14,'Urology'),(15,'Hematology'),(16,'Infectious Disease'),
55(17,'Emergency Medicine'),(18,'General Surgery'),(19,'Radiology'),(20,'Anesthesiology');
56
57INSERT INTO Hospital (hospital_id, hospital_address) VALUES
58(1,'100 Medical Center Dr, New York, NY 10001'),
59(2,'200 Healthcare Blvd, Los Angeles, CA 90001'),
60(3,'300 Wellness Ave, Chicago, IL 60601'),
61(4,'400 Hospital Lane, Houston, TX 77001'),
62(5,'500 Care Street, Phoenix, AZ 85001'),
63(6,'600 Health Pkwy, Philadelphia, PA 19101'),
64(7,'700 Mercy Blvd, San Antonio, TX 78201'),
65(8,'800 General Ave, San Diego, CA 92101'),
66(9,'900 University Dr, Dallas, TX 75201'),
67(10,'1000 Memorial Blvd, San Jose, CA 95101'),
68(11,'101 St. Luke Rd, Austin, TX 73301'),
69(12,'202 Providence Ave, Jacksonville, FL 32201'),
70(13,'303 Cedars Blvd, Fort Worth, TX 76101'),
71(14,'404 Baptist Dr, Columbus, OH 43201'),
72(15,'505 Methodist Ln, Charlotte, NC 28201'),
73(16,'606 Presbyterian Ave, Indianapolis, IN 46201'),
74(17,'707 Sinai Blvd, San Francisco, CA 94101'),
75(18,'808 Covenant Dr, Seattle, WA 98101'),
76(19,'809 Harbor Blvd, Denver, CO 80201'),
77(20,'810 Valley Rd, Nashville, TN 37201'),
78(21,'811 Riverside Dr, Oklahoma City, OK 73101'),
79(22,'812 Lakeview Ave, El Paso, TX 79901'),
80(23,'813 Hillcrest Blvd, Washington, DC 20001'),
81(24,'814 Sunrise Pkwy, Las Vegas, NV 89101'),
82(25,'815 Eastside Dr, Louisville, KY 40201'),
83(26,'816 Northside Ave, Baltimore, MD 21201'),
84(27,'817 Westpark Blvd, Milwaukee, WI 53201'),
85(28,'818 Southgate Dr, Albuquerque, NM 87101'),
86(29,'819 Central Ave, Tucson, AZ 85701'),
87(30,'820 Midtown Blvd, Fresno, CA 93701'),
88(31,'821 Downtown Dr, Sacramento, CA 95801'),
89(32,'822 Uptown Ave, Mesa, AZ 85201'),
90(33,'823 Fairview Rd, Kansas City, MO 64101'),
91(34,'824 Greenfield Dr, Atlanta, GA 30301'),
92(35,'825 Brookside Ave, Omaha, NE 68101'),
93(36,'826 Clearwater Blvd, Colorado Springs, CO 80901'),
94(37,'827 Highpoint Dr, Raleigh, NC 27601'),
95(38,'828 Lakewood Ave, Long Beach, CA 90801'),
96(39,'829 Pinecrest Rd, Virginia Beach, VA 23451'),
97(40,'830 Oakdale Blvd, Minneapolis, MN 55401'),
98(41,'831 Maplewood Dr, Tampa, FL 33601'),
99(42,'832 Elmwood Ave, New Orleans, LA 70112'),
100(43,'833 Cedarbrook Rd, Arlington, TX 76001'),
101(44,'834 Willowbrook Blvd, Wichita, KS 67201'),
102(45,'835 Birchwood Dr, Bakersfield, CA 93301'),
103(46,'836 Rosewood Ave, Aurora, CO 80010'),
104(47,'847 Magnolia Blvd, Anaheim, CA 92801'),
105(48,'848 Chestnut Dr, Santa Ana, CA 92701'),
106(49,'849 Walnut Ave, Corpus Christi, TX 78401'),
107(50,'850 Hawthorn Rd, Riverside, CA 92501'),
108(51,'851 Poplar Blvd, Lexington, KY 40501'),
109(52,'852 Sycamore Dr, St. Louis, MO 63101'),
110(53,'853 Ash Tree Ave, Pittsburgh, PA 15201'),
111(54,'854 Redwood Blvd, Anchorage, AK 99501'),
112(55,'855 Spruce Dr, Stockton, CA 95201'),
113(56,'856 Cypress Ave, Cincinnati, OH 45201'),
114(57,'857 Juniper Rd, St. Paul, MN 55101'),
115(58,'858 Sequoia Blvd, Toledo, OH 43601'),
116(59,'859 Aspen Dr, Greensboro, NC 27401'),
117(60,'860 Douglas Ave, Newark, NJ 07101'),
118(61,'861 Fir Tree Rd, Plano, TX 75023'),
119(62,'862 Hemlock Blvd, Henderson, NV 89002'),
120(63,'863 Laurel Dr, Lincoln, NE 68501'),
121(64,'864 Boxwood Ave, Buffalo, NY 14201'),
122(65,'865 Holly Rd, Fort Wayne, IN 46801'),
123(66,'866 Ivy Blvd, Jersey City, NJ 07302'),
124(67,'867 Moss Dr, Chula Vista, CA 91910'),
125(68,'868 Fern Ave, Orlando, FL 32801'),
126(69,'869 Sage Rd, St. Petersburg, FL 33701'),
127(70,'870 Thyme Blvd, Laredo, TX 78041'),
128(71,'871 Basil Dr, Madison, WI 53701'),
129(72,'872 Rosemary Ave, Norfolk, VA 23501'),
130(73,'873 Lavender Rd, Durham, NC 27701'),
131(74,'874 Mint Blvd, Lubbock, TX 79401'),
132(75,'875 Clover Dr, Winston-Salem, NC 27101'),
133(76,'876 Daisy Ave, Garland, TX 75040'),
134(77,'877 Lily Rd, Glendale, AZ 85301'),
135(78,'878 Tulip Blvd, Hialeah, FL 33010'),
136(79,'879 Orchid Dr, Reno, NV 89501'),
137(80,'880 Iris Ave, Baton Rouge, LA 70801'),
138(81,'881 Violet Rd, Irvine, CA 92602'),
139(82,'882 Pansy Blvd, Chesapeake, VA 23320'),
140(83,'883 Peony Dr, Scottsdale, AZ 85251'),
141(84,'884 Aster Ave, North Las Vegas, NV 89030'),
142(85,'885 Begonia Rd, Fremont, CA 94536'),
143(86,'886 Camellia Blvd, Gilbert, AZ 85296'),
144(87,'887 Dahlia Dr, San Bernardino, CA 92401'),
145(88,'888 Gardenia Ave, Birmingham, AL 35203'),
146(89,'889 Hibiscus Rd, Rochester, NY 14601'),
147(90,'890 Jasmine Blvd, Spokane, WA 99201'),
148(91,'891 Zinnia Dr, Des Moines, IA 50301'),
149(92,'892 Marigold Ave, Modesto, CA 95351'),
150(93,'893 Sunflower Rd, Fayetteville, NC 28301'),
151(94,'894 Buttercup Blvd, Tacoma, WA 98401'),
152(95,'895 Bluebell Dr, Oxnard, CA 93030'),
153(96,'896 Carnation Ave, Fontana, CA 92335'),
154(97,'897 Snapdragon Rd, Moreno Valley, CA 92553'),
155(98,'898 Foxglove Blvd, Glendale, CA 91201'),
156(99,'899 Hollyhock Dr, Huntington Beach, CA 92647'),
157(100,'900 Cosmos Ave, Montgomery, AL 36101'),
158(101,'901 Aster Rd, Akron, OH 44301'),
159(102,'902 Lilac Blvd, Little Rock, AR 72201'),
160(103,'903 Wisteria Dr, Columbus, GA 31901'),
161(104,'904 Forsythia Ave, Grand Rapids, MI 49501'),
162(105,'905 Magnolia Rd, Salt Lake City, UT 84101'),
163(106,'906 Azalea Blvd, Huntsville, AL 35801'),
164(107,'907 Bougainvillea Dr, Knoxville, TN 37901'),
165(108,'908 Verbena Ave, Worcester, MA 01601'),
166(109,'909 Lantana Rd, Brownsville, TX 78520'),
167(110,'910 Impatiens Blvd, Santa Clarita, CA 91380'),
168(111,'911 Primrose Dr, Providence, RI 02901'),
169(112,'912 Yarrow Ave, Garden Grove, CA 92840'),
170(113,'913 Valerian Rd, Oceanside, CA 92054'),
171(114,'914 Chamomile Blvd, Chattanooga, TN 37401'),
172(115,'915 Echinacea Dr, Fort Lauderdale, FL 33301'),
173(116,'916 Goldenrod Ave, Rancho Cucamonga, CA 91730'),
174(117,'917 St Johns Wort Rd, Santa Rosa, CA 95401'),
175(118,'918 Elderflower Blvd, Tempe, AZ 85281'),
176(119,'919 Feverfew Dr, Cape Coral, FL 33990'),
177(120,'920 Dandelion Ave, Sioux Falls, SD 57101');
178
179INSERT INTO Department (department_id, department_name, spec_id)
180SELECT
181 s.i,
182 sp.spec_name || ' Department',
183 sp.spec_id
184FROM generate_series(1, 150) AS s(i)
185JOIN Specialization sp ON sp.spec_id = ((s.i - 1) % 20) + 1;
186
187UPDATE Department SET department_name = 'Emergency Department' WHERE department_id = 17;
188UPDATE Department SET department_name = 'Cardiac ICU' WHERE department_id = 21;
189UPDATE Department SET department_name = 'Surgical ICU' WHERE department_id = 38;
190UPDATE Department SET department_name = 'Pediatric ICU' WHERE department_id = 44;
191UPDATE Department SET department_name = 'Oncology Day Care' WHERE department_id = 66;
192UPDATE Department SET department_name = 'Neuro ICU' WHERE department_id = 82;
193UPDATE Department SET department_name = 'Radiology & Imaging' WHERE department_id = 99;
194UPDATE Department SET department_name = 'General Surgery Suite' WHERE department_id = 118;
195
196INSERT INTO Doctor (doctor_id, spec_id, first_name, last_name, phone, email)
197SELECT
198 s.i,
199 (s.i % 20) + 1,
200 n.name,
201 sn.surname,
202 '+1' || lpad((3000000000 + s.i)::TEXT, 10, '0'),
203 lower(n.name) || '.' || lower(sn.surname) || s.i::TEXT || '@hospital.com'
204FROM generate_series(1, 3000) AS s(i)
205JOIN tmp_names n ON n.rn = (s.i % 100) + 1
206JOIN tmp_surnames sn ON sn.rn = (s.i % 100) + 1;
207
208INSERT INTO Doctor_department (
209 doctor_dept_id, doctor_id, department_id,
210 employment_type, date_from, date_to
211)
212SELECT
213 s.i,
214 s.i,
215 (s.i % 150) + 1,
216 CASE (s.i % 4)
217 WHEN 0 THEN 'FULL_TIME' WHEN 1 THEN 'PART_TIME'
218 WHEN 2 THEN 'CONSULTANT' ELSE 'RESIDENT'
219 END,
220 (CURRENT_DATE - ((s.i % 3650) * INTERVAL '1 day'))::DATE,
221 CASE WHEN s.i % 7 = 0
222 THEN (CURRENT_DATE - ((s.i % 365) * INTERVAL '1 day'))::DATE
223 ELSE NULL
224 END
225FROM generate_series(1, 3000) AS s(i);
226
227INSERT INTO Patient (
228 patient_id, first_name, last_name,
229 date_of_birth, gender, phone, email, address
230)
231SELECT
232 s.i,
233 n.name,
234 sn.surname,
235 (CURRENT_DATE - ((s.i % 29200) * INTERVAL '1 day'))::DATE,
236 n.gender,
237 '+1' || lpad((2000000000 + s.i)::TEXT, 10, '0'),
238 lower(n.name) || '.' || lower(sn.surname) || s.i::TEXT || '@email.com',
239 (s.i % 9999 + 1)::TEXT || ' ' ||
240 CASE (s.i % 10)
241 WHEN 0 THEN 'Main St' WHEN 1 THEN 'Oak Ave'
242 WHEN 2 THEN 'Maple Dr' WHEN 3 THEN 'Cedar Blvd'
243 WHEN 4 THEN 'Pine Rd' WHEN 5 THEN 'Elm St'
244 WHEN 6 THEN 'Park Ave' WHEN 7 THEN 'Lake Dr'
245 WHEN 8 THEN 'River Rd' ELSE 'Highland Ave'
246 END
247FROM generate_series(1, 2000000) AS s(i)
248JOIN tmp_names n ON n.rn = (s.i % 100) + 1
249JOIN tmp_surnames sn ON sn.rn = (s.i % 100) + 1;
250
251INSERT INTO ICD (icd_id, code, description) VALUES
252(1,'I10','Essential hypertension'),
253(2,'E11','Type 2 diabetes mellitus'),
254(3,'J18.9','Pneumonia, unspecified'),
255(4,'M54.5','Low back pain'),
256(5,'J06.9','Acute upper respiratory infection'),
257(6,'E78.5','Hyperlipidemia, unspecified'),
258(7,'G43.9','Migraine, unspecified'),
259(8,'F32.9','Major depressive disorder'),
260(9,'K21.0','Gastro-esophageal reflux disease'),
261(10,'I25.10','Atherosclerotic heart disease'),
262(11,'N39.0','Urinary tract infection'),
263(12,'J45.9','Asthma, unspecified'),
264(13,'M17.9','Osteoarthritis of knee'),
265(14,'E03.9','Hypothyroidism, unspecified'),
266(15,'F41.1','Generalized anxiety disorder'),
267(16,'I50.9','Heart failure, unspecified'),
268(17,'C34.9','Malignant neoplasm of lung'),
269(18,'N18.3','Chronic kidney disease stage 3'),
270(19,'B97.89','Viral infection unspecified'),
271(20,'Z87.891','History of nicotine dependence'),
272(21,'K92.1','Melena'),
273(22,'S72.001','Fracture of femur neck'),
274(23,'G20','Parkinson disease'),
275(24,'G35','Multiple sclerosis'),
276(25,'C50.9','Malignant neoplasm of breast'),
277(26,'C18.9','Malignant neoplasm of colon'),
278(27,'I21.9','Acute myocardial infarction'),
279(28,'I63.9','Cerebral infarction unspecified'),
280(29,'E11.65','Type 2 diabetes with hyperglycemia'),
281(30,'M79.3','Panniculitis unspecified'),
282(31,'L40.0','Psoriasis vulgaris'),
283(32,'K57.30','Diverticulosis of large intestine'),
284(33,'Z00.00','General adult medical examination'),
285(34,'J20.9','Acute bronchitis unspecified'),
286(35,'R05','Cough'),
287(36,'R51','Headache'),
288(37,'R50.9','Fever unspecified'),
289(38,'N20.0','Calculus of kidney'),
290(39,'K35.80','Acute appendicitis'),
291(40,'H35.30','Macular degeneration'),
292(41,'I48.91','Unspecified atrial fibrillation'),
293(42,'E10','Type 1 diabetes mellitus'),
294(43,'J44.1','COPD with acute exacerbation'),
295(44,'M05.79','Rheumatoid arthritis with rheumatoid factor'),
296(45,'N17.9','Acute kidney failure unspecified'),
297(46,'K70.30','Alcoholic cirrhosis of liver without ascites'),
298(47,'G40.909','Epilepsy unspecified'),
299(48,'F20.9','Schizophrenia unspecified'),
300(49,'C61','Malignant neoplasm of prostate'),
301(50,'C92.00','Acute myeloblastic leukemia'),
302(51,'D50.9','Iron deficiency anemia unspecified'),
303(52,'E55.9','Vitamin D deficiency unspecified'),
304(53,'M81.0','Age-related osteoporosis'),
305(54,'H25.9','Unspecified age-related cataract'),
306(55,'H40.9','Unspecified glaucoma'),
307(56,'J30.9','Allergic rhinitis unspecified'),
308(57,'K80.20','Calculus of gallbladder without cholecystitis'),
309(58,'K86.1','Other chronic pancreatitis'),
310(59,'L20.9','Atopic dermatitis unspecified'),
311(60,'M10.9','Gout unspecified'),
312(61,'N40.0','Benign prostatic hyperplasia without LUTS'),
313(62,'N83.20','Unspecified ovarian cyst'),
314(63,'O24.419','Unspecified diabetes mellitus in pregnancy'),
315(64,'P07.30','Preterm newborn unspecified weeks'),
316(65,'Q21.0','Ventricular septal defect'),
317(66,'R00.0','Tachycardia unspecified'),
318(67,'R06.00','Dyspnea unspecified'),
319(68,'R10.9','Unspecified abdominal pain'),
320(69,'R55','Syncope and collapse'),
321(70,'S06.0X0A','Concussion without loss of consciousness'),
322(71,'T14.90','Injury unspecified'),
323(72,'Z23','Encounter for immunization'),
324(73,'Z34.00','Encounter for supervision of normal pregnancy'),
325(74,'Z51.11','Encounter for antineoplastic chemotherapy'),
326(75,'Z79.01','Long-term use of anticoagulants'),
327(76,'I11.9','Hypertensive heart disease without heart failure'),
328(77,'I20.9','Angina pectoris unspecified'),
329(78,'I26.99','Other pulmonary embolism without acute cor pulmonale'),
330(79,'I35.0','Nonrheumatic aortic stenosis'),
331(80,'I42.9','Cardiomyopathy unspecified'),
332(81,'I70.209','Unspecified atherosclerosis of native arteries of extremities'),
333(82,'I80.209','Phlebitis and thrombophlebitis of unspecified deep vessels'),
334(83,'J84.10','Pulmonary fibrosis unspecified'),
335(84,'K50.90','Crohns disease of small intestine without complications'),
336(85,'K51.90','Ulcerative colitis unspecified without complications'),
337(86,'K72.90','Hepatic failure unspecified without coma'),
338(87,'L03.90','Cellulitis unspecified'),
339(88,'M06.9','Rheumatoid arthritis unspecified'),
340(89,'M43.6','Torticollis'),
341(90,'M51.16','Intervertebral disc degeneration lumbar region'),
342(91,'N02.9','Recurrent hematuria unspecified morphologic changes'),
343(92,'N04.9','Nephrotic syndrome with unspecified morphologic changes'),
344(93,'N10','Acute pyelonephritis'),
345(94,'N25.0','Renal osteodystrophy'),
346(95,'N30.00','Acute cystitis without hematuria'),
347(96,'N41.0','Acute prostatitis'),
348(97,'C16.9','Malignant neoplasm of stomach unspecified'),
349(98,'C22.0','Liver cell carcinoma'),
350(99,'C25.9','Malignant neoplasm of pancreas unspecified'),
351(100,'C64.9','Malignant neoplasm of unspecified kidney'),
352(101,'C67.9','Malignant neoplasm of bladder unspecified'),
353(102,'C73','Malignant neoplasm of thyroid gland'),
354(103,'C81.90','Hodgkin lymphoma unspecified'),
355(104,'C85.90','Non-Hodgkin lymphoma unspecified'),
356(105,'C90.00','Multiple myeloma not having achieved remission'),
357(106,'D18.00','Hemangioma unspecified site'),
358(107,'D25.9','Leiomyoma of uterus unspecified'),
359(108,'D35.00','Benign neoplasm of adrenal gland'),
360(109,'E05.90','Thyrotoxicosis unspecified without thyrotoxic crisis'),
361(110,'E06.9','Thyroiditis unspecified'),
362(111,'E13.9','Other specified diabetes mellitus without complications'),
363(112,'E21.0','Primary hyperparathyroidism'),
364(113,'E22.0','Acromegaly and pituitary gigantism'),
365(114,'E27.1','Primary adrenocortical insufficiency'),
366(115,'E66.9','Obesity unspecified'),
367(116,'F06.30','Mood disorder due to known physiological condition'),
368(117,'F10.20','Alcohol dependence uncomplicated'),
369(118,'F31.9','Bipolar disorder unspecified'),
370(119,'F40.10','Social phobia unspecified'),
371(120,'F43.10','Post-traumatic stress disorder unspecified'),
372(121,'F50.9','Eating disorder unspecified'),
373(122,'F60.3','Borderline personality disorder'),
374(123,'G00.9','Bacterial meningitis unspecified'),
375(124,'G30.9','Alzheimers disease unspecified'),
376(125,'G45.9','Transient cerebral ischemic attack unspecified'),
377(126,'G47.00','Insomnia unspecified'),
378(127,'G51.0','Bells palsy'),
379(128,'G54.2','Cervical root disorders'),
380(129,'G62.9','Polyneuropathy unspecified'),
381(130,'G80.9','Cerebral palsy unspecified'),
382(131,'H10.9','Unspecified conjunctivitis'),
383(132,'H16.9','Unspecified keratitis'),
384(133,'H26.9','Unspecified cataract'),
385(134,'H35.00','Unspecified background retinopathy'),
386(135,'H52.4','Presbyopia'),
387(136,'H61.90','Unspecified disorder of external ear'),
388(137,'H65.9','Unspecified nonsuppurative otitis media'),
389(138,'H72.90','Unspecified perforation of tympanic membrane'),
390(139,'H81.10','Benign paroxysmal vertigo unspecified ear'),
391(140,'H83.9','Unspecified disease of inner ear'),
392(141,'J01.90','Acute sinusitis unspecified'),
393(142,'J02.9','Acute pharyngitis unspecified'),
394(143,'J03.90','Acute tonsillitis unspecified'),
395(144,'J31.0','Chronic rhinitis'),
396(145,'J32.9','Chronic sinusitis unspecified'),
397(146,'J35.01','Chronic tonsillitis'),
398(147,'J38.00','Paralysis of vocal cords and larynx unspecified'),
399(148,'K04.0','Pulpitis'),
400(149,'K08.109','Complete loss of teeth unspecified cause'),
401(150,'K11.20','Sialoadenitis unspecified'),
402(151,'K22.0','Achalasia of cardia'),
403(152,'K25.9','Gastric ulcer unspecified'),
404(153,'K29.70','Gastritis unspecified without bleeding'),
405(154,'K40.90','Unilateral inguinal hernia without obstruction or gangrene'),
406(155,'K43.9','Ventral hernia without obstruction or gangrene'),
407(156,'K55.9','Vascular disorder of intestine unspecified'),
408(157,'K59.00','Constipation unspecified'),
409(158,'K59.1','Functional diarrhea'),
410(159,'K63.5','Polyp of colon'),
411(160,'K74.60','Unspecified cirrhosis of liver'),
412(161,'L08.9','Local infection of skin unspecified'),
413(162,'L10.0','Pemphigus vulgaris'),
414(163,'L23.9','Allergic contact dermatitis unspecified'),
415(164,'L30.9','Dermatitis unspecified'),
416(165,'L43.9','Lichen planus unspecified'),
417(166,'L50.9','Urticaria unspecified'),
418(167,'L60.0','Ingrowing nail'),
419(168,'L70.0','Acne vulgaris'),
420(169,'L71.0','Perioral dermatitis'),
421(170,'L72.0','Epidermal cyst'),
422(171,'M00.9','Pyogenic arthritis unspecified'),
423(172,'M12.9','Arthropathy unspecified'),
424(173,'M16.9','Osteoarthritis of hip unspecified'),
425(174,'M19.90','Unspecified osteoarthritis unspecified site'),
426(175,'M20.10','Hallux valgus unspecified foot'),
427(176,'M23.200','Derangement of unspecified meniscus'),
428(177,'M25.50','Pain in unspecified joint'),
429(178,'M32.9','Systemic lupus erythematosus unspecified'),
430(179,'M34.9','Systemic sclerosis unspecified'),
431(180,'M35.9','Systemic involvement of connective tissue unspecified'),
432(181,'N13.30','Unspecified hydronephrosis'),
433(182,'N21.0','Calculus in bladder'),
434(183,'N35.9','Urethral stricture unspecified'),
435(184,'N43.3','Hydrocele unspecified'),
436(185,'N45.1','Orchitis'),
437(186,'N48.29','Other priapism'),
438(187,'N60.01','Solitary cyst of right breast'),
439(188,'N63.0','Unspecified lump in unspecified breast'),
440(189,'N76.0','Acute vaginitis'),
441(190,'N80.0','Endometriosis of uterus'),
442(191,'N94.6','Dysmenorrhoea unspecified'),
443(192,'N95.1','Menopausal and female climacteric states'),
444(193,'O03.9','Complete or unspecified spontaneous abortion without complication'),
445(194,'O09.90','Supervision of high-risk pregnancy unspecified'),
446(195,'O14.00','Mild to moderate preeclampsia unspecified trimester'),
447(196,'O42.00','Premature rupture of membranes onset of labor within 24hrs'),
448(197,'O60.10','Preterm labor without delivery unspecified trimester'),
449(198,'O80','Encounter for full-term uncomplicated delivery'),
450(199,'P00.0','Newborn affected by maternal hypertensive disorders'),
451(200,'Z76.89','Persons encountering health services in other specified circumstances');
452
453INSERT INTO Drug_producers (drug_prod_id, address, producer_name, country_origin, website, phone) VALUES
454(1,'100 Pharma Way, Basel','Novartis AG','Switzerland','www.novartis.com','+41612241111'),
455(2,'1 Infinity Dr, New York NY','Pfizer Inc.','USA','www.pfizer.com','+12125732323'),
456(3,'200 Merck Dr, Kenilworth NJ','Merck & Co.','USA','www.merck.com','+19082404000'),
457(4,'25 New North Place, London','GlaxoSmithKline plc','UK','www.gsk.com','+442089904477'),
458(5,'1 Johnson Dr, New Brunswick NJ','Johnson & Johnson','USA','www.jnj.com','+17325242455'),
459(6,'500 Arcola Rd, Collegeville PA','AstraZeneca','UK','www.astrazeneca.com','+18004562244'),
460(7,'51368 Leverkusen, Germany','Bayer AG','Germany','www.bayer.com','+492143050'),
461(8,'1 Amgen Center Dr, Thousand Oaks CA','Amgen Inc.','USA','www.amgen.com','+18054471000'),
462(9,'4070 Basel, Switzerland','F. Hoffmann-La Roche AG','Switzerland','www.roche.com','+41616881111'),
463(10,'22 Elm St, Whippany NJ','Sanofi S.A.','France','www.sanofi.com','+18003814884'),
464(11,'Grenzacherstr 124, Basel','Novartis Pharma AG','Switzerland','www.novartis.com','+41613245678'),
465(12,'235 E 42nd St, New York NY','Pfizer Global R&D','USA','www.pfizer.com','+12125559876'),
466(13,'One Merck Dr, Whitehouse Station NJ','Merck Sharp & Dohme','USA','www.msd.com','+19082401234'),
467(14,'980 Great West Rd, Brentford','GSK Consumer Healthcare','UK','www.gsk.com','+441895523456'),
468(15,'410 George St, New Brunswick NJ','Janssen Pharmaceutica','Belgium','www.janssen.com','+13217247890'),
469(16,'1800 Concord Pike, Wilmington DE','AstraZeneca US','USA','www.astrazeneca.com','+13028867890'),
470(17,'100 Bayer Blvd, Whippany NJ','Bayer HealthCare','USA','www.bayer.com','+19735945000'),
471(18,'One Amgen Center Dr, Thousand Oaks CA','Amgen Biologics','USA','www.amgen.com','+18054476789'),
472(19,'1 DNA Way, South San Francisco CA','Genentech Inc.','USA','www.gene.com','+16503254321'),
473(20,'Sandoz AG, Basel','Sandoz International GmbH','Germany','www.sandoz.com','+498024476000'),
474(21,'Industriestr 25, Leverkusen','Bayer Vital GmbH','Germany','www.bayervital.de','+492144752345'),
475(22,'54 rue La Boetie, Paris','Ipsen Pharma','France','www.ipsen.com','+33158336500'),
476(23,'1950 Lake Park Dr SE, Smyrna GA','Hikma Pharmaceuticals','Jordan','www.hikma.com','+14046810200'),
477(24,'400 Somerset Corp Blvd, Bridgewater NJ','Sanofi Genzyme','USA','www.sanofi.com','+19087823000'),
478(25,'Global Res Ctr, Cambridge UK','AstraZeneca R&D','UK','www.astrazeneca.com','+441223420000'),
479(26,'Hanauer Landstr 526, Frankfurt','Aventis Pharma','Germany','www.aventis.com','+496914500'),
480(27,'1 Genentech Way, South San Francisco CA','Roche Genentech','USA','www.roche.com','+16503327000'),
481(28,'Bld du Triomphe, Brussels','UCB Pharma','Belgium','www.ucb.com','+3222887211'),
482(29,'430 E 29th St, New York NY','Boehringer Ingelheim','Germany','www.boehringer-ingelheim.com','+12129098000'),
483(30,'700 Chesterfield Pkwy, Chesterfield MO','Mallinckrodt Pharmaceuticals','USA','www.mallinckrodt.com','+16363881000'),
484(31,'Takeda Global HQ, Osaka','Takeda Pharmaceutical','Japan','www.takeda.com','+81669761000'),
485(32,'3-4-1 Marunouchi, Tokyo','Astellas Pharma Inc.','Japan','www.astellas.com','+81332443000'),
486(33,'1-6-5 Marunouchi, Tokyo','Daiichi Sankyo Co.','Japan','www.daiichi-sankyo.com','+81352180800'),
487(34,'2-1-1 Nihonbashi, Tokyo','Eisai Co. Ltd.','Japan','www.eisai.com','+81336721600'),
488(35,'Sumitomo Corp, Tokyo','Sumitomo Pharma','Japan','www.sumitomo-pharma.com','+81362041000'),
489(36,'1 Baxter Pkwy, Deerfield IL','Baxter International','USA','www.baxter.com','+18479481212'),
490(37,'100 Abbott Park Rd, Abbott Park IL','Abbott Laboratories','USA','www.abbott.com','+18473376100'),
491(38,'One Becton Dr, Franklin Lakes NJ','Becton Dickinson','USA','www.bd.com','+12014472000'),
492(39,'702 Electronic Dr, Horsham PA','Teva Pharmaceuticals','Israel','www.tevapharm.com','+12153540600'),
493(40,'Generics UK, Potters Bar','Mylan Laboratories','USA','www.mylan.com','+441707853000'),
494(41,'East Hanover, New Jersey','Novartis Oncology','USA','www.novartisoncology.com','+18622782000'),
495(42,'777 Old Saw Mill River Rd, Tarrytown NY','Regeneron Pharmaceuticals','USA','www.regeneron.com','+19144477000'),
496(43,'1 DNA Way, South San Francisco CA','Biogen Inc.','USA','www.biogen.com','+16173798200'),
497(44,'200 First St SW, Rochester MN','Mayo Clinic Pharmaceuticals','USA','www.mayo.edu','+15072843964'),
498(45,'1209 Orange St, Wilmington DE','Incyte Corporation','USA','www.incyte.com','+13028432300'),
499(46,'Industriepark Hochst, Frankfurt','Hoechst AG','Germany','www.hoechst.com','+496930520'),
500(47,'Zurich, Switzerland','Lonza Group AG','Switzerland','www.lonza.com','+41617165500'),
501(48,'151 Farmington Ave, Hartford CT','Cigna Pharma Services','USA','www.cigna.com','+18604501000'),
502(49,'300 First Stamford Pl, Stamford CT','Purdue Pharma','USA','www.purduepharma.com','+12034888000'),
503(50,'12999 E Caley Ave, Centennial CO','DaVita Inc.','USA','www.davita.com','+17203728989');
504
505INSERT INTO ATC_code (atc_id, atc_code, description) VALUES
506(1,'C09AA01','Captopril - ACE inhibitors'),
507(2,'C09AA05','Ramipril - ACE inhibitors'),
508(3,'C10AA01','Simvastatin - HMG CoA reductase inhibitors'),
509(4,'C10AA05','Atorvastatin - HMG CoA reductase inhibitors'),
510(5,'A10BA02','Metformin - Biguanides'),
511(6,'A10BB01','Glibenclamide - Sulfonylureas'),
512(7,'N02BE01','Paracetamol - Anilides'),
513(8,'N02AA01','Morphine - Natural opium alkaloids'),
514(9,'J01CA04','Amoxicillin - Penicillins with extended spectrum'),
515(10,'J01FA09','Clarithromycin - Macrolides'),
516(11,'N06AB06','Sertraline - SSRIs'),
517(12,'N06AB04','Citalopram - SSRIs'),
518(13,'A02BC01','Omeprazole - Proton pump inhibitors'),
519(14,'A02BC02','Pantoprazole - Proton pump inhibitors'),
520(15,'R03AC02','Salbutamol - Selective beta-2-adrenoreceptor agonists'),
521(16,'H03AA01','Levothyroxine - Thyroid hormones'),
522(17,'C07AB02','Metoprolol - Beta blocking agents selective'),
523(18,'B01AC06','Aspirin - Platelet aggregation inhibitors'),
524(19,'M01AE01','Ibuprofen - Propionic acid derivatives'),
525(20,'N05BA01','Diazepam - Benzodiazepine derivatives'),
526(21,'C09CA01','Losartan - Angiotensin II receptor blockers'),
527(22,'C09CA06','Candesartan - Angiotensin II receptor blockers'),
528(23,'C08CA01','Amlodipine - Dihydropyridine calcium channel blockers'),
529(24,'C08CA05','Nifedipine - Dihydropyridine calcium channel blockers'),
530(25,'C03CA01','Furosemide - High-ceiling diuretics'),
531(26,'C03AA03','Hydrochlorothiazide - Thiazide diuretics'),
532(27,'C01AA05','Digoxin - Cardiac glycosides'),
533(28,'C01BD01','Amiodarone - Antiarrhythmics class III'),
534(29,'B01AA03','Warfarin - Vitamin K antagonists'),
535(30,'B01AF02','Rivaroxaban - Direct factor Xa inhibitors'),
536(31,'A10AE04','Insulin glargine - Long-acting insulins'),
537(32,'A10AB01','Insulin regular - Short-acting insulins'),
538(33,'A10BH01','Sitagliptin - DPP-4 inhibitors'),
539(34,'A10BJ01','Exenatide - GLP-1 receptor agonists'),
540(35,'N03AX09','Lamotrigine - Antiepileptics'),
541(36,'N03AF01','Carbamazepine - Antiepileptics'),
542(37,'N04BA01','Levodopa - Dopaminergic agents'),
543(38,'N04BC05','Pramipexole - Dopamine agonists'),
544(39,'N06AX11','Mirtazapine - Antidepressants'),
545(40,'N06AX16','Venlafaxine - SNRIs'),
546(41,'N05AH04','Quetiapine - Diazepines antipsychotics'),
547(42,'N05AX08','Risperidone - Other antipsychotics'),
548(43,'J02AC01','Fluconazole - Triazole antifungals'),
549(44,'J02AC02','Itraconazole - Triazole antifungals'),
550(45,'J05AB01','Acyclovir - Nucleoside analogues antivirals'),
551(46,'J05AE01','Saquinavir - HIV protease inhibitors'),
552(47,'J01GB03','Gentamicin - Aminoglycoside antibacterials'),
553(48,'J01MA02','Ciprofloxacin - Fluoroquinolone antibacterials'),
554(49,'J01DC02','Cefuroxime - Second-generation cephalosporins'),
555(50,'J01DD04','Ceftriaxone - Third-generation cephalosporins'),
556(51,'L01AA01','Cyclophosphamide - Alkylating agents'),
557(52,'L01BC05','Gemcitabine - Pyrimidine analogues'),
558(53,'L01CD01','Paclitaxel - Taxanes'),
559(54,'L01XC02','Rituximab - Monoclonal antibodies'),
560(55,'L02BA01','Tamoxifen - Antiestrogens'),
561(56,'L04AX03','Methotrexate - Other immunosuppressants'),
562(57,'L04AB02','Etanercept - TNF-alpha inhibitors'),
563(58,'L04AB04','Adalimumab - TNF-alpha inhibitors'),
564(59,'M01AB05','Diclofenac - Acetic acid derivatives'),
565(60,'M01AC06','Meloxicam - Oxicam derivatives'),
566(61,'M04AA01','Allopurinol - Preparations inhibiting uric acid production'),
567(62,'M05BA04','Alendronic acid - Bisphosphonates'),
568(63,'R01AD05','Budesonide - Nasal corticosteroids'),
569(64,'R03BA01','Beclometasone - Inhalation corticosteroids'),
570(65,'R03DC03','Montelukast - Leukotriene receptor antagonists'),
571(66,'R06AX13','Loratadine - Non-sedating antihistamines'),
572(67,'A01AB11','Chlorhexidine - Antiseptics'),
573(68,'A03FA01','Metoclopramide - Propulsives'),
574(69,'A04AA01','Ondansetron - 5HT3 antagonists antiemetics'),
575(70,'A06AD11','Macrogol - Osmotically acting laxatives'),
576(71,'B03BA01','Cyanocobalamin - Vitamin B12'),
577(72,'B03AA07','Ferrous sulphate - Iron preparations'),
578(73,'D07AC01','Betamethasone - Potent topical corticosteroids'),
579(74,'D07AA02','Hydrocortisone - Mild topical corticosteroids'),
580(75,'G03AA07','Levonorgestrel and ethinylestradiol - Combined oral contraceptives'),
581(76,'G04BD04','Oxybutynin - Urinary antispasmodics'),
582(77,'H02AB07','Prednisone - Systemic glucocorticoids'),
583(78,'H02AB06','Prednisolone - Systemic glucocorticoids'),
584(79,'V03AE01','Polystyrene sulphonate - Potassium binders'),
585(80,'V04CG30','Glucose tolerance test agents');
586
587INSERT INTO Drug (
588 product_id, drug_prod_id, atc_id, manufacturer,
589 batch_number, expiry_date, unit_price, storage_condition
590)
591SELECT
592 s.i,
593 (s.i % 50) + 1,
594 (s.i % 80) + 1,
595 CASE (s.i % 10)
596 WHEN 0 THEN 'Novartis' WHEN 1 THEN 'Pfizer'
597 WHEN 2 THEN 'Merck' WHEN 3 THEN 'GSK'
598 WHEN 4 THEN 'J&J' WHEN 5 THEN 'AstraZeneca'
599 WHEN 6 THEN 'Bayer' WHEN 7 THEN 'Roche'
600 WHEN 8 THEN 'Sanofi' ELSE 'Teva'
601 END,
602 'BATCH-' || lpad(s.i::TEXT, 5, '0'),
603 (CURRENT_DATE + INTERVAL '1 year' + (s.i % 730 * INTERVAL '1 day'))::DATE,
604 round((0.50 + (s.i % 200))::NUMERIC, 2),
605 CASE (s.i % 5)
606 WHEN 0 THEN 'Room temperature 15-25C'
607 WHEN 1 THEN 'Refrigerate 2-8C'
608 WHEN 2 THEN 'Cool dry place'
609 WHEN 3 THEN 'Protect from light'
610 ELSE 'Freeze at -20C'
611 END
612FROM generate_series(1, 2000) AS s(i);
613
614INSERT INTO Pharmacy (pharmacy_id, address, name, phone, license_num)
615SELECT
616 s.i,
617 (s.i * 10)::TEXT || ' ' ||
618 CASE (s.i % 15)
619 WHEN 0 THEN 'Health Blvd' WHEN 1 THEN 'Wellness Ave'
620 WHEN 2 THEN 'Care St' WHEN 3 THEN 'Medical Dr'
621 WHEN 4 THEN 'Remedy Rd' WHEN 5 THEN 'Cure Blvd'
622 WHEN 6 THEN 'Restore Ave' WHEN 7 THEN 'Renew St'
623 WHEN 8 THEN 'Revive Dr' WHEN 9 THEN 'Refresh Blvd'
624 WHEN 10 THEN 'Pharmacy Ln' WHEN 11 THEN 'Rx Dr'
625 WHEN 12 THEN 'MedCenter Pkwy' WHEN 13 THEN 'Clinic Rd'
626 ELSE 'Hospital Ave'
627 END || ', ' ||
628 CASE (s.i % 10)
629 WHEN 0 THEN 'New York, NY' WHEN 1 THEN 'Los Angeles, CA'
630 WHEN 2 THEN 'Chicago, IL' WHEN 3 THEN 'Houston, TX'
631 WHEN 4 THEN 'Phoenix, AZ' WHEN 5 THEN 'Philadelphia, PA'
632 WHEN 6 THEN 'San Antonio, TX' WHEN 7 THEN 'San Diego, CA'
633 WHEN 8 THEN 'Dallas, TX' ELSE 'San Jose, CA'
634 END,
635 CASE (s.i % 10)
636 WHEN 0 THEN 'MedPlus Pharmacy #' WHEN 1 THEN 'CareFirst Pharmacy #'
637 WHEN 2 THEN 'Healwell Pharmacy #' WHEN 3 THEN 'RxCare Pharmacy #'
638 WHEN 4 THEN 'HealthHub Pharmacy #' WHEN 5 THEN 'PharmaCare Plus #'
639 WHEN 6 THEN 'QuickRx Pharmacy #' WHEN 7 THEN 'Community Pharmacy #'
640 WHEN 8 THEN 'TrustRx Pharmacy #' ELSE 'Metro Pharmacy #'
641 END || s.i::TEXT,
642 '+1' || lpad((4000000000 + s.i)::TEXT, 10, '0'),
643 200000 + s.i
644FROM generate_series(1, 150) AS s(i);
645
646INSERT INTO Inventory (inventory_id, product_id, pharmacy_id, location, quantity)
647SELECT
648 ROW_NUMBER() OVER ()::INT,
649 d.product_id,
650 p.pharmacy_id,
651 'Shelf-' || chr(65 + (d.product_id % 8)) || (d.product_id % 20 + 1)::TEXT,
652 (d.product_id % 490) + 10
653FROM Drug d
654CROSS JOIN Pharmacy p
655WHERE (d.product_id + p.pharmacy_id) % 13 = 0;
656
657INSERT INTO Inventory_price (
658 inventory_price_id, purchasing_price, referent_price,
659 sale_price, date, inventory_id
660)
661SELECT
662 ROW_NUMBER() OVER ()::INT,
663 round((d.unit_price * 0.6)::NUMERIC, 2),
664 round((d.unit_price * 0.9)::NUMERIC, 2),
665 round((d.unit_price * 1.3)::NUMERIC, 2),
666 (CURRENT_DATE - (i.inventory_id % 730 * INTERVAL '1 day'))::DATE,
667 i.inventory_id
668FROM Inventory i
669JOIN Drug d ON d.product_id = i.product_id;
670
671INSERT INTO ICD (icd_id, code, description) VALUES
672(1, 'I10', 'Essential (primary) hypertension'),
673(2, 'E11', 'Type 2 diabetes mellitus'),
674(3, 'J18.9', 'Pneumonia, unspecified'),
675(4, 'M54.5', 'Low back pain'),
676(5, 'J06.9', 'Acute upper respiratory infection'),
677(6, 'E78.5', 'Hyperlipidemia, unspecified'),
678(7, 'G43.9', 'Migraine, unspecified'),
679(8, 'F32.9', 'Major depressive disorder, single episode'),
680(9, 'K21.0', 'Gastro-esophageal reflux disease'),
681(10, 'I25.10','Atherosclerotic heart disease'),
682(11, 'N39.0', 'Urinary tract infection'),
683(12, 'J45.9', 'Asthma, unspecified'),
684(13, 'M17.9', 'Osteoarthritis of knee, unspecified'),
685(14, 'E03.9', 'Hypothyroidism, unspecified'),
686(15, 'F41.1', 'Generalized anxiety disorder'),
687(16, 'I50.9', 'Heart failure, unspecified'),
688(17, 'C34.9', 'Malignant neoplasm of bronchus and lung'),
689(18, 'N18.3', 'Chronic kidney disease, stage 3'),
690(19, 'B97.89','Other viral agents as cause of disease'),
691(20, 'Z87.891','Personal history of nicotine dependence'),
692(21, 'K92.1', 'Melena'),
693(22, 'S72.001','Fracture of unspecified part of neck of right femur'),
694(23, 'G20', 'Parkinson disease'),
695(24, 'G35', 'Multiple sclerosis'),
696(25, 'C50.9', 'Malignant neoplasm of breast'),
697(26, 'C18.9', 'Malignant neoplasm of colon'),
698(27, 'I21.9', 'Acute myocardial infarction, unspecified'),
699(28, 'I63.9', 'Cerebral infarction, unspecified'),
700(29, 'E11.65','Type 2 diabetes mellitus with hyperglycemia'),
701(30, 'M79.3', 'Panniculitis, unspecified'),
702(31, 'L40.0', 'Psoriasis vulgaris'),
703(32, 'K57.30','Diverticulosis of large intestine without perforation'),
704(33, 'Z00.00','Encounter for general adult medical examination'),
705(34, 'J20.9', 'Acute bronchitis, unspecified'),
706(35, 'R05', 'Cough'),
707(36, 'R51', 'Headache'),
708(37, 'R50.9', 'Fever, unspecified'),
709(38, 'N20.0', 'Calculus of kidney'),
710(39, 'K35.80','Acute appendicitis without abscess'),
711(40, 'H35.30','Unspecified macular degeneration');
712
713INSERT INTO Referral (
714 referral_id, patient_id, department_id,
715 referred_doctor_id, referring_doctor_id
716)
717SELECT
718 s.i,
719 (s.i % 2000000) + 1,
720 (s.i % 150) + 1,
721 CASE WHEN s.i % 3 != 0
722 THEN CASE
723 WHEN ((s.i % 2999) + 1) <> ((s.i % 3000) + 1)
724 THEN ((s.i % 2999) + 1)
725 ELSE ((s.i % 2999) + 2)
726 END
727 ELSE NULL
728 END,
729 (s.i % 3000) + 1
730FROM generate_series(1, 3000000) AS s(i);
731
732INSERT INTO Appointment (
733 appointment_id, referral_id, doctor_id, patient_id,
734 parent_appointment_id, prescription_value,
735 appointment_date, status, appointment_type, priority_level
736)
737SELECT
738 s.i,
739 CASE WHEN s.i % 3 = 0 THEN (s.i % 3000000) + 1 ELSE NULL END,
740 (s.i % 3000) + 1,
741 (s.i % 2000000) + 1,
742 CASE WHEN s.i % 5 = 0 AND s.i > 5
743 THEN ((s.i - (s.i % 5)) % (s.i - 1)) + 1
744 ELSE NULL
745 END,
746 (s.i % 2 = 0),
747 (CURRENT_DATE - ((s.i % 1825) * INTERVAL '1 day'))::DATE,
748 CASE (s.i % 10)
749 WHEN 0 THEN 'SCHEDULED' WHEN 1 THEN 'COMPLETED'
750 WHEN 2 THEN 'COMPLETED' WHEN 3 THEN 'COMPLETED'
751 WHEN 4 THEN 'COMPLETED' WHEN 5 THEN 'COMPLETED'
752 WHEN 6 THEN 'COMPLETED' WHEN 7 THEN 'CANCELLED'
753 WHEN 8 THEN 'NO_SHOW' ELSE 'IN_PROGRESS'
754 END,
755 CASE (s.i % 4)
756 WHEN 0 THEN 'REGULAR' WHEN 1 THEN 'FOLLOW_UP'
757 WHEN 2 THEN 'EMERGENCY' ELSE 'CONTROL'
758 END,
759 CASE (s.i % 4)
760 WHEN 0 THEN 'LOW' WHEN 1 THEN 'MEDIUM'
761 WHEN 2 THEN 'HIGH' ELSE 'URGENT'
762 END
763FROM generate_series(1, 15000000) AS s(i);
764
765INSERT INTO Medical_examination (
766 exam_id, exam_date, notes, doctor_id, appointment_id
767)
768SELECT
769 ROW_NUMBER() OVER ()::INT,
770 appointment_date,
771 CASE (appointment_id % 5)
772 WHEN 0 THEN 'Patient presents with reported symptoms. Examination conducted.'
773 WHEN 1 THEN 'Follow-up examination. Patient condition stable.'
774 WHEN 2 THEN 'Routine checkup. No acute distress noted.'
775 WHEN 3 THEN 'Patient reports improvement since last visit. Vitals normal.'
776 ELSE 'Comprehensive examination performed. Further tests ordered.'
777 END,
778 doctor_id,
779 appointment_id
780FROM Appointment
781WHERE status = 'COMPLETED'
782LIMIT 5000000;
783
784INSERT INTO Patient_diagnosis (
785 patient_diagnosis_id, patient_id, doctor_id,
786 exam_id, icd_id, diagnosis_name,
787 is_primary, date_from, date_to
788)
789SELECT
790 ROW_NUMBER() OVER ()::INT,
791 a.patient_id,
792 me.doctor_id,
793 me.exam_id,
794 (me.exam_id % 200) + 1,
795 CASE (me.exam_id % 5)
796 WHEN 0 THEN 'Hypertension stage 1'
797 WHEN 1 THEN 'Type 2 Diabetes controlled'
798 WHEN 2 THEN 'Anxiety disorder mild'
799 WHEN 3 THEN 'Chronic back pain'
800 ELSE 'Seasonal allergic rhinitis'
801 END,
802 (me.exam_id % 2 = 0),
803 me.exam_date,
804 CASE WHEN me.exam_id % 3 = 0
805 THEN (me.exam_date + ((me.exam_id % 365 + 30) * INTERVAL '1 day'))::DATE
806 ELSE NULL
807 END
808FROM Medical_examination me
809JOIN Appointment a ON a.appointment_id = me.appointment_id
810CROSS JOIN generate_series(1, 2) AS g(n)
811LIMIT 8000000;
812
813INSERT INTO Prescription (
814 presc_id, atc_id, doctor_id, patient_id,
815 presc_date, duration, dosage,
816 inventory_id, patient_diagnosis_id
817)
818SELECT
819 ROW_NUMBER() OVER ()::INT,
820 (pd.icd_id % 80) + 1,
821 pd.doctor_id,
822 pd.patient_id,
823 pd.date_from,
824 (pd.patient_diagnosis_id % 90) + 7,
825 (pd.patient_diagnosis_id % 3) + 1,
826 (pd.patient_diagnosis_id % (SELECT COUNT(*) FROM Inventory)::INT) + 1,
827 pd.patient_diagnosis_id
828FROM Patient_diagnosis pd
829WHERE pd.is_primary = TRUE
830LIMIT 4000000;
831
832INSERT INTO Pharmacy_sale (
833 sale_id, patient_id, pharmacy_id,
834 presc_id, sale_date, total_amount
835)
836SELECT
837 ROW_NUMBER() OVER ()::INT,
838 pr.patient_id,
839 (pr.presc_id % 150) + 1,
840 pr.presc_id,
841 LEAST(
842 (pr.presc_date + (pr.presc_id % 7 * INTERVAL '1 day'))::DATE,
843 CURRENT_DATE
844 ),
845 round((d.unit_price * pr.dosage * 1.3)::NUMERIC, 2)
846FROM Prescription pr
847JOIN Inventory i ON i.inventory_id = pr.inventory_id
848JOIN Drug d ON d.product_id = i.product_id
849LIMIT 3500000;
850
851INSERT INTO Sale_item (sale_item_id, sale_id, presc_id, inventory_id, date)
852SELECT
853 ROW_NUMBER() OVER ()::INT,
854 ps.sale_id,
855 ps.presc_id,
856 (ps.sale_id % (SELECT COUNT(*) FROM Inventory)::INT) + 1,
857 ps.sale_date
858FROM Pharmacy_sale ps
859CROSS JOIN generate_series(1, 3) AS g(n)
860LIMIT 10500000;
861
862INSERT INTO Laboratory_test (
863 lab_id, exam_id, patient_id, doctor_id,
864 result, test_name, status
865)
866SELECT
867 ROW_NUMBER() OVER ()::INT,
868 me.exam_id,
869 a.patient_id,
870 me.doctor_id,
871 CASE (me.exam_id % 4)
872 WHEN 0 THEN 'Normal range'
873 WHEN 1 THEN 'Slightly elevated'
874 WHEN 2 THEN 'Below normal range'
875 ELSE 'Pending review'
876 END,
877 CASE (me.exam_id % 20)
878 WHEN 0 THEN 'Complete Blood Count'
879 WHEN 1 THEN 'Comprehensive Metabolic Panel'
880 WHEN 2 THEN 'Lipid Panel'
881 WHEN 3 THEN 'HbA1c'
882 WHEN 4 THEN 'Thyroid Function Test'
883 WHEN 5 THEN 'Urinalysis'
884 WHEN 6 THEN 'Blood Culture'
885 WHEN 7 THEN 'PT/INR'
886 WHEN 8 THEN 'ESR'
887 WHEN 9 THEN 'CRP'
888 WHEN 10 THEN 'Liver Function Test'
889 WHEN 11 THEN 'Renal Function Test'
890 WHEN 12 THEN 'Electrolytes'
891 WHEN 13 THEN 'Glucose Fasting'
892 WHEN 14 THEN 'HIV Antibody'
893 WHEN 15 THEN 'Hepatitis B Surface Antigen'
894 WHEN 16 THEN 'Chest X-Ray'
895 WHEN 17 THEN 'ECG'
896 WHEN 18 THEN 'MRI Brain'
897 ELSE 'CT Abdomen'
898 END,
899 CASE (me.exam_id % 4)
900 WHEN 0 THEN 'PENDING'
901 WHEN 1 THEN 'IN_PROGRESS'
902 ELSE 'COMPLETED'
903 END
904FROM Medical_examination me
905JOIN Appointment a ON a.appointment_id = me.appointment_id
906CROSS JOIN generate_series(1, 2) AS g(n)
907LIMIT 10000000;
908
909DROP TABLE IF EXISTS tmp_names;
910DROP TABLE IF EXISTS tmp_surnames;