The University of Texas

CS 327e Fall 2002 Test 1

NAME: __________________________________ LAST 4 DIGITS of SSN: __________________

Date: 2002-10-04 Duration: 60 mins Total: 19 Qs (100 pts), 1 bonus Q (4 pts).

For the multiple-choice questions 1 through 15, the correct answer is in Bold. Each multiple-choice question carries 4 points.

1) The final outcome of a natural JOIN yields a table that does not include un-matched pairs.

a. | True.
b. | False.

2) In a relational table, each column represents an attribute, and each column has a distinctive name.

a. | True.
b. | False.

3) The referential integrity rule requires that

a. | Every null foreign key value must reference an existing primary key value.
b. | It makes it possible for an attribute to have a corresponding value.
c. | Every non-null foreign key value must reference an existing primary key value.
d. | It makes it possible to delete a row in one table whose primary key does not have a matching foreign key value in another table.

4) A Relational operator that yields values from all rows in a table is known as a

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

5) A Relational operator that allows for the combination of information from two or more tables is known as a

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

6) A primary key that consists of more than one field is called a

a. | Foreign key.
b. | Secondary key.
c. | Group key.
d. | All of the above
e. | None of the above

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

a. | Referential integrity.
b. | Entity integrity.
c. | Enforced integrity.
d. | All of the above

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. | Referential
b. | Restrictive
c. | Secondary
d. | Redundant

9) Database designers employ data models as communications tools to facilitate the interaction among

a. | The designers.
b. | The applications programmers.
c. | The users.
d. | All of the above.

10) Knowing the ______________ number of entity occurrences is very helpful at the application software level.

a. | Maximum
b. | Minimum
c. | Exact
d. | Maximum and Minimum

11) The term first normal form (1NF) describes the tabular format in which:

a. | All the key attributes are defined.
b. | There are no repeating groups in the table. Row/column intersection can contain one and only one value, not a set of values.
c. | All attributes are dependent on the primary key.
d. | All of the above
e. | None of the above

12) A table that is in 1NF and includes no partial dependencies only is said to be in

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

13) When making corrections you would use the following command

a. | 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) A strong entity results in a weak relationship, and vice versa.

a. | True.
b. | False.

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

T3 is in 1NF, T2 is in 2NF, and T1 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 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 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 such a row). Assume none of the columns in all of the tables allow NULLs.

Draw the ERD for this schema. Clearly show the attributes, relationships, entities, primary key, and cardinality. Choose any model for notation. Clearly state any assumptions.

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 parts to projects being implemented in Athens.

a) (5 points) Write the SQL.

SELECT SUPPLIER.NAME

FROM SUPPLIER, SHIPMENT, PROJECT

WHERE

SUPPLIER.SID = SHIPMENT.SID AND

PROJECT.JID = SHIPMENT.JID AND

PROJECT.CITY = 'Athens'

Another acceptable solution:

SELECT DISTINCT(SUPPLIER.NAME)

FROM SUPPLIER, SHIPMENT, PROJECT

WHERE

SUPPLIER.SID = SHIPMENT.SID AND

PROJECT.JID = SHIPMENT.JID AND

PROJECT.CITY = 'Athens'

b) (5 points) Draw the resulting table.

NAME

Smith

Jones

Jones

Clark

Adams

Adams

Adams

Adams

Adams

Adams

Another acceptable solution:

NAME

Adams

Clark

Jones

Smith

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

a) (5 points) Write the English statement corresponding to the following SQL query:

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

FROM PART, SHIPMENT

WHERE

PART.PID = SHIPMENT.PID

AND PART.COLOR = 'Blue'

GROUP BY PART.NAME

For each shipping blue part, show the name of the part along with its shipping quantity.

b) (5 points) Draw the resulting table.

NAME TOTALQUANTITY

Cam 1100.0

Screw 3500.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 total quantity of all shipments for which the suppliers, parts, and projects belong to the same city.

SELECT SHIPMENT.SID, SHIPMENT.PID, SHIPMENT.JID, SUM (SHIPMENT.QTY) AS TOTALQUANTITY

FROM SHIPMENT, PART, PROJECT, SUPPLIER

WHERE

SHIPMENT.SID = SUPPLIER.SID AND

SHIPMENT.PID = PART.PID AND

SHIPMENT.JID = PROJECT.JID AND

SUPPLIER.CITY = PART.CITY AND

PART.CITY = PROJECT.CITY

GROUP BY SHIPMENT.SID, SHIPMENT.PID, SHIPMENT.JID

BONUS QUESTION:

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

How many rows of data would be returned by the following query?

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

FROM PART, SHIPMENT

WHERE

PART.PID = SHIPMENT.PID

AND ((PART.COLOR = 'Blue') OR (PART.COLOR = 'Red'))

GROUP BY PART.NAME

HAVING SUM (SHIPMENT.QTY) > 4000

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