Part 1: No complicated instructions for this one….we’re going to work with some of the problems out of the book with modifications to the instructions. Be sure and submit what is asked for….if you copy answers from some other site and provide different deliverables, you might find yourself on the wrong side of an academic dishonesty issue. Do your work! For the tables in questions 5,6, and 7 in the problems section on pp.236-239, do the following:
Write the relational schema for the table.
Identify all partial and transitive dependencies. You do not need to create a dependency diagram. You can just list the dependencies and their type. For example, age has a transitive dependency on birthdate. Use the assumptions written into each question.
Remove all dependencies and get the table into 3NF.
Draw the Crow’s Foot ERD for your normalized table(s).
Part 2: We’ve come full circle. I want you to revisit assignment 1 and practice your SQL skills. I’ve started you off with some easier queries and then added some of the more difficult queries that you worked with at the very beginning of the semester. You can just write the SQL statements in your Word doc from the first part of the assignment.
1. Write the SQL to list all patients and their address, city, state, and zip code.
2. Write the SQL to list all possible diagnoses, sorted alphabetically.
3. Write the SQL to list all female patients in the database.
4. Write the SQL to list all of the patients that live in Pennsylvania or Washington.
5. Write the SQL to list all patients that visited between January 1, 2021 and March 31, 2021.
6. Write the SQL to list all patients that were diagnosed with allergies.
7. Write the SQL to determine what day did a patient visit that had a heart attack.
8. Write the SQL to determine how many times Veronica Mars visited the doctor.
9. Write the SQL to list which patients visited the doctor more than two times.
10. Write the SQL to list all female patients that were diagnosed with anxiety.
(Assignment 1 :Excel files Download Excel files)
Submit your work by the assignment due date in Canvas.
Rubric
Lab 5
Lab 5
Criteria Ratings Pts
This criterion is linked to a Learning Outcome1st Question Relational Schema
4 pts
This criterion is linked to a Learning Outcome1st Question Dependencies Discussion
12 pts
This criterion is linked to a Learning Outcome1st Question ERD
8 pts
This criterion is linked to a Learning Outcome2nd Question Relational Schema
4 pts
This criterion is linked to a Learning Outcome2nd Question Dependencies Discussion
12 pts
This criterion is linked to a Learning Outcome2nd Question ERD
8 pts
This criterion is linked to a Learning Outcome3rd Question Relational Schema
4 pts
This criterion is linked to a Learning Outcome3rd Question Dependencies Discussion
12 pts
This criterion is linked to a Learning Outcome3rd Question ERD
8 pts
This criterion is linked to a Learning OutcomeSQL 1
3 pts
This criterion is linked to a Learning OutcomeSQL 2
3 pts
This criterion is linked to a Learning OutcomeSQL 3
3 pts
This criterion is linked to a Learning OutcomeSQL 4
3 pts
This criterion is linked to a Learning OutcomeSQL 5
3 pts
This criterion is linked to a Learning OutcomeSQL 6
3 pts
This criterion is linked to a Learning OutcomeSQL 7
3 pts
This criterion is linked to a Learning OutcomeSQL 8
3 pts
This criterion is linked to a Learning OutcomeSQL 9
3 pts
This criterion is linked to a Learning OutcomeSQL 10
3 pts
Total Points: 102
