Test 2: Questions
[8 pts] Without changing the below HTML, write CSS rules accomplishing each of the following.
<ul>
<li class="hilite">Introduction</li>
<li>Body
<ul class="sub">
<li class="hilite">Point 1</li>
<li class="hilite">Point 2</li>
<li id="point3">Point 3</li>
</ul>
</li>
<li class="hilite">Conclusion</li>
</ul>
a. Color only the text “Point 1” and “Point 2” blue.
b. Display “Introduction”, “Body”,
and “Conclusion” only in italics. The easiest way
to do this is probably to make all text italic, and then to make
the sublist to be in regular text. (Use font-style: italic
and font-style: normal.)
[8 pts] Explain how AJAX-based sites differ from classical Web sites.
[8 pts] In Node.js, functions that may require blocking are designed with callback functions. For example, in accessing a database, most languages will use the first technique below, whereas Node.js uses the second technique.
// Traditional technique (not supported by Node.js)
var result = db.all("SELECT message FROM posts");
// (code handling result goes here)
// Callback technique (used by Node.js)
db.all("SELECT message FROM posts", function (result) {
// (code handling result goes here)
});
Why does Node.js use the callback technique rather than the more traditional technique?
[8 pts] Consider the following Node.js code accessing a database.
db.all("SELECT passwd FROM users WHERE login = '" + req.body.login + "'", // rest omitted
Testing shows that this works correctly under normal conditions. What is nonetheless wrong with it, and how can it be repaired?
[10 pts] For the relation R(a, b, c, d, e), convert the following FD basis to a minimal FD basis.
a b → c d e
a → c
c d → d e
[10 pts] Draw an entity-relationship diagram for a conference hall. The conference hall contains many rooms, each of which can be reserved by an organization for a time interval. The database will store the room number, room capacity, organization name, organization phone number, reservation date, the start and end time, and payment method. (You need not bother indicating keys.)
[10 pts] Below is an entity-relationship diagram for an encyclopedia. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.
[12 pts] Consider the following SQL query.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
login TEXT,
age INTEGER,
email TEXT
);
a. How does PRIMARY KEY lead the database management
system to change how the table is stored?
b. How does PRIMARY KEY constrain what can appear in the
id column of the table?
[8 pts] Write an SQL query that inserts a new user into a table users(id, login, age, email) with an ID of 20500, a login name of barack, an age of 52, and a NULL e-mail address.
[12 pts] For a table users(id, login, age, email), write an SQL query that lists all e-mail addresses that are registered to more than two users who are at least age 18.
[6 pts] Consider the following query intended to list all users with no e-mail address specified in the database.
SELECT login FROM users WHERE email = NULL
In what way will this query fail (i.e., what will the output be), and how can the query be repaired to work as intended?
Test 2: Solutions
| a. | ul.sub li.hilite { color: blue } |
| b. | ul#main { font-style: italic } |
In a classical Web site, information is only loaded from the server as the user navigates from one page to an entirely different page. In AJAX, by contrast, the JavaScript running as part of the page may request and receive information from the server “in the background” — without changing which page being displayed, though it may often change a portion of the displayed page upon receiving a response.
The traditional technique ends up having to wait at
db.all before being able to continue with the code. In a
multithreaded application, since while we are awaiting a
response from the DBMS a different thread may complete some
computation.
Node.js, however, is not multithreaded: All code executes in
one thread. (This allows Node.js to avoid the context-switching
and other overhead that comes with threads.)
If it followed the traditional technique, then,
the Node.js program could do nothing else while we are awaiting
a response from the DBMS. With the callback technique, however,
db.all can store the callback function somewhere as
something to be invoked later, and it can return immediately to
allow additional code in the Node.js program to complete.
Only once a response is
received from the DBMS will Node.js enter the function.
This is vulnerable to an SQL injection attack: One could
enter a login ID such as “'; DROP TABLE users --'”
to lead the program to execute some unintended SQL query — in
this example, to delete an entire table from the database.
Rather than build up a query using the JavaScript addition operator, one should tell the database module to insert the user-supplied value into the query. The database module will insert the appropriate escape characters so that special characters such as the quote will be treated just like quotes. The following code illustrates how this can be accomplished using the SQLite module.
db.all("SELECT passwd FROM users WHERE login = ?", [req.body.login], // rest omitted
a b → d
a → c
c d → e
subjects(name)
articles(language, title, text, author_login)
authors(login, name, institution)
article_subjects(language, title, subject_name)
a. The DBMS will store the table ordered by the value in the
id column, to facilitate quick lookup of a row by the
value in id.
b. All rows must have distinct values for the id column,
and the value can never be NULL. [By the way, SQLite
has a documented bug in that it allows NULL for primary
key columns, contrary to the SQL standard.]
INSERT INTO users (id, login, age, email)
VALUES (20500, 'barack', 52, NULL)
SELECT email
FROM users
WHERE age >= 18
GROUP BY email
HAVING COUNT(*) > 2
Since email = NULL is never true in SQL, even
when email is NULL, this query's result will
always be empty. We can repair the query by using
“email IS NULL”.