Database Design and Administration Module 8

stored procedure

(1) A named collection of procedural and SQL statements. (2) Business logic stored on a server in the form of SQL code or another DBMS-specific procedural language.

cursor

A special construct used in procedural SQL to hold the data rows returned by a SQL query. A cursor may be considered a reserved area of memory in which query output is stored

Characteristics of a Trigger

A trigger is invoked before or after a data row is inserted, updated, or deleted. A trigger is associated with a database table. Each database table may have one or more triggers. A trigger is executed as part of the transaction that triggered it.

row-level trigger

A trigger that is executed once for each row affected by the triggering SQL statement. A row-level trigger requires the use of the FOR EACH ROW keywords in the trigger declaration

updatable view

A view that can update attributes in base tables that are used in the view.

view

A virtual table based on a SELECT query that is saved as an object in the database.

Procedural Language SQL (PL/SQL)

An Oracle-specific programming language based on SQL with procedural extensions designed to run inside the Oracle database.

Numeric INTEGER

An integer can be a positive or negative whole number, which has no decimal or fractional parts.

sequence

An object for generating unique sequential values for a field

Host Language

Any language that contains embedded SQL statements.

Command to create a database schema

CREATE SCHEMA AUTHORIZATION {creator}

COMMIT statement

Commit;

Numeric SMALLINT

Like INTEGER but limited to integer values up to six digits

Numeric DECIMAL(L,D)

Like the NUMBER specification, but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller ones are not. DECIMAL(9,2), DECIMAL(9), and DECIMAL are all acceptable.

Transact-SQL

Microsoft SQL Server's brand of SQL

PRIMARY KEY Sql Command

NOT NULL and UNIQUE specifications are automatically assumed

Which Table must be created first?

One table

Triggering action

PL/SQL code enclosed between the BEGIN and END keywords

Advantages of Stored Procedures

Performance improves for compiled SQL statements Reduced network traffic Improved security Improved data integrity Thinner clients

ROLLBACK statement

ROLLBACK;

ROLLBACK command

Restores data to their original values

Fixed-length character data

typically specified with a length and requires all values to be the specified length

When should an attribute composed of numbers be stored as a character attribute?

when there is no need to perform mathematical procedures on the attribute

Triggers

A procedural SQL code that is automatically invoked by the relational database management system when a data manipulation event occurs

UPDATE FROM

Updating using multiple tables

VARCHAR

Variable-length character data; typically specifies maximum length

CREATE VIEW command

- Specifies the base tables on which the view is to be based and the attributes and rows of the table that are to be included in the view.

DELETE command

- Specify which row(s) of a table are to be deleted based on data values within those rows.

Trigger uses

-Auditing purposes (creating audit logs) -Automatic generation of derived column values -Enforcement of business or security constraints -Creation of replica tables for backup purposes

Updatable view restrictions

-GROUP BY expressions or aggregate functions cannot be used -Set operators cannot be used -JOINs or group operators cannot be used

CREATE SEQUENCE

-name is the name of the sequence. -n is an integer value that can be positive or negative. START WITH specifies the initial sequence value. (The default value is 1.) -INCREMENT BY determines the value by which the sequence is incremented. (The default increment value is 1. The sequence increment can be positive or negative to enable you to create ascending or descending sequences.) -CACHE or NOCACHE/NO CACHE clause indicates whether the DBMS will preallocate sequence numbers in memory.

Trigger definition

-triggering timing: BEFORE or AFTER. This timing indicates when the trigger's PL/SQL code executes -triggering event: causes the trigger to execute (INSERT, UPDATE, or DELETE).

Anonymous PL/SQL Block

A PL/SQL block that has not been given a specific name.

CREATE INDEX

A SQL command that creates indexes on the basis of a selected attribute or attributes.

DROP INDEX

A SQL command used to delete database objects such as tables, views, indexes, and users.

statement-level trigger

A SQL trigger that is assumed if the FOR EACH ROW keywords are omitted. This type of trigger is executed once, before or after the triggering statement completes, and is the default case.

persistent storage module (PSM)

A block of code with standard SQL statements and procedural extensions that is stored and executed at the DBMS server

implicit cursor

A cursor that is automatically created in procedural SQL when the SQL statement returns only one value.

Stored Function

A named group of procedural and SQL statements that returns a value, as indicated by a RETURN statement in its program code.

DROP INDEX Statement

DROP INDEX table_name.index_name

DROP TABLE command

Discards an entire table from a database.

NOT NULL

Ensures that a column will not have null values

Character CHAR(L)

Fixed-length character data for up to 255 characters.

Explicit Cursor

In procedural SQL, a cursor is created to hold the output of a SQL statement that may return two or more rows but could return zero or only one row.

UPDATE command

In the UPDATE command, you have to identify which row(s) of a table are to be updated based on data values within those rows. Then you have to specify which columns are to be updated and what the new data values of those columns in those rows will be.

Embedded SQL

SQL statements contained within application programming languages such as COBOL, C++, ASP, Java, and ColdFusion.

Base tables

Tables on which the view is based

DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );

DELETE Statement

The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column=some_value;

COMMIT

The SQL command that permanently saves data changes to a database.

CREATE UNIQUE INDEX

The SQL command used to ensure that only unique values are allowed in a non-primary key column

ALTER TABLE

The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics.

UPDATE Statement

The UPDATE statement is used to update existing records in a table. UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

Numeric NUMBER(L,D) or NUMERIC(L,D)

The declaration NUMBER(7,2) or NUMERIC(7,2) indicates that numbers will be stored with two decimal places and may be up to seven digits long, including the sign and the decimal place (for example, 12.32 or −134.99).

Authentication

The process through which a DBMS verifies that only registered users can access the database.

ON DELETE CASCADE

To ensure all child tables are deleted if the parent is deleted. This can be your FOREIGN KEY constraint.

Advantages of Triggers

Triggers can be used to enforce constraints that cannot be enforced at the DBMS design and implementation levels. Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action. In fact, one of the most common uses for triggers is to facilitate the enforcement of referential integrity. Triggers can be used to update table values, insert records in tables, and call other stored procedures.

reserved words

Words used by a system that cannot be used for any other purpose

Schema

a logical group of database objects—such as tables and indexes—that are related to each other

composite primary key statement

all of the primary key's attributes are contained within the parentheses and are separated with commas

CHECK constraint

allows you to limit the types of data a user can insert into a the database and that they meet a certain condition.

Column Constraints

applies to just one column

Transact-SQL data types

bit, decimal and numeric. Transact-SQL data types that have a fixed precision and scale. money and smallmoney are Transact-SQL data types you would use to represent monetary or currency values.

CREATE TABLE

creates a new table

UNIQUE

creates a unique index on the respective attribute; used to avoid duplicate values in a column

With what is every user in an enterprise DBMS associated?

database schema

INSERT

enter data into a table

Create Table Statement

entire table definition is enclosed in parentheses. A comma is used to separate each table element definition CREATE TABLE "table_name" ("column 1" "data_type_for_column_1", "column 2" "data_type_for_column_2",... )

table and attribute names

fully capitalized

ON UPDATE CASCADE

if we update one primary key values in one table it will update in the other tables

Why is the order of the primary key components important?

indexing starts with the first mentioned attribute, then proceeds with the next attribute

batch update routine

pools multiple transactions into a single batch to update a master table field in a single operation

Triggering Levels

statement-level triggers row-level triggers

Table Constraint

table constraint may apply to many columns