CGS2545 Chapter 7

Terms for Chapter

Base table - A table in the relational data model containing the inserted raw data. Base tables correspond to the relations that are identified in the database's conceptual schema.

Catalog - A set of schemas that, when put together, constitute a description of a database.

Data control language (DCL) - Commands used to control a database, including administering privileges and the committing (saving) of data.

Data definition language (DDL) - Those commands used to define a database, including creating, altering, and dropping tables and establishing constraints.

Data manipulation language (DML) - Those commands used to maintain and query a database, including updating, inserting, modifying, and querying data.

Dynamic view - A virtual table that is created dynamically upon request by a user. A dynamic view is not a temporary table. Rather its definition is stored in the system catalog and the contents of the view are materialized as a result of an SQL query that uses the view. Distinguish from a materialized view, which may be stored on a disk and refreshed at intervals or when used, depending on the RDBMS.

Materialized view - Copies or replicas of data based on SQL queries created in the same manner as dynamic views. However, a materialized view exists as a table and thus care must be taken to keep it synchronized with its associated base tables.

Referential integrity - An integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of a primary key in the same or another relation.

Relational DBMS (RDBMS) - A database management system that manages data as a collection of tables in which all data relationships are represented by common values in related tables.

Scalar aggregate - A single value returned from an SQL query that includes an aggregate function.

Schema - That structure which contains descriptions of objects created by a user, such as base tables, views, and constraints, as part of a database.

Vector aggregate - Multiple values returned from an SQL query that includes an aggregate function.

Back to the Top


Questions from Companion Website

Multiple Choice

1. A(n) ____ database is one where data are stored in a collection of tables.
Correct answer : relational
All data relationships are represented by common values in related tables.

2. Which is NOT an advantage of a standardized relational language like SQL?
Correct answer : Fixing deficiencies is relatively easy
The considerable effort required to fix deficiencies is not an advantage of a standardized language such as SQL.

3. A ____ is a structure that contains descriptions of objects created by a user.
Correct answer : schema
A schema contains these descriptions and may include base tables, views, and constraints.

4. SQL commands can be classified into three types. Which is NOT an SQL command type?
Correct answer : DGL
The command types are DDL (data definition language), DML (data manipulation language), and DCL (data control language).

5. Three SQL DDL CREATE commands are
Correct answer : Schema, Table, and View.
SQL DDL CREATE commands include Schema, Table, and View.

6. Which is NOT an advantage of using a view?
Correct answer : Decrease system overhead
Decreased system overhead is not an advantage, as additional processing time is needed to recreate a view each time it is accessed.

7. A ___ view constructed automatically, not maintained as real data, is costly since it must be recalculated each time it is requested.
Correct answer : dynamic
A dynamic view is a virtual table, created each time it is requested.

8. A ____ view can improve the performance of distributed queries, especially if the view is relatively static and does not have to be refreshed often.
Correct answer : materialized
A material view can improve performance of distributed queries.

9. Data integrity can be ensured through which updates?
Correct answer : Cascading, set null, and restricted updates
Data integrity can be ensured through cascading, set null, set default, and restricted updates.

10. Which keyword is NOT included in most data retrieval statements?
Correct answer : AS
Most SQL data retrieval statements include the clauses SELECT, FROM, and WHERE.

11. Functions include
Correct answer : COUNT, SUM, and AVG.
Functions include COUNT, MIN, MAX, SUM and AVG.

12. In a basic SELECT statement, ____ determines which tables or views will be used in the query.
Correct answer : FROM
FROM determines which tables or views will be used.

13. In a basic SELECT statement, ____ sets the criteria of the query.
Correct answer : WHERE
WHERE sets the criteria.

14. In a basic SELECT statement, ____ is used to categorize results.
Correct answer : GROUP BY
GROUP BY categorizes query results.

15. Most SQL vendors are moving towards ____ compliance.
Correct answer : CORE SQL-99
Most vendors are moving from Entry SQL-92 to CORE SQL 99.

