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.

1.) Transactions 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.

2.) Most Frequent Transaction Analysis:

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.

3.) Clustered 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.

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.

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.

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.

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.

Professional CE Provider Gende Provider Name Address Starte Date Main Phone Number submits Enrollment Application Enrollment ID ualifi Application ID Code SSN Numbe obtains Qualification License Type ID ction ID Renew License ID rm cation ID Decision Date ua ApplicationID has requires Qua Type License Section cense DocumentID Name Document ction Title CECourse ID Submission Date Section Number has requires ReviewID Term ApplicantFirst Name Room Number ApplicantLast Name Document Description License License ID Qual cationType SSN License Type ID cation eID ua Decision Date Qua cation Name Expiration Date DMQAStaff Staff D DepartmentID License ID Renew alID Hire Date Position ID LoginID cation ua cation ua License Type Degree Type Issue Date School Expiration Date Graduation Date InstructorEmployment oyment ID FEIN Employment Division has has nstructorID SSN nstructorStartDate has Lecturer nstructo struct o ProfessorType Lecturer Type enure Date CE Course CECourse ID FEIN Qual Course Number Course Name Course Hour Professional CE Provider Gende Provider Name Address Starte Date Main Phone Number submits Enrollment Application Enrollment ID ualifi Application ID Code SSN Numbe obtains Qualification License Type ID ction ID Renew License ID rm cation ID Decision Date ua ApplicationID has requires Qua Type License Section cense DocumentID Name Document ction Title CECourse ID Submission Date Section Number has requires ReviewID Term ApplicantFirst Name Room Number ApplicantLast Name Document Description License License ID Qual cationType SSN License Type ID cation eID ua Decision Date Qua cation Name Expiration Date DMQAStaff Staff D DepartmentID License ID Renew alID Hire Date Position ID LoginID cation ua cation ua License Type Degree Type Issue Date School Expiration Date Graduation Date InstructorEmployment oyment ID FEIN Employment Division has has nstructorID SSN nstructorStartDate has Lecturer nstructo struct o ProfessorType Lecturer Type enure Date CE Course CECourse ID FEIN Qual Course Number Course Name Course Hour