Review Questions Ch.7 (SQL for Database Construction and Application Processing)

What does DDL stand for? List the SQL DDL statements.

(DDL) Data Definition Language is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP.

What is a stored procedure? How do they differ from triggers?

A stored procedure is a program that performs some common action on database data and that is stored in the database. Unlike triggers, which are attached to a given table or view, stored procedures are attached to the database. They can be executed by any process using the database that has permission to use the procedure.

What is a trigger?

A trigger is a stored program that is attached to a table or view. The trigger code is invoked by the DBMS when an insert, update, or delete request is issued on the table or view to which the trigger is attached

Name nine possible trigger types.

BEFORE (Insert, Update, Delete) INSTEAD OF (Insert, Update, Delete) AFTER (Insert, Update, Delete)

What does DML stand for? List the SQL DML statements.

DML stands for Data Manipulation Language. The SQL statements that are in the DML class are INSERT, UPDATE, and DELETE. Some people also lump the SELECT statement in the DML classification.

Explain the meaning of the following expression: IDENTITY (4000, 5).

Identity defines a surrogate key. The 4000 is the starting point for the surrogate key values and the 5 is the increment for each additional surrogate key. The surrogate key values will be 4000, 4005, 4010, 4015 and so forth.

What is a SQL view? What purposes do views serve?

In SQL, a view is a virtual table based on the result-set of a SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. It is used to implement the security mechanism in the SQL Server. 1. Views can hide complexity 2. Views can be used as a security mechanism 3. Views can simplify supporting legacy code

Summarize the general idea for determining whether a view is updatable.

In general, to update a view, the DBMS must be able to associate the column(s) to be updated with a particular row in a particular underlying table. If any required (NOT NULL) columns are missing from the view, the view cannot be used for inserts. Multi-table views may not be updatable on the most subordinate table if the primary key or candidate key for that table is in the view.

If a view is missing required items, what action on the view is definitely no allowed?

Inserts.

Explain, in general terms how new and old values are made available to a trigger.

New Values are supplied by prefixing a column name with the expression: "new". Old Values are supplied by prefixing a column name with the expression: "old".

What are PL/SQL and T-SQL? What is the MySQL equivalent?

PL/SQL, or Programming Language for SQL is a proprietary language for the Oracle DBMS. Server triggers are written in a proprietary language called Transact-SQL, or T-SQL

Explain the paradigm mismatch between SQL and programming languages. (7.45)

SQL is set-oriented; most SQL statements return a table or a set of rows. Programs on the other hand, are element- or row oriented.

What is the relationship between a trigger and a table or view?

Triggers are attached to tables. The trigger is fired when an update is made to the table the trigger is assigned to

Summarize how to invoke a stored procedure.

can be executed from application programs written in languages such as Java, C#, C++, or VB.Net. They can also be invoked from Web pages using VBScript or JavaScript. Ad hoc users can run them from products such as SQL*Plus in Oracle or from Query Analyzer in SQL Server. Stored procedures can also be run for either Oracle or SQL Server from the Microsoft VisualStudio.NET environment,

Summarize the key advantages of stored procedures.

1. Greater security 2. Decreased network traffic 3. SQL can be optimized 4. Code sharing 5. Less work 6. Standardized processing 7. Specialization among developers

Describe four uses for triggers.

1. validity checking 2. assigning default values 3. updating views 4. enforcing referential integrity actions

How is the mismatch in your answer to Review Question (7.45) corrected?

The results of SQL statements are treated like pseudo-files. A SQL statement is executed and returns a set of rows. A cursor is placed on the first row and is processed. Then , the cursor is moved to the next row and that row is processed, iterating in this way until all rows have been processed.