S307 Chapter 8

Software-as-a-Service (SaaS)

" cloud computing approach in which the service consists of software solutions/applications intended to directly address the needs of a noncomputing activity.

Physical database design requires several critical decisions that will affect the integrity and performance of the application system. These key decisions include the following:

- Choosing the storage format (called data type) for each attribute from the logical data model. The format and associated parameters are chosen to maximize data integrity and to minimize storage space. - Giving the database management system guidance regarding how to group attributes from the logical data model into physical records. You will discover that although the columns of a relational table as specified in the logical design are a natural definition for the contents of a physical record, this does not always form the foundation for the most desirable grouping of attributes in the physical design. - Giving the database management system guidance regarding how to arrange similarly structured records in secondary memory (primarily hard disks), using a structure (called a file organization) so that individual and groups of records can be stored, retrieved, and updated rapidly. - Consideration must also be given to protecting data and recovering data, if errors are found. - Selecting structures (including indexes and the overall database architecture) for storing and connecting files to make retrieving related data more efficient. - Preparing strategies for handling queries against the database that will optimize performance and take advantage of the file organizations and indexes that you have specified. Efficient database structures will be beneficial only if queries and the database management systems that handle those queries are tuned to intelligently use those structures.

The following are some other typical integrity controls that a DBMS may support:

- Default value - Range control - Null value control - Referential integrity

Following are some of the major components of a recovery plan:

- Develop a detailed written disaster recovery plan. - Schedule regular tests of the plan. - Choose and train a multidisciplinary team to carry out the plan. - Establish a backup data center at an off-site location. This site must be located a sufficient distance from the primary site so that no foreseeable disaster will disrupt both sites. If an organization has two or more data centers, each site may serve as a backup for one of the others. If not, the organization may contract with a disaster recovery service provider. - Send backup copies of databases to the backup data center on a scheduled basis. Database backups may be sent to the remote site by courier or transmitted by replication software."

Advantages of partitioning

- Efficiency: Records used together are grouped together - Local optimization: Each partition can be optimized for performance - Security: data not relevant to users are segregated - Recovery and uptime: smaller files take less time to back up - Load balancing: Partitions stored on different disks, reduces contention

In choosing a file organization for a particular file in a database, you should consider seven important factors:

- Fast data retrieval. - High throughput for processing data input and maintenance transactions. - Efficient use of storage space. - Protection from failures or data loss. - Minimizing need for reorganization. - Accommodating growth. - Security from unauthorized use.

When incorrect data have been processed, the database may be recovered in one of the following ways:

- If the error is discovered soon enough, backward recovery may be used. (However, care must be taken to ensure that all subsequent errors have been reversed.) - If only a few errors have occurred, a series of compensating transactions may be introduced through human intervention to correct the errors. - If the first two measures are not feasible, it may be necessary to restart from the most recent checkpoint before the error occurred and process subsequent transactions again without the error.

The information needed for physical file and database design includes these requirements:

- Normalized relations, including estimates for the range of the number of rows in each table. - Definitions of each attribute, along with physical specifications such as maximum possible length. - Descriptions of where and when data are used in various ways (entered, retrieved, deleted, and updated, including typical frequencies of these events). - Expectations or requirements for response time and data security, backup, recovery, retention, and integrity. - Descriptions of the technologies (database management systems) used for implementing the database.

For example, a trigger can do the following:

- Prohibit inappropriate actions (e.g., changing a salary value outside the normal business day). - Cause special handling procedures to be executed (e.g., if a customer invoice payment is received after some due date, a penalty can be added to the account balance for that customer). - Cause a row to be written to a log file to echo important information about the user and a transaction being made to sensitive data so that the log can be reviewed by human or automated procedures for possible inappropriate behavior (e.g., the log can record which user initiated a salary change for which employee).

In an electronic environment, a user can prove his or her identity by supplying one or more of the following factors:

- Something the user knows, usually a password or personal identification number (PIN). - Something the user possesses, such as a smart card or token. - Some unique personal characteristic, such as a fingerprint or retinal scan."

The most important security features of data management software follow:

