Database Systems | Finals Exam Reviewer

SQL JOIN Clause

• Is used to combine records two or more tables in a database, based on a common field between them other keywords are combined with the SELECT statement.

• Keywords are:

  • INNER JOIN

  • OUTER JOIN

    • LEFT JOIN

    • RIGHT JOIN

    • FULL JOIN

INNER JOIN

• Also known as EQUIJOIN keyword

• Return rows when there is at least one match in both tables.

OUTER JOIN

• Is an extension of INNER JOIN.

• It does not require each record in the two joined tables to have a matching record.

• The joined table retains each record- even if no other matching record exists.

LEFT JOIN

Returns all rows from the left table (table1), even if there are no matches in the right table (table2).

RIGHT JOIN

Returns all the rows from the right table (table2), even if there are no matches in the left table (table1).

FULL JOIN

Both tables are secondary (or optional), such that if rows are being matched in table A and table B, then all rows from table A are displayed even if there is no matching in row table B, and vice versa.

CROSS JOIN

It returns all possible combinations of rows from the two tables.

Subqueries

• SELECT statement embedded within another SELECT statement.

• The results of this inner SELECCT statement (or subselect) are used in the outer statement to help determine the contents of the final result.

1. Scalar subquery

2. Row subquery

3. Table subquery

Types of Subqueries:

Scalar subquery

Returns a single column and a single row, that is, a single value.

Row subquery

Returns multiple columns, but only a single row.

Table subquery

Returns one or more columns and multiple rows.

SQL Indexes

• Used to improve the efficiency of searches and to avoid duplicate column values.

• Can be created on the basis of any selected attribute.

  CREATE [UNIQUE] INDEX indexname ON tablename (column1, [column2])

DROP INDEX

It is used to delete index.

CREATE VIEW

• A view is a virtual table based on a SELECT query

• The query can contain columns, computed columns, aliases, and aggregate functions from one or more tables.

• It is a data definition command that stores the subquery specification—the SELECT statement used to generate the virtual table— in the data dictionary.

ALTER VIEW

Is the keyword used to modify a created view.

DROP VIEW

• Is used to delete a view that was previously created

Information System

• A system that provides for data collection, storage, and retrieval; facilitates the transformation of data into information; and manages both data and information.

• Composed of; hardware, DBMS and other software, People, Procedures.

System Analysis

Is the process that establishes the need for an information system and its extent.

System Development

Is the process of creating an information system.

1. Database design and implementation

2. Application design and implementation

3. Administrative procedures

The performance of an information system depends on three (3) factors:

Database Development

Is the process of database design and implementation

Primary Objectives of Database Design

Is to create complete, normalized, no redundancy (to the greatest extent possible), and fully integrated conceptual, logical, and physical database models.

System Development Life Cycle (SDLC)

• Is a cycle that traces the history of an information system.

• It provides the big picture within which database design and application development can be mapped out and evaluated.

1. Planning

2. Analysis

3. Detailed Systems Design

4. Implementation

5. Maintenance

Five (5) Phases of System Development Life Cycle:

PLANNING

• Yields a general overview of the company and its objectives.

• If the new system is necessary the next step is to check the feasibility. The feasibility study must address the following:

  • The technical aspects of hardware and software requirements.

  • The system cost.

  • The operational cost.

ANALYSIS

• Problems defined during the planning phase are examined in greater detail during this phase.

• A macro analysis must be made both of individual needs and organizational needs.

• Thorough audit of user requirements.

• The existing hardware and software systems are also studied during the analysis phase.

• Includes the creation of logical system design.

DETAILED SYSTEMS DESIGN

• The designer completes the design of the systems processes.

• The design includes all the necessary technical specifications for the screens, menus, reports and other devices that might help make the system a more efficient information generator.

• The steps are laid out for conversion from the old system to the new system. Training principles and methodologies are also planned and must be submitted for managements approval.

IMPLEMENTATION

• The hardware, the DBMS software, and application programs are installed, and the database design is implemented.  

• During the initial stages of this phase, the system enters to a cycle of coding, testing and debugging until it is ready to be delivered.  

• The actual database is created, and the system is customized by the creation of table views, user authorization and so on.

• The system is in full operation at the end of this phase, but it will be continuously evaluated and fine-tuned.

MAINTENANCE

Almost as soon as the system is operational, end users begin to request changes in it. Those changes generate system maintenance activities, which can be grouped into three types:

• Corrective maintenance in response to systems errors

• Adaptive maintenance due to changes in the business environment

• Perfective maintenance to enhance the system

Database Life Cycle (DBLC)

A cycle that traces the history of database within an information system.

1. Database Initial Study

2. Database Design

3. Implementation and Loading

4. Testing and Evaluation

5. Operation

6. Maintenance and Evolution

Six (6) Phases of Database Life Cycle:

