29/29 Questions Answered TIME REMAINING

Saved at 6:06 PM :

Midterm Exam
STUDENT NAME

Search students by name or email… 

Q1 Preliminaries (MUST READ - REALLY!)

1 Point

Instructions

The allowed time for the exam this time will be 80 minutes. Be sure to pay
attention to the time and to budget your exam time accordingly! (Also, don't
forget to periodically save your answers.)

The exam is open pre-prepared cheat sheet, open book, open notes, open
web browser, and even open data platforms. However, you are not allowed to
communicate with or otherwise interact with any other students (or friends)
during the course of the exam, and this includes your HW brainstorming buddy.
This exam is to be a solo effort!

Read each question carefully, in its entirety, and then answer each part of the
question.

If you don't understand something, make your best guess; if you find
ambiguities in a question, note the interpretation that you are taking.
Acknowledgement: I certify that I am taking this exam myself, on my own, with
honesty and integrity, without interaction with others during the exam, and
without having obtained any information about the exam's content from others
prior to taking it.

 True
 False

A Running Example

This being a database class, we'll need some data for the exam! To that end,
we'll use the following relational schema:

Here is a tiny handful of relational example data for questions involving queries
and/or their results:
Here is the same data in JSON form (e.g., for MongoDB):

NOTE: You needn't need to load any of this data into anything for this exam!
You should be able to look at each question and the sample data and then
answer based on your experience and knowledge. The intent here is for you to
think, applying the principles that you've learned, and not to need to use the
different systems to answer the questions. (You may want to pdf-print or
screen-shot the example data so that you can refer to it in another window
during the exam. Do not do try opening the exam twice in different windows, as
that may end badly if you do a "save" in the wrong window!)

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q2 Executive Briefing
24 Points
Choose the best alternative from among the offered answers to each of the
following questions.

Q2.1
3 Points

Is the following a legal JSON value?

 Yes
 No

 Correct

Save Answer Last saved on May 12 at 6:02 PM

Q2.2
3 Points

Consider the following JSON object. If MongoDB were used to store the
sample JSON data from our running example in two collections named
customers and orders, would MongoDB allow the insertion of this object into
orders?

 Yes
 No

 Correct

Save Answer Last saved on May 12 at 6:02 PM

Q2.3
3 Points

Now consider the relational version of our running example. Suppose that the
real data is several TB in size, including 500GB of customer records, and that
the following SQL query must be run frequently:

SELECT C.zipcode, COUNT(*)

FROM customers C JOIN orders O on C.custid = O.custid
GROUP BY C.zipcode;

Suppose the data is currently stored in a parallel RDBMS with 5 nodes. If this
query is currently too slow by a factor of two, and the number of customers is
expected to double by year-end, about how many nodes should the system
aim to have in order to meet the company's storage and speed requirements at
the end of the year?

5
 10
 15
 20
 25

 Correct

Save Answer Last saved on May 12 at 6:02 PM

Q2.4
3 Points

Many parallel RDBMSs use parallel hash joins to process large join queries
such as the one in the previous question. What happens when the sizes of the
input tables are such that each node's share of the smaller table is larger than
the amount of main memory available on a node?
 the system will crash with an out-of-memory error
 the hash join will run more slowly
 the hash join won't run, so a different algorithm will be used

 Correct

Save Answer Last saved on May 12 at 6:02 PM

Q2.5
3 Points

Suppose our parallel RDBMS uses hash-based partitioning on each table's

primary key to distribute its data to the nodes in the cluster. Assuming that
customer names are mostly unique, which of the following indexing choices
would likely be best for a query like the following SQL query:

SELECT * FROM customers C WHERE C.name = 'T. Selleck';

 a global secondary index on customers(name)

 a local secondary index on customers(name)
 no index

 Correct

Save Answer Last saved on May 12 at 6:02 PM

Q2.6
3 Points

And which indexing choice would likely be best for a SQL query like:

SELECT COUNT(*) FROM customers C WHERE C.zipcode LIKE '92%';

 a global secondary index on customers(zipcode)
 a local secondary index on customers(zipcode)
 no index

 Correct

Save Answer Last saved on May 12 at 6:03 PM

Q2.7
3 Points

A NoSQL system's approach to replication can be peer-to-peer (P2P) based or

primary/secondary (P/S) based. Write quorums only pertain to P2P replication
architectures since applications in a P/S architecture only write to the primary
node.

 True
 False

 Correct

Save Answer Last saved on May 12 at 6:03 PM

Q2.8
3 Points

Redis is most accurately classified as a:

 main-memory cache
 key-value server
 data structure server
 document store
 grocery store

 Correct
 Save Answer Last saved on May 12 at 6:03 PM