- Views or subschemas, which restrict user views of the database. - Domains, assertions, checks, and other integrity controls defined as database objects, which are enforced by the DBMS during database querying and updating. - Authorization rules, which identify users and restrict the actions they may take against a database. - User-defined procedures, which define additional constraints or limitations in using a database. - Encryption procedures, which encode data in an unrecognizable form. - Authentication schemes, which positively identify persons attempting to gain access to a database. - Backup, journaling, and checkpointing capabilities, which facilitate recovery procedures.

The key focus of SOX audits is around three areas of control:

1. IT change management. 2. Logical access to data. 3. IT operations.

Disadvantage of Partitioning

1. Inconsistent access speed: Different partitions may have different access speeds, thus confusing users 2. Complexity: Partitioning is usually not transparent to programmers, who will have to write more complex programs when combining data across partitions. 3. Extra space and update time: Data may be duplicated across the partitions, taking extra storage space compared to storing all the data in normalized files.

In addition to these specific challenges of cloud-based database services, DBaaS shares the disadvantages of all cloud-based services, such as the following

1. Releasing the control of critical infrastructure resources to an external provider. 2. A high level of dependency on the cloud service provider. 3. A high level of dependency on the public Internet and related data services. 4. A high level of dependency on standards and technologies that are evolving continuously.

Selecting a data type involves four objectives that will have different relative levels of importance for different applications:

1. Represent all possible values. 2. Improve data integrity. 3. Support all data manipulations. 4. Minimize storage space.

Opportunities for and Type of Denormalization

1. Two entities with a one-to-one relationship 2. A many-to-many relationship (associative entity) with nonkey attributes 3. Reference data

A database management system should provide four basic facilities for backup and recovery of a database:

1. backup facilities 2. journalizing facilities 3. a checkpoint facility 4. a recovery manager

Notice that the repository engine supports five core functions

1. object management 2. relationship management 3. Dynamic extensibility 4. Version management 5. Configuration management

Backup facility

A DBMS COPY utility that produces a backup copy (or save) of an entire database or a subset of a database.

Database-as-a-Service (DBaaS)

A cloud computing approach in which the service consists of a data management platform service.

Platform-as-a-Service (PaaS)

A cloud computing approach in which the service consists of infrastructure resources (as in IaaS) and additional tools and services that allow application and data management solution developers to reach a higher level of productivity than with pure infrastructure resources.

Infrastructure-as-a-Service (IaaS)

A cloud computing approach in which the service consists primarily of hardware and various types of systems software resources.

Information Repository

A component that stores metadata that describe an organization's data and data processing resources, manages the total information processing environment, and combines information about an organization's business information and its application portfolio.

Extent

A contiguous section of disk storage space

After image

A copy of a record (or page of memory) after it has been modified.

Before image

A copy of a record (or page of memory) before it has been modified.

Smart card

A credit card-sized plastic card with an embedded microprocessor chip that can store, process, and output electronic data in a secure manner.

Default Value

A default value is the value a field will assume unless a user enters an explicit value for an instance of that field.

Data type

A detailed coding scheme recognized by system software, such as a DBMS, for representing organizational data

transaction

A discrete unit of work that must be completely processed or not processed at all within a computer system. Entering a customer order is an example of a transaction.

Checkpoint facility

A facility by which a DBMS periodically refuses to accept any new transactions. The system is in a quiet state, and the database and transaction logs are synchronized.

pointer

A field of data indicating a target address that can be used to locate a related field or record of data.

Hash index table

A file organization that uses hashing to map a key into a location in an index, where there is a pointer to the actual data record matching the hash key.

Database change log

A log that contains before and after images of records that have been modified by transactions.

Cloud Computing

A model for provisioning and acquiring computing services on demand using centralized resources that are accessed either through the public Internet or a private network.

Recovery Manager

A module of a DBMS that restores the database to a correct condition when a failure occurs and then resumes processing user questions.

Tablespace

A named logical storage unit in which data from one or more database tables, views, or other database objects may be stored.

Physical file

A named portion of secondary memory (such as a hard disk) allocated for the purpose of storing physical records

Range control

