The University of Texas

CS 327e Spring 2003 Test 2

NAME: __________________________________ LAST 4 DIGITS of SSN: __________________

Date: 2003-03-28 Duration: 60 mins Total: 11 Qs (100 pts)

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

Each correct answer is marked in bold.

1) Procedural SQL allows you to use procedural code and SQL statements that are stored in the database.

a. | True.
b. | False.

2) Triggers can be used to call other stored procedures.

a. | True.
b. | False.

3) A trigger

a. | Is always associated with a view.
b. | Is always invoked after a row is deleted.
c. | Can never be used to enforce constraints.
d. | All of the above.
e. | None of the above.

4) A stored procedure

a. | Is a named collection of processes that need to be followed and stored in a refrigerator
b. | Reduces network traffic.
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. | Directionality
d. | a) And b)
e. | a) And c)

6) A rule that defines the data warehouse is

a. | Data warehouse data are mainly read-only with periodic batch updates from the operational data.
b. | Data warehouse contains historical data over a time horizon.
c. | a) And b)

7) The kinds of permissions that you can grant in sql server are:

a. | Server
b. | Database Object
c. | Database Statement
d. | All of the above
e. | None of the above

8) Facts are non-numeric measurements that represent a specific business aspect or activity.

a. | True.
b. | False.

9) Views can be built using other views.

a. | True.
b. | False.

10) Views can be used to enforce security.

a. | True.
b. | False.

For question 11,

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 IF…ELSE syntax (only if required):

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) (20 points) Write a procedure that lists the count of all the suppliers, parts, and projects that exist in a given (case-insensitive) city. The procedure will check for non-null input, and then return three counts:

NumSuppliers: Number of suppliers belonging to the city

NumParts: Number of parts belonging to the city

NumProjects: Number of projects belonging to the city

In addition, return a status of 0 for success, and –1 for all other situations. When the status is –1, the 3 counts should be set to 0.

Assume all SPJ tables exist. Note that for this procedure, the SHIPMENT table does not play any role.

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

DECLARE @mySuppliers int

DECLARE @myParts int

DECLARE @myProjects int

DECLARE @myStatus int

EXEC GET_SPJCOUNTS_FOR_CITY 'Athens', @mySuppliers OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

IF @myStatus = 0

BEGIN

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

PRINT 'There are ' + CAST (@myParts AS nvarchar (20)) + ' parts in your city.'

PRINT 'There are ' + CAST (@myProjects AS nvarchar (20)) + ' projects in your city.'

PRINT 'Your Procedure was successful!'

END

ELSE

BEGIN

PRINT 'Your Procedure was not successful. Please check your input for non-NULL values. '

END

GO

Should result in:

There are 1 suppliers in your city.

There are 0 parts in your city.

There are 2 projects in your city.

Your Procedure was successful!

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 GET_SPJCOUNTS_FOR_CITY

@City nvarchar (255),

@NumSuppliers int OUTPUT,

@NumParts int OUTPUT,

@NumProjects int OUTPUT,

@Status int OUTPUT

AS

BEGIN

-- Initialize output variables

SET @NumSuppliers = 0

SET @NumParts = 0

SET @NumProjects = 0

SET @Status = -1

-- Get Number of Suppliers belonging to @City

SELECT @NumSuppliers = COUNT (DISTINCT SID) FROM SUPPLIER

WHERE UPPER (RTRIM (SUPPLIER.City)) = UPPER (RTRIM (@City));

-- Get Number of Parts belonging to @City

SELECT @NumParts = COUNT (DISTINCT PID) FROM PART

WHERE UPPER (RTRIM (PART.City)) = UPPER (RTRIM (@City));

-- Get Number of Projects belonging to @City

SELECT @NumProjects = COUNT (DISTINCT JID) FROM PROJECT

WHERE UPPER (RTRIM (PROJECT.City)) = UPPER (RTRIM (@City));

-- Set Successful Status

SET @Status = 0

END

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