Q3 SQL Revisited
12 Points

Show what each of the following analytical SQL queries would print (including
the output column names) if they were run against the sample relational tables
from our running example. The exact format of your answer isn't critical;
something like the following CSV-like format would be just fine:

eno,ename,dept,salary
1,"Sue","Software",110000
2,"Fred","Hardware",95000

Q3.1
8 Points

"city","street","numcusts"
NULL,NULL,5
"St. Louis, MO",NULL,3
"St. Louis, MO","360 Mountain Ave.",2
"St. Louis, MO","201 Main St.",1
"Rome, Italy",NULL,1
"Rome, Italy","Via del Corso",1
"Boston, MA",NULL,1
"Boston, MA","120 Harbor Blvd.",1

Save Answer Last saved on May 12 at 6:06 PM

Q3.2
4 Points
 "rank","custid","name"
1,"C01","B. Pitt"
2,"C05","A. Jolie"
3,"C02","T. Cruise"
3,"C04","T. Hanks"
5,"C03","S. Loren"

Save Answer Last saved on May 12 at 6:06 PM

Q4 ERror Prone Data

11 Points

An ER model just turned up, and something seems amiss when comparing it to
the sample data. The designers who gave us our running example's sample
data and its schema may have been a little sloppy, perhaps? Let's explore how
the customer information might have ended up looking if it had originated from
a proper ER design process. Suppose that the following ER diagram had
resulted from a first interview with the the data's stakeholders:

Further suppose that their application sometimes needs to ask queries about
all customers, sometimes about just US customers, and sometimes just about
rated customers.
Q4.1
2 Points

Take a careful look at the sample customer data. Its design is actually pretty
close to one of the standard design patterns for translating ER designs
involving inheritance into relational schemas.

Which design pattern is it close to?

 delta tables
 union tables
 mashup tables

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q4.2
2 Points

One or more columns are missing from the current customers schema in order
to properly capture the ER design and meet the application's query needs.
Use the SQL syntax ALTER TABLE tabname ADD colname coltype; to fix the
problem:

ALTER TABLE customers ADD type VARCHAR(40);

-- or --
ALTER TABLE customers ADD isUS CHAR(1);
ALTER TABLE customers ADD isRated CHAR(1);

Save Answer Last saved on May 12 at 6:06 PM

Q4.3
2 Points

Is it possible for a particular customer entity to be just a plain Customer

according to the given ER schema?
 Yes
 No

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q4.4
3 Points

Look carefully at the sample data. Do all of the sample customers satisfy the
constraints implied by the ER schema? Answer by identifying any/all rows that
are in violation of the ER schema as it's currently specified.

C01

 C02

C03

 C04

 C05

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q4.5
2 Points
Okay, it's time to "use the force" -- i.e., engage your brain and your common
sense. Assume that the sample data is right and that something went wrong
when the initial stakeholder interview transcript was translated into ER form.
Which of the following aspects of the given ER diagram makes the least sense
of all and thus needs to be changed?

 custid as primary key

 non-optionality of name
 disjoint IsA
 covering IsA
 optionality of rating
 optionality of zipcode

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q5 Insistent on Consistent?
12 Points

Some NoSQL systems provide only record-level atomicity -- operations on

