CSCE 310 Part 3

Database Systems Post-Normalization Flashcards

The Information System

Provides for data collection, storage, and retrieval

The Information System is Composed of:

-People, hardware, and software
-Database(s), application programs, and procedures

Systems Analysis

Process that establishes need for and extent of information system

Systems Development

Process of creating information system

Performance Factors of an Information System

•Database design and implementation
•Application design and implementation
•Administrative procedures

Database Development

Process of database design and its implementation

Systems Development Life Cycle (SDLC)

- Traces history of an information system
- Provides a picture within which database design and application development are mapped out and evaluated
- Iterative rather than sequential process

Computer-Aided Systems Engineering (CASE)

- Includes System Architect and Visio Professional
- Helps produce better systems in a reasonable amounts of time and reasonable cost
- Applications are more structured, better documented and standardized
-\> Prolongs operational life of systems

Purpose of Database Initial Study

- Analyze company situation
- Define problems and constraints
- Define objectives
- Define scope and boundaries

Database Design

- Supports company's operations and objectives
- Most critical phase
- Points for examining completion procedures

The most critical phase of database design

Ensures final product meets user and system requirements

The database design points for examining completion procedures

- Data component is an element of whole system
- System analysts/programmers design procedures to convert data into information
- Database design is an iterative process

Implementation and Loading

- Install the DBMS
- Create the databases
- Load or convert the data

Installing the DBMS involves

Virtualization

Virtualization

Creates logical representations of computing resources independent of underlying physical computing resources

Create the databases

Requires the creation of special storage-related constructs to house the end-user tables

Load or convert the data

Requires aggregating data from multiple sources

Testing and Evaluation Goals

- Physical security
- Password security
- Access rights
- Audit trails
- Data encryption
- Diskless workstations
- Optimization

Levels of Database Backups

- Full backup/dump
- Differential backup
- Transaction log backup
- Backups are provided with high security

Full backup/dump

All database objects are backed up in their entirety

Differential backup

Only modified/updated objects since last full backup are backed up

Transaction log backup

Only the transaction log operations that are not reflected in a previous backup are backed up

Common Sources of Database Failure

Software
Hardware
Programming Exemptions
Transactions
External Factors

Software Database Failure

Software-induced failures may be traceable to the operating system, the DBMS software, application programs, or viruses and other malware.

Hardware Database Failure

Hardware-induced failures may include memory chip errors, disk crashes, bad disk sectors, and disk-full errors.

Programming Exemptions

Application programs or end users may roll back transactions when certain conditions are defined. Programming exemptions can also be caused by malicious or improperly tested code that can be exploited by hackers.

Transactions

The system detects deadlocks and aborts one of the transactions.

External Factors

Backups are especially important when a system suffers complete destruction from fire, earthquake, flood, or other natural disaster.

Maintenance and Evolution Goals

- 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

Preventative Maintenance

Backup

Corrective Maintenance

Recovery

Conceptual Design

Designs a database independent of database software and physical details

Conceptual Data Model

- Describes main data entities, attributes, relationships, and constraints
- Designed as software and hardware independent

Minimum Data Rule

All that is needed is there, and all that is there is needed

Conceptual Design Steps

Step 1: Data analysis and requirements
Step 2: Entity relationship modeling and normalization
Step 3: Data model verification
Step 4: Distributed database design

Data Analysis and Requirements

- Designers efforts are focused on
-\> Information needs, users, sources and constitution
- Answers obtained from a variety of sources
-\> Developing and gathering end-user data views
-\> Directly observing current system: existing and desired output
-\> Interfacing with the systems design group

Description of Operations

Provides precise, up-to-date, and reviewed description of activities defining an organization's operating environment

Developing the Conceptual Model Using ER Diagrams Steps

Step 1: Identify, analyze, and refine the business rules
Step 2: Identify the main entities, using the results of Step 1
Step 3: Define the relationships among the entities, using the results of Steps 1 and 2
Step 4: Define the attributes, primary keys, and foreign keys for each of the entities
Step 5: Normalize the entities. (Remember that entities are implemented as tables in an RDBMS)
Step 6: Complete the initial ER diagram
Step 7: Validate the ER model against the end users' information and processing requirements
Step 8: Modify the ER model, using the results of Step 7

Data Model Verification

- Verified against proposed system processes
- Better if modules' ER fragments are merged into a single enterprise ER model which triggers
-\> Careful reevaluation of entities
-\> Detailed examination of attributes describing entities
- Resulting model verified against each of the module's processes

Module

Information system component that handles specific business function

The ER Model Verification Process Steps

Step 1: Identify the ER model's central entity
Step 2: Identify each module and its components
Step 3: Identify each module's transaction requirements: Internal, External
o Internal: updates/inserts/deletes/queries/reports
External: module interfaces
Step 4: Verify all processes against system requirements
Step 5: Make all necessary changes suggested in Step 4
Step 6: Repeat Steps 2-5 for all modules

Internal Model Requirements

-Updates
-Inserts
-Deletes
-Queries
-Reports

External Model Requirements

Module Interfaces

Cohesivity

Strength of the relationships among the module's entities

Module Coupling

Extent to which modules are independent to one another

Low coupling

Decreases unnecessary intermodule dependencies

Distributed Database Design

- Portions of database may reside in different physical locations
- Ensures database integrity, security, and performance

Database Fragment

Subset of a database stored at a given location

DBMS Software Selection

- Cost
- DBMS features and tools
- Underlying model
- Portability
- DBMS hardware requirements

Logical Design

Designs an enterprise-wide database that is based on a specific data model but independent of physical-level details

Logical design validating logical model involves

