Test 1: Questions
[6 pts] Distinguish the terms database and database management system (DBMS).
[8 pts] Given the below two tables, what is the result of the following query?
invoicestableidname431 Alyssa 805 Ben 865 Alyssa
orderstableidproductprice431 cap 22.00 431 sweatshirt 40.00 805 T-shirt 17.00 805 cap 22.00 865 shorts 35.00
SELECT name, SUM(price)
FROM invoices JOIN orders ON invoices.id = orders.id
GROUP BY name
[12 pts] Suppose we have two relations for a Web site handling customer orders, where each order has just one item on it.
customers(custid, name)
orders(custid, product, price)
a. Write an SQL query that lists each product with the total amount spent on that product (across all orders).
b. Write an SQL query that lists each customer's name and the total amount ordered by that customer; if the customer has never ordered anything, the total displayed should be 0.
[10 pts] HTML distinguishes block and inline elements.
a. Define the distinction; that is, how do block and inline elements behave differently in a page?
b. Identify the elements below that are typically inline.
a(anchor — link to other page) b(boldface text) div(generic element) h1(level-1 header) p(paragraph) span(generic element)
[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 the text “Point 3” blue.
b. Display “Point 1” and “Point 2”
only in italics (use font-shape: italic).
[12 pts] Suppose we have the following HTML.
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>To do list</title></head><body>
<form id="addnew"><input id="newitem" /><button type="submit">Add</button></form>
<ul id="items"></ul>
<script src="jquery.js"></script>
<script src="todo.js"></script>
</body></html>
What JavaScript code should be in todo.js so that whenever the
user clicks the Add button, whatever text is currently in the text field is
added into a new li element at the end of the ul element?
$(document).ready(function () {
[8 pts]
Like Python, JavaScript allows you to simply start using variables
that have not previously been declared (with the var keyword).
This is fine in Python, but it should be carefully avoided in JavaScript.
How does JavaScript treat such variables, so that undeclared variables
should be avoided?
[8 pts] HTTP defines two methods called GET and POST for sending user input into a Web page form to the server. Explain how these two methods are different in how the browser encodes the information sent to the server.
[8 pts] Explain how AJAX-based sites differ from classical Web sites.
[8 pts] For the relation forecasts(city, date, temperature) providing weather forecasts for various city-date combinations, somebody suggests that the FD date temperature → city Give a plausible example of data for this table that would be inconsistent with this FD.
[12 pts] The relation reservations(roomnum, custid, name, cardnum, arrival_date, nights, smoking) is proposed to store reservations at a hotel. Explain why this design may not be in BCNF, including a description of a violating FD, the business logic underlying why the FD is realistic, and a complete explanation of why the FD violates BCNF (that goes beyond rehashing the BCNF definition).
Test 1: Solutions
A database management system like MySQL, Oracle, or SQLite is a piece of software written for managing databases. The database is an individual project built within a DBMS, containing a group of related data. (This distinction is like the distinction between a word processor and a text document.)
| Alyssa | 97.00 |
| Ben | 39.00 |
| a. | SELECT product, SUM(price) |
| b. | SELECT name, SUM(price) |
a. A block element has its own bounding box on the page, which is typically disjoint from the bounding boxes of other block elements on the page (or a sub-box, in the case of ancestor elements). By contrast, An inline element appears as part of running text; the element may wrap around lines, so its boundary is not defined by a bounding box.
b. <a>, <b>, and <span>
are typically inline elements.
a. li#point3 { color: blue }
b. ul.sub li.hilite { font-shape: italic }
$(document).ready(function() {
$('#addnew').submit(function (evnt) {
evnt.preventDefault();
var toAdd = $('#newitem').val();
$('#items').append($('<li>').text(toAdd));
});
});
Any variable that is used but not declared in a JavaScript program
is presumed to be a global variable. Though global variables are
problematic even when intentional, an unintentional
global variable can easily share a name with another
unintentional global variable: Suppose one function
loops over an inadvertently global variable i, and in the
loop it calls another function that loops over an inadvertently
global variable i, which would reset the i being
used in the parent function.
In sending form data using the GET method, all of the form data is incorporated into the URL request sent to the server, with a question mark separating the name of the file which should process the data from the actual data itself. This form data will be on the first line of the HTTP request sent to the server, followed by the auxiliary header information.
With the POST method, the browser sends the header first specifying only which file that should process the data; then it sends the form data as the body of the request, following the auxiliary header information.
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.
| city | date | temperature |
| Conway | Feb 18 | 60 |
| Las Vegas | Feb 18 | 60 |
There are a number of possible violating FDs here. One is roomnum → smoking, assuming that smoking represents whether smoking is allowed in that particular room. After all, hotels often designate each room permanently as a “smoking” room or a “non-smoking” room.
This FD violates BCNF since it is not trivial (smoking appears on the right but not the left) and since the left-hand side is not a superkey: One can easily imagine two different reservations in the table that share the same value for roomnum (but with different dates and customers).