Question: Task: In this exercise you will work from the previous exercises to provide a well designed data model to address the data needs of a

Task: In this exercise you will work from the previous exercises to provide a well designed data model to address the data needs of a clinic. You will need to provide SQL queries that are useful for staff at the clinic. You will also need to provide considerations about the DB design process and considerations about how the data will be secure. You will need to provide a final report with content organized, beginning with an introduction, content separated by section headings, and a conclusion. For this report we will use a similar context as we have been using, explained as follows:You need to develop a database (DB) for a dermatology clinic, where they see patients, and perform various services such as biopsies, laser skin treatments, skin injections and others. The DB will be part of a broader information system for the management of all activities within the clinic. The DB therefore should include all data about staff, patients and activities of the clinic, including scheduling of appointments, procedures, room management, and insurance claims. Other potential uses of the DB may be represented in the model. 1. IntroductionProvide the introduction to your report, the problem it is trying to address, what it presents and how it is organized.This section should be about 1 paragraph.2. Database lifecycleProvide a model for the database lifecycle activities-provide an actual imag(e)/(g)raphic of the steps that need to be taken to develop such database (see Ch.9 of textbook). This is largely standardized, however, make sure to add some explanations for each step that makes reference to your particular context. For example, during the initial study phase, instead of simply saying we will study the clinic mention a bit more specifically what process or who may be worth to be studied (e.g. we will interview staff about their data needs, and observe how a doctor takes notes of patients). Note any potential issues (e.g. as an outside consultant you may not be able to look at patient chart data.) In summary, this will be a standard lifecycle model as shown in the textbook. However, add a few specific notes for each stage about the specific context you are working in. Explain if you will take a bottom-up or two-down design approach to your data model and why.This section should be about 1 or 2 paragraphs + graphic. 3. Data modelProvide a data model for the database. This should be an ER diagram using crows foot notation, including minimum and maximum cardinality. Make sure you identify primary keys and foreign keys, and include the data type for each attribute in the ER diagram.Your model should have at least 8 entities with an average of 5 attributes per entity (some may have more and some less). Any many-to-many relationship needs to be broken down with an associative entity. There should be at least 3 relationships in the data model.Make sure to include a paragraph briefly describing the data model, the normalization checks performed, and any denormalization of the model if appropriate.This section should be about 1 brief paragraph + ER diagram. 4. SQLProvide 8 useful SQL Select queries that your DB will have pre-determined for the information system. Number each query, and each should have a brief explanation in English, including why the query is important and who it is useful for, then followed by the actual SQL syntax.In all, your queries should have the following components at least once (and potentially mixed across queries):WHERE condition with boolean operator (AND, OR, NOT)Calculation of one or more attributes (without using built-in function)Aggregate function to count, sum or average.GROUPBY with an aggregate functionORDER BYJOIN of at least two tables 5. Database securityDiscuss the main aspects of security policies and activities that need to be done in a regular fashion to make sure that the database is secure and used appropriately. For example, is some data accessible only by certain types of people but not others? Indicate if this is so and who (e.g. staff, doctors, patients) has access to what. Identify at least five security best practices that will be implemented in your system. Discuss advantages and disadvantages of using a cloud or in-house database for security purposes.This section should be about 1 paragraph. 6. ConclusionProvide a concluding paragraph that summarizes what was discussed in the report, and make a general assessment about the database development process.This section should be about 1 brief paragraph. ContributionsMake sure to identify the names of the individuals and who worked in each section. The same person cannot work on Section 3 and 4.For example: Lae Smitty worked on sections 1,2 and 4, and document preparation. Joe Smoe worked on sections 3,5 and 6. Submission: Put all content well organized into a single PDF. Only a single PDF file will be accepted as your submission. Make sure the repor

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!