HEALTHCARE DATABASE MANAGEMENT

SQL 2012 Database Assignment: Must be done in VISIO Database

Read the Medical License Management scenario and Use the information in the DIAGRAM to answer the four following statements below:

Medical License Management: The Florida Division of Medical Quality Assurance, a part of the Florida Department of Health, administers licensing for several medical professions including physicians, paramedics, registered nurses, massage therapists, etc. Initial applicants must provide different quantities and documentation of qualifications including passing licensing tests for most professions. While the qualifications for each field differ qualifications can be categorized with categories including, but not limited to, educational requirements, exams, letters of recommendation, documentation of experience, etc. Individuals apply for initial licensing or for renewal licensing. Individual licenses have differing levels of continuing education (CE) requirements and the requirements vary not only by type of license but sometimes by the time elapsed since the initial licensing. The Division maintains a list of authorized CE providers and the course-profession combinations the CE providers offer. The providers directly enter information on Florida professionals that successfully complete CE courses including the topic and hours of credit. Except for the CE credit, each element of both the initial application and renewal applications is verified by a DMQA staff member and when all requirements have been verified the license (whether initial or renewal) is issued. Do not consider any fee information in your modeling nor do you need to consider criminal background checks.

Use the information in the DIAGRAM to answer the four following statements below:

Page 1Transactions and Reports:

Frequent Transactions: List the ten most frequent business events (use cases) that you feel your organization will perform on a recurring basis ranked from most frequently to least frequently performed. Just state the event, such as "Make sales to customers," "Record receipt of replenishment inventory," "Record employee time card entries," etc. Use a numbered list format with double spacing. Don't agonize over the exact sequencing of event frequencies but there should definitely be a clear winner for first place. Don't attempt to explain anything about the use case or its rank ordering in your list.

Reports: On the same page as the transactions above list the most frequent report you think your organization will need to develop as well as the most important from a managerial information value report your organization will need to generate. Reports are any output from the system designed to be printed. A customer invoice or monthly statement is a report as are a wide range of managerial outputs such as, "Sales by SalesRep for Previous Month". Be sure to label which report is the most frequent and which is the most important.

Page 2Most Frequent Transaction Analysis:

SQL: Restate the most frequent business event (use case) and write all of the SQL needed to accomplish this transaction. A single event such as making sales to customers may require several distinct SQL statements. (Do not write stored procedures or triggers--but if SQL logic would be embedded in a trigger do include that SQL. Your SQL must be typed in Courier New 10 pt font. I will be incredibly upset and impose all of the penalties I can justify if you fail to use this font. Be sure to indent parts of your query such as continuation lines for longer lines, parts of the FROM statement. See my examples throughout the modules for indenting examples. Historically I have had groups fail to be complete in their analysis of the SQL needed to accomplish a particular use case. This analysis needs to think back to different techniques used in systems analysis where we timelined user, interface, and system actions. In particular, you need to remember choices presented to the user which are drawn from the system. This step is insidious in its work load in that you essentially need to think through the entire SAD process for this task. NorthWind Example: The sample of this process using the NorthWind database is shown below. I developed this sample for an undergraduate management information systems database course where students not only did the design steps you are doing but coded a working program that manipulated their database. The example is for the use case "Sell products to customers" and includes the user interface which you will not do (but should think about). Notice that this single use case requires seven distinct SQL statements. Some of them only presented choices to users in lists while others were more complicated and updated data.

Indices: With respect to this transaction only state which indices would improve the transaction's performance. State each index as follows: TABLENAME (IndexColumnList) Order (ASC or DESC) Cluster Status ("Clustered" or "NonClustered") e.g.

PRODUCTS ProductID ASC Clustered ProductName ASC NonClustered ORDER DETAILS OrderID, ProductID ASC Clustered

Use Word's table capabilities to format the submission into columns. Be sure to leave a blank line between information for different tables. Remember, you are not physically implementing your indices.

Page 3Clustered Indices:

For each table in your DB, state the table name and whether you, a) Left the PK as a clustered index ("PK Clustered"), b) had no clustered index in the table ("No Clustered Index"), or c) Made another non-PK clustered index ("Other Clustered Index"). For each table, briefly justify your decision. These justifications need not be extensive. An example for a clustered PK might be, "Records are added in PK sequence and often retrieved with a sequential range of records retrieved together." An example for a table with no clustered index might be, "Records are not added in PK order requiring extensive insertions in random orders. A clustered index on the PK would cause extensive table reshuffling and records are not often retrieved by the PK sequence." (I will be looking at your Top Ten list of frequent transactions when reviewing this submission.)

