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 |