DATABASE INITIAL STUDY

• Analyze the company situation

• Define problems and constraints

• Define Objectives

• Define Scopes and Boundaries

Scope

Defines the extent of the design according to operational requirements.

Boundaries

Also known as Limits

DATABASE DESIGN

• It focuses on the design of the database model that will support company operations and objectives.

• The most critical DBLC since it will make sure that the final product meets the user and system requirements.

1. Business View

2. Designer’s View

Two (2) Views of the data within the systems:

Business View

Data as a source of information.

Designer’s View

Data structure, its access, and the activities required to transform the data into information.

IMPLEMENTATION AND LOADING

• This includes creation of tables, attributes, domains, views, indexes, security constraints and storage and performance guidelines.

  • Create Database

  • Load or Convert the data

Virtualization

• A technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.

• This technique is used in many areas of computing, such as the creation of virtual servers, virtual storage, and virtual private networks.

TESTING AND EVALUATION

• Test the Database

• Fine-Tune the Database

• Evaluate the Database and its Performance

Full Backup

• Or dump of the entire database.

• All database objects are backed up in their entirety.

Differential Backup

In which only the objects that have been updated or modified since the last full backup are backed up.

Transaction Log

A DBMS uses a ___________ ___ to keep track of all transactions that update the database

Transactional Log Backup

Which backs up only the transaction log operations that are not reflected in a previous backup copy of the database.

OPERATION

• Once the database has passed the evaluation stage, it is considered as operational.

• The database, its management, its users, and its application program constitute a complete information system.

MAINTENANCE AND EVOLUTION

The database administrator must be prepared to perform routine maintenance activities within the database.

• Preventive maintenance (backup)

• Corrective maintenance (recovery)

• Adaptive maintenance (enhancing performance, adding entities and attributes, and so on)

• Assignment of access permissions and their maintenance for new and old users

• Generation of database access statistics to improve the efficiency and usefulness of system audits and to monitor system performance  

• Periodic security audits based on the system-generated statistics

• Monthly, quarterly, or yearly system usage summaries for internal billing or budgeting purposes.

Some of the required periodic maintenance activities include:

1. Conceptual Design

2. Logical Design

3. Physical Design

Three (3) Stages in Database Design:

Conceptual Design

• It is the first stage in the database design process

• A process that uses data modeling techniques to create a model of a database structure that represents real world objects as realistically as possible. The design is both software- and hardware-independent.

• The output of this process is a conceptual data model that describes the main data entities, attributes, relationships, and constraints of a given problem domain.

1. Data analysis and requirements

2. Entity relationship modeling and normalization

3. Data model verification

4. Distributed database design

5. Database Software Selection

Conceptual Design Steps:

Business Rule

• Is a brief and precise description of a policy, procedure, or principle within a specific organization’s environment.

• Derived from a formal description of operations, which is a document that provides a precise, up-to-date, and thoroughly reviewed description of the activities that define an organization’s operating environment.

Module

• is an information system component that handles a specific business function, such as inventory, orders, or payroll.

• Under these conditions, each module is supported by an ER segment that is a subset or fragment of an enterprise ER model.

Database Fragment

• Is a subset of a database that is stored at a given location.

• It may be a subset of rows or columns from one or multiple tables.

Logical Design

• It is the second stage in the database design process.

• It aims to is to design an enterprise-wide database that is based on a specific data model but independent of physical-level details.

• It requires that all objects in the conceptual model be mapped to the specific constructs used by the selected database model.

• For example, the logical design for a relational DBMS includes the specifications for the relations (tables), relationships, and constraints (in other words, domain definitions, data validations, and security views).

1. Map the conceptual model to logical model components

2. Validate the logical model using normalization

3. Validate the logical model integrity constraints

4. Validate the logical model against user requirements

Logical Design Steps:

Physical Design

• Is the process of determining the data storage organization and data access characteristics of the database to ensure its integrity, security, and performance.

• This is the last stage in the database design process.

1. Define data storage organization

2. Data integrity and security measures

3. Determine performance measurements

Physical Design Steps:

Transaction

• Is any action that reads from or writes to a database

• A sequence of database requests that accesses the database.

• It is a logical unit of work; that is, it must be entirely completed or aborted—no intermediate ending states are accepted.

1. COMMIT

2. ROLLBACK

Transaction support is provided by two (2) SQL statements:

• SELECT - Generate list of table contents

• UPDATE - Change the values of attributes in various tables

• INSERT - Add rows to one or more tables

Transaction consists of the following operation being performed:

• Atomicity

• Consistency

• Isolation

• Durability

ACID Properties

Atomicity

Requires that all operations (SQL requests) of a transaction be completed; if not, the transaction is aborted.

Consistency

Indicates the permanence of the database’s consistent state.

Isolation

