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

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

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