The University of Texas

CS 327e Fall 2002 Test 2

NAME: __________________________________ LAST 4 DIGITS of SSN: __________________

Date: 2002-10-28 Duration: 60 mins Total: 12 Qs (100 pts), 1 bonus Q (4 pts)

For the multiple-choice questions 1 through 10, Use a pencil to circle the correct answer. Each multiple-choice question carries 5 points.

1) The American National Standards Institute prescribes a standard SQL - the most recent version is known as SQL-99.

a. | True.
b. | False.

2) The difference between a data mart and a data warehouse is only the size and scope of the problem being solved. The data definitions and data requirements are essentially the same for both.

a. | True.
b. | False.

3) The basic SQL aggregate function that gives the number or rows containing non-null values for the given column is

a. | COUNT
b. | MIN
c. | MAX
d. | SUM
e. | AVG

4) A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a data manipulation event

a. | A trigger is always associated with a database table.
b. | Each database table may have zero or more triggers.
c. | all of the above
d. | None of the above

5) Operational data storage differs from decision support in main areas which are:

a. | timespan
b. | granularity
c. | dimensionality
d. | all of the above
e. | none of the above

6) Data warehouse's components are

a. | integrated, and subject-oriented.
b. | time-variant and nonvolatile.
c. | a and b

7) A rule that defines the data warehouse is

a. | data warehouse and operational environments are separated and data are integrated.
b. | data warehouse contains historical data over a time horizon.
c. | a and b

8) ROLAP adds the following extensions to traditional RDMBS technology:

a. | multidimensional data schema support within the RDMBS.
b. | data access language and query performance are optimized for multidimensional data.
c. | support for small databases.
d. | a and c
e. | a and b

9) GRANT statements are used to give privileges on objects to users, whereas WITHDRAW statements are used to take away the privileges.

a. | True.
b. | False.

10) All VIEWS are not updateable.

a. | True.
b. | False.

For questions 11 and 12,

Consider the suppliers-parts-projects database, sample data (same as lab1) for which is shown in the last page. There are 4 tables containing relevant data:

1) SUPPLIER (SID, Name, Status, City)

2) PART (PID, Name, Color, Weight, City)

3) PROJECT (JID, Name, City)

4) SHIPMENT (SID, PID, JID, Qty)

Suppliers (S), parts (P), and projects (J) are uniquely identified (PK) by supplier id (SID), part id (PID), and project id (JID), respectively. The significance of an SPJ (shipment) row is that the specified supplier supplies the specified part to the specified project in the specified quantity (and the combination SID-PID-JID uniquely identifies (PK) such a row). Assume none of the columns in all of the tables allow NULLs.

Use the following SQL Server CREATE PROCEDURE syntax:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]

Use the following SQL Server CREATE TRIGGER syntax:

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}

Use the following SQL Server IF…ELSE syntax:

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

e.g.

`IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15`

`BEGIN`

`PRINT 'The following titles are excellent mod_cook books:'`

`SELECT SUBSTRING(title, 1, 35) AS Title`

`FROM titles`

`WHERE type = 'mod_cook'`

`END`

`ELSE`

`IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15`

`BEGIN`

`PRINT 'The following titles are expensive mod_cook books:'`

`SELECT UPPER(RTRIM (title)) AS Title`

`FROM titles`

`WHERE type = 'mod_cook'`

`END`

11) (30 points) Write a procedure that inserts a new supplier in the SUPPLIER table. The procedure will check for non-null input, and then return a new supplier id, along with a count of suppliers that already existed in the same (case-insensitive) city. Return a status of 0 for successful insert, and –1 for all other situations. When the status is –1, the supplier ID should be set to ‘Unknown’, the returned count of local suppliers to 0, and no INSERT statement should be executed.

Assume SUPPLIER table always exist, SID is the PK, and there are all privileges on this table for the user creating/executing this procedure. Assume all supplierIDs are of the form S1, S2, S3,…..etc. Assume no supplierIDs can be deleted or updated from the table. Assume all supplierIDs are in sequence as shown above. So, for example, if there are 4 supplierIDs already in the SUPPLIER table, you can assume that they will always be S1, S2, S3, S4; and hence the new supplierID should be S5. If there are no suppliers to begin with, the new supplierID should be S1. Hence, if there are “n” (where n is >= 1) suppliers already in the table, you can assume that the corresponding supplierIDs are S1, S2, S3, …. Sn.

Hence, for the data shown in the last page, if we execute the following statements on Query Analyzer in SQLServer:

DECLARE @mySupplierID nvarchar(10)

DECLARE @cntLocalSups int

DECLARE @myStatus int

EXEC INS_SUPPLIER 'Jane', 20, 'PaRIs', @mySupplierID OUTPUT, @cntLocalSups OUTPUT, @myStatus OUTPUT

IF @myStatus = 0

BEGIN

PRINT 'Your Supplier ID is : ' + @mySupplierID

PRINT 'There are ' + CAST(@cntLocalSups AS nvarchar(20)) + ' other suppliers in your city.'

PRINT 'Your INSERT statement was successful !'

END

ELSE

BEGIN

PRINT 'Your INSERT statement was not successful. Please check your input for non NULL values. '

END

GO

Should result in:

Your Supplier ID is : S6

There are 2 other suppliers in your city.

Your INSERT statement was successful !

And if you then executed SELECT * FROM SUPPLIER ORDER BY SID, the result would look like

SID Name Status City

