Deck 9: Database Systems

Question 1

Given the relation below

X: $\begin{array} { l l l } \mathrm { A } & \mathrm { B } & \mathrm { C } \\\hline 2 & 5 & 7 \\3 & 3 & 6 \\4 & 4 & 2 \\5 & 2 & 2\end{array}$
What values will be retrieved by the following SQL statement?
SELECT A,B FROM X WHERE X.B = X.C

A) 2, 5
B) 3, 6
C) 2, 2
D) 5, 2

Answer 1

5, 2

Question 2

Given the two relations X and Y below

$\begin{array}{l}\text { X: } \quad \begin{array} { l l } \text { A } & \text { B } \\\hline 7 & \mathrm {~s} \\2 & \text { Z }\end{array}\\\\\mathrm { Y } :\quad \begin{array} { l l } \mathrm { C } & \mathrm { D } \\\hline \mathrm { t } & 3 \\\mathrm { r } & 2\end{array}\end{array}$
What value would be retrieved by executing the following SQL statement?
SELECT Y.C FROM X,Y WHERE X.A < Y.D

A) s
B) z
C) t
D) r

Answer 2

t

Question 3

Given the relation X below

X: $\begin{array} { l l l } \mathrm { A } & \mathrm { B } & \mathrm { C } \\\hline 2 & 5 & 7 \\3 & 3 & 3 \\4 & 3 & 2 \\5 & 2 & 8\end{array}$
What value will be extracted by the following query?
TEMP ? SELECT from X where B > C
RESULT ? PROJECT A from TEMP

A) 2
B) 3
C) 4
D) 5

Answer 3

4

Question 4

Given the relation X below


X: $\begin{array} { l l l } \mathrm { A } & \mathrm { B } & \mathrm { C } \\\hline 2 & 5 & 7 \\3 & 3 & 3 \\4 & 4 & 2 \\5 & 2 & 8\end{array}$
What value will be retrieved by the following query?
TEMP ? SELECT from X where B = C
RESULT ? PROJECT B from TEMP

A) 2
B) 3
C) 4
D) 5

Question 5

Which of the following relational operations is performed by the SQL statement below? SELECT A,B,C FROM X

A) SELECT
B) PROJECT
C) JOIN

Question 6

Which of the following file structures is associated with the problem of clustering?

A) Sequential
B) Indexed
C) Hash

Question 7

Which of the following data mining techniques would be applied when trying to identify traits that predict whether a citizen in a democracy will or will not vote?

A) Class description
B) Class discrimination
C) Cluster analysis
D) Association analysis

Question 8

Which of the following relational operations extracts entire columns from a relation?

A) SELECT
B) PROJECT
C) JOIN

Question 9

Which of the following relational operations combine data from more than one relation?

A) SELECT
B) PROJECT
C) JOIN

Question 10

Which of the following file structures requires a small "auxiliary" storage system that must be updated as entries in the file are inserted or deleted.

A) Sequential
B) Indexed
C) Hash

Question 11

Which of the following relational operations extracts entire rows from a relation?

A) SELECT
B) PROJECT
C) JOIN

Question 12

Which of the following features within a DBMS is not provided to maintain database integrity?

A) Concurrent transaction processing
B) Log
C) Locking protocol
D) Commit points

Question 13

Which of the following file structures is commonly used for the storage of text files?

A) Sequential
B) Indexed
C) Hash

Question 14

Which of the following accesses a database in terms of a database model?

A) Application software
B) Database management system
C) Actual database

Question 15

Which of the following data mining techniques would be applied when trying to identify any underlying heterogeneity within housing patterns in a community?

A) Class description
B) Class discrimination
C) Cluster analysis
D) Association analysis

Question 16

Which of the following data mining techniques would be applied when trying to identify traits that characterize the citizens of a democracy who fail to vote?

A) Class description
B) Class discrimination
C) Cluster analysis
D) Association analysis

Question 17

Which of the following is not a potential problem caused by multiple transactions manipulating a database at the same time?

A) Lost update problem
B) Clustering
C) Deadlock
D) Incorrect summary problem

Question 18

Which of the following file structures is most efficient in cases in which the file is always processed in its entirety a predetermined order?

A) Sequential
B) Indexed
C) Hash

Question 19

Which of the following describes only the portion of a database available to a particular user?

A) Database model
B) Schema
C) Subschema
D) DBMS

Question 20

Which of the following data mining techniques would be applied when trying to identify common properties between different groups of shoppers?

A) Class description
B) Class discrimination
C) Cluster analysis
D) Association analysis

Question 21

Given the two relations X and Y below

what values would be retrieved by executing the following statement?
SELECT X.A,X.B,Y.C
FROM X,Y
WHERE X.A < Y.D

Question 22

Which of the operations SELECT,PROJECT,and JOIN are actually used when executing the following SQL instruction?
SELECT A,B
FROM X
WHERE C = D

Question 23

Place an X in the space before those requests below that require data mining techniques rather than traditional database techniques.Leave the other spaces blank.
_____ Identify all shoppers who bought dog food last week.
_____ Identify items that tend to be purchased by common shoppers.
_____ Identify any correlation between time-of-day and items purchased.
_____ Identify the items purchased during the first hour after opening the store.

Question 24

Identify two database models.
____________________

Question 25

List four data mining techniques.
_____________________
_____________________
_____________________
_____________________

Question 26

In a relational database,information is presented as though it were stored in tables called
_________________ ,each of which has columns called _________________ and rows
called _________________ .

Question 27

Suppose you were going to construct a hash file with 20 to 25 buckets using the division hash function discussed in the text.How many buckets should you actually use?

Question 28

