Solved by verified expert:The instructions are on the word document. This one will take some time so I’ve given a full day if more time is needed please ask! Follow the instrucyions and complete theassignment. Let me know if you have any questions. Thank you again!pinkbike42
drs_office_fall_2017.docx
Unformatted Attachment Preview
Mid Term Doctor Database
NAME:_______________
Project Scenario:
You are a small database consulting company specializing in developing databases
for the medical industry. You have just been awarded the contract to develop an
ERD for a small doctor’s office with 3 doctors, to keep track of health claims
including patient information, provider(doctor) information, information about patient
visits to their doctor, and insurance information.
Information such as patient name, address, phone, email etc. are needed as well as
who each patient’s primary care doctor is (but patients may see any doctor available
if they need to get in right away), their insurance ID number and insurance company
name. We also want information on each doctor such as their specialty.
Well visits must be scheduled at least 365 days after the last well visit for the
insurance to pay for it. We also need to track which doctor the patient saw at their
visit. Eventually, the database will be used to track trends and for some
extrapolative modeling based on the accumulated data.
The database will be accessible in English only right now, although plans include
making it available in multiple languages eventually.
Patients have complained about long wait times, so now they must sign in on a sign
in sheet when they come in to visit to track the times they end up waiting so we can
improve our service.
We also need to track weather information for each day to see if it helps us predict
cancelations.
You will print and turn in the following (your work should look neat and
professional):
1. Completed ERD Diagram showing relationships and naming those
relationships.
2. A sheet listing all of the ERD sentences for your ERD.
3. A sheet listing all of the Procedural (programmatic) business rules.
4. Your completed Physical Charts.
5. A reference sheet showing the research that was done.
Follow the steps below to create your ERD diagram.
CIS 1513
Fall 2017
1
Step 1: Determining entities, attributes, UIDs (Sections 2 & 3)
Based on the business scenario stated above you will identify the database needs, and then
create a conceptual data model to support these needs.
1. Review the types of information a medical database may contain and research medical
information to better understand your topic. Read articles and check the internet to understand
the challenges of tracking this information.
Include with your final project a reference sheet that shows the website links that you
used as reference.
2. Build a list of procedural (programmatic) business rules and assumptions based on your
scenario. Minimum of 3 for credit.
3. Develop a list of potential entities including their attributes, the attribute’s optionality as
well as a possible UID for each entity.
4. Create a preliminary Entity Relationship Diagram (ERD) that meets these needs and
objectives. Note: be sure to create the relationship lines and state your relationships
Step 2: Supertypes and subtypes
(Section 4)
As stated in the scenario we need to track the visits a patient makes to their doctor. Some patient
visits are related to a new issue/illness, some are follow up visits to an existing diagnosis and some
visits are routine “well patient” visits or checkups. We would like to be able to track which type of visit
each instance is so we can keep specific information regarding the visit. For example:
1. For a new issue/illness visit we will store an initial diagnosis
2. For follow-up visits we need to keep track of the patient’s status regarding the diagnosis
3. For routine checkups we need to track patient vital information such as current blood
pressure, height and weight and if any condition is discovered a diagnosis will be noted.
Modify the ERD using a supertype/subtype structure within the Office Visit.
Step 3: Intersection Entities
Any many-to-many relationships will need to be resolved.
Modify the ERD to include the relationships.
CIS 1513
Fall 2017
2
Step 4: Normalization
(Section 6)
Ensure that each entity has been normalized to third normal form – this means:
1. 1st normal form states that all attributes have a single value – no
multivalued attributes. For example: each patient can only
have one primary doctor, each doctor can only have one
specialty etc.
2. 2nd normal form says that all attributes must be dependent on the
entire key of the entity.
3. 3rd normal form states that no non-UID attribute can be dependent
on another non-UID attribute. For example: A patient’s insurance
ID number will determine what insurance company they are
insured with. The ID number determines the insurance company’s
name.
Modify the ERD to incorporate all 3 stages of normalization.
Step 5: Relationships (Section 3)
While creating your entities you should have been thinking about what relationships
the entities would have with each other. Create any additional relationships between
your entities including the relationship’s optionality and cardinality from steps 2, 3,
and 4.
Write out the ERD sentences for each of the relationships in your ERD in a separate
word document.
Step 6: Recursive Relationships
(Section 7)
Some patients in the patient entity may be part of the same family and be covered by
the same insurance – we would like to designate a field in the patient entity showing
who is the insurance holder for each patient – this field would be the patient ID
number of the person holding the insurance for the family.
Modify the ERD to include a recursive relationship on the Patient entity showing the
insurance owners role.
CIS 1513
Fall 2017
3
4
Step 7: Basic Mapping (ERD to Physical Charts)
Transform the following HealthOne database entities into table diagrams – use
suitable naming conventions. Transform relationships into foreign-key columns.
Transform the Office Visit supertype entity using the single-table implementation.
Using the following table diagrams, include as many rows as necessary:
Key Type (pk,fk,uk)
Optionality (“*” or “o”)
Column Name
PATIENTS
DOCTORS
OFFICE VISITS
Step 8: Final ERD Review
You should complete a final review of the requirements and show a final, complete
ERD below.
CIS 1513
Fall 2017
4
…
Purchase answer to see full
attachment
