ERD Instructions:

• The ERD should not have any many-to-many (M:N) relationships.
• All attributes should be placed within an entity.
• Each entity must have a primary key defined. A primary key can consist of one or more attributes.
• Each relationship must have a foreign key. Denote the foreign key(s) with the notation (FK) on the ERD.
• Each relationship must include both a maximum and minimum cardinality for both sides of the relationship.
• Each relationship should have a verb or verb phrase to describe it.
• You do not have to differentiate between an identifying or non-identifying relationship.

The purpose of this database is to store summarized data about hospital visits in order to do data analysis for a hospital. Although the charges and reimbursement data are accurate, all the rest of the data is made-up to protect the privacy of patients. Both the patient and doctor’s names in this dataset are created; they are not real. The sample data is provided for you in an Excel workbook called Hospital Visit Data.

Each visit to the hospital has its own primary key (HospitalStayID). A patient may visit the hospital more than once. Each hospital visit by a patient will have the following data: patientID, total charges, total charges reimbursed, and the length of stay in days. A hospital stay is related to one and only one patient. For each patient, we store the patientID (which is a unique value for each patient), and the patient’s first and last names.

Each hospital stay may be related to zero or many procedures. A given procedure may be related to many different hospital stays. For each procedure, the hospital keeps track of a standard procedure code (primary key) and a standard longdescription. A procedure is identified by a procedure code. These procedure codes are standardized by the healthcare industry and are known as CPT codes. These codes are the basis for Medicare billing and standardize the billing for services performed on Medicare patients across the United States.

During a hospital visit, a patient may or may not have procedures done on them by a doctor. Data stored about a doctor includes a unique doctorID, first name, and last name. A procedure for a given hospital stay is performed by one and only one doctor.

Sample data for this database is available in the HospitalVisitData Excel workbook. Please note that there is significant redundant data in the worksheet because there is not one row per hospital stay, there is one row in this worksheet per procedure per hospital stay.

After drawing the ERD for this application scenario, create each table required to implement the database in SQL Server. Copy the CREATE TABLE statements you write to the Word document you intend to submit for grading for this assignment.

Insert two rows of data from the HospitalVisitData Excel worksheet into each of the tables you create in SQL server. I do not recommend that you use the SSMS Import/Export utility to accomplish this task. I recommend that you use the SSMS designer utilities to insert the data—that would be the quickest way to do it.

