A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables.

SQL

RDBMS

Has benefits of a Reduced training costs

Specify syntax/semantics for data definition and manipulation.

Importance of SQL is not usually apparent to the application users.

These commands are used to create, alter, and drop tables, views and indexes. - It is also used to establish constraints in the database.

Creates an object (a table, for example) in the database.

Deletes an object in a database, usually irretrievably.

Modifies the structure of an existing object in various ways. For example, adding a column in a existing table

Commands used to maintain and query a database, including those for updating, inserting, modifying and querying data.

Data type selection is usually dictated by nature of data and by intended use.

The declaration (NUMBER 7, 2) indicates the numbers that will be stored with two decimal places may be up to six digits long, including the sign and decimal places. Examples are 12.32, -134.99.

May be abbreviated as INT. Integers are whole counting numbers, so they cannot be used if you want to store numbers with decimal places.

Like INTEGER but it is limited to integer values up to six(6) digits. If your integer values are relatively small, use SMALLINT instead of INT.

Like the NUMBER specification. DECIMAL(9,2), DECIMAL(9), and DECIMAL are all acceptable.

Fixed length character data up to 255 characters. If you store strings that are not as long as the CHAR parameter value, the remaining spaces are left unused

Variable-length character data. The designation _______(25) will let you store characters up to 25 characters long.

Stores dates (YYYY-MM-DD format).

Defines the ff. Use one line per column (attribute) definition. - Use spaces to line up attribute characteristics and constraints.

Adding Table Rows

Listing Table Rows

Updating Table Rows

Deleting Table Rows

a key which uniquely identifies each record in a table and it should not contain NULL values

a key used to link two tables together. It is a field in one table that refers to the PRIMARY KEY in another table.

Used to check whether attribute value is within range

A Special Operator: that Used to check whether attribute value is null

A Special Operator: that Used to check whether attribute value matches any value within a value list

A Special Operator: that Used to check whether attribute value matches given string pattern

Finds any values that starts with "a"

Finds any values that end with "a"

Finds any values that have with "or" in any position

Finds any values that have with "or" in any position

Finds any values that have with "or" in that second position

Finds any values that starts with 'a' and are at least 2 characters in length

Finds any values that starts with 'a' and are at least 3 characters in length

Finds any values that end with "a" and ends with "o"

All changes in table structure are made by using ALTER command, followed by keyword that produces specific change. The following three options are available: ADD, MODIFY, DROP

SQL provides useful functions that can count, find minimum and maximum values, and calculate sum and average.

Returns the number of rows with non-null values for a given column

Returns the minimum value found in a given column

Returns the minimum value found in a given column

Returns the maximum value found in given column

Returns the sum value of all values for a given column

Returns the average of all values for a given column

A view is a virtual table based on SELECT query. It can contain columns, aliases and functions from one or more table

Changes made to table are not physically saved on disk until one of the following occurs: the database is closed, program is closed, or the COMMIT command is used.

The ROLLBACK command is used to restore database to its previous condition

A relational operation that causes two tables with a common domain to be combined into a single-table or view.

Selects records that have matching values in both tables.

SYNTAX:

SELECT columnname(s)

FROM tablename

LEFT JOIN tablename

ON tablename.columnname = tablename.columnname;

SYNTAX:

SELECT columnname(s)

FROM tablename

RIGHT JOIN tablename

ON tablename.columnname = tablename.columnname;

Operator used to combine the results of two or more SELECT statements.

A logical unit of work that must be either entirely completed or aborted.

A logical unit of work that must be either entirely completed or aborted.

All properties of a transaction must be completed.

Permanence of database’s consistent state

Data used during transaction cannot be used by second transaction until the first is completed.

Once transaction are committed, they cannot be undone.

Concurrent execution of several transaction yields consistent results.

ANSI has defined standards that govern SQL transactions

it stores a record for the beginning of transaction.

Coordination of simultaneous transaction execution in multiprocessing database.

Problem that has Two concurrent transaction update same data element

First transaction rolled back after second already accessed uncommitted data.

Transaction might read some data before they are changed and other data after changed

Interleaves execution of database operations:

○ Ensures serializability.

○ Ensures isolation.

It Guarantees exclusive use of a data item to a current transaction

Responsible for assigning and policing the locks used by transactions.

It Indicates level of lock use

Entire database is locked.

Entire table is locked.

Entire disk page is locked.

Allows concurrent transaction to access different rows of same table.

Allows concurrent transaction the access same

Two states: locked (1) or unlocked (0).

Access is specifically reserved for transaction that locked object.

Access is specifically reserved for transaction that locked object.

Concurrent transactions are granted read access on basis of a common lock.

a Two Phase Locking that Transaction acquires all required locks without unlocking any data.

a Two Phase Locking that Transaction release all locks and cannot obtain any new lock.

Condition that occurs when two transactions wait for each other to unlock data.