6 Chapter 6 Database Design Database Systems Design Implementation and Management 5th Edition Peter Rob Carlos Coronel Changing Data into Information 6 Data are the raw facts that are stored in databases Raw facts are seldom immediately useful to a decision maker What the decision maker really needs is information which is defined as data processed and presented in a meaningful form Table 6 1 A Simple Tabulation Transforming Data into Information The Information System 6 A database is a carefully designed and constructed repository of facts and is part of larger whole known as an information system An IS provides for data collection storage and retrieval IS also facilitates the transformation of data into information and the management of both data and information Components of an information system People Hardware Software Database s Application programs Procedures The Information System System analysis is the process that establishes the need for and the extent of an IS 6 The process of creating an IS is known as systems development Applications transform data into the information An application is composed of two parts the data and the code Figure 6 1 The performance of an IS depends on three factors Database design and implementation DB development Applications design and implementation Administrative procedures Generating Information for Decision Making 6 Figure 6 1 The Systems Development Life Cycle The Systems Development Life Cycle SDLC traces the history life cycle of an IS 6 Database design takes place within the confines of an IS Five phases of SDLC Figure 6 2 Planning Analysis Detailed Systems Design Implementation Maintenance Systems Development Life Cycle 6 Figure 6 2 The Systems Development Life Cycle Planning 6 The planning phase yields a general overview of the company and its objectives An initial assessment of the information flow andextent requirements must be made Should the existing system be continued Should the existing system be modified Should the existing system be replaced A feasibility study must address the following issues if a new system is necessary Technical aspects of hardware and software requirements The system cost The Systems Development Life Cycle Analysis 6 Problems defined during the planning phase are examined in greater detail What are the precise requirements of the current system s end users Do those requirements fit into the overall information requirements The analysis phase is a thorough audit of user requirements The existing hardware and software are studied End users and system designer s work together to identify processes and potential problem areas The Systems Development Life Cycle 6 The analysis phase includes the creation of a logical system design The logical design specifies conceptual data model inputs processes and expected output requirements System design tools Data flow diagram DFD Hierarchical input process and output HIPO Entity Relationship E R diagrams Defining the logical system also yields functional descriptions FD of the system s components modules for each process within the database environment The Systems Development Life Cycle Detailed Systems Design 6 The designer completes the design of the system s processes including all technical specifications for Screen Menus Reports Other devices Conversion steps are laid out Training principles and methodologies are planned The Systems Development Life Cycle Implementation 6 The hardware the DBMS software and application programs are installed and the database design is implemented The system enters into a cycle of coding testing and debugging The database is created and the system is customized The database contents are loaded The system is subjected to exhaustive testing The final documentation is reviewed and printed End users are trained The Systems Development Life Cycle Maintenance 6 End users requests for changes generate system maintenance activities Three types of system maintenance Corrective maintenance in response to systems errors Adaptive maintenance due to changes in the business environment Perfective maintenance to enhance the system Computer assisted systems engineering CASE technology helps make it possible to produce better systems within a reasonable amount of time and cost Database Lifecycle DBLC 6 Figure 6 3 The Database Life Cycle The Database Initial Study 6 Overall Purpose of the Initial Study Analyze the company situation Define problems and constraints Define objectives Define scope and boundaries Initial Study Activities Figure 6 4 6 The Database Life Cycle 6 Analyze the Company Situation What is the organization s general operating environment and what is its mission within that environment What is the organization s structure Define Problems and Constraints How does the existing system function What input does the system require What documents does the system generate How is the system output used By Whom What are the operational relationships among business units What are the limits and constraints imposed on the system The Database Life Cycle 6 Define the Objective What is the proposed system s initial objective Will the system interface with other existing or future systems in the company Will the system share the data with other systems or users Define Scope and Boundaries Scope What is the extent of the design based on operational requirements Boundaries What are the limits Budget Hardware and software Two Views of Data 6 Figure 6 5 6 Figure 6 6 The Database Life Cycle Conceptual Design 6 Data modeling is used to create an abstract database structure that represents real world objects The design must be software and hardwareindependent Minimal data rule All that is needed is there and all that is there is needed Four Steps Data analysis and requirements Entity relationship modeling and normalization Data model verification Distributed database design The Database Life Cycle 6 Data analysis and requirements Designer s efforts are focused on Information needs Information users Information sources Information constitution Sources of information for the designer Developing and gathering end user data views Direct observation of the current system existing and desired output Interface with the systems design group The designer must identify the company s business rules and analyze their impacts The Database Life Cycle Entity Relationship Modeling and Normalization 6 Table 6 2 Developing the Conceptual Model Using E R Diagrams A