Project management Healthcare sytems

Tasks

1. Create a normalized data design for the UWG system using standard notation format. Normalize your designs for all tables to ensure they are 3NF, and verify that all primary, secondary, and foreign keys are identified properly. Show your work from 0 Normal Formal to 3rd Normal Form. Example tables include: Insurance Company, Provider, CPT Code & Fee, Patient, Household, Payment, Claim, Appointment Service, Charge. Note: there will be additional tables once you get rid of any M:N relationships. For additional review on how to normalize a database, please review this video: Normalization.

2. Create an initial ERD based on your standard notation for the new system that contains at least eight entities. Be sure to identify if the relationship is 1:1, 1:M, or M:N. All M:N relationships should be removed.

Format

Task 1)

Remember standard notation starts with the name of the table, followed by and parentheses that contains the field names separated by a comma. The primary key field is underlined. See the example below.

First Normal Format

Patient (Patient_ID, Household_ID, First Name, Last Name, Birthdate, Phone Number, Relationship to household)

Insurance Company (Insurance Company_ID, Insurance Name, Insurance address, Insurance Phone, Insurance, Charges, Payments)

Provider (Provider_ID, First Name, Last Name)

CPT Code & Fee (CPT_Code, Patient_ID, Description, Fee)

Household (Household_ID, Patient_ID, First Name, Last Name, Home Phone, Employer Name, Employer Address, Insurance Company Name, Balance)

Payment (Payment_ID, Household_ID, Date, Amount, Balance, Source)

Claim (Claim_ID, Insurance Company ID, Claim Amount, Claim Date

Appointment Service (Appontment_ID, Provider_ID, Patient_ID, CPT_Code Date, Time, Reason)

Charge (Charge_Number,Patient_ID, Date, Services, Charges, Balance)

Task 2