Database Schema-SQL Statements
Programming
|
Please see attached to use as a resource to create SQL queries and see attached for Topic 2 assignment to use to complete this assignment. Thanks. Topic 2 assignment Database Schema Instructor feedback Instructors Comments: Stephanie, outstanding first iteration and well defined relationships by PatientID, PhysID, and PharmID! Add date to Diagnosis. Patient vitals, progress, and summary would typically be stored in an encounter table since multiple records exist per patient and recording history is mandatory. You can move them to an encounter table or just remove them from Patient. Your data dictionary has outstanding definitions and examples but include data type and field length for every attribute. Exemplary work Stephanie! Please include data type and field length for every attribute. Database Schema-SQL Statements |
For this assignment you will create SQL queries use the following database schema:
- Patient—PatientID, Name, DOB
- Doctor—DoctorID, Name, MedLicenseNumber, Phone
- Medication—MedicationID, BrandName, GenericName
- Prescription—PrescriptionID, Date, PatientID, PrescriberID, MedicationID
Specify the SQL queries to retrieve:
- A list of all doctors.
- The number of patients who were born before 01/01/1994.
- The patient information along with the ID of any prescribing doctor for each patient.
- The prescription information along with patient name, DOB, medication brand name, and prescribing doctor name sorted by most recent date.
- The most prescribed generic medication name.
APA style is not required, but solid academic writing is expected.
Based on the feedback received on your Topic 2 assignment Database Schema, provide SQL statements to create the (revised) database and populate it with sample data (at least four rows per table). Please include data type and field length for every attribute.
Modify the database by adding an additional column (“Deleted_Date”) to those tables that represent entities that could contain data which can be deleted and justify your rationale in a short paragraph. In addition, explain what the tradeoffs are in using a marker-column for deletion, as opposed to actually deleting a row from a table?
This assignment uses a rubric. Please see attached. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Center.
