Ch. 5 Databases

How and where can SQL be used?

1) SQL can be used as a complete language using its own SQL PSMs 2) It can be embedded in a host programming language and separately compiled by a precompiler 3) iIt can be used with a standard API through ODBC or JDBC

Bitemporal

A relation that has both valid time and transaction time

Snapshot

A single version of the database that shows only data that is current, losing all historical information and not providing a place for data about planned events

Transaction time relation

A temporal table that uses transaction time

GROUP BY...HAVING

Determine which groups have some quality

COUNT(DISTINCT ....)

Eliminate duplicates by not counting colname more than once

UNION

Eliminates duplicates and combine result set of two or more select statements

DISTINCT

Eliminates duplicates when projected over columns in SELECT

SET SERVEROUTPUT ON

Enables module to produce output on the client console

DELETE

Erases records while keeping architecture in tact

3 parts of a trigger

Event, condition and action

Options of a SELECT

GROUP BY, ORDER BY...RESTRICT, GROUP BY...HAVING, LIKE

Column heading

Gives the column name and data type in a base table

'M%ton'

If NAME contains character string data, to retrieve only records in which NAME begins with M and ends with 'ton', we use the SQL SELECT specification WHERE NAME LIKE:

INSERT INTO Student VALUES(stuId_seq.NEXTVAL,...);

If a sequence called stuId_seq has already been created and used to insert records into the Student table, and we want to insert a new record, incrementing the value of the sequence, we write:

tablename.colname

If the same column name appears on two tables what notation can we use to prevent confusion with retrieval?

Both renamed and reordered

In SQL, when creating a view, columns from the original table can be:

Both views and base tables

In a relational database environment, a user's external model can consist of:

Valid time

In a temporal database, the time data that is valid in the real world

PREPARE

In dynamic SQL, the command to parse and compile a host-language string containing an SQL command is:

User valid time

In the relational model, the type of time in a temporal relation that corresponds to the time that events occur in the real world is called

Natural join

Links tables by selecting only the rows with common values in their common attributes

Steps in SQL/Java program

Load driver, connect to database, run SQL, get a result set, query is a string to the program, process on server

Examples of column constraints

NULL/NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REF, CHECK and DEFAULT

CallableStatement

Object that is used to execute a stored procedure

PrepareStatement

Object to execute parameterized SQL queries

Cartesian product will be formed without

On a natural join why is a condition like Enroll.stuId=Student.studId necessary?

IMS

SQL was used as a query language in all of the following DBMSs EXCEPT (between IMS, Oracle, System R and DB2)

Cursor

The pointer, reference to a result set

FOREIGN KEY

The specification in the SQL CREATE TABLE command that enforces referential integrity is:

SQL

The standard data language for relational-model databases

Attributes

qualities or characteristics that distinguish entities from one another

action

A procedure that is performed when the event occurs and the condition evaluates to true, also called firing the trigger

Class A

An example of an Alias inside a SELECT on the FROM line

Persistent stored modules

An extension of SQL that allows users to write procedures that are stored on the database server

Statement

An object that is used to execute SQL that has no parameters

Event

An occurrence of interest, which could be some change made to the database or the execution of a procedure

Steps in ODBC/JDBC architecture

Application initiates the connection with the database, submits the data requests as SQL statements to the DBMS, retrieves the results, performs processing and terminates the connection all using a standard API

Transaction

Begins immediately after the last COMMIT or ROLLBACK, or if neither of these occurred, then at the beginning of the current user session

Built in aggregate functions of SELECT

COUNT, SUM, AVG., MIN AND MAX

SQL DDL Commands

CREATE TABLE and CREATE INDEX

Column constraints (in-line constraints)

Constraints defined on the same line as the column immediately after the specification of the column name and data type

Timestamp

Contains the system clock reading at the time the value was created

Data rows

Correspond to instances; each row contains one data value of the specified data type for each of the columns

System change number

A number that uniquely identifies change to a database

Condition

A logical predicate that evaluates to true or false

sequence

A more flexible mechanism used for generating values

Temporal database

A database that includes some representation of time

Active database

A database which the DBMS monitors the contents in order to prevent from occurring, using constraints and triggers

Dynamic SQL

A version of SQL that allows the type of database access to be specified at run time rather than compile time

PL/SQL

Allows a programmer to write code in a procedural format (A complete programming environment for creating SQL PSMs)

Java Database Connectivity (JDBC)

An API created to provide standard ways of integrating SQL code and Java by providing a common interface

Open Database Connectivity (ODBC)

An API created to provide standard ways of integrating SQL code and general-purpose languages by providing a common interface

CLUSTER

An SQL specification that causes records with the same values on the indexed fields to be stored close together is:

IDENTITY column

Column where items cannot be inserted

Table constraints (out-of-line constraints)

Constraints defined on a table that appear after all the columns have been declared

Internal routines

Procedures that are stored on the database server

External routines

Programs that are written in a host language and are stored on the client side

multiset

Result of executing a SELECT with duplicate rows

COUNT

Returns the number of values in a column

Integrity constraints

Rules or restrictions that apply to all instances of the database

The several forms of SELECT

SELECT, PROJECT and JOIN

SQL DML Commands

SELECT, UPDATE, INSERT and DELETE

True

SQL Includes a data definition language and a data manipulation language (T/F)

LIKE

Show a pattern string for character fields

Trigger

Similar, but more flexible than, constraints, they allow the DBMS to monitor the database through specifying an event, condition and action to be performed if the event and condition occur

Multiset

Since the result of an SQL SELECT statement can have duplicates, the result is referred to as a:

Examples of table constraints

Specification of primary key, foreign key, uniqueness, references, checks and general constraints

FETCH

Statement that advances the cursor and assigns the values of the attributes named in the SELECT to the corresponding variables on the INTO line

ROLLBACK

Statement that undoes all changes that were made by the current transaction

WHERE

Used with tuples to determine which records have some quality

EXISTS

Test on the WHERE line of a select for the inclusion of a record

GROUP BY

The SQL option HAVING can only be used with the option:

COMMIT

The SQL statement that is used to end a transaction that makes any changes permanent

Base tables

The actual tables in the schema for a relational database; the logical-level tables

Four components of ODBC/JDBC architecture

The application, the driver, the driver manager and the data source

Logical level

The base tables in a relational database correspond to which level in the standard three-level database structure?

Primary key

The candidate key chosen as the preferred way of identifying entities and accessing records

Transaction time

The date and time changes were made to the database

DELETE FROM Employee;

To erase all records in the Employee table while keeping the structure of the table, we could write the SQL command:

DESCRIBE Employee;

To have Oracle display the structure of a table named Employee, write:

Views in a data dictionary

USER, DBA and ALL

IN

Used because result of a subquery is a set of values rather than a single value

UPDATE

Used to change values in records already stored in a table

TO_CHAR

Used to convert a DATE or a timestamp value to a string

TO_DATE or TO_TIMESTAMP

Used to convert a string to a corresponding type

Subselect

What can be used in place of a join provided that the result is displayed in a single table and the data retrieved from the subquery only consists of one column?

Impedance mismatch

When a SELECT statement returns more than one row, since many host languages are not capable of handling sets

DROP

When executed, the table itself and all records contained in it are removed

SELECT * FROM EMPLOYEE E;

Which of the following is the proper way to create an alias, E, for relational table Employee and retrieve all employee data?

Composite primary key

Which type of relational table constraint cannot be specified as a column-level constraint?

To keep every class from being included

Why do qualified names have to be used in the SELECT line even when they are introduced as aliases before?