S1 Smith 20.0 London

S2 Jones 10.0 Paris

S3 Blake 30.0 Paris

S4 Clark 20.0 London

S5 Adams 30.0 Athens

S6 Jane 20.0 PaRIs

Clearly state any assumptions.

HINT: You may need to use the CAST, RTRIM, and UPPER built-in functions, usage of which is shown above, syntax is shown below.

``

`1)` `CAST`

`CAST ( expression AS data_type )`

`Returns the “expression” converted as “data_type”`

2) RTRIM

RTRIM ( character_expression )

Returns a character string after truncating all trailing blanks.

3) UPPER

UPPER ( character_expression )

Returns a character expression with any (lower, upper, or mixed) case character data converted to uppercase.

Write the solution to this problem on next page.

CREATE PROCEDURE INS_SUPPLIER

@Name nvarchar (255),

@Status int,

@City nvarchar (255),

@SupplierID nvarchar(10) OUTPUT,

@cntLocalSuppliers int OUTPUT,

@myStatus int OUTPUT

AS

BEGIN

-- Initialize my output variables

SELECT @SupplierID = 'Unknown'

SELECT @cntLocalSuppliers = 0

SELECT @myStatus = -1

-- Validate non-null input

IF @Name IS NOT NULL

BEGIN

IF @Status IS NOT NULL

BEGIN

IF @City IS NOT NULL

BEGIN

-- Get the count of existing suppliers

DECLARE @cntExistingSuppliers int

SELECT @cntExistingSuppliers = COUNT(*) FROM SUPPLIER

-- Manufacture the new SupplierID

SELECT @SupplierID = 'S' + CAST ((@cntExistingSuppliers + 1) AS nvarchar (9))

SELECT @SupplierID = RTRIM (@SupplierID)

-- Get the count of local suppliers, before INSERTing the new one

SELECT @cntLocalSuppliers = COUNT(*) FROM SUPPLIER WHERE UPPER(RTRIM(CITY)) = UPPER(RTRIM(@City))

-- Everything is all set to do an INSERT

INSERT INTO SUPPLIER (SID, Name, Status, City) VALUES (@SupplierID, @Name, @Status, @City)

-- Finally set the successful status

SELECT @myStatus = 0

END -- City is not null

END -- Status is not null

END -- Name is not null

END

12) (20 points) Assume PKs and FKs are defined in the schema shown on last page (same schema in lab1).

Also, assume the following table exists:

SPJ_CHANGES (TableName nvarchar (20), Cnt_UPD int, Cnt_DEL int, Cnt_INS int)

Initially, the table contains following data:

TableName Cnt_UPD Cnt_DEL CntINS

PART 0 0 0

SUPPLIER 0 0 0

PROJECT 0 0 0

Write a trigger UPD_PID that updates the SPJ_CHANGES.Cnt_UPD, after a successful UPDATE to the PART.PID takes place. Assume all 5 tables always exist, and there are all privileges on all tables for the user creating this trigger.

Clearly state any assumptions.

SOLUTION:

CREATE TRIGGER UPD_PID

ON PART

AFTER UPDATE

AS

IF UPDATE (PID)

BEGIN

UPDATE SPJ_CHANGES

SET Cnt_UPD = Cnt_UPD + 1

WHERE UPPER(RTRIM(TableName)) = 'PART'

END

13) BONUS QUESTION (4 points):

Consider the data shown on the last page.

a) SELECT COUNT ('Paris') FROM Supplier;

What would be the resultant count returned from executing this query?

SOLUTION: 5

b) SELECT COUNT (DISTINCT 'Austin') FROM Supplier;

What would be the resultant count returned from executing this query?

SOLUTION: 1

SUPPLIER

SID | Name | Status | City
S1 | Smith | 20 | London
S2 | Jones | 10 | Paris
S3 | Blake | 30 | Paris
S4 | Clark | 20 | London
S5 | Adams | 30 | Athens

PART

PID | Name | Color | Weight | City
P1 | Nut | Red | 12.2 | London
P2 | Bolt | Green | 17.0 | Paris
P3 | Screw | Blue | 17.0 | Rome
P4 | Screw | Red | 14.0 | London
P5 | Cam | Blue | 12.0 | Paris
P6 | Cog | Red | 19.0 | London

PROJECT

JID | Name | City
J1 | Sorter | Paris
J2 | Display | Rome
J3 | OCR | Athens
J4 | Console | Athens
J5 | RAID | London
J6 | EDS | Oslo
J7 | Tape | London

SHIPMENT

SID | PID | JID | Qty
S1 | P1 | J1 | 200
S1 | P1 | J4 | 700
S2 | P3 | J1 | 400
S2 | P3 | J2 | 200
S2 | P3 | J3 | 200
S2 | P3 | J4 | 500
S2 | P3 | J5 | 600
S2 | P3 | J6 | 400
S2 | P3 | J7 | 800
S2 | P5 | J2 | 100
S3 | P3 | J1 | 200
S3 | P4 | J2 | 500
S4 | P6 | J3 | 300
S4 | P6 | J7 | 300
S5 | P2 | J2 | 200
S5 | P2 | J4 | 100
S5 | P5 | J5 | 500
S5 | P5 | J7 | 100
S5 | P6 | J2 | 200
S5 | P1 | J4 | 100
S5 | P3 | J4 | 200
S5 | P4 | J4 | 800
S5 | P5 | J4 | 400
S5 | P6 | J4 | 500