Here are two examples from the Northwind database.

Products: Clustered. PK values are added sequentially so there is no harm in the clustered index. There is no particular retrieval advantage for this index being clustered.

Order Details: Clustered. PK values are added roughly sequentially according to OrderID and roughly at the end of the table minimizing insertion turbulence. Records are frequently retrieved by OrderID so having records physically together is advantageous. OrderID is also chronological and records are also frequently retrieved by chronological ranges so having records together will be an advantage in this circumstance.

Be sure that there is a blank line between each table's entry and list tables in alphabetical order.

Page 4 (et seq.)Other Indices:

List each table and each index in that table using the same format specified for the SQL indices above. Note that you will include the indices you already included for the SQL question in this list as well as many others. Do indicate whether the indices are clustered or not.

Don't allow the index lists for a single table to span multiple pages. Force a page break to keep all information for a single table on the same page.

Place your tables in alphabetical order.

File 2: Data Model (Submitted as a Visio document)

NorthWind

NorthWind Example: This is the sample of this process using the NorthWind database is shown below.

Professional CE Provide FEN stName stName Provider LastName LastName submit Gende starte Date Address Main Ph ane Numbe Application Enrollment quellifies lment SSN Enrollment Status ioense Type ID Code Renevu License ID Nur Decision Date SectionID Term Qual SSN ApplicationID License Ty Qual Name Section Section structor Provider ID CECourselD Section Number License Degree Type SSN Term School sue Date Null Room icenseTypeID Graduation Date Expiration Date Decision Date Approve dByID ExpirationDate ApplicationID Document Title QualificationType Submission Date Review ByID ApplicantFirst Name Qual cationName ApplicentLast Name DocumentDescription Received From Received Date QualificationID Docume nt Status DMQAStaff DocumentStatusID Received Date Staff ID Not eviewedD Staff FirstName ApprovedDate ines StaffLast Name DisapprovedDate rtmentID pal License ID Renew allD Hire Date Position ID Staff need to relate to LoginID document or qua cation because they approve. One staff member per document. *Determines status Received, ApprovedDate, DisapprovedDate. If the last two are null, then it has been received nstructorEmployment FEI Employment Division cto Instructor1 SSN Instruct arStartDate CE Course QualificationTypeID ourse Name ourse Hou Doc and Qualification License Type Qual Qual nName particular type of license. For that license type, here are the things you have to do. Professional CE Provide FEN stName stName Provider LastName LastName submit Gende starte Date Address Main Ph ane Numbe Application Enrollment quellifies lment SSN Enrollment Status ioense Type ID Code Renevu License ID Nur Decision Date SectionID Term Qual SSN ApplicationID License Ty Qual Name Section Section structor Provider ID CECourselD Section Number License Degree Type SSN Term School sue Date Null Room icenseTypeID Graduation Date Expiration Date Decision Date Approve dByID ExpirationDate ApplicationID Document Title QualificationType Submission Date Review ByID ApplicantFirst Name Qual cationName ApplicentLast Name DocumentDescription Received From Received Date QualificationID Docume nt Status DMQAStaff DocumentStatusID Received Date Staff ID Not eviewedD Staff FirstName ApprovedDate ines StaffLast Name DisapprovedDate rtmentID pal License ID Renew allD Hire Date Position ID Staff need to relate to LoginID document or qua cation because they approve. One staff member per document. *Determines status Received, ApprovedDate, DisapprovedDate. If the last two are null, then it has been received nstructorEmployment FEI Employment Division cto Instructor1 SSN Instruct arStartDate CE Course QualificationTypeID ourse Name ourse Hou Doc and Qualification License Type Qual Qual nName particular type of license. For that license type, here are the things you have to do