Data Management: Chapter 10 exercises

2.

What does it mean to say “SQL is a complete database language?"

SQL is a complete database language because it is both a data definition language (DDL) and a data manipulation language (DML). It can create a relational database, query it, and manipulate the data in the database.

4.

List some operational advantages of a relational DBMS.

One of the major operational advantages of a relational DBMS is that DDL statements (data definition statements such as creating a table) can be executed at any time. Earlier DBMSs often require halting the entire system to create a new table, views, and indexes.

6.

What is the difference between a primary key and a unique key?

The primary key is used to uniquely identify any record of a table and is typically the primary index by which a table is searched. A unique key also uniquely identifies a table, but it is only used for additional indexes to the table.

8.

Consider the three choices for the ON DELETE clause associated with the foreign key constraint. What are the pros and cons of each option?

The three choices for the ON DELETE command are: restrict, cascade, set null. The restrict option does permit deletion of a primary key when there are matching foreign keys in a related table. The cascade option automaticallydeletes all rows in a related table with matching foreign keys. The set null option sets all corresponding rows to null. The following table shows the advantages and disadvantages of all three options.

Option	Advantages	Disadvantages
RESTRICT	* referential integrity * no accidental loss of records	* update is cumbersome: each value of the corresponding foreign key must be deleted before one value of a primary key can be deleted
CASCADE	* referential integrity * convenient update	* all related records are lost, some data might be lost accidentally
SET NULL	* convenient update * no accidental loss of records	* referential integrity: possibly many useless records with null values as foreign key

12.

Give some reasons for creating a view.

A view can be created for a variety of reasons:

to restrict access to certain rows or columns
to handle derived data (data conversion)
to avoid writing common SQL queries

14.

Write SQL codes to create a unique index on firm name for the shr table defined in Chapter 3. Would it make sense to create a unique index for PE ratio in the same table?

CREATE UNIQUE INDEX firmname ON shr (shrfirm);

a. No, it would not make sense to create a unique index for PE since two companies might have the same PE.

16.

What is the difference between an equijoin and a natural join?

An equijoin table contains two identical columns. If one of these two columns is dropped, the remaining table is called a natural join.

18.

What function would you use for the following situations?

18a.

Computing the total value of a column.

SUM

18b.

Finding the minimum value of a column.

MIN

18c.

Counting the number of customers in the customer table.

COUNT

18d.

Displaying a number with specified precision.

DECIMAL(n,p,d)

18e.

Reporting the month part of a date.

MONTH

18f.

Displaying the second part of a time.

SECOND

18g.

Retrieving the first five characters of a city's name.

SUBSTR(cityname,1,5)

18h.

Reporting the distance to the sun in feet.

distance* 5280 (assuming distance is in miles)

20.

Write SQL statements for the following:

20a.

Let Hui-Tze query and add to the nation table.

GRANT SELECT, INSERT ON nation TO hui-tze;

20c.

Remove all of William's privileges.

REVOKE ALL ON tablename FROM william;

20d.

Give Chris permission to grant other users authority to select from the address table.

(This has to be repeated for each table for which William has been granted privileges)

GRANT SELECT ON address TO chris WITH GRANT OPTION;

20f.

List all the tables owned by user CMS5432.

SELECT tname FROM system.syscatalog WHERE creator = 'CMS5432';

20h.

Find all columns that have a data type of SMALLINT .

SELECT tname, cname FROM system.syscolumns WHERE COLTYPE ='SMALLINT';

22.

How do procedural programming languages and SQL differ in the way they process data? How is this difference handled in an application program? What is embedded SQL?

SQL processes one table at a time while COBOL processes one record at a time. Thus, COBOL uses a loop to process each record of a table.

Chapter 10

Answers to exercises