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

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

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

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