Given the relation Employees containing the attributes Name,Address,and BirthDate,what question is answered by the following sequence of operations?
Temp ? SELECT from Employees where BirthDate < "January 4,1975"
Result? PROJECT Name from Temp

Question 29

Place an X in the space before those questions whose answers might be obtained by means of sequential pattern analysis.Leave the other spaces blank.
_____ What are progressing stages in youthful behavior that led to criminal activity?
_____ What are the symptoms of the various stages of a particular progressing disease?
_____ What crimes does a particular judge punish most harshly?
_____ What items had the highest sales volume last week?

Question 30

Suppose a relation X had the attributes Name,EmployeeID,and Address.Complete the following statement to obtain a list of the names and addresses of all employees.
Result ← ___________________________________ from X

Question 31

Place an X in the space before those structures that are designed to provide efficient access to randomly chosen items.Leave the other spaces blank.
_____ Sequential file
_____ Indexed file
_____ Hash file
_____ Hash table

Question 32

Suppose a relation X had the attributes PartID (part identification number),StockGoal (quantity held when fully stocked),and CurrentQuantity (quantity actually in stock).Complete the following statement to obtain information about those parts that are not fully stocked.
Result ? SELECT from X where

Question 33

Given the relation Employees containing the attributes Name,Address,and CurrentJobID and the relation Jobs containing the attributes JobID,SkillRating,Department,what question is answered by the following SQL statement?
SELECT SkillRating
FROM Employees,Jobs
WHERE Employees.Name = "Joe Smith"
AND Employees.CurrentJobID = Jobs.JobID

Question 34

Place an X in the space before those issues that are not problems associated with data mining.Leave the other spaces blank.
_____ Cascading rollback
_____ Deadlock
_____ Incorrect summary problem
_____ Lost update problem

Question 35

Given the three relations X,Y,and Z below

what values would be retrieved by executing the following statement?
SELECT X.B,Y.C,Z.F
FROM X,Y,Z
WHERE X.A > Y.D AND X.A = Z.E

Question 36

Place an X in the space before those questions whose answers might be obtained by means of outlier analysis.Leave the other spaces blank.
_____ Which are the flawed parts on a production line conveyor belt?
_____ What items have not sold during the last two days?
_____ What sales region generated the most orders over the last sales period?
_____ Which shoppers in a busy shopping mall are potential shoplifters?

Question 37

Given the two relations X and Y below
$\begin{array}{l}\text { X: }&\begin{array}{ll}\mathrm{A} & \mathrm{B} \\\hline 2 & \mathrm{~s} \\5 & \mathrm{Z}\end{array}&\text { Y: }&\begin{array}{ll}\mathrm{C} & \mathrm{D} \\\hline \mathrm{t} & 1 \\\mathrm{r} & 3 \\\mathrm{~W} & 2\end{array}\end{array}$
what values would be in the tuple produced by the following statement?
Result ? JOIN X and Y where X.A < Y.D

Question 38

The term "lossless decomposition" refers to a decomposition of one relation into several relations such
that _____________________________________________ .

Question 39

Given the two relations X and Y below

what values would be in the tuple produced by the following statements?
Temp ? JOIN X and Y where X.A = Y.D
Result ? PROJECT X.B,Y.C from Temp

Question 40

Given the relation Employees containing the attributes Name,Address,and CurrentJobID and the relation Jobs containing the attributes JobID,SkillRating,Department,what question is answered by the following sequence of operations?
Temp1 ? JOIN Employees and Jobs
where Employees.CurrentJobID = Jobs.JobID
Temp2 ? SELECT from Temp1 where Department = "Personnel"
Result ? PROJECT Name from Temp2

Question 41

Given the relation Parts containing the attributes PartName,PartNumber,and SupplierID as well as the relation Suppliers containing the attributes SupplierID,Address,FaxNumber,write a sequence of SELECT,PROJECT,and JOIN operations to obtain the supplier identifications and fax numbers for all the suppliers that supply the part whose part number is X4J26.

Question 42

What does it mean to say that an object is persistent?

Question 43

Describe the wound-wait protocol.

Question 44

Describe a scenario in which computing the total deposits in a bank while also transferring $100 from account X to account Y would result in a final sum that is $100 too great.Then describe a scenario in which the final sum turns out to be $100 too small.

Question 45

Given a relation called People whose attributes are Name,Father,and Mother (containing each person's name as well as the name of that person's parents),write an SQL statement to obtain a list of all the children of Nathan.

Question 46

Describe the distinction between a traditional database and a data warehouse.

Question 47

Given the relation Parts containing the attributes PartName,PartNumber,and SupplierID as well as the relation Suppliers containing the attributes SupplierID,Address,FaxNumber,write an SQL statement to obtain the supplier identifications and fax numbers for all the suppliers that supply the part whose part number is X4J26.

Question 48

Translate the following query into a single SQL statement.
Temp ← SELECT from X where A = B
Result ← PROJECT A,C from Temp

Question 49

What information is available from a relational database containing one relation with the attributes Name,Employee identification number,and Address that is not available from a database containing two relations,one with attributes Name and Address and the other with attributes Address and Employee identification number? Explain your answer.

Question 50

Given the two relations X and Y below

draw the relation Result that would be produced by the following statements?
Temp ← JOIN X and Y where X.A > Y.D
Result ← PROJECT X.B,Y.C from Temp

Question 51

Describe the distinction between class description and cluster analysis.

Question 52

If a database contained a relation containing information about individual people (name,address,birthday,etc.)and another relation containing information about music composers (name,style,education level,etc.),how would you extend the database's design to include links between each individual and his or her favorite composer?