Back to the Top


True/False Questions

1. Most vendors are moving from Entry SQL-92 to Core SQL-01.
Correct answer : FALSE
Most vendors are moving to Core SQL-99.

2. SQL runs on midsize computers to large mainframes.
Correct answer : FALSE
Products that support SQL run on all machine sizes, from personal computers to mainframes.

3. Each vendorís version of SQL may be different.
Correct answer : TRUE
Each version may offer enhancements, features, and capabilities that extend the version beyond the baseline standards.

4. Most companies keep at least two versions of any database they are using.
Correct answer : TRUE
Usually a production and development version of each database is maintained.

5. SQL commands can be classified into two types.
Correct answer : FALSE
The three types of SQL commands are data definition language commands, data manipulation language commands, and data control commands.

6. Use the DROP command to delete a table, schema, or view.
Correct answer : TRUE
Usually only a table creator can delete or DROP the table.

7. A materialized view is a virtual table that is created upon request by the user.
Correct answer : FALSE
A dynamic view is a virtual table.

8. The UPDATE command is used to populate tables.
Correct answer : FALSE
The INSERT command is used to populate tables.

9. For optimum response time, create an index for every column in a table.
Correct answer : FALSE
Each index created uses extra storage space and requires overhead maintenance; this may slow retrieval response times.

10. Expressions are mathematical manipulations of the data in a table.
Correct answer : TRUE
Expressions may also use stored functions, such as SUM or AVG.

11. To find a range of values, use the THROUGH keyword.
Correct answer : FALSE
Use the > and < operators or the keywords BETWEEN and NOT BETWEEN.

12. When aggregate functions are used in a GROUP BY clause and several values are returned, they are called vector aggregates.
Correct answer : TRUE
Single values returned are called scalar aggregates.

13. The DML commands are used to define a database.
Correct answer : FALSE
The DDL commands are used to define a database.

14. To keep duplicate values from being returned use the DISTINCT keyword.
Correct answer : TRUE
DISTINCT and its counterpart, ALL, come directly after the keyword SELECT.

15. AND, OR, and IF are Boolean operators.
Correct answer : FALSE
IF is not a Boolean operator.

Back to the Top


Essay Questions

1. What are some positive aspects of using dynamic views?
Some positive aspects are simplified query commands, improved data security and confidentiality, and improved programmer productivity. Dynamic views contain the most current base table data, use little storage space, provide a customized view for the user, and establish physical data independence.

2. Why is it necessary to use caution when deciding to create a new index?
You may want to limit index creation because each index consumes extra storage space and requires overhead maintenance whenever indexed data change value.

3. What are the benefits of SQL standards?
Benefits can include reduced training costs, improved productivity, application portability and longevity, reduced dependence on single vendors, and improved cross-system communication.

4. What are some steps to follow when preparing to create a table?
1) Identify the datatypes for each attribute.
2) Identify columns that should accept null values.
3) Identify columns that should be unique.
4) Identify all primary key-foreign keys.
5) Determine any default values for attributes.
6) Identify any columns with validation/constraint rules.
7) Create the table and any desired indexes.

5. What can SQL do within an RDBMS?
SQL can be used to create tables, translate user requests, maintain the data dictionary and system catalog, update and maintain the tables, establish security, and carry out backup and recovery procedures.

Back to the Top


Additional Notes from Chapter 7 or Lecture

Most SQL data retrieval statements include the following

three clauses (key words):

SELECT : Lists the columns (including expressions involving columns) from base tables or views to be projected into the table that will be the result of the command. The general from is: TABLE.attribute. If more than one is required, a comma will inserted between them. Example:TABLE.attribute, TABLE.attribute
FROM : Identifies the tables or views from which columns will be chosen to appear in the result table, and includes the tables or views needed to join tables to process the query.
WHERE : Includes the conditions for row selection within a single table or view, and the conditions between tables or views for joining.
The first two are required but the third is necessary when only certain table rows are to be retrieved, or multiple table are to be joined.