different records are essentially separate transactions in the SQL transaction
sense. In addition, they have to deal with consistency of replicated data.
Answer each of following questions related to this brave new consistency
world. To seed the questions, suppose the following sequence of CQL updates
and queries are run by different client programs (shown in the three columns
below) against the customer table at the indicated times (ti's). Assume that the
data at time t0 has the values listed in our running example. Also assume that
the system containing the data is peer-to-peer, like Cassandra, and has the
replication level set to maintain 3 copies of the data.

t0: (initial customers' states based on running example data)

t1: UPDATE customers
SET rating = 500
WHERE custid = 'C01';

t2: UPDATE customers

SET rating = 700
WHERE custid = 'C03';

t3: SELECT SUM(rating) FROM

customers;

Q5.1
2 Points

What would the output of the SELECT query be if it were run at time t0, before
any of the updates, instead of at time t3 as shown?

 null
0
 2925
 3000
 3425
 3500
 4000

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q5.2
4 Points

What could the output of the SELECT query be at time t3 if all of the operations
were done under eventual consistency? E.g., assume that the UPDATEs and
the SELECT are each done at consistency level CL=1 in Cassandra.
 null

 2925

 3000

 3425

 3500

4000

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q5.3
4 Points

What could the output of the SELECT query be at time t3 if all of the operations
were done under strong consistency? E.g., assume that the UPDATEs and the
SELECT are each done at consistency level CL=ALL in Cassandra.
 null

2925

3000

3425

 3500

4000

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q5.4
2 Points

Are there any other consistency level choices that the UPDATEs and SELECT
could have used to achieve strong consistency for the SELECT in Cassandra?

 Yes
 No

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q6 The Key(s) to Cassandra

20 Points

Consider again the relational schema for our running example:

We can move this data to Cassandra by appropriately adjusting the data types
(e.g., VARCHAR(40) --> TEXT, INTEGER --> INT, and so on) and removing the
NOT NULL constraints since CQL doesn't support those. Suppose we do that,
keeping the schemas and keys otherwise the same. We could then insert our
sample data, which as a reminder looks as follows:

Now let's consider how Cassandra will respond to some queries with the keys
specified above as well as with some other possible alternatives.

Q6.1
3 Points

SELECT * FROM items WHERE price < 20.00;

 this query will run "as is"
 this query will run iff "ALLOW FILTERING" is added
 this query will not run with this table design
 this query will not run at all in Cassandra

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q6.2
3 Points

SELECT orderno, itemno, price FROM items;

 this query will run "as is"

 this query will run iff "ALLOW FILTERING" is added
 this query will not run with this table design
 this query will not run at all in Cassandra

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q6.3
3 Points

SELECT * FROM items i, orders o WHERE i.orderno = o.orderno;

 this query will run "as is"

 this query will run iff "ALLOW FILTERING" is added
 this query will not run with this table design
 this query will not run at all in Cassandra

 Correct
 Save Answer Last saved on May 12 at 6:06 PM

Q6.4
3 Points

SELECT * FROM items WHERE orderno = 101 ORDER BY price;

 this query will run "as is"

 this query will run iff "ALLOW FILTERING" is added
 this query will not run with this table design
 this query will not run at all in Cassandra

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q6.5
8 Points

One of the application developers is developing a shipping-related web page

for their order management application that needs the answer to
parameterized queries of the following form; the parameter (an order date of
interest) is shown as a ? in the query:

Write a complete Casssandra CREATE TABLE statement that will meet the
needs of this developer by filling in all the missing details (i.e., the . . .'s) in the
partial solution below. (Hint: Cutting and pasting and then editing will save you
time here! :-))

CREATE TABLE orderinfo (

orderno INT,
custid TEXT,
 ship_date TEXT,
itemno INT,
price DECIMAL,
qty INT,
...
PRIMARY KEY (( . . . ) . . . )
) WITH CLUSTERING ORDER BY ( . . . );

CREATE TABLE orderinfo (

orderno INT,
custid TEXT,
ship_date TEXT,
itemno INT,
price DECIMAL,
qty INT,
order_date TEXT,
PRIMARY KEY ((order_date), orderno, price, itemno)
) WITH CLUSTERING ORDER BY (orderno ASC, price DESC)

Save Answer Last saved on May 12 at 6:06 PM

Q7 Did You Say Mongo, or Mangle?

20 Points

It's time to make sure your MongoDB knowledge isn't mangled, which means
that the JSON version of our running example will now be the focus:

Suppose that these customers and orders have been loaded into a pair of
MongoDB collections.
Q7.1
5 Points

Which of the following are true statements about MQL's support for joins:

joins are supported in both the find and aggregate MQL APIs

joins are not supported in MQL

 joins are supported via the aggregation pipeline's $lookup operator

joins in MQL are roughly equivalent to FULL OUTER JOINs in SQL

joins work when both argument collections are large and sharded

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q7.2
3 Points

MongoDB's architectural approach to replication and consistency is:

 peer-to-peer with synchronous write replication to peers

 peer-to-peer with asynchronous write replication to peers
 primary/secondary with synchronous write replication to secondaries
 primary/secondary with asynchronous write replication to secondaries

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q7.3
8 Points
Which of the following pymongo MQL snippets would lead Atlas to compute an
output equivalent to what the following SQL query would print:

SELECT custid, rating FROM customers WHERE name = 'A. Jolie';

Indicate all snippets from the above that are equivalent:

MQL snippet 1

MQL snippet 2

 MQL snippet 3

 MQL snippet 4

 Correct

Save Answer Last saved on May 12 at 6:06 PM

Q7.4
4 Points

Consider the following pymongo MQL aggregation pipeline snippet:

Show the JSON result that would be produced by dumping the cursor from this
MQL snippet using the sample JSON data:

[{"rating": null}, {"rating": 750}, {"rating": 800}, {"rating": 625}]

Save Answer Last saved on May 12 at 6:06 PM

Save All Answers Submit & View Submission 