Quiz 3 - Information Management - Database Design PDF

MODULE 6 - DATABASE DESIGN The Information System Provides for data collection, storage, and retrieval It is composed of: ○ People, Hardware, Software ○ Database(s), Application Programs, Procedures ○ Systems Analysis Systems Analysis ○ Process th...

MODULE 6 - DATABASE DESIGN The Information System Provides for data collection, storage, and retrieval It is composed of: ○ People, Hardware, Software ○ Database(s), Application Programs, Procedures ○ Systems Analysis Systems Analysis ○ Process that establishes need for and extent of information system. Systems Development ○ Process of creating information system Application Transform data into information that forms basis for decision making ○ Usually produce the following: PLANNING Formal Report General overview of company and objectives Tabulations Assessment of flow-and-extent requirements Graphic Displays ○ Should the existing system be continued? ○ Composed of the following two parts: ○ Should the existing system be modified? Data ○ Should the existing system be replaced? Code: program instructions Study and evaluate alternate solutions ○ Technical aspects of hardware and software requirements ○ System cost ○ Operational cost ANALYSIS Problems defined during planning phase are examined in greater detail during analysis Thorough audit of user requirements Existing hardware and software systems are studied Goal: ○ Better understanding of: System’s functional areas Actual and potential problems Performance depends on three factors: Opportunities ○ Database design and implementation DETAILED SYSTEMS DESIGN ○ Application Design and implementation Designer completes design of system’s processes ○ Administrative Procedures Includes all necessary technical specifications Steps laid out for conversion from old to new system Database Development Training principles and methodologies are also planned Process of database design and implementation ○ Submitted for management approval ○ Implementation phase includes: IMPLEMENTATION Creating database storage structure Hardware, DBMS software, and application programs Loading data into the database are installed Providing for data management ○ Database design is implemented The System Development Life Cycle Cycle of coding, testing, and debugging continues until Traces history (life cycle) of information system database is ready for delivery Database design and application development mapped Database is created and system is customized out and evaluated ○ Creation of tables and views ○ Divided into following five phases: ○ User authorizations Planning MAINTENANCE Analysis Three types of maintenance activity: Detailed Systems Design ○ Corrective maintenance Implementation ○ Adaptive maintenance Maintenance ○ Perfective maintenance ○ Iterative rather than sequential process Computer-Aided Systems Engineering (CASE) dee | Q3 1  ○ Produce better systems within reasonable amount ○ Upgrade software and/or hardware platform of time and at reasonable cost Integrity ○ CASE-produced applications are structured, ○ Enforced via proper use of primary, foreign key documented, and standardized rules The Database Life Cycle Backup and Recovery Six Phases ○ Full backup ○ Database initial study ○ Differential backup ○ Database design ○ Transaction log backup ○ Implementation and loading ○ Testing and evaluation Operation ○ Operation Once database has passed evaluation stage, it is ○ Maintenance and evolution considered operational Beginning of operational phase starts process of system evolution Problems not foreseen during testing surface Solutions may include: ○ Load-balancing software to distribute transactions among multiple computers ○ Increasing available cache Maintenance and Evolution Required periodic maintenance: ○ Preventive maintenance (backup) ○ Corrective maintenance (recovery) ○ Adaptive maintenance ○ Assignment of access permissions and their maintenance for new and old users ○ Generation of database access statistics ○ Periodic security audits ○ Periodic system-usage summaries Conceptual Design Database Initial Study Required periodic maintenance Overall purpose: ○ Represents real-world objects ○ Analyze company situation Embodies clear understanding of business and its ○ Define problems and constraints functional areas ○ Define objectives Ensure that all data needed are in model, and that all ○ Define scope and boundaries data in model are needed Interactive and iterative processes required to complete Requires four steps first phase of DBLC successfully Database Design Necessary to concentrate on data characteristics Data Analysis and Entity Relationship required to build database model Modeling and Requirements Two views of data within system: Normalization ○ Business view: Discover data element Designer enforces Data as information source characteristics standards in design ○ Designer’s view ○ Obtains documentation Data structure, access, and activities required characteristics ○ Use of diagrams to transform data into information from different and symbols, sources documentation Implementation and Loading Requires thorough writing style, Implement all design specifications from previous understanding of the layout, other phase: company’s data types conventions ○ Install the DBMS and their extent and Business rules must Virtualization: creates logical representations uses be incorporated into of computing resources independent of Take into account conceptual model physical resources business rules ER model is a ○ Create the Database ○ Derived from communications tool ○ Load or Convert the Data description of as well as design operations blueprint Testing and Evaluation Occurs in parallel with applications programming Database tools used to prototype applications If implementation fails to meet some of system’s Data Model Verification evaluation criteria: Verified against proposed system processes ○ Fine-tune specific system and DBMS configuration Revisions of original design parameters ○ Careful reevaluation of entities ○ Modify physical or logical design dee | Q3 2  ○ Detailed examination of attributes describing ○ Triggers-prevent inappropriate actions, invoke entities special handling procedures, write to log files Define design’s major components as modules: ○ Module: information system component that Authorization Rules handles specific function Controls incorporated in the data management Restrict: Distributed Database Design ○ Access to data Portions of database may reside in different physical ○ Actions that people can take on data locations Implementing authorization rules ○ Database fragment: subset of a database stored at a given location Processes accessing the database vary from one location to another Designer must also develop data distribution and allocation strategies Data Security Protection of the data against accidental or intentional loss, destruction, or misuse Increased difficulty due to Internet access and client/server technologies Possible locations of data security (see figure below) Encryption-the coding or scrambling of data so that humans cannot read them Authentication schemes ○ Passwords: First line of defense Should be at least 8 characters long Should combine alphabetic and numeric data Should not be complete words or personal information Should be changed frequently ○ Strong Authentication Two-factor (smart card plus PIN) Three-factor (smart card, biometric, PIN) Data Recovery Threats to Data Security Database Software Recovery Facilities: Security Features ○ Back-up Facilities-DBMS copy utility that produces Accidental losses Views or subschemas backup copy of the entire database or subset attributable to: Integrity controls ○ Journalizing Facilities-audit trail of transactions ○ Human Error Authorization rules and database updates ○ Software Failure User-defined ○ Transaction integrity-commit or abort all ○ Hardware procedures transaction changes Failure Encryption ○ Transaction ACID Properties Theft and Fraud Authentication ATOMIC Loss of privacy or schemes transaction cannot be subdivided confidentiality Backup, journalizing, CONSISTENT ○ Loss of privacy and checkpointing constraints don’t change from before (personal data) transaction to after transaction ○ Loss of ISOLATED confidentiality database changes not revealed to users (corporate data) until after transaction has completed Loss of data integrity DURABLE Loss of availability database changes are permanent (e.g., through sabotage) Deadlock An impasse that results when two or more transactions have locked common resources, and each waits for the Views and Integrity Controls other to unlock their resources Views ○ Subset of the database that is presented to one or Data Availability more users It is about the timeliness and reliability of access to and ○ User can be given access privilege to view without use of data allowing access privilege to underlying tables Integrity Controls ○ Protect data from unauthorized use ○ Domains-set allowable values ○ Assertions-enforce database conditions dee | Q3 3