Example where all three are required:

Query: Which products have a standard price of less than $275?
SELECT PRODUCT_NAME, STANDARD_PRICE
FROM PRODUCT_V
WHERE STANDARD_PRICE < 275;

Results:
PRODUCT_NAME STANDARD_PRICE
End table 175
Computer Desk 250
Coffee Table 200
clauses (key words):

INSERT: Used to populate tables.
If the table name is CUSTOMER_T...
Example: INSERT INTO CUSTOMER_T VALUES( list the values in the order, separated by comas, which are to be inserted, surround with parentheses, and then end with a semicolon );

DELETE: Used to delete data.
Example: DELETE FROM CUSTOMER_T

UPDATE: Used to update a change in data.
Example: UPDATE CUSTOMER_T

SET: Used in conjunction with update.
Example: UPDATE CUSTOMER_T SET UNIT PRICE = 775

CREATE: CREATE TABLE, CREATE SCHEMA, CREATE VIEW.

DROP: Used to reverse CREATE command.
Example: DROP TABLE, DROP VIEW, DROP SCHEMA.

ALTER: Used to make changes.
Example: ALTER TABLE may be used to change the definition of an existing base table by adding, dropping, or changing a column or by dropping a constraint.

DISTINCT: Used to get only one occurrence of a particular item.
Example: SELECT DISTINCT ORDER_ID

Mathematical manipulations

'+' for addition
'-' for subtraction
'*' for multiplication (also see WILDCARD)
'/' for division
'%' for modulo (also see WILDCARD)

WILDCARD:

* : Astrisk (*) can be a wildcard in a SELECT statement
Example: SELECT*, where * is used as a wildcard to indicate all columns, displays all columns from all the tables or views in the FROM clause.
% : Can be used to represent any collection of character.
Example: Like '%Desk" when searching PRODUCT_DESCRIPTION will find all different types of desks in that database.

FUNCTIONS:
  • COUNT: Can be used to count the number of items.
  • MIN: Can be used with any data type.
  • MAX: Can be used with any data type.
  • SUM : Can be used only with numeric columns.
  • AVG : Can be used only with numeric columns.
COMPARISON OPERATORS:
  • Equal to: =
  • Greater than: >
  • Greater than or equal to: >=
  • Lesser than: <
  • Lesser than or equal to: <=
  • Not equal to: <>
  • Not equal to: !=
BOOLEAN OPERATORS:
  • AND: Joins two or more conditions and returns results only when all conditions are true.
  • OR: Joins two or more conditions and returns results when any conditions are true.
  • NOT: Negates an expression.
  • If multiple Boolean operations are used in an SQL statement, NOT is evaluated first, then AND, then OR.
RANGES:
  • BETWEEN: The comparison operators < and > are used to establish a range of values or the word BETWEEN.
    Example:
  • WHERE STANDARD_PRICE >199 AND STANDARD_PRICE <301;
    Another example giving the same results:
    WHERE STANDARD_PRICE BETWEEN 199 AND 301;
  • NOT BETWEEN: Adding NOT before BETWEEN in this query will return all the other products because their prices are less than $200 or more than $300 .
IN and NOT IN Lists:
  • IN: IN is useful in SQL statements that use subqueries, which will be covered in chapter 8.
  • NOT IN: The counter part of IN.
SORTING:
  • ORDER BY: Sorts the final results rows in ascending or descending (DESC) order.
  • GROUP BY: Groups rows in a intermediate results table where the values in those rows are the same for one or more columns.
  • HAVING: Can ONLY be used following a GROUP BY and acts as a secondary WHERE clause, returning only those groups which meet a specified condition (WHERE qualifies rows, whereas HAVING qualifies groups).


Links to Other Pages in this Website:

Back to the Top