Means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.

Durability

Ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure.

Concurrency Control

• A DBMS feature that coordinates the simultaneous execution of transactions in a multiprocessing database system while preserving data integrity

• The objective of this is to ensure the serializability of transactions in a multiuser database environment.

• It is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems.

1. Lost Updates

2. Uncommitted Data

3. Inconsistent Retrieval

Three (3) Main Problems of Concurrency Control:

Lost Updates

Problem occurs when two concurrent transactions, T1 and T2, are updating the same data element and one of the updates is lost (overwritten by other transaction).

Uncommitted Data

It occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data—thus violating the isolation property of transactions.

Inconsistent Retrieval

• It occurs when a transaction accesses data before and after one or more other transactions finish working with such data.

• For example, this would occur if transaction T1 calculated some summary (aggregate) function over a set of data while another transaction (T2) was updating the same data.

• The problem is that the transaction might read some data before it is changed and other data after it is changed, thereby yielding inconsistent results.

Scheduler

• It is a special DBMS process that establishes the order in which the operations are executed within concurrent transactions.

• It interleaves the execution of database operations to ensure serializability and isolation of transactions

• Its main job is to create a serializable schedule of a transaction’s operations, in which the interleaved execution of the transactions (T1, T2, T3, etc.) yields the same results as if the transactions were executed in serial order (one after another).

Lock

• Guarantees exclusive use of data item to a current transaction.

• In other words, transaction T2 does not have access to a data item that is currently being used by transaction T1.

Pessimistic Locking

The use of locks based on the assumption that conflict between transactions is referred to as

Lock Manager

All lock information is handled by a ____, which is responsible for assigning and policing the locks used by the transactions.

Exclusive Lock

• Is used when a transaction requests permission to update a data item and no locks are held on that data item by any other transaction.

• It does not allow other transactions to access the database.

Shared Lock

• A lock that is issued when a transaction requests permission to read data from a database and no exclusive locks are held on the data by another transaction.

• It allows other read only transactions to access the database.

Mutual Exclusive Rule

Is a condition in which only one transaction at a time can own a exclusive lock on the same object.

Time Stamping

• Approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction.

• This value produces an explicit order in which transactions are submitted to the DBMS.

• It must have two properties:

  1. uniqueness; and

  2. monotonicity.

Uniqueness

Ensures that no equal time stamp values can exist.

Monotonicity

Ensures that time stamp values always increase.

Wait/Die Scheme

• A concurrency control scheme in which an older transaction must wait for the younger transaction to complete and release the locks before requesting the locks itself.

• Otherwise, the newer transaction dies and is rescheduled.

Wound/Wait Scheme

• A concurrency control scheme in which an older transaction can request the lock, pre-empt the younger transaction, and reschedule it.

• Otherwise, the newer transaction waits until the older transaction finishes.

1. Wait/Die Scheme

2. Wound/Wait Scheme

Two (2) Schemes for Time Stamping Method:

Optimistic Approach

• Is based on the assumption that the majority of database operations do not conflict.

• Requires neither locking nor time stamping techniques. Instead, a transaction is executed without restrictions until it is committed.

• Each transaction moves through two or three phases, referred to as read, validation, and write.

Read Phase

The transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values.

Validation Phase

The transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database.

Write Phase

The changes are permanently applied to the database.

ANSI SQL Standard (1992)

Defines transaction management based on transaction isolation levels.

Transaction Isolation Levels

• Refer to the degree to which transaction data is “protected or isolated” from other concurrent transactions.

• The isolation levels are described on what data other transactions can see (read) during execution.

• Dirty Read

• Nonrepeatable Read

• Phantom Read

• Read Uncommitted

• Read Committed

• The Repeatable Read

Types of Read Operation

Dirty Read

A transaction can read data that is not yet committed.

Nonrepeatable Read

• A transaction reads a given row at time T1, and then it reads the same row at time T2, yielding different results.

• The original row may have been updated or deleted.

Phantom Read

A transaction executes a query at time T1, and then it runs the same query at time T2, yielding additional rows that satisfy the query.

Read Uncommitted

At this isolation level, the database does not place any locks on the data, which increases transaction performance but at the cost of data consistency.

Read Committed

• This is the default mode of operation for most databases (including Oracle and SQL Server).

• At this level, the database will use exclusive locks on data, causing other transactions to wait until the original transaction commits.

The Repeatable Read

• Isolation level ensures that queries return consistent results.

• This type of isolation level uses shared locks to ensure other transactions do not update a row after the original query reads.

Database Recovery

Restores a database from a given state (usually inconsistent) to a previously consistent state.

Atomic Transaction Property

• Recovery techniques are based on the ____.

• All portions of the transaction must be treated as a single, logical unit of work in which all operations are applied and completed that affects the recovery process.