Case Description
Up to this point, you have developed the conceptual and logical models for Mountain View Community Hospital’s database. After considering several options, the hospital has decided to use Microsoft SQL Server, a relational DBMS, for implementing the database. Before the functional database is actually created, it is necessary to specify its physical design to ensure that the database is effective and efficient. As you have learned, physical database design is specific to the target environment and must conform to the capabilities of the DBMS to be used. It requires a good understanding of the DBMS’s features, such as available data types, indexing, support for referential integrity and other constraints, and many more. (You can alternatively assume that MVCH chose another DBMS with which you are familiar and then answer the following questions accordingly.)
Case Questions
Consider the following query against the MVCH database: For each treatment ordered in the past two weeks, list by treatment ID and date (in reverse chronological order) the number of times a physician performed that treatment that day, sorted alphabetically by physician name.
a. Which secondary key indexes would you suggest to optimize the performance of this query? Why? Make any assumptions you need in order to answer this question.
b. Following the examples in this chapter, write the SQL statements that create these secondary key indexes.
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.