hospitalstayid patientid 047WTB5YYG OGITCWK21D 047WTB5YYG OGITCWK21D 047WTB5YYG OGITCWK21D 047WTB5YYG OGITCWK21D 047WTB5YYG OGITCWK21D 047WTB5YYG OGITCWK21D totalcharges reimbursement lengthofstay PatientlastName PatientFirstName dateofbirth patientprocedureid procedurecode longdescription doctorid DoctorlastName Doctorfirstname payerid PayerName 62922.75 19616.85 3 Maynes Mary 8/8/1957 0:00 87 0.4 Procedure on single vessel 10603 Phillips Anthony 12 UNITED HEALTHCARE 62922.75 19616.85 3 Maynes Mary 8/8/1957 0:0o 85 39.5 Angioplasty of other non-coronary vessel(s) 10603 Phillips Anthony 12 UNITED HEALTHCARE 62922.75 19616.85 3 Maynes Mary 8/8/1957 0:0o 91 39.95 Hemodialysis 81885 Cox James 12 UNITED HEALTHCARE 62922.75 19616.85 3 Maynes Mary 8/8/1957 0:00 89 86.59 Closure of skin and subcutaneous tissue of other sites 10603 Phillips Anthony 12 UNITED HEALTHCARE 62922.75 19616.85 3 Maynes Mary 8/8/1957 0:00 88 88.49 Arteriography of other specified sites 10603 Phillips Anthony 12 UNITED HEALTHCARE 62922.75 19616.85 3 Maynes Mary 8/8/1957 0:00 90 99.04 Transfusion of packed cells 80143 Allen Kenneth 12 UNITED HEALTHCARE 047WTBSYYG OGITCWK21D 62922.75 3 Maynes 8/8/1957 0:00 99.1 Injection or infusion of thrombolytic agent 10603 Phillips Anthony 12 UNITED HEALTHCARE 19616.85 Mary 86 06IMO6CRRX L6IVCEFF8T 163056.5 20009 23 Burkholder Mark 2/13/1932 0:00 125 38.93 Venous catheterization, not elsewhere classified 25684 Miller Betty 5 WORKERS COMP 06IMO6CRRX L6IVCEFF8T 06IMO6CRRX L6IVCEFF8T 163056.5 20009 23 Burkholder Mark 2/13/1932 0:00 126 39.95 Hemodialysis 63313 Baysinger Ross 5 WORKERS COMP Mark 2/13/1932 0:00 2/13/1932 0:00 163056.5 20009 23 Burkholder 128 96.04 Insertion of endotracheal tube 65318 Bird Stacie 5 WORKERS COMP 06IMO6CRRX L6IVCEFF8T 163056.5 20009 23 Burkholder Mark 127 96.71 Continuous invasive mechanical ventilation for less than 96 consecutive hours 65318 Bird Stacie 5 WORKERS COMP 06IMO6CRRXx L6IVCEFF8T 163056.5 20009 23 Burkholder Mark 2/13/1932 0:00 124 99.04 Transfusion of packed cells 25684 Miller Betty 5 WORKERS COMP 8 OTHER 8 OTHER 08QUL4B7EC W9KMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 164 37.72 Initial insertion of transvenous leads [electrodes] into atrium and ventricle 18903 Webb Walter 08QUL4B7EC W9KMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 166 37.78 Insertion of temporary transvenous pacemaker system 27821 Abramson Christopher 08QUL4B7EC WYKMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 165 37.83 Initial insertion of dual-chamber device 18903 Webb Walter 8 OTHER 08QUL4B7EC W9KMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 169 38.93 Venous catheterization, not elsewhere classified 51714 Jacobs James 8 OTHER 8 OTHER 8 OTHER 08QUL4B7EC W9KMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 170 43.11 Percutaneous [endoscopic] gastrostomy [PEG] 74260 Gore Kathy 08QUL4B7EC W9KMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 168 96.04 Insertion of endotracheal tube 51714 Jacobs James 08QUL4B7EC W9KMK9IXL7 279940.75 45692.05 30 Hubbard Daniel 4/14/1984 0:00 167 96.72 Continuous invasive mechanical ventilation for 96 consecutive hours or more 51714 Jacobs James 8 OTHER 09ARQ7TRCD DBR208T32M 40201.75 800245 Hughes 6 Holt O Williams 10637 Patti 4/26/1969 0:00 177 39.95 Hemodialysis Linwood 4 MEDICARE OA2HE3LB6G XMVIL7DFXJ 29571.5 7792.61 Florence 6/19/1980 0:00 205 0.17 Infusion of vasopressor agent 55202 Abney Ronnie 4 MEDICARE O Williams O Williams O Williams O Williams OA2HE3LB6G XMVIL7DFXJ 29571.5 7792.61 Florence 6/19/1980 0:00 203 33.22 Fiber-optic bronchoscopy 13946 Barnett George 4 MEDICARE OA2HE3LB6G XMVIL7DFXJ 29571.5 7792.61 Florence 6/19/1980 0:00 201 96.04 Insertion of endotracheal tube 13946 Barnett George 4 MEDICARE OA2HE3LB6G XMVIL7DFXJ 29571.5 7792.61 Florence 6/19/1980 0:00 202 96.05 Other intubation of respiratory tract 13946 Barnett George 4 MEDICARE OA2HE3LB6G XMVIL7DFXJ 29571.5 7792.61 Florence 6/19/1980 0:00 204 99.6 Cardiopulmonary resuscitation, not otherwise specified 55202 Abney Ronnie 4 MEDICARE OA7UGVNOQ9 5XV9EA7FY9 47382.5 5683 4 Soliz Jeremy 5/22/1991 0:00 214 43.42 Local excision of other lesion or tissue of stomach 42994 Anderson Dominick 7 SELF PAY OA7UGVNOQ9 5XV9EA7FY9 47382.5 5683 4 Soliz Jeremy 5/22/1991 0:00 213 51.23 Laparoscopic cholecystectomy 42994 Anderson Dominick 7 SELF PAY