The University of Texas

CS 327e Spring 2003 Test 1

NAME: __________________________________ LAST 4 DIGITS of SSN: __________________

Date: 2003-02-21 Duration: 60 mins Total: 19 Qs (100 pts), 1 bonus Q (2 pts).

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

For all questions, the answers are marked in bold.

1) Relationships are always WEAK when they are created between optional entities.

a.	True.
b.	False.

2) A table, that does not have a composite primary key i.e. its PK is made up of 1 column, and is in 1NF, is

a.	Not in 2NF.
b.	Also in 2NF.
c.	Can never hope to be in 3NF.
d.	Is made up of wood.

3) A table is said to be in 3NF when

a.	It is in 2NF and does not contain partial dependencies.
b.	It is in 1NF and contains transitive dependencies.
c.	It is in 2NF and does not contain any transitive dependencies.
d.	It is dependent on its 3 legs, instead of all 4.

4) A Relational operator that yields all possible pairs of rows from 2 tables is known as

a.	Difference.
b.	Product.
c.	Select.
d.	Project.

5) A Relational operator that yields all values for selected attributes in a table is known as

a.	Select.
b.	Project.
c.	Product.
d.	Difference.
e.	Join.

6) A Natural Join is the result of a 3-stage process: PRODUCT, SELECT, and UNION.

a.	True.
b.	False.

7) Within a table, the foreign key must be unique so that it will identify each row. When this is the case, the table is said to exhibit Entity Integrity.

a.	True.
b.	False.

8) If a foreign key contains either matching values or nulls, the table(s) that makes use of such a foreign key is/are said to exhibit __________ integrity.

a.	Restrictive
b.	Referential
c.	Secondary
d.	National

9) When NULL values appear for unmatched values, as a result of a JOIN between 2 tables, the type of join could be

a.	Equi Join
b.	Left Outer Join
c.	Right Outer Join
d.	All of the above
e.	b. Or c.

10) A recursive relationship is often found in a binary relationship.

a.	True.
b.	False.

11) In a generalization hierarchy, the relationship between supertype and subtype is 1:1.

a.	True.
b.	False.

12) In a 1:M relationship, if both sides are mandatory, the following foreign key rules and properties apply:

a.	NOT NULL
b.	ON UPDATE CASCADE
c.	ON DELETE RESTRICT
d.	All of the above
e.	None of the above

13) When making corrections/modifications, you would use the following command

a.	PLEASE CHANGE PRODUCT SET P_INDATE = '01/18/2002' WHERE P_CODE = '13-Q2/P2';
b.	ROLLBACK PRODUCT SET P_INDATE = '01/18/2002' WHERE P_CODE = '13-Q2/P2';
c.	EDIT PRODUCT SET P_INDATE = '01/18/2002' WHERE P_CODE = '13-Q2/P2';
d.	UPDATE PRODUCT SET P_INDATE = '01/18/2002' WHERE P_CODE = '13-Q2/P2';

14) Both the Chen and Crow’s Foot models use rectangles to represent entities.

a.	True.
b.	False.

15) Consider 3 tables in a database: T1, T2, T3.

T1 is in 3NF, T2 is in 2NF, and T3 is in 3NF. Hence, the entire database is said to be in

a.	1NF.
b.	2NF.
c.	3NF.
d.	4NF.
e.	BCNF.

16) (10 points) Consider the following table in 1NF:

STUDENT (SSN, FirstName, LastName, Dept, Zip, State)

With the following functional dependencies:

FD1) SSN à FirstName, LastName, Dept, Zip, State

FD2) Zip à State

Decompose this table into table(s) that is (are) in 3NF. Clearly show the resultant table(s) along with their respective FDs.

NOTE: Since the table does not have a composite PK, it is already in 2NF.

STUDENT (SSN, FirstName, LastName, Dept, Zip)

FD1) SSN à FirstName, LastName, Dept, Zip

ZIPINFO (Zip, State)

FD2) Zip à State

17) Consider the suppliers-parts-projects database, sample data (same as lab1) for which is shown in the last page.

Give the names of suppliers who have shipped Blue parts.

a) (5 points) Write the SQL.

SELECT DISTINCT SUPPLIER.NAME

FROM SUPPLIER, PART, SHIPMENT

WHERE

SHIPMENT.PID = PART.PID

AND

SHIPMENT.SID = SUPPLIER.SID

AND

PART.COLOR = 'Blue'

b) (5 points) Draw the resulting table.

NAME

Adams

Blake

Jones

18) Consider the suppliers-parts-projects database, sample data (same as lab1) for which is shown in the last page.

(10 points) Draw the resulting table for the following query.

SELECT PART.NAME, SUM (SHIPMENT.QTY) AS TOTALQUANTITY

FROM PART, SHIPMENT

WHERE

PART.PID = SHIPMENT.PID

AND

(SHIPMENT.QTY > 300)

GROUP BY PART.NAME

HAVING SUM (SHIPMENT.QTY) > 800

NAME TOTALQUANTITY

Cam 900.0

Screw 4000.0

19) (10 points) Consider the suppliers-parts-projects database, sample data (same as lab1) for which is shown in the last page.

Write the SQL for the following English statement:

Determine the part name, along with the average quantity of its shipments, for all parts that weigh at least 15.0 pounds.

SELECT PART.NAME, AVG (SHIPMENT.QTY) AS AVERAGEQUANTITY

FROM PART, SHIPMENT

WHERE

PART.PID = SHIPMENT.PID

AND

PART.WEIGHT >= 15.0

GROUP BY PART.NAME

(The following is not required for the solution)

resulting in

NAME AVERAGEQUANTITY

Bolt 150.0

Cog 325.0

Screw 388.88888888888891

BONUS QUESTION:

(2 points) What does the “X” in IDEF1X stand for?

eXtended
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