A range control limits the set of permissible values a field may assume. The range may be a numeric lower-to-upper bound or a set of specific values.

Transaction log

A record of the essential data for each transaction that is processed against the database.

Hashing algorithm

A routine that converts a primary key value into a relative record number or relative file address

Hashed file organization

A storage system in which the address for each record is determined using a hashing algorithm.

system catalog

A system-created database that describes all database objects, including data dictionary information, and also includes user access information.

File Organization

A technique for physically arranging the records of a file on secondary storage devices

Restore/rerun

A technique that involves reprocessing the day's transactions (up to the point of failure) against the backup copy of the database.

Forward recovery (rollforward)

A technique that starts with an earlier copy of a database. After images (the results of good transactions) are applied to the database, and the database is quickly moved forward to a later state.

Aborted transaction

A transaction in progress that terminates abnormally.

A recovery manager

Allows the DBMS to restore the database to a correct condition and restart processing transactions.

journalizing facility

An audit trail of transactions and database changes.

DATE

Any date from January 1, 4712 b.c., to December 31, 9999 a.d.; DATE stores the century, year, month, day, hour, minute, and second

TIMESTAMP

Any date from January 1, 4712 b.c., to December 31, 9999 a.d.; TIMESTAMP stores the century, year, month, day, hour, minute, and second

BLOB

Binary large object, capable of storing up to 4 gigabytes of binary data (e.g., a photograph or sound clip).

CLOB

Character large object, capable of storing up to 4 gigabytes of one variable-length character data field

Authorization rules

Controls incorporated in a data management system that restrict access to data and also restrict the actions that people may take when they access data.

Vertical partitioning

Distribution of the columns of a logical relation into several separate physical tables

Horizontal Partitioning

Distribution of the rows of a logical relation into several separate tables

Version management

During development, it is important to establish version control. The information repository can be used to facilitate version control for software design tools

CHAR

Fixed-length character data with a maximum length of 2,000 characters; default length is 1 character

Configuration management

It is necessary to group versioned objects into configurations that represent the entire system, which are also versioned. It may help you to think of a configuration as similar to a file directory, except configurations can be versioned and contain objects rather than files.

Journalizing Facilities

Maintain an audit trail of transactions and database changes

Database Recovery

Mechanisms for restoring a database quickly and accurately after loss or damage.

Object management

Object-oriented repositories store information about objects. As databases become more object oriented, developers will be able to use the information stored about database objects in the information repository.

secondary key

One field or a combination of fields for which more than one record may have the same combination of values. Also called a nonunique key

NUMBER

Positive or negative number in the range 10−130 to 10126; can specify the precision (total number of digits to the left and right of the decimal point to a maximum of 38) and the scale

Backup Facilities

Provide periodic backup (sometimes called fallback) copies of portions of or the entire database.

A checkpoint facility

The DBMS periodically suspends all processing and synchronizes its files and journals to establish a recovery point.

Backward recovery (rollback)

The backout, or undo, of unwanted changes to a database. Before images of the records that have been changed are applied to the database, and the database is returned to an earlier state. Rollback is used to reverse the changes made by transactions that have been aborted, or terminated abnormally.

Encryption

The coding or scrambling of data so that humans cannot read them.

Database destruction

The database itself is lost, destroyed, or cannot be read.

Denormalization

The process of transforming normalized relations into nonnormalized physical record specifications.

Relationship management

The repository engine contains information about object relationships that can be used to facilitate the use of software tools that attach to the database.

Dynamic extensibility

The repository information model defines types, which should be easy to extend, that is, to add new types or to extend the definitions of those that already exist.

Field

The smallest unit of application data recognized by system software.

Indexed file organization

The storage of records either sequentially or nonsequentially with an index that allows software to locate individual records.

Sequential file organization

The storage of records in a file in sequence according to a primary key value.

User-defined procedures

User exits (or interfaces) that allow system designers to define their own security procedures in addition to the authorization rules.

VARCHAR2

Variable-length character data with a maximum length of 4,000 characters; you must enter a maximum field length

data dictionary

a repository of information about a database that documents data elements of a database

Index

a table or other data structure used to determine in a file the location of records that satisfy some condition