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