INF3703 Exam Summary.

INF3703 Exam Summary. INF3703 - Databases II. Basically, a database is a carefully designed and constructed repository of facts. The database is a part of a larger whole known as an information system, which provides for data collection, storage, and retrieval. Systems analysis is the process that establishes the need for and the extent of an information system. The process of creating an information system is known as systems development. DBLC – Database Life Cycle. DBLC has 6 phases:  database initial study,  database design,  implementation and loading,  testing and evaluation,  operation,  maintenance and evolution.  The overall purpose of the database initial study is to:  Analyse the company situation.  Define problems and constraints.  Define objectives.  Define scope and boundaries. Interactive and iterative processes required to complete first phase of DBLC successfully Analyze the Company Situation • Analysis–To break up any whole into its parts so as to find out their nature, function, and so on • Company situation – General conditions in which company operates, its organizational structure, and its mission • Analyze company situation – Discover what company’s operational components are, how they function, and how they interact Define Problems and Constraints • Managerial view of company’s operation is often different from that of end users • Designer must continue to carefully probe to generate additional information that will help define problems within larger framework of company operations • Finding precise answers is important • Defining problems does not always lead to perfect Solution Define Objectives • Designer must ensure that database system objectives correspond to those envisioned by end user(s) • Designer must begin to address following questions: – What is proposed system’s initial objective? – Will system interface with other existing or future systems in the company? – Will system share data with other systems or users? Database Design Database Design • Necessary to concentrate on data • Characteristics required to build database model • Two views of data within system: – Business view of data as information source – Designer’s view of data structure, its access, and activities required to transform data into information • Loosely related to analysis and design of larger system • Systems analysts or systems programmers are in charge of designing other system components – Their activities create procedures that will help transform data within database into useful information • Does not constitute sequential process – Iterative process that provides continuous feedback designed to trace previous steps Database design process AKA I. Conceptual Design Overview • Data modeling used to create an abstract database structure that represents real-world objects in most realistic way possible • Must embody clear understanding of business and its functional areas • Ensure that all data needed are in model, and that all data in model are needed • Requires four steps – Data analysis and requirements – Entity relationship modeling and normalization – Data model verification – Distributed database design II. DBMS Software Selection • Critical to information system’s smooth operation • Advantages and disadvantages should be carefully studied III. Logical Design Overview • Used to translate conceptual design into internal model for selected database management system • Logical design is software -dependent • Requires that all objects in model be mapped to specific constructs used by selected database software  The logical design stage consists of the following phases: 1. Creating the logical data model. 2. Validating the logical data model using normalization. 4. Assigning and validating integrity constraints. 5. Merging logical models constructed for different parts for the database together. 6. Reviewing the logical data model with the use IV. Physical Design Overview • Process of selecting data storage and data access characteristics of database • Storage characteristics are function of device types supported by hardware, type of data access methods supported by system, and DBMS • Particularly important in older hierarchical and network models • Becomes more complex when data are distributed at different locations Physical database design can be broken down into a number of stages: 1. Analyze data volume and database usage. 2. Translate each relation identified in the logical data model into tables. 3. Determine a suitable file organization. 4. Define indexes. 5. Define user views. 6. Estimate data storage requirements. 7. Determine database security for users. Implementation and Loading • New database implementation requires creation of special storage‐related constructs to house end-user tables. Performance • One of most important factors in certain database implementations • Not all DBMSs have performance-monitoring and fine-tuning tools embedded in their software • There is no standard measurement for database performance • Not only (nor even main) factor Backup and Recovery • Database can be subject to data loss through unintended data deletion and power outages • Data backup and recovery procedures – Create safety valve • Allow database administrator to ensure availability of consistent data Integrity • Enforced through proper use of primary and foreign key rules Company Standards • May partially define database standards • Database administrator must implement and enforce such standards Database Security • Data must be protected from access by unauthorized users • Establish security goals – What are we trying to protect the database from? – What security related problems are we trying to prevent? • The most common security goals relate to the integrity, confidentiality and the availability of data. Threats to Security • Threats are any set of circumstances that have the potential to cause loss, misuse or harm to the system and/or its data. • Threats can cause: • The loss of the integrity of data through unauthorized modification. – For example a person gaining unauthorized access to a bank account and removing some money from the account. • The loss of availability of the data. – For example some adversary causes the database system from being operational which stops authorized users of the data from accessing it. • The loss of confidentiality of the data (also referred to as the privacy of data). – This could be caused by a person gaining access to private information such as a password or a bank account balance. Examples of Threats • Theft and fraud of data. • Human error which causes accidental loss of data. • Electronic infections – Viruses – Email Viruses – Worms – Trojan Horses • The occurrence of natural disasters such as hurricanes, fires or floods. • Unauthorized access and modification of data. • Employee sabotage is concerned with the deliberate acts of malice against the organization. • Poor database administration. Data Security Measures • Physical security allows only authorized personnel physical access to specific areas. • User authentication is a way of identifying the user and verifying that the user is allowed to access some restricted data or application. – achieved through the use of passwords and access rights. • Audit trails are usually provided by the DBMS to check for access violations. • Data encryption • User‐defined policies and procedures • Backup and recovery strategies • Antivirus software • Firewalls Testing and Evaluation • Occurs in parallel with applications programming • Database tools used to prototype applications • If implementation fails to meet some of system’s evaluation criteria: – Fine-tune specific system and DBMS configuration parameters – Modify physical design – Modify logical design – Upgrade or change DBMS software and/or hardware platform Operation • Once database has passed evaluation stage, it is considered operational • Beginning of operational phase starts process of system evolution 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 Database Design Strategies Two classical approaches to database design: – Top-down design • Identifies data sets • Defines data elements for each of those sets – Bottom-up design • Identifies data elements (items) • Groups them together in data sets Centralized vs. Decentralized Design • Database design may be based on two very different design philosophies: – Centralized design • Productive when data component is composed of relatively small number of objects and procedures – Decentralized design • Used when data component of system has considerable number of entities and complex relations on which very complex operations are performed Centralized vs. Decentralized Design Centralized Design Decentralized Design • Aggregation process – Requires designer to create single model in which various aggregation problems must be addressed: • Synonyms and homonyms • Entity and entity subtypes • Conflicting object definitions Database Administration • Data management is a complex job • Led to the development of the database administration function. • The person responsible for the control of the centralized and shared database is the database administrator (DBA). The Placement of the DBA functions DBA Activities • Database planning, including the definition of standards, procedures and enforcement. • Database requirements gathering and conceptual design. • Database logical design and transaction design. • Database physical design and implementation. • Database testing and debugging. • Database operations and maintenance, including installation, conversion and migration. • Database training and support. The Managerial Role of the DBA • End-User Support – Gathering user requirements – Building end‐user confidence. – Resolving conflicts and problems. – Finding solutions to information needs. – Ensuring quality and integrity of applications and data. – Managing the training and support of DBMS users. • Policies, Procedures and Standards – Policies are general statements of direction or action that communicate and support DBA goals. – Standards are more detailed and specific than policies and describe the minimum requirements of a given DBA activity. – Procedures are written instructions that describe a series of steps to be followed during the performance of a given activity.  Data Security, Privacy and Integrity – Protecting the security and privacy of the data in the database is a function of authorization management. – Authorization management defines procedures to protect and guarantee database security and integrity. – Includes: user access management, view definition, DBMS access control and DBMS usage monitoring. • Data Backup and Recovery – Many DBA departments have created a position staffed by the database security officer (DSO). – The DSO’s activities are often classified as disaster management. – Disaster management includes all of the DBA activities designed to secure data availability following a physical disaster or a database integrity failure. – Disaster management includes all planning, organizing and testing of database contingency plans and recovery procedures. • Data Distribution and Use – The DBA is responsible for ensuring that the data are distributed to the right people, at the right time and in the right format. The Technical Role of the DBA • Evaluating, selecting and installing the DBMS and related utilities. • Designing and implementing databases and applications. • Testing and evaluating databases and applications. • Operating the DBMS, utilities and applications. • Training and supporting users. • Maintaining the DBMS, utilities and applications. Evaluating, Selecting and Installing the DBMS and Utilities (DBA) • Covers the selection of the database management system, utility software and supporting hardware for use in the organization. • Must be based primarily on the organization’s needs • The DBA would be wise to develop a checklist of desired DBMS features. Designing and Implementing Databases and Applications (DBA) • Covers data modelling and design services to the end-user community • Determine and enforce standards and procedures to be used. • DBA then provides the necessary assistance and support during the design of the database at the conceptual, logical and physical levels Testing and Evaluating Databases and Applications (DBA) • The DBA must also provide testing and evaluation services for all of the database and end‐ user applications. • Those services are the logical extension of the design, development and implementation services. • Testing procedures and standards must already be in place before any application program can be approved for use in the company. Operating the DBMS, Utilities and Application (DBA) • DBMS operations can be divided into four main areas: – System support. – Performance monitoring and tuning. – Backup and recovery. – Security auditing and monitoring. Training and Supporting Users (DBA) • Training people to use the DBMS and its tools is included in the DBA’s technical activities. • The DBA also provides or secures technical training in the use of the DBMS and its utilities for the applications programmers. Maintaining the DBMS, Utilities and Applications (DBA) • The maintenance activities of the DBA are an extension of the operational activities. • Maintenance activities are dedicated to the preservation of the DBMS environment. Developing a Data Administration Strategy • Critical step for any organization is to ensure its information system supports strategic plans for each of the company’s business areas • Database administration strategy must not conflict with information systems plans • Information engineering – Translates strategic company’s strategic goals into data and applications that will help company achieve those goals – Output of IE process is information systems architecture that serves as basis for planning, development, and control of future information systems