What additional kinds of information do you need for the physical database design besides the 3 NF relations developed earlier on?
Apart from the 3 NF relations, the following sets of additional information are required:
i. The definition of each attribute used in the database design
ii. Descriptions of the places where data are used and how often the different forms of data manipulation are conducted.
iii. The expectations and the requirements for data security to ensure retention and data integrity.
iv. A detailed description of the technologies which are used to implement the data.
What different types or forms of clinical data that are collected at a hospital such as MVCH? Can you identify data that may not be easily accommodated by the standard data types provided by a DBMS? How would you handle that?
There are different types of clinical data that can be collected in a hospital such as MVCH. They include: patients name, patients contacts, Nature of illness, drugs administered, composition of the drugs used, Doctor’s name, duration expected for the patient to take to the hospital and previous forms of medication administered to the patient. There are some sets of data that cannot be easily accommodated by the standard data types which are provided by DBMS. They include the composition of the drugs administered. Since the composition of the drugs being administered cannot be easily accommodated in the in the standard data types provided by DBMS, we can introduce the use of a Null data type. This will take care of all the data types which do not fall under integers and characters.
Are there opportunities for horizontal or vertical partitioning of this database? If you are not sure, what other information would you need in order to answer this question with a lot of certainty? There are opportunities for vertical partitioning whereby information about the doctors and the patients are separated from each other and then connected through the use of relationships. In this case, vertical partitioning will enable the separation of data hence making the process of information manipulation easier.
Do you see an opportunity for using a bitmap index or join index for this database? Why or why not?
There are opportunities for using the bitmap index. The bitmap index has been found to be very useful especially when dealing with information which has distinct characteristics. It is very useful when dealing with information that needs grouping of patients in terms of sex/gender. In this case, both the patients and the doctors can be grouped on gender basis. This makes the process of using the bitmap indexes the most appropriate.
Consider the following query for Mountain View community hospital database:
For each treatment ordered in the past week, list by treatment ID and date in reverse chronological order the number of times a physician performed that treatment in that day, sorted alphabetically by physician name.
a. Which secondary key indexes would you suggest to optimise the performance of this query? Why? Make any assumptions you need to answer this question.
The secondary key index that can be used to optimise the performance of the query is the create index. This is because the index can be used for both searching and sorting. When using the index, there is no need to use a different index to perform the searches and the sorting.
b. Following the examples in chapter 6, write the SQL statements that create these secondary key indexes.
The following set of index gives a sample SQL statement which is used to create the secondary index.
CREATE INDEX i1 ON TABLE t1 (col1, col2);
CREATE INDEX i2 ON TABLE t1 (col2, col1);
CREATE INDEX i3 ON TABLE t1 (col1, col2);
-- (ASCending vs. DESCending) is different.
CREATE INDEX i3b ON TABLE t1 (col1, col2) DESC;
Chapter 6 describes the 2002 Sarbanes-Oxley Act, which is not focused on not-for providers such as many community hospitals.
a. Can you see how MVCH could benefit from voluntarily complying?
There are several benefits that come along with voluntary complying. They include:
i. There is an improvement in the comprehensiveness of the data of MVCH
ii. Data consistency is achieved and data duplication is eliminated
iii. Voluntary complying also helps in improving the accuracy of data being handled.
b. How specifically can proper physical database design help with compliance and implement the following:
Improve accuracy and completeness of MVCH data
Data is usually checked with some standards so as to ensure that the data is accurate and complete. When using database design with compliance, accuracy is usually achieved through carrying out a comparison of the provided data with some sets control data.
Eliminate duplicates and data consistencies
A proper database design also helps in eliminating duplicates and achieving data consistencies. This achieved through carrying out comparison of the manipulated data with earlier forms of presented data. This helps in removing any aspect data duplication and helps in achieving data consistencies.
Improve understandability of MVCH data?
Properly maintained data is usually well organised. This helps in making the data more understandable and easy to interpret.
References
1. Codd, E.F. (1970).A Relational Model of Data for Large Shared Data Banks". In: Communications of the ACM 13 (6): 377–387.
2. Development of an object-oriented DBMS (1986); Portland, Oregon, United States; Pages: 472 - 482;
3. Jeffrey A. Hoffer (2010). Modern Database Management. Pearson Education
4. Jeffrey A. Hoffer, Jeffrey A. Hoffer (Author)
5. Mary Prescott, and Heikki Topi (2008) Modern Database Management Prentice Hall Publishers
5. Performance enhancement through replication in an object-oriented DBMS (1986); Pages 325-336;
6. Seltzer, M. (2008). Beyond Relational Databases. Communications of the ACM, 51(7), 52-58. Retrieved July 6, 2009, from Business Source Complete database
