Reference no: EM133561584

Data Modelling and Database Design

Assessment - Database Implementation

Learning Outcome 1: Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders.

Task Summary
In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements.

Context
Being able to query a database is a fundamental skill that is required by all information systems professionals who work with relational databases. In this assessment, you will utilize the basic query skills that are typically used to extract information for analysis, reporting and data cleansing in a data management setting. Timely provisioning of key business information promotes effective communication and enhanced solution delivery.

Task Instructions
Please read and examine carefully the attached MIS602_Assessment 2_ Case study and then derive the SQL queries to return the required information. Your focus should be providing the output as meaningful and presentable possible. Please note, extra marks will be awarded for presentation and readability of SQL queries including ordering of the columns

Please note all the SQL queries should be written using MySQL server either using MySQL workbench or MySQL Command Line Client.

Question

1 How many patients are there in the patient table?

2 Write a query to determine if there are more males than females in the patient table.

3 Retrieve the names of patients who have 'Lee' in their name and 'follow-up' in the appointment notes.

4 Retrieve the medications that have been prescribed for patients whose names ending with 'Smith'.

5 List the doctors who have not been assigned any appointments using a subquery.

6 Retrieve the doctors who have prescribed medications containing 'pain' in their description more than 2 times.

7 Write a query to find patients who have appointments scheduled in the month of June 2023.

8 Write a query to find the doctor with the highest number of appointments scheduled in a single day

9 List the total number of appointments based on the day of the week

10 Display the patients if they are sharing the same birth month as another patient

11 How many patients have never had an appointment or scheduled any appointments so far?

12 Show medications that have never been prescribed to by any doctor using a JOIN of your choice.

13 List the doctors who have treated at least one patient from ‘WA'

14 Which medicine has the second-highest overall prescription rate?

15 Retrieve the patients who have never cancelled an appointment.

16 Retrieve the youngest and oddest female patient.

17 Find medications that have appeared only once in any prescription.

18 Find medications that have appeared only once in any prescription along with the doctors who prescribed them.

19 Retrieve the patients who have had appointments with at least three different doctors

20 In not more than 200 words, comment on whether the tables are in 3NF. Justify your argument with relevant examples, and then explain at least two ways to improve this database based on

Referencing
It is essential that you use appropriate APA style for citing and referencing research.

Attachment:- Case_Study.rar