Database Schema
Design a database schema for the proposed Database Design Proposal in Module 1.
Identify and list all relevant entities and their relationships:
1) Select no less than five entities.
2) Provide a diagram.
3) Map this to a database schema.
4) Provide a data dictionary for all entity attributes.
Focus on one or two areas of your design that seemed especially difficult to develop and provide a brief assessment of the difficulty you encountered in modeling or mapping to the schema. In addition, provide the rationale for the design chosen, its limitations, and its possible extensions.
Tip: If you do not have access to ER modeling software or a diagramming tool like Visio or OmniGraffle, you can simply create diagrams in a presentation tool like PowerPoint. This is an essential skill which is used in the field.
APA format is not required, but solid academic writing is expected.
This assignment uses a grading rubric. Instructors will be using the rubric to grade the assignment; therefore, students should review the rubric prior to beginning the assignment to become familiar with the assignment criteria and expectations for successful completion of the assignment.
This assignment will be utilized again in Modules 4 and 5. Prior to Module 4, review any feedback provided to you by the instructor.
60.0
Database Design Proposal: Proposal Outline
The Database Design Proposal assignment consists of several parts which are due in various modules throughout the course. The assignment will also be referred to in some of the other individual assignments within the course. Module 1 discussion focused on data types and their categories and some challenges and strategies of dealing with data. In this Database Design Proposal, you are required to design and develop a database which will be used to compile and report distilled information using clinical health care data. The Proposal Outline is the initial step of this assignment.
Create a short outline of about 250–500 words of the project proposal. In the outline, briefly define and describe the scenario for which the database will be designed, the major problem(s) that the users in the given scenario would solve, and any other additional components of a standard project proposal outline that are needed. Utilize the following outline as a guideline:
1) Title Page
2) Abstract: A summary of the whole proposal in about 75 words.
3) Introduction: The introduction should explain the situation, the cause of the problems, the statement of the project problem, and definition of terms.
4) Solution: This should include the objectives of what needs to be created to solve the problem and achieve the proposed outcomes. Identify the limits of the project and outline steps required to meet the above stated objectives.
5) Resources: What resources will be required for this project?
6) Budget: What is the estimated budget?
7) Users (Personnel/Credentials): Who are your users? What are their competencies?
8) Conclusion: This part should clearly point out the value of the project with emphasis on feasibility, necessity, usefulness, and the benefit of the expected results.
APA format is not required, but solid academic writing is expected.
This assignment uses a grading rubric. Instructors will be using the rubric to grade the assignment; therefore, students should review the rubric prior to beginning the assignment to become familiar with the assignment criteria and expectations for successful completion of the assignment.
This is an ongoing project and the feedback from the instructor at every stage will help you in improving your final project. Be sure to consult with your instructor throughout the course and incorporate suggestions and recommendations in your project.
Introduction
Health care is a field that is data-driven and reliant on the most complete access to information in order to enable care providers to make the best decisions for their patients during the delivery of care.
Types of Health Data
One factor that makes health care such a complex field is that there are numerous types of health care data spanning a broad spectrum. These data types can be classified along different categories:
• Clinical data vs. administrative data (e.g., lab test data vs. admissions data)
• Raw data vs. aggregate data (e.g., temperature reading vs. discharge statistics)
• Analog data vs. digital data (e.g., electrocardiogram vs. vital signs)
• Structured data vs. unstructured data (e.g., ICD-9 coded diagnoses vs. free-text narrative)
Furthermore, health care data can appear in many different forms or modalities:
• Numerical values (e.g., clinical measurements in specific units)
• Text (e.g., discharge summary)
• Codes (e.g., ICD-9, CPT)
• Images (e.g., X-ray, MRI)
• Video (e.g., ultrasound)
All health care data is collected and stored with the purpose of being retrieved at a later point in time, such as for possible processing or analysis, and to ultimately be acted upon. In general, data is collected within an information system to support a business process; in health care, the business process is the proper delivery of care. In this context, data is used with the ultimate goal of enabling decision-making and achieves this purpose by:
• Providing a historical and legal record and a baseline of clinical data;
• Being shared as information among the care providers;
• Allowing for future health forecasts and identification of abnormal trends;
• Recording treatments and preventative interventions; and
• Supporting clinical and biological research.
There are several computing components to facilitate the collection, storage, retrieval, and analysis of data, critical for decision making in a timely manner based on all the relevant information:
1) Hardware
a) Database Server: Typically a dedicated server, part of or connected to other hospital information systems via a network; used to store and process data.
b) Client Workstations: Computers that staff use for data entry into and retrieval from a database.
c) Input Devices: Medical devices with the capability to directly input captured data to a database.
2) Software
a) Database (DB): A database is a structured collection of data; structured data means that data is stored based on a data model with relationships between data items, as opposed to flat-files or spreadsheets, which only store raw, unstructured data.
i) Databases gained traction in the 1970s and are of different types, such as hierarchical, object-oriented, XML-based, document-based, or image-based, as in a Picture Archiving and Communication System (PACS). The most common and widely supported type is the relational database.
b) Database Management System (DBMS): a collection of software tools that enable the design, construction, maintenance, and backup of databases and allow for data retrieval, manipulation, and processing.
c) Flat files: Data storage in a file and folder system; typically data is stored in an unstructured list format in text files.
d) Spreadsheets: Data stored in rows and columns with processing and charting capabilities; spreadsheets are especially geared for numerical data analysis.
3) Clients
a) Terminal/Text-based Client: Command-line DBMS interface for data entry and retrieval.
b) Forms: A graphical user interface custom-developed for capturing and manipulating data in a specific context, with a fixed set of built-in queries and data-entry fields that allow the user to point, click, and deal with data without having to learn a specific data manipulation language.
c) Graphical DB Client: A graphical user interface to a DBMS that allows queries to be defined graphically by clicking and dragging with the mouse instead of learning and employing the syntax of a DB query language.
d) Dashboards: A user interface component that pulls only relevant information from the database in a predefined format and often allows the information to be visualized in a graphical way for ease of comprehension.
e) Reports: A predefined format for retrieving specific information from a database, often in aggregate form and for a user-specified time frame, clearly formatted and suitable for printing.
4) People
a) Data Collectors: Care providers and administrative staff that enter data into DB systems.
b) Database Administrator (DBA): Designs the data model, creates, maintains, and tunes the performance of the database, and creates predefined reports according to data consumer specifications.
c) Data Consumer: Care providers, administrators and hospital management, decision makers, researchers, and others who will interpret the data.
Data Management Components
Different kinds of software components for data management include flat files, spreadsheets, and database systems.
Flat files are easiest to use because they are essentially lists of raw data stored in text files, thus there is no extra cost involved with dedicated data management software; this is a viable option for small and temporary data sets.
Spreadsheets store data in rows and columns but are more powerful than flat files because they come with data processing, analysis, and graphing functionality. Spreadsheet software often is available as part of an office productivity suite, such as Microsoft Excel or Open Office Calc, and is geared towards the manipulation and calculation of numerical data. The limitations are that extremely large datasets and concurrent usage are often not supported.
Relational database management systems and the related hardware can be expensive to acquire, depending on the system and size of the dataset, but there are an increasing number of free editions available. The most common DBMS vendors are Oracle, IBM, Microsoft, MySQL, Sybase, and PostgreSQL. Relational databases are more complex than spreadsheets and can incur a significant training investment or require a dedicated DBA to maintain. The benefits over spreadsheets and flat files are that data is organized into logical and related structures, allowing for querying for answers to non-trivial business questions and for tracking information. The structured nature of the data storage also means that repeated data can be stored more efficiently, relationships can be represented, and constraints and validation rules can be defined to prevent incorrect data from being entered. DBMS provide backup and redundancy features and often have recovery capabilities that mitigate data corruption or loss in the event of failure. Database access can be simultaneous by multiple users, and access control can be defined for each user or user group. The same data can be defined as different views for different users, without having to duplicate the data. DBMS are designed to scale to extremely large data sets and are often only bound by the amount of physical storage space available. While database systems come with sophisticated data processing capabilities, it is not uncommon for a consumer familiar with routine spreadsheet software to retrieve specific data from a database and then use a spreadsheet tool to further analyze and produce visual graphs and charts from it.
Conclusion
From the above overview, it should be clear that the health care environment and its many critical data components and requirements of timely, secure, and concurrent access lend itself to being managed within a database management system.