* Using Normalization
* Integrity Constraints
* Validating Against User Requirements

Physical Design

Process of data storage organization and data access characteristics of the database

Logical Design Steps

Step 1: Map the conceptual model to logical model components
Step 2: Validate the logical model using normalization
Step 3: Validate the logical model integrity constraints
Step 4: Validate the logical model against user requirements

Mapping The Conceptual Model To The Relational Model Steps

Step 1: Map strong entities
Step 2: Map supertype/subtype relationships
Step 3: Map weak entities
Step 4: Map binary relationships
Step 5: Map higher-degree relationships

Physical Design Steps

Step 1: Define data storage organization
Step 2: Define integrity and security measures
Step 3: Define integrity and security measures

Clustered Tables

Technique that stores related rows from two related tables in adjacent data blocks on disk

Database Role

Set of database privileges that could be assigned as a unit to a user or group

Database Performance Tuning Concepts

- Goal of database performance is to execute queries as fast as possible
- Fine-tuning the performance of a system requires that all factors must operate at optimum level with minimal bottlenecks

Database performance tuning

Set of activities and procedures that reduce response time of database system

Performance Tuning: Client Side

- SQL Performance Tuning
- Using minimum amount of resources at server

SQL Performance Tuning Goal

Generates SQL query that returns correct answer in least amount of time

Performance Tuning: Server Side

- DBMS Performance Tuning
- Optimum use of existing resources

DBMS Performance Tuning

DBMS environment configured to respond to clients' requests as fast as possible

DBMS Architecture

- All data in a database are stored in data files
- Data files are grouped in file groups or table spaces
- Data cache or buffer cache
- SQL cache or procedure cache
- DBMS retrieves data from permanent storage and places them in RAM
- Input/output request
- Data cache is faster than working with data files
- Majority of performance-tuning activities focus on minimizing I/O operations

Extends

Data files automatically expand in predefined increments

Data cache or buffer cache

- Shared, reserved memory area
- Stores most recently accessed data blocks in RAM

SQL cache or procedure cache

Stores most recently executed SQL statements or PL/SQL procedures

Input/output request

Low-level data access operation that reads or writes data to and from computer devices

Database Query Optimization Modes

- Algorithms proposed for query optimization are based on:
-\> Selection of the optimum order to achieve the fastest execution time
-\> Selection of sites to be accessed to minimize communication costs
-Evaluated on the basis of:
-\> Operation mode
-\> Timing of its optimization

Classification of Operation Modes

- Automatic query optimization
- Manual query optimization

Automatic Query Optimization

DBMS finds the most cost effective access path without user intervention

Manual query optimization

Requires that the optimization be selected and scheduled by the end user or programmer

Classification Based on Timing of Optimization

- Static query optimization
- Dynamic query optimization

Static query optimization

- Best optimization strategy is selected when the query is compiled by the DBMS
- Takes place at compilation time

Dynamic query optimization

- Access strategy is dynamically determined by the DBMS at run time, using the most up-to-date information about the database
- Takes place during execution

Classification Based on Type of Information Used to Optimize the Query

- Statistically based query optimization algorithm
- Rule-based query optimization algorithm

Statistically based query optimization algorithm

- Statistics are used by the DBMS to determine the best access strategy

Statistical information is generated by DBMS through

- Dynamic statistical generation mode
- Manual statistical generation mode

Rule-based query optimization algorithm

Based on a set of user defined rules to determine the best query access strategy

Query Processing

- Parsing
- Execution
- Fetching

Parsing

DBMS parses the SQL query and chooses the most efficient access/execution plan

Execution

DBMS executes the SQL query using the chosen execution plan

Fetching

DBMS fetches the data and sends the result set back to the client

SQL Parsing Phase

- Query is broken down into smaller units
- Original SQL query transformed into slightly different version of original SQL code which is fully equivalent and more efficient
- Query optimizer
- Access plans
- If access plan already exists for query in SQL cache, DBMS reuses it
-\> If not, optimizer evaluates various plans and chooses one to be placed in SQL cache for use

Query Optimizer

Analyzes SQL query and finds most efficient way to access data

Access plans

DBMS-specific and translate client's SQL query into a series of complex I/O operations

SQL Execution Phase

All I/O operations indicated in the access plan are executed

All I/O operations indicated in the access plan are executed involve

- Locks are acquired
- Data are retrieved and placed in data cache
- Transaction management commands are processed

SQL Fetching Phase

Rows of resulting query result set are returned to the client

Rows of resulting query result set are returned to the client involve

- DBMS may use temporary table space to store temporary data
- Database server coordinates the movement of the result set rows from the server cache to the client cache

Query Processing Bottlenecks

- Delay introduced in the processing of an I/O operation that slows the system

Query Processing Bottlenecks Causes

- CPU
- RAM
- Hard disk
- Network
- Application code

Indexes and Query Optimization

- Indexes
- Data sparsity
- Data structures used to implement indexes
- DBMSs determine best type of index to use

Indexes

- Help speed up data access
- Facilitate searching, sorting, using aggregate functions, and join operations
- Ordered set of values that contain the index key and pointers
- More efficient than a full table scan

Data Sparsity

Number of different values a column could have

Data structures used to implement indexes

- Hash indexes
- B-tree indexes
- Bitmap indexes

Optimizer Choices

- Rule-based optimizer
- Cost-based optimizer

Rule-based optimizer

Uses preset rules and points to determine the best approach to execute a query

Cost-based optimizer

Uses algorithms based on statistics about objects being accessed to determine the best approach to execute a query

Optimizer might not choose the best execution plan because

- Makes decisions based on existing statistics, which might be old
- Might choose less-efficient decisions