ERD Instructions: The ERD should not have any many-to-many (M:N) relationships. All attributes should be placed within
Question:
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 maxi
mum 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.
Expert Answer:
Diagram ERD The above ERDEntity Relationship Diagram consists of primary keys and foreign keys Primary key is a key which uniquely identifies a row in ... View the full answer
Management Fundamentals Concepts Applications & Skill Development
ISBN: 978-1483352268
6th edition
Authors: Robert N. Lussier
